2

I have a parameterized query that looks like this:

SELECT title, html, shortname FROM dbo.Videos
WHERE Topic_ID = ? AND dbo.gradeLevelCheck(?, Grade_Lower, Grade_Upper) = 1
ORDER BY shortname ASC

When I run it from ASP, I get an error that reads:

Incorrect syntax near the keyword 'from'

The parameters are 56 and 1 (so no nulls). The stored function dbo.gradeLevelCheck looks like this:

ALTER FUNCTION [dbo].[gradeLevelCheck]
(
    @value int,
    @lower int,
    @upper int
)
RETURNS int
AS
BEGIN
    DECLARE @result int;
    IF (@lower IS NULL OR @lower <= @value) AND (@upper IS NULL OR @upper >= @value)
        SET @result = 1;
    ELSE
        SET @result = 0;
    RETURN @result;
END

It works fine if I submit it with the parameters hardcoded into the query. It also works fine if I remove the ORDER BY clause. But I can't get it to work in this form. Can anyone help me figure out why?

EDIT: Here are some additional details. The application is written in Classic ASP with JScript. The connection is established thus:

        var conn = Server.CreateObject('ADODB.Connection');
        var connectionString =
            'Provider=SQLOLEDB;' +
            'Data Source=' + Settings.dbServer + ';' +
            'Initial Catalog=' + Settings.dbCatalog + ';' +
            'User Id=' + Settings.dbUser + ';' +
            'Password=' + Settings.dbPass + ';' +
            'Connect Timeout=15;';
        conn.Open(connectionString, conn);

The code used to submit the query looks like this:

    DB.query = function(query) {
    ...
            var cmd = Server.CreateObject("ADODB.Command");
            cmd.activeConnection = conn;
            cmd.commandText = query;
            for (var i = 1; i < arguments.length; i++) {
                cmd.Parameters(i-1).value = arguments[i];
            }
            return cmd.Execute();
    ...
    }

(Here, the parameters 1 and 56 are passed as additional arguments to DB.query.)

Improve this question
7
  • 2
    How are you accessing it? Please post the data access code. Is this classic asp or ASP.NET? What data provider are you using? Commented Jan 25, 2013 at 20:45
  • I don't think your code actually adds new parameters - I can see the array access, but my VBScript is very rusty, but I don't think that actually adds the parameters. Did you profile the DB to see what it gets from the page? Commented Jan 25, 2013 at 20:56
  • Hm, this server doesn't have the profiler installed, and my boss doesn't know how to install it. Anyway, this code seems to work in other places in the codebase. Commented Jan 25, 2013 at 21:02
  • @Oded: I think I got the code for DB.query from this site: blogs.technet.com/b/neilcar/archive/2008/05/21/… Commented Jan 25, 2013 at 21:22
  • What version of SQL Server are you using? Did you try running the function with those parameters directly? Commented Jan 25, 2013 at 21:31

3 Answers 3

Reset to default
6
+25

The "Incorrect syntax near the keyword 'from'" is a classical error you get when building your query by concatenating strings and forgetting some spaces between words. I guess here there is a space missing between the end of the first line (...videos) and the beginning of the second line (WHERE...)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Nice suggestion, but unfortunately that's not the issue here. See my comment on Joel's answer.
2

I see the query text and I see how the function accepts that text, but I'm still missing one thing to accurately diagnose this: how do you place the query text into your code?

I ask, because I have a feeling you have something like this:

query = "SELECT title, html, shortname FROM dbo.Videos"
      + "WHERE TopicID = ? .... ";

Notice what will happen when that string variable is created. It will look like this:

SELECT title, html, shortname FROM dbo.VideosWHERE TopicID = ? ....

Notice that there is nothing at all between dbo.Videos and WHERE. You did not include any whitespace between the two lines. The line break in your original code is not part of either string literal. You need something like this instead:

query = "SELECT title, html, shortname FROM dbo.Videos"
     + " WHERE TopicID = ? .... ";

Note the addition of a space for the WHERE keyword.

An additional suggestion is to drop the dbo. part and just use Videos for the table name. I know that what you have should be valid syntax, but something unusual must be going on or we'd have solved it already. One more thing to try is to place brackets around the table and column names.

Improve this answer

6 Comments

var videos = DB.getAllAssocs( DB.query('SELECT title, html, shortname FROM dbo.Videos ' + 'WHERE Topic_ID = ? ' + 'AND dbo.gradeLevelCheck(?, Grade_Lower, Grade_Upper) = 1 ' + 'ORDER BY shortname ASC', 56, 1) );
The debug error message my site is printing out also confirms that I have enough spaces: DB error: {"number":-2147467259,"description":"Incorrect syntax near the keyword 'from'.","message":"Incorrect syntax near the keyword 'from'."} Query was: SELECT title, html, shortname FROM dbo.Videos WHERE Topic_ID = ? AND dbo.gradeLevelCheck(?, Grade_Lower, Grade_Upper) = 1 ORDER BY shortname ASC; with parameters: [56,1]
I'd still check this... You may have a weird Unicode character in that spot or something. Delete everything from the s in videos through the w in where inclusive and put the characters back without the break in the literal.
@BrianBi new suggestion: drop the schema and just say "Videos"
I wish I knew how to do that, haha. If it were up to me we would've been using PHP and MySQL...
|
1

I believe I've figured out the problem. Here's my hypothesis:

It seems that ordinarily ADO is able to infer the types of arguments based on the structure of the query. However, in this case one of the arguments to the prepared statement is a parameter for the function dbo.gradeLevelCheck, which causes the type inference to fail. (Presumably, ADO doesn't understand stored functions or something, so it doesn't know how to check what types the arguments should be.) Because it can't figure out the types, the query is invalid.

I still don't know why it is that this query appears to work if the ORDER BY clause is removed, but it's clear that I have to change the code anyway.

Improve this answer

3 Comments

Have you tried constructing the query by hardcoding those parameters into the string, while having ′ORDER BY′ clause in place?
That's what I'm doing now, and it works, though it makes me feel at least a little bit dirty.
Umm I think there's a solution that wouldn't make you feel dirty. Please follow this guideline to create your parameters: devguru.com/technologies/ado/quickref/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.