Solved

syntax error message.

Posted on 2003-12-09
10
404 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
  • 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

810 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