Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2003
    Posts
    7

    Unanswered: ASP, SQL and Quotation Syntax...

    I am trying to construct an SQL statement using VB Script. I want to retrieve all of the records of a certain year...

    SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart("y", Table.DatePlayed) = 2003 "

    I get an error page stating...

    Expected end of statement at the quotation marks:
    ... DatePart("yyyy" ...

    I have tried this with double quotes, single quotes, no quotes, double single quotes, etc., but no luck!

    Anyone?

    Thanks,

    fergy

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart('yy', Table.DatePlayed) = 2003"

  3. #3
    Join Date
    Feb 2003
    Posts
    7
    When modifying it to your suggestion I get the error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Ah access.
    try
    SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart(""y"", Table.DatePlayed) = 2003"

    Can't remember the datepart systax for access but something like this.

  5. #5
    Join Date
    Feb 2003
    Posts
    7
    Originally posted by nigelrivett
    Ah access.
    try
    SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart(""y"", Table.DatePlayed) = 2003"

    Can't remember the datepart systax for access but something like this.

    With the double quotes around it I get:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

  6. #6
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    I'll just throw this in here...

    SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart("yyyy", Table.DatePlayed) = 2003"

  7. #7
    Join Date
    Feb 2003
    Posts
    7
    Originally posted by Memnoch1207
    I'll just throw this in here...

    SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart("yyyy", Table.DatePlayed) = 2003"

    When trying your suggestion, Iget the following:

    Microsoft VBScript compilation error '800a0401'

    Expected end of statement


    It seems the quotations are throwing the syntax out of whack. How can you use datepart in an SQL statement if you can't use quote characters?

  8. #8
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    SELECT DatePlayed FROM table WHERE DatePart("y", DatePlayed) = 2003

    OR

    SELECT DatePlayed FROM table WHERE DatePart(year, DatePlayed) = 2003

  9. #9
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    That would cause problems with the embedded quote - would have to be


    SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart(""yyyy"", Table.DatePlayed) = 2003"

    tried this in access and it doesn't lik Table - don't know if you have that as the name

    SQL = "SELECT Table.Dateplayed FROM [Table] WHERE DatePart(""yyyy"", Table.DatePlayed) = 2003"


    Shouldn't give the 2 expeted parameters error though.
    Can you post the code you are using.
    And do you succeed with other similar queries?

  10. #10
    Join Date
    Feb 2003
    Posts
    7
    Originally posted by nigelrivett
    That would cause problems with the embedded quote - would have to be


    SQL = "SELECT Table.Dateplayed FROM Table WHERE DatePart(""yyyy"", Table.DatePlayed) = 2003"

    tried this in access and it doesn't lik Table - don't know if you have that as the name

    SQL = "SELECT Table.Dateplayed FROM [Table] WHERE DatePart(""yyyy"", Table.DatePlayed) = 2003"


    Shouldn't give the 2 expeted parameters error though.
    Can you post the code you are using.
    And do you succeed with other similar queries?
    The double quotes you suggested above doesn't do it. I've constructed numerour SQL statements in the past with no difficulty. However, I have never tried building one that required the use of quotation marks, other than for variable names and strings in WHERE clauses.

    Here is the code I am working with in my asp script:
    ---------------------------------------------------------
    IF StatTimeFrame = "Career" THEN
    set rs = db.execute("SELECT Rounds.*, Rounds.MemberID FROM Rounds WHERE Rounds.Status = 'LOCKED' )
    ELSE
    set rs = db.execute("SELECT Rounds.*, Rounds.MemberID, Rounds.Dateplayed FROM Rounds WHERE Rounds.Status = 'LOCKED' AND Rounds.MemberID <> '"&MemberName&"' AND StatTimeFrame = DatePart(""yyyy"", Rounds.DatePlayed) ")
    END IF
    ---------------------------------------------------------

    The first SQL statement above works fine. I have confirmed that the second SQL statement works fine without the DatePart code. The DatePart code does in fact cause the difficulty.

    By the way, thanks for your help!

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    fergy, in the meantime, while you are digging out the appropriate docs about how to escape a doublequote in ASP or whatever you're using, you can bypass the problem with this workaround --

    change

    StatTimeFrame = DatePart("yyyy", Rounds.DatePlayed)

    to

    StatTimeFrame = year(Rounds.DatePlayed)


    rudy
    http://rudy.ca/

  12. #12
    Join Date
    Feb 2003
    Posts
    7
    [SIZE=1]Originally posted by r937

    change

    StatTimeFrame = DatePart("yyyy", Rounds.DatePlayed)

    to

    StatTimeFrame = year(Rounds.DatePlayed)
    Even when I attempt this workaround, I get the following error:

    Too few parameters. Expected 1.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Too few parameters. Expected 1." is a simple syntax error

    what query did you get this on?

    not the ASP code that builds the query, but the query string after any variables have been suffed into it

  14. #14
    Join Date
    Feb 2003
    Posts
    7
    Originally posted by r937
    "Too few parameters. Expected 1." is a simple syntax error

    what query did you get this on?

    not the ASP code that builds the query, but the query string after any variables have been suffed into it
    Well, I've found the problem I had with implementing your suggested work - around. In fact it wasn't the work-around that was the issue. It was actually the following:

    year(Rounds2.DatePlayed) = StatTimeFrame

    I failed to put puntuation around my variable name. I corrected it to be:

    '"&StatTimeFrame&"'

    I still can't get this to work using the DATEPART function, as the quotation marks cause syntax errors. However, using the workaround above solves the problem.

    I sincerely thank everyone for their help on this issue.

    fergy

  15. #15
    Join Date
    Apr 2003
    Location
    Granville, Ohio, US
    Posts
    1

    Arrow

    I know this issue has already been solved but another solution to the problem would have been the following:

    Where you use
    StatTimeFrame = DatePart("yyyy", Rounds.DatePlayed)

    Replace the quotes arount yyyy with 4 single quotes
    StartTimeFrame = DatePart(''''yyyy'''', Rounds.DatePlayed)

    When the SQL is being executed, it will interpret the 4 single quotes with as 1 double quote.

    Hope this is of help to someone in the future.

    -Jon

    www.madridcom.com
    Last edited by jmadrid; 04-03-03 at 18:11.

Posting Permissions

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