• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 692
  • Last Modified:

error in my SQL syntax

Hi Guys,

I am at a loss if i run the following statement in MySQLworkbench it works fine. However when I try using the ODBC connection in c# i get the following error;

ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.56-community]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 'SELECT @@IDENTITY' at line 1

the SQL i am using is very simple;

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

            String oQRY = String.Format("INSERT INTO `message` (fromID,subject,message,date,important) VALUES ({0},'{1}','{2}','{3}',{4}); SELECT @@IDENTITY AS `identity`;", userID, SubjectTextBox.Text, MessageMemo.Text, timestamp, ImportantCheckBox.Value);
            //Response.Write(oQry + "<BR>")
            OdbcCommand com = new OdbcCommand(oQRY, connection);
            com.ExecuteNonQuery();
            com.CommandText = "SELECT Last_Insert_ID()";
            int messageID = (int) com.ExecuteScalar();

the value of oQRY is

"INSERT INTO `message` (fromID,subject,message,date,important) VALUES (0,'test','test','2011-03-16 00:00:00',True); SELECT @@IDENTITY AS `identity`;"

any ideas whats going on here please?

Thanks in advance,

Matt.
0
flynny
Asked:
flynny
1 Solution
 
enachemcCommented:
@@IDENTITY is for MS SQL only
you are using mysql
0
 
Shinesh PremrajanTechnical ManagerCommented:
Replace with this query

            String oQRY = String.Format("INSERT INTO `message` (fromID,subject,message,date,important) VALUES (SELECT @@IDENTITY AS `identity`,'{0}','{1}','{2}',{3});", userID, SubjectTextBox.Text, MessageMemo.Text, timestamp, ImportantCheckBox.Value);

Hope this helps
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now