?
Solved

syntax error message.

Posted on 2003-12-09
10
Medium Priority
?
408 Views
Last Modified: 2008-02-01
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
Comment
Question by:intox1221
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 

Author Comment

by:intox1221
ID: 9907127
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
 
LVL 28

Expert Comment

by:AzraSound
ID: 9907137
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
 

Author Comment

by:intox1221
ID: 9907233
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.

 
LVL 28

Expert Comment

by:AzraSound
ID: 9907318
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
 
LVL 6

Expert Comment

by:Plamodo
ID: 9908591
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
 

Author Comment

by:intox1221
ID: 9908752
If i cut and paste ur code, its giving me blue squglies. By the way i am using .NET.
0
 

Author Comment

by:intox1221
ID: 9908762
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
 
LVL 28

Accepted Solution

by:
AzraSound earned 2000 total points
ID: 9908942
Remove the last comma before the WHERE portion, e.g.,

& "Zip=" & CInt(txtZip.Text) & " " _
0
 

Author Comment

by:intox1221
ID: 9909054
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
 
LVL 28

Expert Comment

by:AzraSound
ID: 9909198
Thanks!  Glad I could help...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question