Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2013
    Posts
    11

    Unanswered: Syntax error (missing operator) in query expression

    Hi guys,

    I am new here, i need your help for my vb project. My update (sql statement is not working and it is showing an error.

    Please find below my code.

    Else
    sID = -1
    sID = con.BeginTrans
    con.Execute "UPDATE tblAddress SET CompanyName = '" & Trim$(APOSTROPHY(txtCompany.Text)) & "', FirstName = '" & Trim$(APOSTROPHY(txtFirst.Text)) & "', LastName = '" & Trim$(APOSTROPHY(txtLast.Text)) & "', [Position] = '" & Trim$(APOSTROPHY(txtPosition.Text)) & "', [Category] = '" & Trim$(APOSTROPHY(cboCategory.Text)) & "', [Country] = '" & Trim$(APOSTROPHY(txtCountry.Text)) & "', POBox = '" & Trim$(APOSTROPHY(txtPbox.Text)) & "', City = '" & Trim$(APOSTROPHY(txtCity.Text)) & "', State = '" & Trim$(APOSTROPHY(txtState.Text)) & "', Address = '" & Trim$(APOSTROPHY(txtAddress.Text)) & "', Telephone = '" & Trim$(APOSTROPHY(txtTelephone.Text)) & "', Fax = '" & Trim$(APOSTROPHY(txtFax.Text)) & "', Mobile = '" & Trim$(APOSTROPHY(txtPhone.Text)) & "', Direct = '" & Trim$(APOSTROPHY(txtDirect.Text)) & "', Email = '" & Trim$(APOSTROPHY(txtMail.Text)) & "', Website = '" & Trim$(APOSTROPHY(txtSite.Text)) & "', Comments = '" & Trim$(APOSTROPHY(txtComment.Text)) & "'" _
    & "' WHERE AddressID = '" & txtAccount.Text & "';"


    If sID > 0 Then
    con.CommitTrans
    sID = -1
    End If

    End If

    Your help will be highly appreciated.

    Thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a smarter move is always to assign the value of the SQL to a variable BEFORE using the SQL. that way round you cna see what the SQL you are sending actually is if you use the debug tools

    this sort of message indicates either an unclosed quote on string literals OR a missing comma or a missing key word.

    in this particular case its the extra apostrophe BEFORe the WHERE
    Code:
    ...Trim$(APOSTROPHY(txtComment.Text)) & "'" _
    & "' WHERE AddressID = '" & txtAccount.Text & "';"
    another technique is to put the SQL one line per term. the human eye is very good at pattern recognition and it can quickly spot errors on a simple line of text, but struggles with what effectivley it sees as a paragraph

    Code:
     dim strSQL as string
    ...
    strSQL = " CompanyName = '" & Trim$(APOSTROPHY(txtCompany.Text)) & "'," & _
    " FirstName = '" & Trim$(APOSTROPHY(txtFirst.Text)) & "',"
    " LastName = '" & Trim$(APOSTROPHY(txtLast.Text)) & "',"
    " [Position] = '" & Trim$(APOSTROPHY(txtPosition.Text)) & "'"
    "' WHERE AddressID = '" & txtAccount.Text & "';"
    con.Execute strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2013
    Posts
    11
    Hi Healdem,

    Thank you for your reply

    I have tried your first solution but it is giving me an error of "Data type mismatch in criteria expression"

  4. #4
    Join Date
    May 2013
    Posts
    11
    The secon option is giving me a read mark.

    Dim strSQL As String
    ...
    strSQL = " CompanyName = '" & Trim$(APOSTROPHY(txtCompany.Text)) & "'," & _
    " FirstName = '" & Trim$(APOSTROPHY(txtFirst.Text)) & "',"
    " LastName = '" & Trim$(APOSTROPHY(txtLast.Text)) & "',"
    " [Position] = '" & Trim$(APOSTROPHY(txtPosition.Text)) & "'"
    "' WHERE AddressID = '" & txtAccount.Text & "';"
    con.Execute strSQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    did you remove the apostrophe?
    can we see the sql you are actually sending to the SQL engine
    ie the value of strSQL not the VBA
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2013
    Posts
    11
    Please find below my entire code for add and update.

    Private Sub cmdSave_Click()
    On Error GoTo eh
    Dim sID As Integer

    rep = MsgBox("Save?", vbYesNo + vbQuestion)

    If rep = vbNo Then Exit Sub

    If txtAccount.Text = "<New>" Then
    sID = -1
    sID = con.BeginTrans
    con.Execute "INSERT INTO tblAddress(CompanyName,FirstName,LastName,[Position],[Category],Address,POBox,[Country],City,State,Direct,Telephone,Fax,Mobile,Email,Webs ite,Comments)" _
    & " VALUES ('" & Trim$(APOSTROPHY(txtCompany.Text)) & "', '" & Trim$(APOSTROPHY(txtFirst.Text)) & "', '" & Trim$(APOSTROPHY(txtLast.Text)) & "','" & Trim$(APOSTROPHY(txtPosition.Text)) & "', '" & Trim$(APOSTROPHY(cboCategory.Text)) & "', '" & Trim$(APOSTROPHY(txtAddress.Text)) & "', '" & Trim$(APOSTROPHY(txtPbox.Text)) & "','" & Trim$(APOSTROPHY(txtCountry.Text)) & "','" & Trim$(APOSTROPHY(txtCity.Text)) & "','" & Trim$(APOSTROPHY(txtState.Text)) & "','" & Trim$(APOSTROPHY(txtDirect.Text)) & "','" & Trim$(APOSTROPHY(txtPhone.Text)) & "','" & Trim$(APOSTROPHY(txtTelephone.Text)) & "','" & Trim$(APOSTROPHY(txtFax.Text)) & "','" & Trim$(APOSTROPHY(txtMail.Text)) & "','" & Trim$(APOSTROPHY(txtSite.Text)) & "','" & Trim$(APOSTROPHY(txtComment.Text)) & "')"

    If sID > 0 Then
    con.CommitTrans
    sID = -1
    End If

    Else
    sID = -1
    sID = con.BeginTrans
    con.Execute "UPDATE tblAddress SET CompanyName = '" & Trim$(APOSTROPHY(txtCompany.Text)) & "', FirstName = '" & Trim$(APOSTROPHY(txtFirst.Text)) & "', LastName = '" & Trim$(APOSTROPHY(txtLast.Text)) & "', [Position] = '" & Trim$(APOSTROPHY(txtPosition.Text)) & "', [Category] = '" & Trim$(APOSTROPHY(cboCategory.Text)) & "', [Country] = '" & Trim$(APOSTROPHY(txtCountry.Text)) & "', POBox = '" & Trim$(APOSTROPHY(txtPbox.Text)) & "', City = '" & Trim$(APOSTROPHY(txtCity.Text)) & "', State = '" & Trim$(APOSTROPHY(txtState.Text)) & "', Address = '" & Trim$(APOSTROPHY(txtAddress.Text)) & "', Telephone = '" & Trim$(APOSTROPHY(txtTelephone.Text)) & "', Fax = '" & Trim$(APOSTROPHY(txtFax.Text)) & "', Mobile = '" & Trim$(APOSTROPHY(txtPhone.Text)) & "', Direct = '" & Trim$(APOSTROPHY(txtDirect.Text)) & "', Email = '" & Trim$(APOSTROPHY(txtMail.Text)) & "', Website = '" & Trim$(APOSTROPHY(txtSite.Text)) & "', Comments = '" & Trim$(APOSTROPHY(txtComment.Text)) & "'" _
    & " WHERE AddressID = '" & txtAccount.Text & "';"


    If sID > 0 Then
    con.CommitTrans
    sID = -1
    End If

    End If



    Exit Sub
    eh:
    If sID > 0 Then
    con.RollbackTrans
    sID = -1
    End If

    MsgBox Err.Description
    End Sub

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is APOSTROPHY?
    is it a function that wraps an apostrophe around the supplied variable?
    if it is a home written function then I'd also push the trim call their as well
    if it does add an apostrophe to the value then you dont' need to also add another apostrophe
    can we see the code that is Apostrophy?
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2013
    Posts
    11
    Public Function APOSTROPHY(REPLACESTRING As String) As String
    If REPLACESTRING = "" Then
    APOSTROPHY = ""
    Exit Function
    End If
    If IsNull(APOSTROPHY) = True Then
    APOSTROPHY = ""
    Else
    APOSTROPHY = Replace(REPLACESTRING, "'", "''")
    End If
    End Function

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm not interested in your VBA
    I am interested in the actual SQL you are sending to the SQL engine. so Id strongly recommend that you assign the built SQL to a variable and then examine that variable either in a message box or better yet set a break point on the code and see what the SQL looks like

    there is a world of difference between the VBA that creates the SQL and the SQL that you actually send to the db engine

    also when posting code please make certain you use the [ c o d e ] & [ / c o d e ] to denote where your code stops and starts. but remove the spacers so [ / c o d e ] becomes [/code]

    please make certain your code is indented
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2013
    Posts
    11
    Thank you for the comments, but i am totally lost as i don't know SQL 100%. This is the first time that i have joined a forum.

  11. #11
    Join Date
    May 2013
    Posts
    11
    By the way healdem i am using VB6 and Access 2010, is there any possibility you can help me on this?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the code tags is about legibility
    Code:
    Public Function APOSTROPHY(REPLACESTRING As String) As String
    If REPLACESTRING = "" Then
    APOSTROPHY = ""
    Exit Function
    End If
    If IsNull(APOSTROPHY) = True Then
    APOSTROPHY = ""
    Else
    APOSTROPHY = Replace(REPLACESTRING, "'", "''")
    End If
    End Function
    is easier to read than
    Public Function APOSTROPHY(REPLACESTRING As String) As String
    If REPLACESTRING = "" Then
    APOSTROPHY = ""
    Exit Function
    End If
    If IsNull(APOSTROPHY) = True Then
    APOSTROPHY = ""
    Else
    APOSTROPHY = Replace(REPLACESTRING, "'", "''")
    End If
    End Function

    its easier to read, and as this is a public forum where unpaid volunteers try to help you need to make it as easy as possible for them to read your code

    there is no point putting up every bit of code as most contributors don't have the time to read through huge chunks of code, they just want to see the stuff where the error is reported. posting a block of code that is not relevant just demotivates contributors (effectively it says you don't know where your error is or you can't be bothered

    also there is no point in posting VBA code if the problems is a SQL one as the VBA code creates the SQL but it isn't the same as SQL

    to show your SQL code here put a watch / break point on the code
    then examine the value of the variable in the immediate window

    eg

    ?strSQL

    if you don't know how to debug VBA code then do a google on VB debugging tools
    Last edited by healdem; 05-08-13 at 07:11.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    indenting code is also about legibility
    Code:
    Public Function APOSTROPHY(REPLACESTRING As String) As String
      If REPLACESTRING = "" Then
        APOSTROPHY = ""
        Exit Function
      End If
      If IsNull(APOSTROPHY) = True Then
        APOSTROPHY = ""
      Else
        APOSTROPHY = Replace(REPLACESTRING, "'", "''")
      End If
    End Function
    is better than

    Code:
    Public Function APOSTROPHY(REPLACESTRING As String) As String
    If REPLACESTRING = "" Then
    APOSTROPHY = ""
    Exit Function
    End If
    If IsNull(APOSTROPHY) = True Then
    APOSTROPHY = ""
    Else
    APOSTROPHY = Replace(REPLACESTRING, "'", "''")
    End If
    End Function
    which is better than

    Public Function APOSTROPHY(REPLACESTRING As String) As String
    If REPLACESTRING = "" Then
    APOSTROPHY = ""
    Exit Function
    End If
    If IsNull(APOSTROPHY) = True Then
    APOSTROPHY = ""
    Else
    APOSTROPHY = Replace(REPLACESTRING, "'", "''")
    End If
    End Function
    Last edited by healdem; 05-08-13 at 07:07.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Mabel04 View Post
    is there any possibility you can help me on this?
    I'm trying
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    May 2013
    Posts
    11
    Thankl you very very much healdem, i am getting 80% now

    I have tried to delete the below code, in order to have 1 line code and it works, but i need the comment box to be updated also.


    Code:
    Comments = '" & Trim$(APOSTROPHY(txtComment.Text)) & "'"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •