syntax error but don't know where 
Author Message
 syntax error but don't know where

Hello all,

I have been trying for the past several hours to get this line to run and am
at my wits end.  Someone please help.

lcsql = "Select * from Results Where ID = 'Clng(recordnum)'"

recordnum is a session varriable which was picked up from the access
database.  It is the primary key autoincrement field in my database.  I can
use response.write on recordnum and it gives me what I am expecting.  I can
do TypeName() and I get Long returned.  I get "Data type mismatch in
criteria search" whether I use the Clng function or not.  I've tried things
like  ID = '+"Clng(recordnum)"+'" and others.  Nothing seems to work.  I
think the sql string wants a string but the field is of "Long" type.  What
do I do?

Thanks

Terry



Sat, 16 Jul 2005 02:39:26 GMT  
 syntax error but don't know where

Quote:
> lcsql = "Select * from Results Where ID = 'Clng(recordnum)'"

Try:

lcsql = "Select * from Results Where ID = " & Clng(recordnum)

or:

lcsql = "Select * from Results Where ID = " & recordnum

or:

lcsql = "Select * from Results Where ID = " & Session("recordnum")



Sat, 16 Jul 2005 02:58:23 GMT  
 syntax error but don't know where
lcsql = "Select * from Results Where ID = 'Clng(recordnum)'"

Will result in the string:

    Select * from Results Where ID = 'Clng(recordnum)'

However, what you are really looking for is:

    lcsql = "Select * from Results Where ID = '" & CLng(recordnum) & "'"

   NB: The first set of quotes is [single, double] and the closing set of quotes is [double, single, double quote].

This will result in (if recordnum=10):

    Select * from Results Where ID = '10'

However, I suspect that the ID field is *numeric* in which case the single quotes are *not* wanted and you should do:

    lcsql = "Select * from Results Where ID = " & CLng(recordnum)

Resulting in [again if recordnum=10):

    Select * from Results Where ID = 10

Basically, a single quote is not a string delimiter in VBScript but *is* used to denote a character string in an SQL
statement. Numerical values should not be enclosed in single quotes. Dates should be quoted or delimited with #s.

Hope this helps.

Chris Barber.

Quote:

> lcsql = "Select * from Results Where ID = 'Clng(recordnum)'"

Try:

lcsql = "Select * from Results Where ID = " & Clng(recordnum)

or:

lcsql = "Select * from Results Where ID = " & recordnum

or:

lcsql = "Select * from Results Where ID = " & Session("recordnum")



Sat, 16 Jul 2005 03:10:32 GMT  
 syntax error but don't know where
Sorry,

Error message says "No value given for one or more required parameters"
I think it needs to be inside the closing quote.
Any other thoughts?

Terry

Quote:
> Try:

> lcsql = "Select * from Results Where ID = " & Clng(recordnum)

> or:

> lcsql = "Select * from Results Where ID = " & recordnum

> or:

> lcsql = "Select * from Results Where ID = " & Session("recordnum")



Sat, 16 Jul 2005 03:12:13 GMT  
 syntax error but don't know where
This one did work. Thanks a whole lot.

Terry

Quote:
> Try:

> lcsql = "Select * from Results Where ID = " & Clng(recordnum)



Sat, 16 Jul 2005 03:18:50 GMT  
 syntax error but don't know where
The second one worked.  And thanks for the explanation also.  And to think I
wasted almost 3 hours on this before I gave up and asked for help.  Thanks
again.

Terry

Quote:
> Will result in the string:

>     Select * from Results Where ID = 'Clng(recordnum)'

> However, what you are really looking for is:

>     lcsql = "Select * from Results Where ID = '" & CLng(recordnum) & "'"

>    NB: The first set of quotes is [single, double] and the closing set of

quotes is [double, single, double quote].
Quote:

> This will result in (if recordnum=10):

>     Select * from Results Where ID = '10'

> However, I suspect that the ID field is *numeric* in which case the single

quotes are *not* wanted and you should do:
Quote:

>     lcsql = "Select * from Results Where ID = " & CLng(recordnum)

> Resulting in [again if recordnum=10):

>     Select * from Results Where ID = 10

> Basically, a single quote is not a string delimiter in VBScript but *is*

used to denote a character string in an SQL
Quote:
> statement. Numerical values should not be enclosed in single quotes. Dates

should be quoted or delimited with #s.
Quote:

> Hope this helps.

> Chris Barber.




Quote:
> > lcsql = "Select * from Results Where ID = 'Clng(recordnum)'"

> Try:

> lcsql = "Select * from Results Where ID = " & Clng(recordnum)

> or:

> lcsql = "Select * from Results Where ID = " & recordnum

> or:

> lcsql = "Select * from Results Where ID = " & Session("recordnum")



Sat, 16 Jul 2005 03:20:24 GMT  
 syntax error but don't know where
What is the full SQL statement before and after exeuction?
(Temporarily add "MsgBox strSQL" before it is executed.)

What is the data type of ID?

What is the data type of "recordnum"?



Sat, 16 Jul 2005 03:27:00 GMT  
 syntax error but don't know where
The idea when creating a dynamic sql string is to create a
string that can be sent to the database and run as-is in
your database's query tool (always tell us your database
type and version). This means that you can only include
names that will be recognized by the database's query
engine.

Tell me, do you think your database will know
what "recordnum" is? I can tell you that it won't. And if
the database is not Access, "Clng" will not be recognized
either. You cannot send a sql statement to the database
containing the word "recordnum". What you have to do is
concatenate the VALUE contained by recordnum into the sql
string, like this (get rid of the Clng - we're creating a
STRING variable)"

lcsql = "Select * from Results Where ID = " & recordnum

Response.write lcsql

Always use response.write to verify that you've done this
concatenation correctly. When you run the page, the
statement written by response.write should be a valid sql
statement. You should be able to copy it to the clipboard,
paste it into your database's query tool, and run it
without modification. Response.write is your best friend.

HTH,
Bob Barrows

PS. If you are going to continue to use dynamic sql (you
shouldn't - stored procedures are also your friend), you
need to read the SQL Injection FAQ at www.sqlsecurity.com -
 protect your server!

Quote:
>-----Original Message-----
>Hello all,

>I have been trying for the past several hours to get this
line to run and am
>at my wits end.  Someone please help.

>lcsql = "Select * from Results Where ID = 'Clng
(recordnum)'"

>recordnum is a session varriable which was picked up from
the access
>database.  It is the primary key autoincrement field in
my database.  I can
>use response.write on recordnum and it gives me what I am
expecting.  I can
>do TypeName() and I get Long returned.  I get "Data type
mismatch in
>criteria search" whether I use the Clng function or not.  
I've tried things
>like  ID = '+"Clng(recordnum)"+'" and others.  Nothing
seems to work.  I
>think the sql string wants a string but the field is

of "Long" type.  What

- Show quoted text -

Quote:
>do I do?

>Thanks

>Terry

>.



Sat, 16 Jul 2005 03:55:51 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. don't know how to handle error

2. ADO Error! Don't know why

3. Help-don't know what i'm missing

4. Don't know if i'm in the right newsgroup

5. not exactely scripting but don't know where else

6. Don't Know who else to ask.

7. Don't know y not work

8. Don't Know How to Publicly Declare Variable

9. Microsoft Windows 95 don't know calculation

10. Migration to VS 2003 worked though I don't know why

11. Don't tell me no one knows the answer

12. does anybody know why this code don't work

 

 
Powered by phpBB® Forum Software