MySQL insert from C# App.

[benkyma]

I've spent a bit of time porting my database connection over to MySQL. It's taken a bit of time, but generally been fairly easy. However, one of my insert queries is currently failing due to an auto increment ID field. I have this insert query:

INSERT INTO entries(text, name, screen_name, user_id, posi_score)values(@txt, @nm, @scrNm, @uid, @posi_score)

This table also has an entry_id field that is auto Increment. The query runs fine when I use it in PHPMyAdmin, but when debugging my application, I get an exception telling me that entry_id cannot be null.

[benkyma]

Ok, I figured out that the error was actually triggered further down the code. It seems none of my parameters are being added into the query. I'm going to post the rest in case anyone can see what is wrong with it...

OdbcCommand myCommand = myConnection.getCommand("INSERT INTO entries(text, name, screen_name, user_id, posi_score)values(@txt, @nm, @scrNm, @uid, @posi_score)");
                myCommand.Parameters.AddWithValue("@txt", txt);
                myCommand.Parameters.AddWithValue("@nm", nm);
                myCommand.Parameters.AddWithValue("@scrNm", scrNm);
                myCommand.Parameters.AddWithValue("@uid", uid);
                myCommand.Parameters.AddWithValue("@posi_score", posiScore);
                myConnection.openConnection();
                try
                {
                    myConnection.executeNonQuery(myCommand);
                }
                catch (Exception e)
                {
                    Console.Write("Insert failed on entries "+e.Message);
                }

Is there another way to pass these parameters in for an Odbc connection? I worked fine with a straight SQL server connection...

[DdoubleD]

Ok, I figured out that the error was actually triggered further down the code. It seems none of my parameters are being added into the query. I'm going to post the rest in case anyone can see what is wrong with it...

OdbcCommand myCommand = myConnection.getCommand("INSERT INTO entries(text, name, screen_name, user_id, posi_score)values(@txt, @nm, @scrNm, @uid, @posi_score)");
                myCommand.Parameters.AddWithValue("@txt", txt);
                myCommand.Parameters.AddWithValue("@nm", nm);
                myCommand.Parameters.AddWithValue("@scrNm", scrNm);
                myCommand.Parameters.AddWithValue("@uid", uid);
                myCommand.Parameters.AddWithValue("@posi_score", posiScore);
                myConnection.openConnection();
                try
                {
                    myConnection.executeNonQuery(myCommand);
                }
                catch (Exception e)
                {
                    Console.Write("Insert failed on entries "+e.Message);
                }

Is there another way to pass these parameters in for an Odbc connection? I worked fine with a straight SQL server connection...

As I understand it, ODBC parameters are positional, not named. So, you would need to have something like this, making sure your parameters are added in the same order as they appear in your SQL statement:

OdbcCommand myCommand = 
                    new OdbcCommand("INSERT INTO entries(text, name, screen_name, user_id, posi_score)values(?,?,?,?,?)");
                myCommand.Parameters.AddWithValue("txt", txt);
                myCommand.Parameters.AddWithValue("nm", nm);
                myCommand.Parameters.AddWithValue("scrNm", scrNm);
                myCommand.Parameters.AddWithValue("uid", uid);
                myCommand.Parameters.AddWithValue("posi_score", posiScore);

EDIT: You may want to consider using a different library to connect to MySQL. I am using MySql.Data.dll, which you can find that and others available free on the internet.

[benkyma]

I moved this to the MySql library yesterday and it seems to work well. That's good to know though. Thanks