using windows form c# to add values to Mysql database

Question

I need to get the userid(primary key auto_increment) from another table(login) into userdetails table. When trying to run it I keep getting this error " incorrect integer value: 'LAST_INSERT_ID()' for column 'userid' at row 1".

I've tried to take LAST_INSERT_ID() out and run another query after query4 to insert the value into the userid but I can't get it to insert into the right row it just opens a new row.

this is the code am trying to run.

try
{
    //This is my connection string i have assigned the database file address path  
    string MyConnection2 = "datasource=localhost;port=3310;database=e-votingsystem;username=root;password=Password12;";
    //this is my insert query in which i am taking input from the user through windows forms                
    string Query2 = "INSERT INTO vote (username) VALUE ('" + usernameInputBox.Text + "');";
    string Query3 = "INSERT INTO login (username,upassword) VALUE ('" + usernameInputBox.Text + "','" + passwordInputBox.Text + "');";
    string Query4 = "INSERT INTO userdetails (nationalinsurance,userid,forename,middlename,surname,housenumber,street,towncity,postcode,suffix) VALUES ('" + nationalInsuranceInputBox.Text + "','"+"LAST_INSERT_ID()"+"','" + forenameInputBox.Text + "','" + middleNameInputBox.Text + "','" + surnameInputBox.Text + "','" + houseNumberInputBox.Text + "','" + streetTextBox.Text + "','" + towncityTextBox.Text + "','" + postcodeInputBox.Text + "','" + suffixComboBox.Text+"');";                    
    //This is  MySqlConnection here i have created the object and pass my connection string.  
    MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
    //This is command class which will handle the query and connection object.  
    MySqlCommand MyCommand2 = new MySqlCommand(Query2, MyConn2);
    MySqlCommand MyCommand3 = new MySqlCommand(Query3, MyConn2);                    
    MySqlCommand MyCommand4 = new MySqlCommand(Query4, MyConn2);


    MySqlDataReader MyReader2;
    MySqlDataReader MyReader3;                    
    MySqlDataReader MyReader4;


    // opens new connection to database then executes command
    MyConn2.Open();
    MyReader2 = MyCommand2.ExecuteReader();    // Here the query will be executed and data saved into the database.                    

    while (MyReader2.Read())
    {
    }
    MyConn2.Close();

    // opens new connection to database then executes command
    MyConn2.Open();
    MyReader3 = MyCommand3.ExecuteReader();

    while (MyReader3.Read())
    {
    }
    MyConn2.Close();                 


    //opens new connection to database the exexcutes command
    MyConn2.Open();
    MyReader4 = MyCommand4.ExecuteReader();

    while (MyReader4.Read())
    {
    }
    MyConn2.Close();

}
catch(Exception ex)
{
    MessageBox.Show(ex.Message);
}
MessageBox.Show("Hello " + forename + surname, "read and accept the terms and conditions to continue");

//new termsAndConditionsPage().Show();
//Hide();
}

LAST_INSERT_ID works returning the last id added to an autoincrement field using the same connection. From this code is not possible to understand if you are using the same connection context. Please add more code before this one. — Steve, Dec 13 '16 at 13:20
Would I be able to execute all the Queries together using one connection ? — Moto28, Dec 13 '16 at 13:45
Not only you can, but you should. The ExecuteReader is not the correct execution method to use to insert data. It works but its logic expects that you _read_ some data coming back from the database and an insert doesn't return data. — Steve, Dec 13 '16 at 13:58

score 0 · Answer 1 · edited Dec 13 '16 at 13:19

0

you current query has an error

string Query4 = "INSERT INTO userdetails (nationalinsurance,userid,forename,middlename,surname,housenumber,street,towncity,postcode,suffix) VALUE ('" + nationalInsuranceInputBox.Text + "','"+"LAST_INSERT_ID()"+"','" + forenameInputBox.Text + "','" + middleNameInputBox.Text + "','" + surnameInputBox.Text + "','" + houseNumberInputBox.Text + "','" + streetTextBox.Text + "','" + towncityTextBox.Text + "','" + postcodeInputBox.Text + "','" + suffixComboBox.Text + "');SELECT LAST_INSERT_ID();"

try the attached query

edited Dec 13 '16 at 13:19

