Checking if INSERT INTO statement was successful

Question

I am using a MS Access database as the backend of my VB.NET application. I am entering users details into the database using an INSERT INTO statement:

cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & config("DatabasePath") & ";")
cn.Open()
cmd = New OleDbCommand("INSERT INTO blah blah blah...", cn)
dr = cmd.ExecuteReader

Everything works, but I wanted to check if the data has actually been entered into the database. I have tried using:

cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & config("DatabasePath") & ";")
cn.Open()
cmd = New OleDbCommand("INSERT INTO blah blah blah...", cn)
dr = cmd.ExecuteReader
If dr.Read() Then
    ' Blah
End If

but obviously the insert statement doesn't return anything so this doesn't work. Any suggestions?

Answer 1

Can you run a quick SELECT COUNT(*) FROM blah blah blah using the same key criteria as you used for the insert ?

Answer 2

If all you have is the INSERT statement you can use the ExecuteNonQuery() method which returns how many rows were affected.

Like this:

cmd = New OleDbCommand("INSERT INTO blah blah...", cn)
rowCount = cmd.ExecuteNonQuery()
If rowCount < 1 Then 
    ' Blah

You have to excuse me if the VB isn't correct, I didn't test it, but I hope you get the idea.

Answer 3

Use the ExecuteNonQuery method, as the query doesn't return any result. The method returns an integer which is the number of rows affected.

Dim count As Integer = cmd.ExecuteNonQuery()