2 Replies - 174 Views - Last Post: 01 December 2011 - 08:10 AM Rate Topic: -----

#1 m_wylie85  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 96
  • View blog
  • Posts: 899
  • Joined: 15-October 10

saving to an access database

Posted 01 December 2011 - 04:15 AM

Hi all i am getting this error and i cant spot the mistake: No value given for one or more required parameters.

my code:

    Sub UpdateCustomer(ByVal HCNNO As Double, ByVal Forename As String, ByVal Surname As String, ByVal DOB As String, ByVal Gender As String, ByVal PasID As String, ByVal CaseNoteNumber As String, ByVal DisputedDataItem As String, ByVal CorrectData As String, ByVal DisputedDataConfirmedbyPatient As String, ByVal ConfirmedbyGP As String, ByVal SubmittedBy As String, ByVal DateSubmitted As String, ByVal DateForwardedToFPS As String, ByVal NHIASUpdated As String, ByVal CommentsFPS As String, ByVal DateReturnedToDQT As String, ByVal ActionedBy As String, ByVal SourceOfAction As String)

        OpenDatabaseConnction()

        Dim cm As New OleDb.OleDbCommand(" Update Details Set HCNNO = '" & HCNNO & "',Forename= '" & Forename & "',Surname= '" & Surname & "',DOB='" & DOB & "',Gender= '" & Gender & "',PasID='" & PasID & "',CaseNoteNumber='" & CaseNoteNumber & "',DisputedDataItem='" & DisputedDataItem & "',CorrectData='" & CorrectData & "',DisputedDataConfirmedByPatient='" & DisputedDataConfirmedbyPatient & "',ConfirmedbyGp='" & ConfirmedbyGP & "',SubmittedBy='" & SubmittedBy & "',DateSubmitted ='" & DateSubmitted & "',DateForwardToFPS='" & DateForwardedToFPS & "',NHAISUpdate='" & NHIASUpdated & "',CommentsFPS='" & CommentsFPS & "',dateRetuenTODQT='" & DateReturnedToDQT & "',ActionedBy='" & ActionedBy & "',SourceOfAction='" & SourceOfAction & "' WHERE HCNNO = " & HCNNO, DBCon)
            Try
                cm.ExecuteScalar()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                CloseDatabaseConnction()
            End Try
    End Sub


I get the error in the above try block.

This rest of the code:


        Dim ds As DataSet

        dataAccess.UpdateCustomer(frmShowAllDetails.HCNNO, txtForename.Text, txtSurname.Text, txtDOB.Text, txtGender.Text, txtPasID.Text, txtCaseNoteNumber.Text, txtDisputedDataItem.Text, txtCorrectData.Text, txtDisputedDataConfirmedByPatient.Text, txtConfirmedBYGP.Text, txtSubmittedBy.Text, txtDataSubmitted.Text, txtDateForwardedToFPS.Text, txtNHIASUpdated.Text, txtCommentsFPS.Text, txtDateReturnedToDQT.Text, txtActionedBy.Text, txtSourceOfAction.Text)

        MsgBox("Customer updates saved", MsgBoxStyle.Information)
        ds = dataAccess.ShowALLCustomerAccountDetails()
        Me.Close()
        frmShowAllDetails.Show()


Just to let you know HCNNO is set as a double in the code and in my database.

The problem it get me the error and does not save the changes to the database.

Is This A Good Question/Topic? 0
  • +

Replies To: saving to an access database

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: saving to an access database

Posted 01 December 2011 - 06:44 AM

to be easy to read and safer i suggest you use Parameterized queries
the error says that you dont set value to one or more elements in your update query
Was This Post Helpful? 1
  • +
  • -

#3 Tryparse  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 80
  • View blog
  • Posts: 193
  • Joined: 20-April 10

Re: saving to an access database

Posted 01 December 2011 - 08:10 AM

offers an excellent suggestion about parameters. They make for a lot more code, but it's easier to read, easier to maintain, and they help guard against SQL injection. That said, let's look at your statement.
Dim cm As New OleDb.OleDbCommand(" Update Details Set HCNNO = '" & HCNNO & "' ... WHERE HCNNO = " & HCNNO, DBCon)

So in the Set section, you've got the HCNNO value surrounded by ' ', but in the where clause you don't. You said it's set to a double in the the database, so you're trying to set a double value to a string. Try getting rid of the ' ' and see what happens.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1