Monday, March 19, 2012

ExecuteNonQuery - Add working/Update not working

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@.UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@.FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@.Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try

When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...Before you do your executeNonQuery - - make sure (do a response.write or a Trace.Write) to make sure your USERID and textbox1.text values are actually populated.

Many times, if the update statement has a where clause, and it continues through, the WHERE arguments are not being fulfilled.|||Thanks for your reply...
I followed the code and the Id field is getting a value. I also added:


Dim NbrRows As Integer = cmd.ExecuteNonQuery()
Label1.Text = NbrRows

And I do receive a message that 1 row has been affected. However, the value I entered changes back to the original value and the record is not updated.

No comments:

Post a Comment