I'm having a problem getting my records to update in my SQL database. I can write the code to update fine in SQL Management Studio but when I put the information in Textboxes in my Visual Basic win form it runs fine but does not update. The code I am using is as follows:

    Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
        Dim Objconn As New SqlClient.SqlConnection("Data Source=PEEKSLAPTOP\SQLEXPRESS;Initial Catalog=gmcontainer;Integrated Security=True;Pooling=False")
        Objconn.Open()
        Dim strSQL As String

        Dim UpdateCmd As New SqlCommand(strSQL, Objconn)

        UpdateCmd = New SqlCommand("UPDATE ContainerInfo SET PriorLocation = @PriorLocation WHERE (ContainerName = @Original_ContainerName); SELECT ContainerName, CurrentLocation, VendorCode, DatePlaced, PriorLocation, PriorVendorCode, PriorDatePlaced FROM ContainerInfo WHERE (ContainerName = @ContainerName))", Objconn)

        UpdateCmd.Parameters.Add("@ContainerName", Data.SqlDbType.NVarChar).Value = comContainerNumber.Text
        UpdateCmd.Parameters.Add("@CurrentLocation", Data.SqlDbType.NVarChar).Value = comCurrentLocation.Text
        UpdateCmd.Parameters.Add("@VendorCode", Data.SqlDbType.NVarChar).Value = comVendorCode.Text
        UpdateCmd.Parameters.Add("@DatePlaced", Data.SqlDbType.NVarChar).Value = dateDatePlaced.Text
        UpdateCmd.Parameters.Add("@PriorLocation", Data.SqlDbType.NVarChar).Value = comPriorLocation.Text
        UpdateCmd.Parameters.Add("@PriorVendorCode", Data.SqlDbType.NVarChar).Value = comPriorVendorCode.Text
        UpdateCmd.Parameters.Add("@PriorDatePlaced", Data.SqlDbType.DateTime).Value = datePriorDatePlaced.Text

        Dim strContainerName As String = comContainerNumber.Text
        If strContainerName = "" Then
            MsgBox("Container Number cannot be empty")
            Exit Sub
        End If

        Objconn.Close()
        Me.comContainerNumber.Text = ""
        Me.comVendorCode.Text = ""
        Me.comCurrentLocation.Text = ""
        Me.dateDatePlaced.Text = ""
        Me.comPriorLocation.Text = ""
        Me.comPriorVendorCode.Text = ""
        Me.datePriorDatePlaced.Text = ""
        MsgBox("Container Number updated successfully")
    End Sub



Let me know if you guys can help. Thanks

I think I might need to have a stored procedure for this, I already made one for an update query, but still no avail.

Hi there..

Two weirds things I see on your code. You are actually creating an update command, but you are never executing it.. Also, you are sending a lot of parameters to an update query, but the query never use them...

And also, you are testing the value of container number, before you add it to the parameters collection, shouldn't that be done before that?

And yes, you can use an SP to do this.

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Oh thanks Gonzalo. I sent all those parameters but didnt declare them yet b/c I was testing.

Everytime I put

UpdateCmd.ExecuteNonQuery()

After my parameters I always crash the program... why I dont know.

And for testing the value of container number before I add it to the parameters, you are suggesting I just switch the code around?

Thanks

PK

Well, it will crash if you send all those parameters that doesn't exists...

Anyway, can you debug your app. and test what is failing?

you are receiving an error somewhere...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Thanks for the suggestions Gonzalo. For some reason the power went out to my office last night and this morning I ran my program and it magically worked...

LOL...