Solved

syntax error message.

Posted on 2003-12-09
10
402 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now