ExecuteScalar multiple transactions

▲
0
▼

I am trying to execute ExecuteScalar() with 2 instruction with ODBC connection to mysql in a query. My query is : 

string query = @"SET @MY_ID := (SELECT IFNULL(MAX(id) + 1, 1) FROM sc);" + Environment.NewLine + "INSERT INTO sc (id, name) values (@MY_ID, 'TESTE');"

I am getting:

{System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [MySQL][ODBC 5.1 Driver] [mysqld-5.0.83-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO

c#
executescalar
asked on Stack Overflow Feb 24, 2014 by user3346290 • edited Feb 24, 2014 by Abdur Rahim

2 Answers

▲
0
▼

You can't use SET to initialize a variable from a result set, you would need to actually execute the query 

 SELECT @MY_ID := ...
answered on Stack Overflow Feb 24, 2014 by James
▲
0
▼

Try This:

string query = "Declare @MY_ID int;Select @MY_ID  = IFNULL(MAX(id) + 1, 1) from sc;INSERT INTO sc (id, name) values (@MY_ID, 'TESTE');"
OleDBCommand command=new OleDbCommand(query,connectionObj);
int status;

status=command.ExecuteNonQuery();

if(status>0)
{
//success
}
answered on Stack Overflow Feb 24, 2014 by Sudhakar Tillapudi • edited Feb 24, 2014 by Sudhakar Tillapudi

User contributions licensed under CC BY-SA 3.0