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.
intox1221Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.