Easy SQL syntax question

Dim varBookID as String
varBookID = request.params("BookID")

Dim varBookNo as String
varBookNo = request.params("BookNo")

***What is incorrect with the string concatenation below? I know the SQL syntax is incorrect, but i cannot locate the problem.***

DBCommand = New OleDbDataAdapter("SELECT * FROM Books WHERE BookID=" & "'" & varBookID & "'" AND BookNo= & "'" & varBookNo & "'"", DBConn)

You have a string with a part that ends after

"SELECT * FROM Books WHERE BookID=" & "'" & varBookID & "'"

Then comes

AND BookNo= & "'" & varBookNo & "'""

That should give you a compiler syntax error right there. Simplified:

"SELECT * FROM Books WHERE BookID='" & varBookID & "' AND BookNo='" & varBookNo & "'"

Among other things, it is because of stuff like this that you should use
Stored procedures and SqlParameters, which eliminate these kinds of tedious string concatenations.
You can find many examples on the net

|||It looks like you've got an extra quote mark at the end.
sql

Dynamic WHERE clause

Hi
I have a WHERE clause, based on Params, passed in from a report, however, I
cannot get it to work properly.
I have to return two product codes, when a certain Types are passed in,
however, when it's anything else, I am only to return one product type.
Here's a sample of what I am trying to do:
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'CC', 'PO'
ELSE
'CC'
END)
Kind Regards
RickyTry
WHERE [Product Code] = 'CC' OR ( [Product Code] = 'PO' AND @.Type IN ('MD',
'BI') )
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ricky" <ricky@.ricky.com> wrote in message
news:u$sWO8fjGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have a WHERE clause, based on Params, passed in from a report, however,
> I
> cannot get it to work properly.
> I have to return two product codes, when a certain Types are passed in,
> however, when it's anything else, I am only to return one product type.
> Here's a sample of what I am trying to do:
>
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'CC', 'PO'
> ELSE
> 'CC'
> END)
>
> Kind Regards
> Ricky
>|||No, you can't do this. You can try something like this though...
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'PO'
ELSE
'CC'
END, 'CC')
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi chaps
Thank you both for the suggestion, couldn't work this out, been stuck for a
few hours. I thought it would be a simple thing to do...anyway, thanks
again.
Kind Regards
Ricky
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:06AFD45A-62AB-49EE-AFED-F6095D3C0C2C@.microsoft.com...
> No, you can't do this. You can try something like this though...
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'PO'
> ELSE
> 'CC'
> END, 'CC')
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Dynamic SQL with output values

Hi:
How can I in SQL Server 2000 (using Transact SQL) execute a dynamic sql string and at the same time retrieve output params ?
ThanksPlease give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?|||Originally posted by rnealejr
Please give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?

Here's an simple example:

SELECT @.num_records = COUNT(*)
FROM @.TableName