asked on

Problems with VB.net UPDATE Statement and Parameterised query

Hi, I have the code below apparently working fine but it doesn't actually do anything. It's VB.net with an Access database. I'm just trying to UPDATE a row.
I'd really appreciate any suggestions on what I'm doing wrong - thanks.

Dim myOLEDBConnection As New OleDbConnection(My.Settings.ContactsDBConnectionString)
        Dim myCommand As New OleDbCommand("UPDATE tblCONTACTS SET strContactNotes = @strContactNotes, strCompSur = @strCompSur, strMiddleName = @strMiddleName , strFirstName= @strFirstName, strSalutation = @strSalutation , strSpouseSurname = @strSpouseSurname, strSpouseMiddleName = @strSpouseMiddleName , strSpouseFirstName = @strSpouseFirstName , strField1Name = @strField1Name , strField1Details= @strField1Details, strField2Name = @strField2Name, strField2Details = @strField2Details, strField3Name= @strField3Name, strField3Details = @strField3Details, strField4Name = @strField4Name, strField4Details= @strField4Details, strField5Name = @strField5Name, strField5Details= @strField5Details, strField6Name = @strField6Name, strField6Details= @strField6Details, strScribbleBox= @strScribbleBox, strAdd1= @strAdd1, strAdd2 = @strAdd2, strAdd3 = @strAdd3, strAdd4= @strAdd4, strAdd5 = @strAdd5, strAdd6 = @strAdd6, strPrompt1= @strPrompt1, strPrompt2 = @strPrompt2, strPrompt3 = @strPrompt3 WHERE strCONTACTID = @strCONTACTID ", myOLEDBConnection)
        
        With myCommand.Parameters
            .Add(New OleDbParameter("@strContactID", Me.txtContactID.Text))
            .Add(New OleDbParameter("@strcontactNotes", Me.txtContactNotes.Text))
            .Add(New OleDbParameter("@strCompSur", Me.txtCompSur.Text))
            .Add(New OleDbParameter("@strMiddleName", Me.txtMainMiddle.Text))
            .Add(New OleDbParameter("@strFirstName", Me.txtMainFirst.Text))
            .Add(New OleDbParameter("@strSalutation", Me.cmbMainSalutation.Text))
            .Add(New OleDbParameter("@strSpouseSurname", Me.txtSpoSur.Text))
            .Add(New OleDbParameter("@strSpouseMiddleName", Me.txtSpoMiddle.Text))
            .Add(New OleDbParameter("@strSpouseFirstName", Me.txtSpoFirst.Text))
            .Add(New OleDbParameter("@strField1Name", Me.txtField1Name.Text))
            .Add(New OleDbParameter("@strField1Details", Me.txtField1Details.Text))
            .Add(New OleDbParameter("@strField2Name", Me.txtField2Name.Text))
            .Add(New OleDbParameter("@strField2Details", Me.txtField2Details.Text))
            .Add(New OleDbParameter("@strField3Name", Me.txtField3Name.Text))
            .Add(New OleDbParameter("@strField3Details", Me.txtField3Details.Text))
            .Add(New OleDbParameter("@strField4Name", Me.txtField4Name.Text))
            .Add(New OleDbParameter("@strField4Details", Me.txtField4Details.Text))
            .Add(New OleDbParameter("@strField5Name", Me.txtField5Name.Text))
            .Add(New OleDbParameter("@strField5Details", Me.txtField5Details.Text))
            .Add(New OleDbParameter("@strField6Name", Me.txtField6Name.Text))
            .Add(New OleDbParameter("@strField6Details", Me.txtField6Details.Text))
            .Add(New OleDbParameter("@strScribbleBox", Me.txtRoughWork.Text))
            .Add(New OleDbParameter("@strAdd1", Me.txtAdd1.Text))
            .Add(New OleDbParameter("@strAdd2", Me.txtAdd2.Text))
            .Add(New OleDbParameter("@strAdd3", Me.txtAdd3.Text))
            .Add(New OleDbParameter("@strAdd4", Me.txtAdd4.Text))
            .Add(New OleDbParameter("@strAdd5", Me.txtAdd5.Text))
            .Add(New OleDbParameter("@strAdd6", Me.txtAdd6.Text))
            .Add(New OleDbParameter("@strPrompt1", Me.txtPrompt1.Text))
            .Add(New OleDbParameter("@strPrompt2", Me.txtPrompt2.Text))
            .Add(New OleDbParameter("@strPrompt3", Me.txtPrompt3.Text))
        End With
 
 
 
        myOLEDBConnection.Open()
 
        myCommand.ExecuteNonQuery()
        myOLEDBConnection.Close()
        MsgBox("Entry updated for " & Me.txtMainFirst.Text & " " & Me.txtCompSur.Text)

Open in new window

Bob Learned

You need to look at the overloaded form for the OleDbParameter constructor, and make sure that you have the form that sets a value. My recollection of 2003 is that you aren't actually setting any values for the parameter with the form shown.

Bob

BozM

ASKER

Hi Bob, Sorry I don't understand your comment - you're going to have to break down down a little for me. I'm still only a beginner in VB.
What's an overloaded form? This code is run from a button click event on a form. Not sure what the "overloading" you refer to is though.
The code is from VB.net 2005.

Bob Learned

For 2005, try this:

.AddWithValue("@strContactID", Me.txtContactID.Text)

Bob

BozM

ASKER

Ok so i changed all the lines in the "With mycommand.parameters" section of the code to the .addwithvalue format you suggested above but no joy - it didn't work . I don't really understand what is going on, as I have almost identical code for an INSERT statement that works fine. Is there an error in the SQL statement?

Bob Learned

There is a way to test:

Dim rowsAffected As Integer = myCommand.ExecuteNonQuery()

Bob

BozM

ASKER

OK Bob,
Thanks for that, now we're getting places. The number of rows affected is 0 so I guess my SQL is faulty. Any suggestions on that?
Boz.

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BozM

ASKER

Right, I've commented out the strContactID line in the "with mycommand.parameters" Section and I've typed in the name of the textbox where it should take the value of strContactID from. And that now it works perfectly. That's great - thanks for your (very patient) help with this.