Verify record insertion using ExecuteNonQuery

[webwiz082]

Hi,

I'm using parameters on a SQL stored procedure to insert a record with data entered on a website form (and on the form I'm using VB.net). As of right now, the form works perfectly; the record is inserted. However, after the user hits the submit button, everything executes and then the page will basically refresh with emtpy values in all the fields. So I want to give the user some type of SUCCESS message, assuring the user the information was successfully entered into the database. So my question is: How can I verify that the record insertion was successful? I want to do this with the ExecuteNonQuery statement, because that's what I'm using. Code is below:

If Page.IsValid then
Dim MyConn as SQLConnection= New SQLConnection("Blah")
Dim MySQL As SQLCommand
MySQL= New SqlCommand("proc_newWebEntry", MyConn)
MySQL.CommandType = CommandType.StoredProcedure
MySQL.Parameters.Add(New SQLParameter("@UID", UID.text))
MySQL.Parameters.Add(New SQLParameter("@FirstName", txtFirstName.text))
MySQL.Parameters.Add(New SQLParameter("@LastName", txtLastName.text))

MyConn.Open()
MySQL.ExecuteNonQuery
MyConn.Close()

'Clear fields after record insertion
UID.text = ""
txtFirstName.text = ""
txtLastName.text = ""

End If


Thanks in advance!
-Wes

[kleinma]

executenonquery returns an integer to represent the number of rows affected by the SQL Statement. In your case, it should only ever return 1 (if success) or 0 (if the insert failed, which would actually likely just throw an exception anyway)

So assign the return value of the executenonquery method to an integer variable, and then you can use it however you need.

[webwiz082]

executenonquery returns an integer to represent the number of rows affected by the SQL Statement. In your case, it should only ever return 1 (if success) or 0 (if the insert failed, which would actually likely just throw an exception anyway)

So assign the return value of the executenonquery method to an integer variable, and then you can use it however you need.

Thanks for the reply. I really hate to sound like a dumbass, but is this how I would do that:

Dim recordsAffected As Integer

MyConn.Open()
MySQL.ExecuteNonQuery
recordsAffected = MySQL.ExecuteNonQuery
MyConn.Close()

If recordsAffected = 0 Then
lblSubmission.text = "Record insertion not successful! Please see Wes to remedy this situation."
Else
lblSubmission.text = "Record insertion successful!"
End If

The reason I ask is because it's not exactly working. The recordsAffected variable is NEVER EQUALS ZERO, even though I have IF NOT EXISTS code in my stored procedure which prevents the same values being entered twice.

So, in other words, if I enter the same information in each field and press the submit button, the record is never entered. However, that label still says "Record insertion successful!" Please advise.

[kleinma]

yes, however it could be simplified further to

MyConn.Open()
If MySQL.ExecuteNonQuery = 0 Then
    lblSubmission.text = "Record insertion not successful! Please see Wes to remedy this situation."
Else 
    lblSubmission.text = "Record insertion successful!"
End If
MyConn.Close()
MyConn.Dispose()

if all you do based on the value of the queryresult, is set a labels text. No need to make the variable then.

Also I would recommend you use try/catch/finally code blocks to capture any errors that may occur. You never know when the routine could crash because of something like the database happens to be down (or any number of unforseen issues that could arise)

using try/catch/finally blocks will put structured error handling in your app, and never leave a user with a nasty weird error message when something you didn't plan for goes wrong.

[webwiz082]

The value of MySQL.ExecuteNonQuery is ALWAYS negative one. Please see code below:

lblSubmission.text = MySQL.ExecuteNonQuery

And the label keeps on showing "-1"

Please advise.

Also... as another potential solution, I attempted assigning @@ROWCOUNT to a variable in my stored procedure and passing it back as a Output parameter. But I kept having trouble with it on the web form. Kept on getting following error message:

Operator '=' is not defined for types 'System.Data.SqlClient.SqlParameter' and 'Integer'

If anyone could help me with this, it would be greatly appreciated also.

[kleinma]

I noticed in the code you posted for me to look at, you were calling executenonquery 2 times...

make sure you are only calling it once.

you had

MySQL.ExecuteNonQuery
recordsAffected = MySQL.ExecuteNonQuery

however after reading through the documentation, and your original code, it may always return -1 from the stored proc.

It says

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

so even though you are doing an insert, its via stored proc, and not via a SQL insert statement.

so you may have to look at using executescalar instead of executenonquery. It does the same thing, but returns a single value. You could have your stored proc return a value to indicate it has completed successfully

[webwiz082]

I'm going to look into it soon. Thanks for all your help and speedy replies, kleinma.

[okutbay]

please check your stored procedure and be sure not to turn on "no count"

if you have "SET NOCOUNT ON" the execute non query returns -1

[okutbay]

PS: sql server management studio add "SET NOCOUNT ON" to every new stored procedure.