How to check for errors when running DataAdapter/DataSet SQL query?

Question

This is how I update a table using DataAdapter and DataSet in VB using SQL Server:

sqlStmt = String.Format("INSERT INTO my_table (name, lastname) VALUES ('John', 'Doe')")
ds = New DataSet
da = New SqlDataAdapter(sqlStmt, My.Settings.ConnectionString)
da.Fill(ds)

I know that the Fill method does not make sense in case of an INSERT statement, but I am new to this technology and the above statement does the job and updates the table w/o problems. My question is this: If there was an error (say a duplicate key error) how would I know this in my application? Should I be putting the above code in a try/catch block?

Also, if there is a "proper" method for running INSERT statements using a DataAdapter/DataSet combination that does not use the Fill method, please indicate that as well.

Many thanks,

Todd

Answer 1

For update statements, you should use the SqlCommand object.

SqlCommand cmd = new SqlCommand( "INSERT INTO my_table (name, lastname) VALUES ('John', 'Doe')", My.Settings.ConnectionString);

cmd.ExectureNonQuery();

However it is recommended that you use parameterized SQL queries, if by any chance you are acquiring the data from the user to reduce the chance of SQL Injection attacks :)

SqlCommand cmd = new SqlCommand( "INSERT INTO my_table (name, lastname) VALUES (@FirstName, @LastName)", My.Settings.ConnectionString);

cmd.Parameters.Add("FirstName", SqlDbType.NVarChar);
cmd.Parameters.Add("LastName", SqlDbType.NVarChar);

cmd.Parameters[0].Value = txtFirstName.Text;
cmd.Parameters[1].Value = txtLastName.Text;

cmd.ExecuteNonQuery();

Answer to your other question:

Yes. If there was a primary key violation, a SQLException will be thrown. You can catch it using a try-catch block and show a message or do whatever appropriate.

try
{
  cmd.ExecuteNonQuery();
}
catch (Exception ex)
{      
  MessageBox.Show("Error! " + ex.Message);   
}