.NET Error: FbCommand is currently busy Open,

Hi,

I'm getting this error: FbCommand is currently busy Open,
Fetching.
when I'm trying to run this code:

cnn.Open();
FbTransaction trans = cnn.BeginTransaction();
cmd = new FbCommand("SELECT USER_PASSWORD FROM USERS WHERE USER_NAME='" +
uid + "'",cnn,trans);
dr = cmd.ExecuteReader();
trans.Commit();
cnn.Close();

Anybody got an idea what i'm doing wrong?

Hello:

try closing the FbDataReader:

cnn.Open();
FbTransaction trans = cnn.BeginTransaction();
cmd = new FbCommand("SELECT USER_PASSWORD FROM USERS WHERE USER_NAME='" +
uid + "'",cnn,trans);
dr = cmd.ExecuteReader();
dr.Close(); <---------------
trans.Commit();
cnn.Close();



--
Best regards

Carlos GuzmánÁlvarez
Vigo-Spain

If i do this i'm getting:

FirebirdSql.Data.Firebird.Gds.GdsException: Exception of type FirebirdSql.Data.Firebird.Gds.GdsException was thrown.

as I'm trying to access the DataReader later

Hello:

It's working fine for me, here is my test code:

FbConnection connection = new FbConnection(connectionString);
connection.Open();

FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand("SELECT INT_FIELD FROM TEST_TABLE_01", connection, transaction);
FbDataReader dr = command.ExecuteReader();
dr.Close();

transaction.Commit();
connection.Close();


Which version of the .NET provider are you using ??

ya it works fine if you do it your way but with this piece here it gives me the error:

cnn.Open();
FbTransaction trans = cnn.BeginTransaction();
cmd = new FbCommand("SELECT USER_PASSWORD FROM USERS WHERE USER_NAME='" + uid + "'",cnn,trans);
dr = cmd.ExecuteReader();
trans.Commit();
while(dr.Read())
{
if (string.Compare(dr["USER_PASSWORD"].ToString(),passwd,false)==0)
{
cnn.Close();
return true;
}
}
dr.Close();

coming from a MySql background this .Commit() is confusing me?
What is it acutally doing?

Hello:

>ya it works fine if you do it your way but
>with this piece here it gives me the error:

Because you can't make a Commit with an open datareader, try this:

cnn.Open();
FbTransaction trans = cnn.BeginTransaction();
cmd = new FbCommand("SELECT USER_PASSWORD FROM USERS WHERE USER_NAME='" + uid + "'",cnn,trans);
dr = cmd.ExecuteReader();
while(dr.Read())
{
if (string.Compare(dr["USER_PASSWORD"].ToString(),passwd,false)==0)
{
cnn.Close();
return true;
}
}
trans.Commit();
dr.Close();

If you are using version 1.5 of the .NET Data Provider you can make this:

cnn.Open();
cmd = new FbCommand("SELECT USER_PASSWORD FROM USERS WHERE USER_NAME='" + uid + "'",cnn);
dr = cmd.ExecuteReader();
while(dr.Read())
{
if (string.Compare(dr["USER_PASSWORD"].ToString(),passwd,false)==0)
{
cnn.Close();
return true;
}
}
dr.Close();

i have the 1.5 b3

is there no .Commit() neccessary at your 2nd version?

This way I'm getting no error message anymore but it's not doing what it used to do with mysql


also you left out the trans in this cmd: cmd = new FbCommand("SELECT USER_PASSWORD FROM USERS WHERE USER_NAME='" + uid + "'",cnn);

on purpose or just an error? If I do it this way I get an error

Hah, I finally figured out the problem. My connection string when the variable is passed looks like:

SELECT user_password FROM users WHERE user_name='fname.lname@email.com"

There seems to be an error with the '@' because when i remove it it works. Any way around that?

>There seems to be an error with the '@' because when i remove it it works. Any way around that?


At this moment only using parameters, this is a problem with the named parameters support.

do you know of any workaround? because i'm using the login names from our email server which are stored in the same database and can't use anything else.

Hello:

As i tell before using parameters:

...

cmd = new FbCommand("SELECT USER_PASSWORD FROM USERS WHERE USER_NAME=@user_name",cnn,trans);

cmd.Parameters.Add("@user_name", FbDbType.VarChar).Value = uid;

....

or

...

cmd = new FbCommand("SELECT USER_PASSWORD FROM USERS WHERE USER_NAME=?",cnn,trans);

cmd.Parameters.Add("@user_name", FbDbType.VarChar).Value = uid;

....