maklot maklot - 2 months ago 9
MySQL Question

Error with Sql Parameters

private void btnadd_Click(object sender, EventArgs e)
{
try
{
conn.Open();
string sql = ("Insert into tbl_books values NameOfBook = @book, Author =@author, Publisher=@publisher,YearPublished=@year,Category=@category,ISBN=@isbn");
MySqlCommand sda = new MySqlCommand(sql,conn);
sda.Parameters.AddWithValue("@book", txtbook.Text);
sda.Parameters.AddWithValue("@author", txtauthor.Text);
sda.Parameters.AddWithValue("@publisher", txtpublisher.Text);
sda.Parameters.AddWithValue("@year", txtyear.Text);
sda.Parameters.AddWithValue("@category", cmbcategory.Text);
sda.Parameters.AddWithValue("@isbn", txtisbn.Text);
sda.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Item has been added");
showlv("Select * from tbl_books", lvbooks);
}
catch (Exception)
{
MessageBox.Show("Cannot Add Item");
}
}


What is wrong with the code? It keeps on going into the catch block.

Answer

Your SQL is messed up. Try:

  try
{
    conn.Open();
    string sql = "Insert into tbl_books (NameOfBook,Author,Publisher,YearPublished,Category,ISBN) values (@book,@author,@publisher,@year,@category,@isbn)";
    MySqlCommand sda = new MySqlCommand(sql,conn);
    sda.Parameters.AddWithValue("@book", txtbook.Text);
    sda.Parameters.AddWithValue("@author", txtauthor.Text);
    sda.Parameters.AddWithValue("@publisher", txtpublisher.Text);
    sda.Parameters.AddWithValue("@year", txtyear.Text);
    sda.Parameters.AddWithValue("@category", cmbcategory.Text);
    sda.Parameters.AddWithValue("@isbn", txtisbn.Text);
    sda.ExecuteNonQuery();
    conn.Close();
    MessageBox.Show("Item has been added");
    showlv("Select * from tbl_books", lvbooks);  
}

And THANK YOU for taking the time to learn about parameterization. In-line SQL is the ripest tool for hackers and the most embarrassing and easy-to-fix security hole there is!

NOTE: you may want to bring your conn into the TRY block and wrap it in a USING statement to save resources:

  using(SqlConnection conn = getMyConnection())
  {
     conn.Open();
     //blah
     conn.Close();
  }