MySQL/C# Insert Issue

Question

Not sure what is wrong with this

//Setup MySQL
 private MySqlConnection datconnection = new MySqlConnection();
 //private MySqlDataAdapter datdata = new MySqlDataAdapter();
 DataGrid datgridInfo = new DataGrid();
 int connect;
 private Form_NewEnquiry frm_parent;
 public Form_AddVenue(Form_NewEnquiry frm1)
 {
 InitializeComponent();
 frm_parent = frm1;
 }
 private void btnAdd_Click(object sender, EventArgs e)
 {
 connect = 0;
 try
 {
 datconnection.ConnectionString =
 "server=127.0.0.1;"
 + "database=as;"
 + "uid=a;"
 + "password=a;";
 connect = 1;
 }
 catch (Exception)
 {
 MessageBox.Show("Connection Unavailable");
 }
 //INSERT SQL Code to Insert Venue
 if (connect == 1)
 {
 try
 {
 datconnection.Open();
 MySqlCommand command = new MySqlCommand("INSERT INTO venues (venue_name,venue_address1,venue_address2,venue_town,venue_county,venue_postcode,venue_telephone,venue_fax,venue_email,venue_web,venue_maxguests) VALUES (?,?,?,?,?,?,?,?,?,?,?)", datconnection);
 command.Parameters.Add("name", MySqlDbType.VarChar, 45, tbName.Text.ToString());
 command.Parameters.Add("address1", MySqlDbType.VarChar, 45, tbAddress1.Text.ToString());
 command.Parameters.Add("address2", MySqlDbType.VarChar, 45, tbAddress2.Text.ToString());
 command.Parameters.Add("town", MySqlDbType.VarChar, 45, tbTown.Text.ToString());
 command.Parameters.Add("county", MySqlDbType.VarChar, 45, tbCounty.Text.ToString());
 command.Parameters.Add("postcode", MySqlDbType.VarChar, 10, tbPostcode.Text.ToString());
 command.Parameters.Add("telephone", MySqlDbType.VarChar, 15, tbTelephone.Text.ToString());
 command.Parameters.Add("fax", MySqlDbType.VarChar, 15, tbFax.Text.ToString());
 command.Parameters.Add("email", MySqlDbType.VarChar, 255, tbEmail.Text.ToString());
 command.Parameters.Add("web", MySqlDbType.VarChar, 255, tbWeb.Text.ToString());
 command.Parameters.Add("maxguests", MySqlDbType.Int32, 11, nudNoOfGuests.Value.ToString());
 command.ExecuteNonQuery();
 //datdata.InsertCommand = command;
 }
 catch (Exception eea)
 {
 MessageBox.Show("Error storing data");
 MessageBox.Show(eea.ToString());
 connect = 0;
 }
 finally
 {
 datconnection.Close();
 }
 }
 if (connect == 1)
 {
 MessageBox.Show("Data Saved");
 //Print
 //Close Window
 frm_parent.reloadVenue();
 this.Close();
 }
 }

This gives me the error System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

• Can Gencer: what line is throwing the exception?
• Matt Clements: At the command.ExecuteNonQuery();
• Matt Clements: I have fixed this by just coding the SQL string without command.Parameters.Add (instead the SQL string has the values concatenated. I know there is a risk of SQL Injection by doing so, however this is only a small internal system.

Answer 1

Seems like the size of some of the data is more than the size of the parameter you have specified. for example

command.Parameters.Add("name", MySqlDbType.VarChar, 45, tbName.Text);

is the size of the tbName.Text string less than 45. what about using

command.Parameters.Add("name", MySqlDbType.VarChar, tbName.Text.Length, tbName.Text);

• Matt Clements: Hello, Thanks for this - however when changing the same error is thrown. I have used the static lengths here as these are the MySQL length, therefore throwing a proper exception when the values are longer than MySQL will accept.

Answer 2

You don't need the ToString() on the Text property it already is a string please avoid it.

Also

command.Parameters.Add("maxguests", MySqlDbType.Int32, 11, nudNoOfGuests.Value.ToString())

This is expecting a int parameter and you are passing in a string, please use Convert.ToInt32 to pass in the value.

Normally these errors occur due to difference in the number of parameters declared and passed to the command but seems fine in your case. What type of control is nudNoOfGuests maybe that is causing the format error

• Matt Clements: Hey,Thanks for your help - however the .Value.ToString() has to be there as Parameters.Add always expects the 4th parameter to be a String. Changing this to command.Parameters.Add("maxguests", MySqlDbType.Int32, 11, 10); throws an "Argument 4 cannot convert from int to string" I do agree, however, that the ToString() isn't needed on a Text property - force of habbit
• V4Vendetta: So nudNoOfGuests is returning a valid value ?
• Matt Clements: Yes - However even with hard coding a string of an integer in this fails
• V4Vendetta: Can you try the same by changing the params as ?name,?address1,?address2 ... so on