Can't get oleDbCommand to ExecuteScalar more than once

[Brad Wood]

While debugging a stored procedure in a test application, I was doing
this against an MS Access database in a button click event:

oleDbCommand1.Parameters[0].Value = textBox.Text;
object dude = oleDbCommand1.ExecuteScalar();
if ( dude == null )
{
MessageBox.Show("no result from stored proc");
}
else
{
txtAnother.Text = dude.ToString();
}

This works fine the first time it is called and any subsequent times as
long as the parameter passed in from the textBox doesn't change, but if
I change it to another string that should give me a valid result from
the stored procedure, the result of ExecuteScalar is null.
If I restart the application and use that new string from the beginning,
I get a valid result.
The connection remains open, and I'm not changing parameter collection
of the command object.

What the hell?

 

[Cowboy \(Gregory A. Beamer\)]

Try making a new command every time the button is clicked and adding the
parameter before firing. If this works, it means you have the command wired
up incorrectly and you need to alter more of the command with each click.

The most common problem I find with people reusing command objects is they
do not reset the object correctly. The second most common problem is they do
not debug through and ensure they are setting values correctly (Debug.Assert
is a wonderful addition to your arsenal in cases like this).

One more unrelated bit. It is a good idea to set up automated tests on each
of your methods. This is difficult when you wire the work into your form
class, as you have done. I would go with a model more like this:

object dude = DataClass.GetDude(textBox.Text);
if ( dude == null )
{
MessageBox.Show("no result from stored proc");
}
else
{
txtAnother.Text = dude.ToString();
}
....

public class DataClass //Do not use this name please
{
public static object GetDude(string textboxValue)
{
//Get data here
//NOTE: If you truly wish to persist the data connection, change
this
//object so you can instance it and hold the connection as static
}
}

The above method is testable.

I am not overly fond of keeping connections open, although there are
certainly cases where it is proper to do so (you see this more often in
desktop applications). If you are the only person using a database, and the
app will never scale, there is no big deal with keeping the conn open. When
you move to multiple concurrent users, keeping a connection open can help
with locking data to avoid having to handle concurrency errors, but I see
very few cases where the expense of the connection is worth having a lock
mechanism in place.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************

While debugging a stored procedure in a test application, I was doing this
against an MS Access database in a button click event:

oleDbCommand1.Parameters[0].Value = textBox.Text;
object dude = oleDbCommand1.ExecuteScalar();
if ( dude == null )
{
MessageBox.Show("no result from stored proc");
}
else
{
txtAnother.Text = dude.ToString();
}

This works fine the first time it is called and any subsequent times as
long as the parameter passed in from the textBox doesn't change, but if I
change it to another string that should give me a valid result from the
stored procedure, the result of ExecuteScalar is null.
If I restart the application and use that new string from the beginning, I
get a valid result.
The connection remains open, and I'm not changing parameter collection of
the command object.

What the hell?

 

[Brad Wood]

Try making a new command every time the button is clicked and adding the
parameter before firing. If this works, it means you have the command wired
up incorrectly and you need to alter more of the command with each click.

The most common problem I find with people reusing command objects is they
do not reset the object correctly. The second most common problem is they do
not debug through and ensure they are setting values correctly (Debug.Assert
is a wonderful addition to your arsenal in cases like this).

I tried this. I found that if I recreate the paramater object (but not
the command object), everything is fine. If I reset all the possible
values of the parameter by giving them the same values I give to the
constructor of the new parameter object, it still doesn't work. I
examine all the properties of the command object before each call to the
stored procedure and everything is the same except the actual value of
the parameter (as expected), but it still won't work unless I create a
new parameter object.

I am totally baffled.

The problem is that back in my application, I don't want to recreate a
command object before every call to the stored procedure. Although the
performance hit would be negligible, I don't want to recreate it every
time; I don't need a new parameter (I want to create it only once in a
base class).