Need Help with SQL syntax

DeannaF829 · April 28th, 2007, 10:43 PM

Hi!
Can someone direct me to a good source or tutorial to help me with my SQL syntax?
Here is the section in question:

    strSQL = strSQL & " FROM "
    strSQL = strSQL & Chr(34) & "(cust INNER JOIN (contact_xref INNER JOIN (address INNER JOIN person ON address.address_id = person.address_id) ON contact_xref.person_id = person.person_id) ON cust.company_id = person.company_id)" & Chr(34)
    strSQL = strSQL & " WHERE "
    strSQL = strSQL & Chr(34) & "cust" & Chr(34) & "." & Chr(34) & "cust_id" & Chr(34) & " = " & Chr(34) & "to_company_id" & Chr(34)

I need help with the second line. I'm not sure where to break it up and insert my '& Chr(34) &' and what to put in quotes.

Thanks greatly.
Deanna

gbianchi · April 30th, 2007, 08:00 AM

hi there.. I don't remember what is chr(34) but I'm sure you don't need that anywhere...

also why do you want to break the line???

nice tutorial on sql syntax: http://www.w3schools.com/sql/default.asp

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========

BrianWren · April 30th, 2007, 12:12 PM

It makes no difference how you break up the lines, as VB puts it all back together.

Repeatedly changing a string

Code:

    str = str & . . .
    str = str & . . .
    str = str & . . .

really taxes your program. For each of these statements, the whole string gets copied into a new piece of memory. If you add 20 characters each time, the first line ekes out 40 characters, copies the original string into it, followed by the next 20 chars.

The next statement ekes out 60 characters, copies the original 40 into it, plus the 20 new chars. the 3rd statement ekes out 80 chars, copies the original 60, plus the new 20.

So, fo 80 characters, 180 copying operations have been done. Use the line continuation character

Code:

    str = str & . . . & _
                . . . & _
                . . .

In VB to add a " to a string, there is an "escape sequence."

Instead of

Code:

    str = "Some stuff like " & Chr$(34) & "quoted text" & Chr$(34)

use

Code:

    str = "Some stuff like ""quoted text"""

The bold red pair of quotes will be converted to one double quote in the string:

Code:

    Some stuff like "quoted text"

I presume to_company_id is numeric, so it needs no quotes:

Code:

    strSQL = strSQL & _
             " FROM (cust INNER JOIN (contact_xref                " & _
             "      INNER JOIN (address INNER JOIN person         " & _
             "      ON address.address_id = person.address_id)    " & _
             "      ON contact_xref.person_id = person.person_id) " & _
             "      ON cust.company_id = person.company_id)       " & _
             " WHERE custcust_id = to_company_id"