Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Unanswered: VB SQL String error

    I've been toying around with this SQL statement for several hours now. I've gotten several different error messages when I try changing it up. Currently have a syntax error message. Anyway, maybe you guys can see what I'm missing here.

    SQLEdit1 = "UPDATE Claims SET Claims.claimNumber = " & CStr(Claim) & _
    ", Claims.claimDescription = '" & txtDes & _
    "', Claims.claimValue = " & CStr(txtValue) & _
    ", Claims.claimDispute = '" & txtDispute & _
    "', Claims.claimRegNoticeDate = #" & txtNotice & _
    "#, Claims.claimRegSubmitDate = #" & txtSubmit & _
    "#, Claims.claimRegAckDate = #" & txtAck & _
    "#, Claims.claimRegDenialDate = #" & txtDenial & _
    "#, Claims.claimHOSubmitDate = #" & txtHOSubmit & _
    "#, Claims.claimHOAckDate = #" & txtHOAck & _
    "#, Claims.claimDeny = " & cbDeny & _
    ", Claims.claimSettlement = '" & CStr(txtSet) & _
    "', Claims.claimDenyDate = #" & txtDenyDate & _
    "#, Claims.claimSettlementDate = #" & txtSetDate & _
    "# WHERE conNumber = '" & txtConNum & "' AND claimNumber = '" & CStr(Claim) & "';"

    EDIT: Note the txtValues with the Cstr wrapped around it are number values in the database, all the dates are DateTime values, and claimDescription, claimDispute, and claimDeny are Text values

    EDIT2: Ooh I think I might have a solution. Maybe. Not all the date values are mandatory so if nothing is entered into the txtboxes the SQL comes up with ## instead of just "". How would I solve that?

    EDIT3: So I added the following in front. It gets rid of the ## error but I still get an Syntax error in Update statement msgbox. (HO Dates are mandatory so it's not necessary to edit those values. I've also replaced txtNotice (etc.) in the SQL with the new String values.

    Dim Notice As String
    Dim Submit As String
    Dim Ack As String
    Dim Denial As String
    Dim DenyDate As String
    Dim SetDate As String

    Notice = "#" & txtNotice & "#"
    Submit = "#" & txtSubmit & "#"
    Ack = "#" & txtAck & "#"
    Denial = "#" & txtDenial & "#"
    DenyDate = "#" & txtDenyDate & "#"
    SetDate = "#" & txtSetDate & "#"

    If Notice = "##" Then
    Notice = ""
    End If
    If Submit = "##" Then
    Submit = ""
    End If
    If Ack = "##" Then
    Ack = ""
    End If
    If Denial = "##" Then
    Denial = ""
    End If
    If DenyDate = "##" Then
    DenyDate = ""
    End If
    If SetDate = "##" Then
    SetDate = ""
    End If
    Last edited by BartvanLeeuwen; 07-20-10 at 11:29.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    best thing to do is to set a watch or breakpoint on your code and examine the SQL and spot where there may be errors

    first off there should be no need to convert a controls value to text/string.. implicitly they already are
    dates should be in either ISO format yyyy/mm/dd or US format mm/dd/yyyy

    check you are encapsulating text values with either quotes ' or speechmarks "

    ", Claims.claimSettlement = '" & CStr(txtSet) & _
    "', Claims.claimDenyDate = #" & txtDenyDate & _
    doesn't look right to me

    trying to debuig this is a pig unless yopu display the actual SQL being sent to the SQL engine

    theres ways of finding where the error is such as
    SQLEdit1 = "UPDATE Claims SET Claims.claimNumber = " & CStr(Claim)
    SQLEdit1 = SQLEdit1 & ", " & Claims.claimDescription = '" & txtDes & "',"
    SQLEdit1 = SQLEdit1 & ", " & Claims.claimValue = " & CStr(txtValue)
    SQLEdit1 = SQLEdit1 & ", " & ", Claims.claimDispute = '" & txtDispute & "',"
    SQLEdit1 = SQLEdit1 & ", " & "', Claims.claimRegNoticeDate = #" & txtNotice & "#"
    ....
    SQLEdit1 = SQLEdit1 & " " & " WHERE conNumber = '" & txtConNum & "' AND claimNumber = '" & CStr(Claim) & "';"

    the advantage whilst developing the SQL is that each element is on a separate line.. if as and when it craps out you can quickly spot the offending line. once you have completed the developemnt you can merge all the SQL back into one line

    personally I prefer to encapsulate text values with chr$(34).. the " symbol but using chr$(34) makes it more legible to me

    eg
    SQLEdit1 = SQLEdit1 & Claims.claimDescription = " & chr$(34) & txtDes & chr$(34) & ", "
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2010
    Posts
    5
    Thanks Healdem, I did the whole SQLEdit1 = SQLEdit1 & "..." thing. Still have a problem. I know in Visual Studio 08 that you can look at the whole SQL string as your building it. Is that possible in VB? All I see when I hover over the String value in breakpoint mode is the first little bit. Not very helpful if you ask me.

    I don't know what chr$(34) is but I assume it acts just like a quotation mark.

    And what part doesn't look right to you in this?

    ", Claims.claimSettlement = '" & CStr(txtSet) & _
    "', Claims.claimDenyDate = #" & txtDenyDate & _

  4. #4
    Join Date
    Jul 2010
    Posts
    5
    At this point my code looks like this

    SQLEdit1 = "UPDATE Claims SET Claims.claimNumber = " & CStr(Claim)
    SQLEdit1 = SQLEdit1 & ", Claims.claimDescription = " & Chr$(34) & txtDes & Chr$(34)
    SQLEdit1 = SQLEdit1 & ", Claims.claimValue = " & txtValue
    SQLEdit1 = SQLEdit1 & ", Claims.claimDispute = " & Chr$(34) & txtDispute & Chr$(34)
    SQLEdit1 = SQLEdit1 & ", Claims.claimRegNoticeDate = " & Chr$(34) & Notice & Chr$(34)
    SQLEdit1 = SQLEdit1 & ", Claims.claimRegSubmitDate = " & Chr$(34) & Submit & Chr$(34)
    SQLEdit1 = SQLEdit1 & ", Claims.claimRegAckDate = " & Chr$(34) & Ack & Chr$(34)
    SQLEdit1 = SQLEdit1 & ", Claims.claimRegDenialDate = " & Chr$(34) & Denial & Chr$(34)
    SQLEdit1 = SQLEdit1 & ", Claims.claimHOSubmitDate = #" & txtHOSubmit & "#"
    SQLEdit1 = SQLEdit1 & ", Claims.claimHOAckDate = #" & txtHOAck & "#"
    SQLEdit1 = SQLEdit1 & ", Claims.claimDeny = " & Chr$(34) & cbDeny & Chr$(34)
    SQLEdit1 = SQLEdit1 & ", Claims.claimDenyDate = " & Chr$(34) & DenyDate & Chr$(34)
    SQLEdit1 = SQLEdit1 & ", Claims.claimSettlement = " & Chr$(34) & txtSet & Chr$(34)
    SQLEdit1 = SQLEdit1 & ", Claims.claimSettlementDate = " & Chr$(34) & SetDate & Chr$(34)
    SQLEdit1 = SQLEdit1 & " WHERE conNumber = '" & txtConNum & "' AND claimNumber = '" & CStr(Claim) & "';"

    EDIT: replaced new current code with older code I had. Now I just got a type mismatch error. Thanks for all your help. I ended up just showing my SQLEdit1 on a textbox so I could look at it. Found several major errors right away. haha
    Last edited by BartvanLeeuwen; 07-20-10 at 12:21.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    had you set a breakpoint or watch you would have been able to see the code running as you step through it
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can use this after the string is built to print the finished string out to the VBA Immediate window:

    Debug.Print SQLEdit1

    You can also copy/paste that SQL to a new query and try to run it, where you'll often get a more descriptive error message.

    A type mismatch error is typically due to mismatch between the data type of a field and the delimiter surrounding the value in code. Generally, Date/Time values need to be surrounded by #, text values by ' and numeric values by nothing. I'd double check all them first. You have a couple of fields that look like dates but are surrounded by Chr$(34).
    Paul

  7. #7
    Join Date
    Jul 2010
    Posts
    5
    Thanks guys, I've figured it all out and it works great now.
    Last edited by BartvanLeeuwen; 07-20-10 at 15:02.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think you want Null rather than ##. If I'm right, you'll need to test the textbox so you either add Null without the delimiters or a date with delimiters.
    Paul

  9. #9
    Join Date
    Jul 2010
    Posts
    5
    Yea Pbaldy, I needed Null. I have if statements checking for ## and before I had it reset to "" if it was indeed ##. I had tried Notice = Null but it turns out I needed to make it Notice = "Null"

Posting Permissions

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