• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

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
        MsgBox("Entry updated for " & Me.txtMainFirst.Text & " " & Me.txtCompSur.Text)

Open in new window

  • 4
  • 4
1 Solution
Bob LearnedCommented:
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.

BozMAuthor Commented:
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 LearnedCommented:
For 2005, try this:

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

BozMAuthor Commented:
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 LearnedCommented:
There is a way to test:

Dim rowsAffected As Integer = myCommand.ExecuteNonQuery()

BozMAuthor Commented:
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?
Bob LearnedCommented:
The first place I would look is the WHERE clause:


BozMAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now