I have a web app written in C#. I have created a .mdf file that is included in the project. The connection string that I use seems to work because I can open a connection to the .mdf file. I opened SSMS and attached this .mdf file so that I could enter data into the table (I don't have a UI where I can enter records yet). In my application, I write a SQL query to basically validate if a record was found. If I run the query in SSMS, it returns a single row, as it should. However, when I try and execute the same query in my app, record count is zero. This is actually the first time I've ever used a .mdf file so I'm not sure if I'm doing it correctly. I'm also new to this forum.
Here is my C# code.
public static string ValidateLogin(string userName, string passWord)
{
try
{
string sqlQuery = "SELECT Username FROM MyUsers WHERE Username = '" + userName + "' AND Password = '" + passWord + "'";
if (cnn.State == System.Data.ConnectionState.Open)
{
ds = new DataSet();
sqlcmd = new SqlCommand(sqlQuery, cnn);
sqlcmd.CommandType = System.Data.CommandType.Text;
sqladp.SelectCommand = sqlcmd;
sqladp.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dRow = ds.Tables[0].Rows[0];
string strValue = (string)dRow[0];
}
}
}
catch (Exception ex)
{
errorMsg = ex.Message;
MsgBox(ex.Message);
}
return errorMsg;
}
Thanks!
where is sqladp being defined at?
It's defined in the same class as the method.
I would run sql profiler from SSMS and run your code and see what the query looks like in the profiler?
Also have you checked to see if it is error-ing out or not?
I've never used the profile before so I need to read up on it. Let me ask you this...if I attach to the mdf from SQL Server and enter data in table and write Stored Procedures and then detattach from the mdf file...do all those changes remain? Like I said in my original post, I've never used a .mdf file before.
Thanks
yes the data changes you made should stay.
Ok, well at least I know that...thanks for that answer. It's weird when I run that query, it doesn't err out or anything but just returns a value of 0 for the rows count. I've tried it a couple different ways just to see what would happen.
try this to fake thing to see if it is something else
change
string sqlQuery = "SELECT Username FROM MyUsers WHERE Username = '" + userName + "' AND Password = '" + passWord + "'";
to
string sqlQuery = "SELECT 'yosiasz' as username union SELECT 'blakemckenna' ";
you might need to fix things up with the quotation mark issues above in the second example and see what happens. this will help eliminate if the issue is in your query itself or something else.
But I take the exact query and run it in SSMS and it works as it should...but I'll give it a shot!
I tried your test code and got the error "Specified cast is not valid".
where in the code is that happening
It throws the exception on the below line:
retValue = (int)sqlcmd.ExecuteScalar();
ok that rabbit hole did not work. reapply the original and test uaing sql profiler.
I'm still not quite sure how to use the profile. Do I run my application and then start the profile?
Thanks
How are you 'opening' the mdf file? Normally - you have an instance of SQL Server running on a server and the database (mdf/ldf) are attached to that instance and always available.
If you are using AttachDbFilename - you should review this article: https://blogs.sentryone.com/aaronbertrand/bad-habits-attachdbfilename/
Here is my connection string:
static string connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=MyFriends;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
It looks like you are connecting SSMS to the local default instance - but your connection string is directing you to the (localdb)\ProjectsV13 named instance.
Try changing the connection string to "Data Source=(local);Initial Catalag=..." and see if that connects you to the right instance. Or - in SSMS connect to the localdb named version.
One other note: your code is susceptible to SQL Injection attacks - you should never concatenate string values into a SQL statement like that.
1 Like
I finally figured out how to edit and add data to the .mdf tables from within VS. Now I'm getting a completely different error that has to do with not recognizing parameters. I should be to figure this one out.
Thanks for your suggestions.
I ended up just using my regular instance of SQL Server rather than an MDF file. It's all working now.
