Return value from OleDbCommand

Question

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

Truss

What error messages are you getting? Also, you seem to be muddled with your @CallerName parameter. What is the variable "name" and also, ExecuteNonQuery won't perform a select. – Othaothe

Do you want all ID's with this name or just one? If the latter it would be better to SELECT TOP 1 ID FROM [users] WHERE CallerName=@CallerName. Then use ExecuteScalar(link inside) to retrieve only the one selected ID (if any). – Acriflavine

Answer 1

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.

Busch

I thought using statements should do the work of closing the connection... In what cases they don't do it? – Yseulta

That is what a using statement is supposed to do. I'm not sure why Access does not. Just guessing, I would say it is because Access databases are typically local and on the user's PC and the Access group decided there was no reason to close their database while the program was up and running. – Busch

Are you talking about connection pooling? if you do it is actually a good idea, since you don't have an overhead for opening connections, and you can consider the "closed" connections as effectively closed (ASP will close them for you after the server is closed). I think it works like this with finally-connection.close() as well – Yseulta

I'm not sure if Access uses Connection Pooling. There is only 1 database, and it can only have a single connection open at any time. – Busch

Answer 2

ExecuteNonQuery doesn't return data, only the rows affected by your command
You need to use ExecuteReader with a OleDbDataReader

OleDbDataReader reader = cmd.ExecuteReader();           
if(reader.HasRows)
{
    reader.Read();
    var result = reader.GetInt32(0);
}

Thermophone

Answer 3

If a query returns one value, you can use ExecuteScalar to retrieve the value. ExecuteNonQuery doesn't return a value from your database; rather, it's intended for use with UPDATE statements and the like, and it returns the number of rows affected by the statement.

You probably know this, but in general, SELECT queries can return more than one row (and more than one column), so to "read data from a SELECT query", you use ExecuteReader to get a DbDataReader.

Kenna

Answer 4

To Read Data and Load it into DataTable:

OleDataReader rdr = (OleDataReader) cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(rdr);

To Read Scalor Value Data and Load it into Variable:

int result = (int)cmd.ExecuteScalar(); //Assume scalar value to be return is int

I hope it helps

Fukien