[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

syntax error message.

In my application im trying to save a clients data into the the data base (access). But when i press the save button it gives me the following error, but does not halt the application.
Index #0
Message: syntax error (missing operator) in query expression "159 purfect street, city='orlando'.
Native: -524553244
source: Microsoft JEt database engine
SQL: 3075

Please help.
0
intox1221
Asked:
intox1221
  • 5
  • 4
1 Solution
 
intox1221Author Commented:
This is my pseudo for the save button which calles two procedures UpdateData() and SaveNewData().

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        '********************************************
        ' This event will disable and save the entry
        ' fields
        '********************************************

        'check that the data entry is valid
        If IsUserEntryValid() Then

            'disable the entry fields
            EnableControlEntry(False)

            'if user was editing data, save the updated data
            If lvwMailingList.SelectedIndices.Count > 0 Then

                'save the data to database
                If UpdateData() > 0 Then
                    'save data to listview
                    lvwMailingList.Items(lvwMailingList.SelectedIndices(0)).SubItems(0).Text = txtClient.Text 'NID
                    lvwMailingList.Items(lvwMailingList.SelectedIndices(0)).SubItems(1).Text = txtFirst.Text 'First Name
                    lvwMailingList.Items(lvwMailingList.SelectedIndices(0)).SubItems(2).Text = txtLast.Text 'Last Name
                    lvwMailingList.Items(lvwMailingList.SelectedIndices(0)).SubItems(3).Text = txtAddress.Text 'Exams
                    lvwMailingList.Items(lvwMailingList.SelectedIndices(0)).SubItems(4).Text = txtCity.Text
                    lvwMailingList.Items(lvwMailingList.SelectedIndices(0)).SubItems(5).Text = txtState.Text 'Projects
                    lvwMailingList.Items(lvwMailingList.SelectedIndices(0)).SubItems(6).Text = txtZip.Text '# of Projects
                    'lvwMailingList.Items(lvwMailingList.SelectedIndices(0)).SubItems(7).Text = txtAssgnScore.Text 'Assignments
                    'lvwMailingList.Items(lvwMailingList.SelectedIndices(0)).SubItems(8).Text = CStr(nudAssgn.Value) '# of Assignments
                End If 'updating data

            Else  'otherwise, the user was adding

                'save new data to database
                If SaveNewData() > 0 Then
                    'add new data to listview
                    lvwMailingList.Items.Add(txtClient.Text) 'NID
                    lvwMailingList.Items(lvwMailingList.Items.Count - 1).SubItems.Add(txtFirst.Text) 'First Name
                    lvwMailingList.Items(lvwMailingList.Items.Count - 1).SubItems.Add(txtLast.Text) 'Last Name
                    lvwMailingList.Items(lvwMailingList.Items.Count - 1).SubItems.Add(txtAddress.Text) 'Exams
                    lvwMailingList.Items(lvwMailingList.Items.Count - 1).SubItems.Add(txtCity.Text) 'Projects
                    lvwMailingList.Items(lvwMailingList.Items.Count - 1).SubItems.Add(txtState.Text) 'Assignments
                    lvwMailingList.Items(lvwMailingList.Items.Count - 1).SubItems.Add(txtZip.Text) 'Assignments

                End If 'saving new data

            End If 'edit or new

        End If 'validate
End sub
0
 
AzraSoundCommented:
If it contains single quotes and you are doing updating/inserting with an SQL statement, you need to replace single quotes with two single quotes, usually accomplished using a simple function like:


strOriginal = Replace(strOriginal, "'", "''")

So, for example, 'orlando' would become ''orlando'' before submitting your SQL statement.
0
 
intox1221Author Commented:
Azra i need a little more help with this. here are my updatedata() and savenewdata() procedures.

*****
updatedata procedure
*****
    Private Function UpdateData() As Integer
        '****************************************************
        ' This procedure updates values into the db
        ' and returns the number of records affected (e.g., 1)
        '****************************************************

        Dim strQuery As String

        'set up update query
        strQuery = "UPDATE tblClient SET " _
                 & "ClientID=" & CInt(txtClient.Text) & ", " _
                 & "FirstName=" & "'" & txtFirst.Text & "'" & ", " _
                 & "LastName=" & "'" & txtLast.Text & "'" & ", " _
                 & "Street=" & "'" & txtAddress.Text & ", " _
                 & "City=" & "'" & txtCity.Text & ", " _
                 & "State=" & "'" & txtState.Text & ", " _
                 & "Zip=" & CInt(txtZip.Text)

        'run update query
        Return DBTier.RunNonQuery(cstrConnection, strQuery)

    End Function

****
savenewdata procedure
****
Private Function SaveNewData() As Integer
        '****************************************************
        ' This procedure inserts the new values into the db
        ' and returns the number of records affected (e.g., 1)
        '****************************************************

        Dim strQuery As String

        'set up insert query
        strQuery = "INSERT INTO tblClient " _
                 & "(ClientID, FirstName, LastName, Street, City, State, Zip) " _
                 & "VALUES (" _
                 & CInt(txtClient.Text) & ", " _
                 & "'" & txtFirst.Text & "'" & ", " _
                 & "'" & txtLast.Text & "'" & ", " _
                 & "'" & txtAddress.Text & ", " _
                 & "'" & txtCity.Text & ", " _
                 & "'" & txtState.Text & ", " _
                 & CInt(txtZip.Text) & ")"


        'run insert query
        Return DBTier.RunNonQuery(cstrConnection, strQuery)

    End Function

where am i going wrong with this ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AzraSoundCommented:
The only issue with what you have now is if someone enters any information with a single quote in it.  For example, for last name they type in:

O'Hara

Well, that would cause an error in your function as is stands.  You need to ensure you double up on those single quotes so it actually goes through as O''Hara.  I usually wrote a generic function and call it where necessary, e.g.,


Public Function FixQuotes(ByVal OriginalString As String) As String
    FixQuotes = Replace$(OriginalString, "'", "''")
End Function


You would use it like:


& "'" & FixQuotes(txtLast.Text) & "'" & ", " _
0
 
PlamodoCommented:
I notice that for half of your fields didn't have the second "'"   ..  But what I think is even better is using chr$(34)  ..

I set up my string queries like this:

Private Function UpdateData() As Integer
        '****************************************************
        ' This procedure updates values into the db
        ' and returns the number of records affected (e.g., 1)
        '****************************************************

        Dim strQuery As String

        'set up update query

         strQuery = "UPDATE tblClient SET " _
                 & "ClientID=" & CInt(txtClient.Text) & ", " _
                 & "FirstName=" & chr$(34) & txtFirst.Text & chr$(34) & ", " _
                 & "LastName=" & chr$(34) & txtLast.Text & chr$(34) & ", " _
                 & "Street=" & chr$(34) & txtAddress.Text & chr$(34) & ", " _
                 & "City=" & chr$(34) & txtCity.Text & chr$(34) & ", " _
                 & "State=" & chr$(34) & txtState.Text & chr$(34) & ", " _
                 & "Zip=" & CInt(txtZip.Text)
'run update query
        Return DBTier.RunNonQuery(cstrConnection, strQuery)

    End Function

Private Function SaveNewData() As Integer
        '****************************************************
        ' This procedure inserts the new values into the db
        ' and returns the number of records affected (e.g., 1)
        '****************************************************

        Dim strQuery As String

        'set up insert query
        strQuery = "INSERT INTO tblClient " _
                 & "(ClientID, FirstName, LastName, Street, City, State, Zip) " _
                 & "VALUES (" _
                 & CInt(txtClient.Text) & ", " _
                 & chr$(34) & txtFirst.Text & chr$(34) & ", " _
                 & chr$(34) & txtLast.Text & chr$(34) & ", " _
                 & chr$(34) & txtAddress.Text & chr$(34) & ", " _
                 & chr$(34) & txtCity.Text & chr$(34) & ", " _
                 & chr$(34) & txtState.Text & chr$(34) & ", " _
                 & CInt(txtZip.Text) & ")"


        'run insert query
        Return DBTier.RunNonQuery(cstrConnection, strQuery)

    End Function
0
 
intox1221Author Commented:
If i cut and paste ur code, its giving me blue squglies. By the way i am using .NET.
0
 
intox1221Author Commented:
By the i got my savenewdata to work. And heres what i have on updatedata... but theres still something wrong with it... if u can figure it out...

   Private Function UpdateData() As Integer
        '****************************************************
        ' This procedure updates values into the db
        ' and returns the number of records affected (e.g., 1)
        '****************************************************

        Dim strQuery As String






        'set up update query
        strQuery = "UPDATE tblClient SET " _
                 & "ClientID=" & CInt(txtClient.Text) & ", " _
                 & "FirstName=" & "'" & txtFirst.Text & "'" & ", " _
                 & "LastName=" & "'" & txtLast.Text & "'" & ", " _
                 & "Street=" & "'" & txtAddress.Text & "'" & ", " _
                 & "City=" & "'" & txtCity.Text & "'" & ", " _
                 & "State=" & "'" & txtState.Text & "'" & ", " _
                 & "Zip=" & CInt(txtZip.Text) & ", " _
                 & "WHERE ClientID =" & CInt(txtClient.Tag)

        Return DBTier.RunNonQuery(cstrConnection, strQuery)
0
 
AzraSoundCommented:
Remove the last comma before the WHERE portion, e.g.,

& "Zip=" & CInt(txtZip.Text) & " " _
0
 
intox1221Author Commented:
That kept me for the better part of my day in front of the damn computer....  ur a lifesaver Azra... thats exactly what it was.... Kudos and hats off to you... full points...
0
 
AzraSoundCommented:
Thanks!  Glad I could help...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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