Solved

unterminated String Constant - Ugh!

Posted on 2004-10-26
213 Views
Last Modified: 2008-02-01
I'm receiving an unterminated string constant for the following sql statement:

strSQL =  "SELECT siteID, name1, addr1a, city1, state1,zip1, from tblsite left join tblgroup
                                                                                          on tblsite.siteid=tblgroup.siteid left join tblphysgroup
                                                                                          on tblgroup.physid=tblphysgroup.physid left join tblphysician
                                                                                          on tblphysgroup.physid=tblphysician.physid where tblsite.siteid= " & strSpecialty & " order by tblsite.name1"

Can anyone show me where the error is?
0
Question by:johnson00
    14 Comments
     
    LVL 15

    Expert Comment

    by:justinbillig
    can you post teh exact value of strSQL
    0
     
    LVL 2

    Author Comment

    by:johnson00
    All of that is what I'm attempting to set strSQL equal to.  
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    What datatype is strSpecialty?  Can it be concatenated directly to a string constant?
    0
     
    LVL 15

    Expert Comment

    by:SRigney
    You need a single quote around your value strSpecialty

    strSQL =  "SELECT siteID, name1, addr1a, city1, state1,zip1, from tblsite left join tblgroup
                                                                               on tblsite.siteid=tblgroup.siteid left join tblphysgroup
                                                                               on tblgroup.physid=tblphysgroup.physid left join tblphysician
                                                                               on tblphysgroup.physid=tblphysician.physid where tblsite.siteid= '" & strSpecialty & "' order by tblsite.name1"


    0
     
    LVL 15

    Expert Comment

    by:SRigney
    Also make sure the code is either all on one line, or you separate it with the underscore.

    strSQL =  "SELECT siteID, name1, addr1a, city1, state1,zip1, from tblsite left join tblgroup " _
                    & "on tblsite.siteid=tblgroup.siteid left join tblphysgroup " _
                    & "on tblgroup.physid=tblphysgroup.physid left join tblphysician " _
                    & "on tblphysgroup.physid=tblphysician.physid where tblsite.siteid= '" _
                    & strSpecialty & "' order by tblsite.name1"
    0
     
    LVL 2

    Author Comment

    by:johnson00
    Well, now I get this error:

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
    0
     
    LVL 15

    Expert Comment

    by:SRigney
    Can you show us the value of strSQL when it is attempting to run?

    Is SiteID an int field or a character field?

    Since it's Access (instead of my assumed SQL Server) you may just need to end with a semi-colon.

    & strSpecialty & "' order by tblsite.name1;"
    0
     
    LVL 2

    Author Comment

    by:johnson00
    SiteID is an int field.  I can't get far enough to output what the sql statement gives me.  It won't execute past it, but just keeps erring on the line.  Well, this is actually an ASP page that contains a SQL reference to a table to select data.  It's worth a shot.  I'm getting desperate and frustrated.
    0
     
    LVL 2

    Author Comment

    by:johnson00
    doesn't like the semi colon either.
    0
     
    LVL 2

    Author Comment

    by:johnson00
    Ok, I have a new error now:

    Syntax error in FROM clause.
    0
     
    LVL 2

    Author Comment

    by:johnson00
    Here is the output of the SQL statement:

    SELECT siteID, name1, addr1a, city1, state1 from tblsite left join tblgroupon tblsite.siteid=tblgroup.siteid left join tblphysgroupon tblgroup.physid=tblphysgroup.physid left join tblphysicianon tblphysgroup.physid=tblphysician.physid where tblsite.siteid= '' order by tblsite.name1;
    0
     
    LVL 2

    Author Comment

    by:johnson00
    Where should I look from here?
    0
     
    LVL 6

    Expert Comment

    by:PePi
    SELECT siteID, name1, addr1a, city1, state1 from tblsite left join tblgroupon .... you forgot to put a space between tblgroup and ON

    also for

    tblphysgroupon ... should be tblphysgroup ON
    tblphysicianon... should be tblphysician ON


    HTH!!!
    0
     
    LVL 6

    Accepted Solution

    by:
    also i found this

    SELECT siteID, name1, addr1a, city1, state1,zip1, ...


    remove the comma after zip1 field
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now