sqlQuery = "SELECT [ID] from [users] WHERE CallerName=@CallerName";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
cmd = new OleDbCommand(sqlQuery, conn);
cmd.CommandText = sqlQuery;
cmd.Parameters.Add("@CallerName", OleDbType.VarChar).Value = labelProblemDate.Text.Trim();
cmd.Parameters["@CallerName"].Value = name;
cmd.ExecuteNonQuery();
conn.Close();
I was told that this is how to read data from a SELECT query using Parameters but it's not working. I think I did something wrong.
I am using WinForms and Microsoft Access 2007
It looks like you have your answer, but I wanted to point out a few things from your example code:
sqlQuery = "SELECT [ID] from [users] WHERE CallerName=@CallerName";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
cmd = new OleDbCommand(sqlQuery, conn);
cmd.CommandText = sqlQuery;
cmd.Parameters.Add("@CallerName", OleDbType.VarChar).Value = labelProblemDate.Text.Trim();
cmd.Parameters["@CallerName"].Value = name;
cmd.ExecuteNonQuery();
conn.Close();
First, note that your SQL Query is using Microsoft SQL syntax and that Microsoft Access prefers a slightly different syntax. Instead of wrapping your column names in square brackets, use the tilde mark:
sqlQuery = "SELECT `ID` from `users` WHERE `CallerName`=@CallerName";
Next, in your SQL Query, be aware that Microsoft Access does not accept named parameters. Your SQL text above using @CallerName
will execute with no problem, but all the OleDb object will see is this:
sqlQuery = "SELECT `ID` from `users` WHERE `CallerName`=?";
If, at some point later on, you decide to go with Stored Procedures instead of text SQL, remember to call Prepare() on your OleDbCommand
after adding your parameters and before executing the command.
If you have multiple parameters, ensure that you add these parameters to your OleDbCommand
in the same order that you called them in your SQL text. OleDb does not care what you name them, but you can use them for yourself, to aid you; it is NOT used in the query. @CallerName
will make no attempt to match up with anything in your SQL text.
Next, I wanted to look at your usage of the OleDbParameter
item. In the two lines below, you are adding one (1) parameter to your OleDbCommand
with the value labelProblemDate.Text.Trim() and in the very next line you are re-assigning that same parameter's value to a variable (that is unknown to us) called name
. It does no good for you to declare the parameter with one value then re-assign it to something else.
You could have used the modified snippet below and gotten the same results (remember to add the size field, as shown below and specified in your database):
cmd.Parameters.Add("@CallerName", OleDbType.VarChar, 255).Value = labelProblemDate.Text.Trim();
// cmd.Parameters["@CallerName"].Value = name;
Similarly, your OleDbCommand
is being created with your sqlQuery
parameter, so specifying your command's CommandText
property is unnecessary:
cmd = new OleDbCommand(sqlQuery, conn);
//cmd.CommandText = sqlQuery;
Finally, as others have said, if you want to query your data as your SQL statement suggest, you must read the data in as opposed to calling ExecuteNonQuery()
(notice it is called Non Query).
To sum it up, I have written it out here:
sqlQuery = "SELECT `ID` from `users` WHERE `CallerName`=?";
int result = 0;
OleDbConnection conn = new OleDbConnection(connectionString);
try {
conn.Open();
var cmd = new OleDbCommand(sqlQuery, conn);
//cmd.CommandText = sqlQuery; This command was specified by your initializer
cmd.Parameters.Add("?", OleDbType.VarChar, 255).Value = labelProblemDate.Text.Trim();
//cmd.Parameters["@CallerName"].Value = name; Possible bug here
using (OleDbDataReader reader = cmd.ExecuteReader())
{
if(reader.HasRows)
{
reader.Read();
result = reader.GetInt32(0);
}
}
} finally {
conn.Close();
}
return result;
Always put the Close in a finally
block in case your program throws an error for any reason. This prevents your application from crashing and leaving the file open. A using
clause, I have found, does not necessarily close a connection when it is done (like they are supposed to do).
I hope this helps. I'm refreshing my knowledge of OleDb
at the moment, and wanted to point out a few things.