When I try to execute a command using ODBC.net provider, it gives the
following error:

{"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near '('." }


Code:
OdbcConnection conn = new OdbcConnection("DSN=...");

conn.Open();


OdbcCommand cmd = new OdbcCommand("insert into tblUser (username,password)
values(?,?) set ?=SCOPE_IDENTITY()",conn);

OdbcParameter [] arParms = new OdbcParameter[3];



arParms[0] = new OdbcParameter("@username", OdbcType.VarChar );

arParms[0].Value = aUser.Username;

arParms[1] = new OdbcParameter("@password", OdbcType.VarChar );

arParms[1].Value = aUser.Password;


arParms[2] = new OdbcParameter("@id", OdbcType.Int );

arParms[2].Direction = ParameterDirection.Output;

cmd.Parameters.Add(arParms[0]);

cmd.Parameters.Add(arParms[1]);

cmd.Parameters.Add(arParms[2]);

cmd.ExecuteNonQuery();



and stack trace is



StackTrace " at System.Data.Odbc.OdbcConnection.HandleError(Handle Ref
hrHandle, SQL_HANDLE hType, RETCODE retcode)\r\n at
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior,
String method)\r\n at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()\r\n
at .....

How can I return an output value back using ODBC.net provider?

Thanks in advance.

Hi Karahan,

Please try to add a ';' at the end of the insert SQL statement. It has to
be "insert into tblUser (username,password)
values(?,?); set ?=SCOPE_IDENTITY()". In OdbcCommand requires the SQL
statements to be separated by ';'. Please also set the Size property of the
parameter. Because if we specify the parameter type, we have to set the
size, or some exception might be thrown by ADO .net.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Thanks for your quick reply but that didn't solve the problem
unfortunatelly. I got the same error message.

Here is the last version of the code:

OdbcCommand cmd = new OdbcCommand("insert into tblUser (username,password)
values(?,?); set ?=SCOPE_IDENTITY()",conn);

OdbcParameter [] arParms = new OdbcParameter[3];



arParms[0] = new OdbcParameter("@username", OdbcType.VarChar, 50 );

arParms[0].Value = aUser.Username;

arParms[1] = new OdbcParameter("@password", OdbcType.VarChar, 50 );

arParms[1].Value = aUser.Password;


arParms[2] = new OdbcParameter("@id", OdbcType.Int, 4 );

arParms[2].Direction = ParameterDirection.Output;

cmd.Parameters.Add(arParms[0]);

cmd.Parameters.Add(arParms[1]);

cmd.Parameters.Add(arParms[2]);

cmd.ExecuteNonQuery();


"Kevin Yu [MSFT]" <v-kevy (AT) online (DOT) microsoft.com> wrote

Hi Karahan,

It seems that we cannot use '?' as parameter in SET statement in ODBC .NET
provider. I think you can try the following code for substitution.

OdbcCommand cmd = new OdbcCommand("insert into tblUser (username,password)
values(?,?); SELECT SCOPE_IDENTITY()",conn);

OdbcParameter [] arParms = new OdbcParameter[2];
arParms[0] = new OdbcParameter("@username", OdbcType.VarChar, 50 );
arParms[0].Value = aUser.Username;
arParms[1] = new OdbcParameter("@password", OdbcType.VarChar, 50 );
arParms[1].Value = aUser.Password;

cmd.Parameters.Add(arParms[0]);
cmd.Parameters.Add(arParms[1]);
int id = (int)cmd.ExecuteScalar();

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Hi Kevin,
Yes, I know I can do that workaround but does it mean that it is not
possible to use output parameters with ODBC.NET?
If so why is it allowed to set the direction of a parameter to OUTPUT?

Thanks

"Kevin Yu [MSFT]" <v-kevy (AT) online (DOT) microsoft.com> wrote

Hi Karahan,

Output parameters are supported in ODBC .net provider. However, they cannot
be used in the SET statement. They are used to get the return value or
output parameter of a stored procedure or something like that. Here are
some KB articles for your reference:

http://support.microsoft.com/default...b;en-us;177736
http://support.microsoft.com/default...b;en-us;310130

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Hi Kevin,
Yes, I think you can only use output params with stored procedures.

Thanks.

"Kevin Yu [MSFT]" <v-kevy (AT) online (DOT) microsoft.com> wrote