C with MySQL INSERT parameters

Question

Good day to all  I m using Visual C  2010 and MySQL Version 5 1 48-community  I hope you can help me with this code  I don t find it working on me  What am I missing   string connString   ConfigurationManager ConnectionStrings  default   ConnectionString  MySqlConnection conn   new MySqlConnection connString   conn Open    MySqlCommand comm   conn CreateCommand    comm CommandText    INSERT INTO room person address  VALUES  person   address    comm Parameters Add   person    Myname    comm Parameters Add   address    Myaddress    comm ExecuteNonQuery    conn Close      And when I try to compile it  It says      Person column cannot be null   EDITED   But when I try this code    comm CommandText    INSERT INTO room person address  VALUES  Myname    Myaddress       But this code is prone to sql injection attack but it works  doesn t gives me an error    EDITED   I tried to use this  I found it here so I thought It would work but gives me this error     Index  zero based  must be greater than or equal to zero and less than   the size of the argument list    Any idea       string a    myname       MySqlCommand cmd   new MySqlCommand        cmd Connection   conn      cmd CommandText    INSERT INTO room person address  VALUES              cmd Prepare         cmd Parameters Add  person   MySqlDbType VarChar  Value   a      cmd Parameters Add  address   MySqlDbType VarChar  Value    myaddress       cmd ExecuteNonQuery       HERE I GOT AN EXCEPTION IN THIS LINE   Any help would be much appreciated   EDITED  SOLVED I used this code   cmd CommandText    INSERT INTO room person address  VALUES  person  address    cmd Parameters Add   person   MySqlDbType VarChar  Value    myname   cmd Parameters Add   address   MySqlDbType VarChar  Value    myaddress   cmd ExecuteNonQuery      Thanks SO

User · Answer

I had the same issue -- Finally tried the ? sigil instead of @, and it worked.

According to the docs:

Note. Prior versions of the provider used the '@' symbol to mark parameters in SQL. This is incompatible with MySQL user variables, so the provider now uses the '?' symbol to locate parameters in SQL. To support older code, you can set 'old syntax=yes' on your connection string. If you do this, please be aware that an exception will not be throw if you fail to define a parameter that you intended to use in your SQL.

Really? Why don't you just throw an exception if someone tries to use the so called old syntax? A few hours down the drain for a 20 line program...

MySQL::MySQLCommand

User · Answer

comm Parameters Add  person    Myname

User · Answer

try this it is working   MySqlCommand dbcmd    conn CreateCommand        dbcmd CommandText   sqlCommandString      dbcmd ExecuteNonQuery        long imageId   dbcmd LastInsertedId

User · Answer

Use the AddWithValue method   comm Parameters AddWithValue   person    Myname    comm Parameters AddWithValue   address    Myaddress

User · Answer

You may use AddWithValue method like   string connString   ConfigurationManager ConnectionStrings  default   ConnectionString  MySqlConnection conn   new MySqlConnection connString   conn Open    MySqlCommand comm   conn CreateCommand    comm CommandText    INSERT INTO room person address  VALUES  person   address    comm Parameters AddWithValue   person    Myname    comm Parameters AddWithValue   address    Myaddress    comm ExecuteNonQuery    conn Close      OR  Try with   instead of    like   string connString   ConfigurationManager ConnectionStrings  default   ConnectionString  MySqlConnection conn   new MySqlConnection connString   conn Open    MySqlCommand comm   conn CreateCommand    comm CommandText    INSERT INTO room person address  VALUES  person   address    comm Parameters Add   person    Myname    comm Parameters Add   address    Myaddress    comm ExecuteNonQuery    conn Close      Hope it helps

User · Answer

What I did is like this   String person  String address  person   your value  address  your value  string connString   ConfigurationManager ConnectionStrings  default   ConnectionString      MySqlConnection conn   new MySqlConnection connString       conn Open        MySqlCommand comm   conn CreateCommand        comm CommandText    INSERT INTO room person address  VALUES    person       address           comm ExecuteNonQuery        conn Close

User · Answer

I was facing very similar problem while trying to insert data using mysql-connector-net-5 1 7-noinstall and Visual Studio 2015  in Windows Form Application  I am not a C  guru  So  it takes around 2 hours to resolve everything    The following code works lately   string connetionString   null  connetionString    server localhost database device db uid root pwd 123     using  MySqlConnection cn   new MySqlConnection connetionString         try               string query    INSERT INTO test table user id  user name  VALUES   user id  user name             cn Open            using  MySqlCommand cmd   new MySqlCommand query  cn                         cmd Parameters Add   user id   MySqlDbType Int32  Value   123              cmd Parameters Add   user name   MySqlDbType VarChar  Value    Test username               cmd ExecuteNonQuery                        catch  MySqlException ex                MessageBox Show  Error in adding mysql row  Error    ex Message

User · Answer

Three things  use the using statement  use AddWithValue and prefix parameters with   and add Allow User Variables True to the connection string    string connString   ConfigurationManager ConnectionStrings  default   ConnectionString   using  var conn   new MySqlConnection connString            conn Open          var comm   conn CreateCommand          comm CommandText    INSERT INTO room person address  VALUES  person   address          comm Parameters AddWithValue   person    Myname          comm Parameters AddWithValue   address    Myaddress          comm ExecuteNonQuery          Also see http   msdn microsoft com en-us library system data sqlclient sqlcommand parameters aspx for more information about the command usage  and http   dev mysql com doc refman 5 1 en connector-net-connection-options html for information about the Allow User Variables option  only supported in version 5 2 2 and above

User · Answer

Try adjusting the code at  SqlDbType  to match your DB type if necessary and use this code   comm Parameters Add   person  SqlDbType VarChar  Value MyName    or   comm Parameters AddWithValue   person   Myname     That should work but remember with Command Parameters Add    you can define the specific SqlDbType and with Command Parameters AddWithValue    it will try get the SqlDbType based on parameter value implicitly which can break sometimes if it can not implicitly convert the datatype   Hope this helps

[c#] C# with MySQL INSERT parameters

Examples related to c#

Examples related to mysql

Examples related to database

Examples related to sql-insert