Return value from OleDbCommand

c#, oledb, parameters, sql, sqlparameter

Solution

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.

Problem

Original source