We help IT Professionals succeed at work.

String Handling: Quoting already quoted strings

pwdells
pwdells asked
on
279 Views
Last Modified: 2012-05-11
Hello,

I am building a dynamic function that will create the WHERE clause for a SQL statement based on how other queries returned results.

Here are some excerpts from my code:
Case Statement Background - I have a function that builds the str_SWX using concatenated binary results from other queries.  ("001" Query 1 = False, Query 2 = False, Query 3 = True) The str_SWX is then evaulated in this CASE statement as follows:  
    'Case statement
    Select Case str_SWX
        Case "000"      'No Local Matches
            lstSplash.AddItem ("No Local Matches for: " & str_SREGION & _
                " OR " & str_COUNTRY)
            Call CLOSE_BATCH
            Call ADD_REM_REQ
        Case "001"      'Match on Subregion ONLY
            lstSplash.AddItem ("Match for Subregion: " & str_SREGION)
            str_WHERE = " WHERE SubREGION = '" & str_SREGION & "' AND COUNTRY IS NULL "
            Call UPDATE_BATCH
        Case "010"      'Match on Country ONLY
            lstSplash.AddItem ("Match for Country: " & str_COUNTRY)
            str_WHERE = " WHERE  SubREGION IS NULL AND COUNTRY = '" & str_COUNTRY & "' "
            Call UPDATE_BATCH
        Case "011"      'Match on Subregion OR Country
            lstSplash.AddItem ("Matches for Country: " & str_COUNTRY & _
                " AND Subregion: " & str_SREGION)
            str_WHERE = " WHERE SubREGION = '" & str_SREGION & "' OR COUNTRY = '" & str_COUNTRY & "' "
            Call UPDATE_BATCH
        Case "100"      'Match on Subregion AND Country
            str_WHERE = " WHERE SubREGION = '" & str_SREGION & "' AND COUNTRY = '" & str_COUNTRY & "' "
            Call UPDATE_BATCH
        Case Else       'Should not hit this ELSE statement
            lstSplash.AddItem ("SERIOUS ERROR! ")
    End Select

Open in new window


Then I save the str_WHERE variable into a field in the batch table using this code:
str_SQL = "UPDATE TMP_FCST_BATCH " & _
        "SET TMP_FCST_BATCH.SWX = '" & str_SWX & "' " & _
            ", TMP_FCST_BATCH.WHERE = '" &  str_where & "' " & _
            "', TMP_FCST_BATCH.PASS = TRUE " & _
        " WHERE TMP_FCST_BATCH.ID = " & int_ID
DoCmd.RunSQL (str_SQL)

Open in new window


My problem (I think) is within the syntax and the string handling of the strings containing strings.  When I go to concatenate the the next SELECT statement with the saved str_WHERE statement, it doesn't handle the text variables correctly and errors out.

What are your thoughts?

Best Regards,

Wendee
Comment
Watch Question

Author

Commented:
This is the error: String Error

Commented:
Can you stop the code and get a print out of str_SQL at the line DoCmd.RunSQL (str_SQL)?

I think you have the syntax wrong for the update statement.

Should follow:
UPDATE tablename
SET field1=value1, field2=value2,...
WHERE whereclause

Looks like you have too many WHEREs
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
@rveliz: this is not my week -- you are right.  Let me fix this, then address LSMConsulting's comment.

Thank you.

Author

Commented:
@rveliz:  Actually, no, there is not too many where's.  when I first looked at it, upon your suggestion, I thought you had a valid point.  However, it's saving a WHERE statement into the table.  So it is saying that set TMP_FCST_BATCH.WHERE to "Where subregion = ..."  So there would be 3 "wheres" in the code.  Maybe I should just take the "WHERE " out and concatenate that later.

Commented:
ahh, ok I see.

Try this then:

 
str_SQL = "UPDATE TMP_FCST_BATCH " & _
        "SET TMP_FCST_BATCH.SWX = """ & str_SWX & """ " & _
            ", TMP_FCST_BATCH.WHERE = """ &  str_where & """" & _
            ", TMP_FCST_BATCH.PASS = TRUE " & _
        " WHERE TMP_FCST_BATCH.ID = " & int_ID

Open in new window


Leave single quotes in the "where" string variables and replace the single quotes with two double quotes in the UPDATE
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
I always use Chr(34) instead of " and ' when constructing strings.
I also never use & _
SomeString = "Something "
SomeString = SomeString & "Some more stuff"

You can then drop a breakpoint on each line and watch it get built.
Or throw a msgbox in between each line and see what you are getting.

Mega-quotes = Evil.
But that's me

And I DEFINITELY would have used a recordset instead of SQL to do the update.

That mess is just way too hard to debug.
But that's why you posted :)

Commented:
Best thing to do is set a breakpoint at the runsql line and using the immediate window, get the value of the sql_SQL variable at that point.  This way you can see what the sql statement actually looks like.  You can copy paste the sql statement to a new query just to verify it's correctness if you like.

Author

Commented:
@Nick67:  Thank you for your advice.

I agree with the Mega-Quotes!!  :)  I have to follow the requirements and specifications for coding and documentation.  One requirement is that all SQL is formatted like:

SELECT Field1, ...Fieldx
FROM Table
WHERE CLAUSE

Which (as far as I know) requires me to use the "& _"  Right?

As far as the recordset...requirements and specifications require me to write to TMP tables.  I am not the first or last developer for this project.  I don't have too many liberties here.  :(
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
@Nick67,

So are you saying that your method listed above using the With/End statement is or is not optimized for thousand's of records?  

This is an app that will be bridging a gap between 2 SAP "modules".  The SAP version is not slated to be finished until later this year.  So, this will work interim.  This will be handling about 30,000 records at once.

Wendee
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
And... :)
What you build as a shim to test that it works right

SomeString = "Select Something "
SomeString = SomeString & "from someplace "
SomeString = SomeString & "where something = something else"

can be transformed to

SomeString = "Select Something " & _
"from someplace " &_
"where something = something else"

AFTER you know it'll work ;)
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
How many updates does it do in one go is the question.
Does it just update a single record at a time, or is there some code I don't see that this is going to update 30000+ records in one fell go?

Looping is evil when you get into very large datasets--but nothing I've seen suggests a loop.
And if the if the looping is external to this, it makes little difference if the routine executes 30000 one-off updates using SQL or recordset code.

There is a difference if the SQL will update 30000 records in one execution, but the recordset has to update them one at a time

Catch my drift?

Author

Commented:
It appears that my question has a few solutions.

@LSMConsulting:
Your suggestion to do the Replace, worked.   That was an answer to my syntax issue.

@Nick67:
I am going to take your suggestion to the With Statement w/ the RS.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.