Error in nextresult() and obdc

G

Guest

Hello,
I am try to update a row in a database (Basis 9.1) with this code:

OdbcConnection con= null;
OdbcTransaction tran = null;
try
{
con = new OdbcConnection("DSN=BASIS
administration;DBUPW=*****;UID=*****;DBUID=*****");
con.Open();
OdbcCommand command = new OdbcCommand("update NPAGA_NPAGA.T_USUA set
USU_MOD='01;02' where USU_ID='pppppppp' ",con);

OdbcDataReader a = command.ExecuteReader(CommandBehavior.SingleResult);
a.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);

}
finally
{
con.Close();
}
when i execute this code I get the next error:
ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this
function

stack:
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle,
SQL_HANDLE hType, RETCODE retcode)\r\n at
System.Data.Odbc.OdbcDataReader.NextResult()\r\n
at System.Data.Odbc.OdbcDataReader.FirstResult()\r\n
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method)\r\n at
System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)\r\n at
conbasis.Form1.Grabar_Click(Object sender, EventArgs e) in
d:\\alvaro\\conbasis\\form1.cs:line 173

the problem is in the NextResult().The Basis driver do not support the
nextresult command. The update work in the table but I receive the error. If
I want to work with transactions the update do not work (automatic rollback)
and it's no posible to do the update.
How can I do to eliminate the automatic nextresult?
If I change ExecuteReader and put executenonquery I get the same error.

Thanks
Alvaro
 
V

Val Mazur

Hi,

I believe you cannot use UPDATE statement as a part of ExecuteReader, base
purpose of ExecuteReader is to open forward-only, read-only cursor and
UPDATE does not return it. Even more, your next SET statement will not do it
as well, but rather will return scalar value. What you need to do is to
split your statement into two one - one for UPDATE and another one for SET.
Then execute first one using ExecuteNonQuery method of the command and use
ExecuteScalar method to get value from the SET
 
G

Grzegorz Danowski

U¿ytkownik "Val Mazur said:
Hi,

I believe you cannot use UPDATE statement as a part of ExecuteReader, base
purpose of ExecuteReader is to open forward-only, read-only cursor and
UPDATE does not return it. Even more, your next SET statement will not do
it as well, but rather will return scalar value. What you need to do is to
split your statement into two one - one for UPDATE and another one for
SET.
Click to expand...

Why? Do you think that it is possible to use update command without set
clause???
Then execute first one using ExecuteNonQuery method of the command and use
ExecuteScalar method to get value from the SET
Click to expand...

Alvaro has written that he had tried it:

(...)
But I would also try ExecuteNonQuery instead ExecuteReader.
Grzegorz
 
G

Guest

Hi,
I am using executeReader because in another thread I read that the param
CommandBehavior.SingleResult forces .net return a single result. The error
even return with executeNonquery and executescalar.
If I change the query and I put a select and fill a dataadapter the error
continues. The problem is the nextresult in the .net.

thanks
Alvaro

Val Mazur said:
Hi,

I believe you cannot use UPDATE statement as a part of ExecuteReader, base
purpose of ExecuteReader is to open forward-only, read-only cursor and
UPDATE does not return it. Even more, your next SET statement will not do it
as well, but rather will return scalar value. What you need to do is to
split your statement into two one - one for UPDATE and another one for SET.
Then execute first one using ExecuteNonQuery method of the command and use
ExecuteScalar method to get value from the SET

--
Val Mazur
Microsoft MVP


Alvaro said:
Hello,
I am try to update a row in a database (Basis 9.1) with this code:

OdbcConnection con= null;
OdbcTransaction tran = null;
try
{
con = new OdbcConnection("DSN=BASIS
administration;DBUPW=*****;UID=*****;DBUID=*****");
con.Open();
OdbcCommand command = new OdbcCommand("update NPAGA_NPAGA.T_USUA set
USU_MOD='01;02' where USU_ID='pppppppp' ",con);

OdbcDataReader a = command.ExecuteReader(CommandBehavior.SingleResult);
a.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);

}
finally
{
con.Close();
}
when i execute this code I get the next error:
ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support
this
function

stack:
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle,
SQL_HANDLE hType, RETCODE retcode)\r\n at
System.Data.Odbc.OdbcDataReader.NextResult()\r\n
at System.Data.Odbc.OdbcDataReader.FirstResult()\r\n
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method)\r\n at
System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)\r\n
at
conbasis.Form1.Grabar_Click(Object sender, EventArgs e) in
d:\\alvaro\\conbasis\\form1.cs:line 173

the problem is in the NextResult().The Basis driver do not support the
nextresult command. The update work in the table but I receive the error.
If
I want to work with transactions the update do not work (automatic
rollback)
and it's no posible to do the update.
How can I do to eliminate the automatic nextresult?
If I change ExecuteReader and put executenonquery I get the same error.

Thanks
Alvaro
Click to expand...
Click to expand...
 
V

Val Mazur

You are right. I probably was tired and for some reason decided that there
are two statements there - one for updating and another one for setting
variable to some value (definitely no reason for this).
In this case just one ExecuteNonQuery should work fine
 
Top