Add procedure working/Update not working

[kristinac]

I have both an Add and an Update function and stored procedure. I am using the same code for both functions with the exception of the parameters (Update has 2 Add doesn't have). The Add function works fine, the Update doesn't work at all.

VB Code:
Private Sub Update()
Dim cmd As New SqlCommand("pContact_Update", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text
cmd.Parameters.Add("@MiddleName", SqlDbType.VarChar).Value = txtMiddleName.Text
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text
...etc more parameters... cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteScalar()

lblMessage.Text = "SUCCESS"
lblNoProfile.Visible = False
Catch ex As Exception
lblMessage.Text = ex.Message
End Try
End Sub

When I run the program, I receive the SUCCESS message, however the record is not in fact updated.

The add function looks the same but it actually adds a record. Could it be my stored procedures?

CREATE PROCEDURE [dbo].[pContact_Update]
@Id int,
@UserId int,
@FirstName varchar(20),
@MiddleName varchar(20),
@LastName varchar(20),
...etc
AS

UPDATE tContacts SET
UserId = @UserId, FirstName = @FirstName,
MiddleName = @MiddleName, LastName = @LastName,
BusinessName = @BusinessName,
...etc...
EMail = @EMail
WHERE Id = @Id
GO

I would appreciate any advice. I can not seem to find the difference, why one is working and the other is not...

Thanks!

 

[BG12424]

If your insert is not working when running this, that is because you cannot insert a new record with an update statement. You will need to create an insert statement within your stored procedure.


regards,
Brian

http://www.gainesconsulting.com

 

[kristinac]

Thanks for your reply Brian...
Actually I'm not trying to enter a new record with the update procedure. I have an Add procedure to do that. This one is just to Update an existing record. The Add procedure is working. The Update procedure is not...

 

[BG12424]

try this..

Dim val as string = cmd.ExecuteScalar().ToString
if val = "" then
do something
end if


regards,
Brian

http://www.gainesconsulting.com

 

[kristinac]

I've changed cmd.ExecuteScalar() to ExecuteNonQuery(). Didn't help though. I did the following 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]

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"

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

When I use the pContact_Add procedure this works fine. When I use the pContact_Update procedure, I receive the "done" message, but the record does not update.

 

[kristinac]

Okay I am trying your suggestion...I'm getting "Object reference not set to an instance of an object". Perhaps this is my problem?

 

[BG12424]

Have you put a breakpoint in your code and stepped through it?


regards,
Brian

http://www.gainesconsulting.com

 

[kristinac]

Yes I have. Everything goes how it should except for the fact that the record changes back to what it was before the update.

 

[kschumer]

kristinc

Have you found a solution to this yet? I am having the same problem.

 

[kristinac]

Yes it turned out it was because I did not say

If not IsPostBack

at the PageLoad event. When I added that before calling my procedure everything started working. Apparently the page was pulling the old information before the new information could be saved. (I probably didn't say that right. )