DaniWeb Forum Index > Software Development > C#

MySQL insert from C# App.

Please support our C# advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Sep 2009

Posts: 30

Reputation: benkyma is an unknown quantity at this point

Solved Threads: 0

benkyma

Offline

Light Poster

MySQL insert from C# App.

Nov 4th, 2009

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
INSERT INTO entries(text, name, screen_name, user_id, posi_score)values(@txt, @nm, @scrNm, @uid, @posi_score)
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.

•

Join Date: Sep 2009

Posts: 30

Reputation: benkyma is an unknown quantity at this point

Solved Threads: 0

benkyma

Offline

Light Poster

Nov 4th, 2009

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...

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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);
                }
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...

•

Join Date: Jul 2009

Posts: 956

Reputation: DdoubleD is a jewel in the rough

Solved Threads: 199

DdoubleD

Offline

Posting Shark

Nov 5th, 2009

•

Originally Posted by 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...
Help with Code Tags

C# Syntax (Toggle Plain Text)
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);
                }
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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
                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);
                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.

Last edited by DdoubleD; Nov 5th, 2009 at 7:32 pm.