Solved

error in my SQL syntax

Posted on 2011-03-16
2
679 Views
Last Modified: 2012-05-11
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
Comment
Question by:flynny
2 Comments
 
LVL 12

Accepted Solution

by:
enachemc earned 500 total points
ID: 35145787
@@IDENTITY is for MS SQL only
you are using mysql
0
 
LVL 17

Expert Comment

by:Shinesh Premrajan
ID: 35147037
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now