Juan Carlos Oropeza

47,252
12
78
118

answered Dec 13 '16 at 13:19

Lucky Ncube

108
1
1
8

Can you explain why your 'fix' should help to insert the LAST_INSERT_ID for the userid field of the table userdetails? – Steve Dec 13 '16 at 13:23
Because of the function ;SELECT LAST_INSERT_ID(); that function retrieves the ID of the last inserted record – Lucky Ncube Dec 15 '18 at 11:13

score 0 · Answer 2 · answered Dec 13 '16 at 13:52

In your text query string you have: "','"+"LAST_INSERT_ID()"+"','". Note that the "','"s before and after the "LAST_INSERT_ID()" are incorrectly enclosing the LAST_INSERT_ID() term in single quotes.

Try the following query:

string Query4 = "INSERT INTO userdetails (nationalinsurance,userid,forename,middlename,surname,housenumber,street,towncity,postcode,suffix) VALUE ('" + nationalInsuranceInputBox.Text + "',"+"LAST_INSERT_ID()"+",'" + forenameInputBox.Text + "','" + middleNameInputBox.Text + "','" + surnameInputBox.Text + "','" + houseNumberInputBox.Text + "','" + streetTextBox.Text + "','" + towncityTextBox.Text + "','" + postcodeInputBox.Text + "','" + suffixComboBox.Text + "');";

The full query string has singleton quotes before and after the adjacent terms. So the string I gave correctly results in a ...'previousterm',LAST_INSERT_ID(),'nextterm'... So I believe you DO need the commas. And yes, he should protect himself from SQL injections. — Brian, Dec 13 '16 at 14:22

score 0 · Accepted Answer · edited May 23 '17 at 10:30

As explained in other answer, you have the LAST_INSERT_ID between single quotes and this transform it in a literal string not in a statement to execute. However also removing the quotes I am not sure that you can retrieve the LAST_INSERT_ID using a connection different from the one that originates the AUTOINCREMENT number on the login table. In any case you should use a different approach and, as a first thing, you should remove ASAP the string concatenations and use parameters (Reason: Sql Injection or SurName = O'Neill)

string Query2 = "INSERT INTO vote (username) VALUE (@uname)";
string Query3 = @"INSERT INTO login (username,upassword) VALUE (@uname, @upass); 
                  SELECT LAST_INSERT_ID();";
string Query4 = @"INSERT INTO userdetails 
                  (nationalinsurance,userid,forename,middlename,
                   surname,housenumber,street,towncity,postcode,suffix) 
                   VALUES (@insurance, @userid, @forename, @middlename,
                   @surname, @housenum, @street, @town, @postcode, @suffix)";

Now open just one connection and build three commands, all between an using statement

 using(MySqlConnection con = new MySqlConnection(.....constring here....))
 using(MySqlCommand cmd2 = new MySqlCommand(Query2, con))
 using(MySqlCommand cmd3 = new MySqlCommand(Query3, con))
 using(MySqlCommand cmd4 = new MySqlCommand(Query4, con))     
 {
      con.Open();

      // Add the parameter to the first command
      cmd2.Parameters.Add("@uname", MySqlDbType.VarChar).Value = usernameInputBox.Text;
      // run the first command
      cmd2.ExecuteNonQuery();

      // Add parameters to the second command
      cmd3.Parameters.Add("@uname", MySqlDbType.VarChar).Value = usernameInputBox.Text;
      cmd3.Parameters.Add("@upass", MySqlDbType.VarChar).Value =  passwordInputBox.Text;

     // Run the second command, but this one
     // contains two statement, the first inserts, the 
     // second returns the LAST_INSERT_ID on that table, we need to
     // catch that single return
     int userID = (int)cmd3.ExecuteScalar();

     // Run the third command 
     // but first prepare the parameters
     cmd4.Parameters.Add("@insurance", MySqlDbType.VarChar).Value = nationalInsuranceInputBox.Text;
     cmd4.Parameters.Add("@userid", MySqlDbType.Int32).Value = userID;
     .... and so on for all other parameters
     .... using the appropriate MySqlDbType for the column type
     cmd4.ExecuteNonQuery();
}

using windows form c# to add values to Mysql database

3 Answers3