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.

For a start, please use the <code> </code> tags when posting code :) Alternatively, select the code and press Ctrl+K :) — Jude Cooray
– Jude Cooray, Commented Jul 8, 2010 at 18:12
@Ranhiru, thanks for showing me how it's done, just did an edit and corrected the formatting. — Sabuncu
– Sabuncu, Commented Jul 8, 2010 at 20:40

Jude Cooray · Accepted Answer · 2010-07-08 18:19:13Z

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);   
}

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related