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

Easy (?) data-editing solution? Need help pls.

I have several thousand rows of (text) data that have some incorrect pieces. I need a way to delete part of the data but leave the rest intact.

Example:

Current data: "Bloomfield, CT"

Needs to become: Bloomfield

In other words, I need to remove the left quote, and everything after (including) the comma.

there are dozens of different cities in the DB like this.

I can write the simple query that can pull out all of the data that has a comma, or quotes. What I don't seem to get is how to then "erase" the quotes (or the string that includes the comma and everything after it) and then update the DB with this new value.

Help?Most databases have a substring and in-string (or position string) function which you can use in combination to do parsing. My example is oriented to DB2:

SUBSTR(current_data, 2, POSSTR(current_data, ',') - 1)
Result would be Bloomfield

You can use the above in an UPDATE statement:
UPDATE table SET city = SUBSTR(current_data, 2, POSSTR(current_data, ',') - 1)

Originally posted by rexnervous
I have several thousand rows of (text) data that have some incorrect pieces. I need a way to delete part of the data but leave the rest intact.

Example:

Current data: "Bloomfield, CT"

Needs to become: Bloomfield

In other words, I need to remove the left quote, and everything after (including) the comma.

there are dozens of different cities in the DB like this.

I can write the simple query that can pull out all of the data that has a comma, or quotes. What I don't seem to get is how to then "erase" the quotes (or the string that includes the comma and everything after it) and then update the DB with this new value.

Help?|||Also, use the 'REPLACE' and 'GRATER' functions to eliminate the quotes:

UPDATE mytable
SET city = REPLACE(SUBSTR(city, 1
,GREATER(POSSTR(city, ',') - 1,LENGTH(city)))
,'"','')
WHERE POSSTR(city, ',') > 0
OR POSSTR(city, '"') > 0

:cool:|||Thanks you both, will give it a shot. Unfortunately, I'm using MS Access and it doesn't recognize those particular functions, but I think I can replace them.|||For Access take a look at Instr and Mid there is even a Replace. Have fun!

Originally posted by rexnervous
Thanks you both, will give it a shot. Unfortunately, I'm using MS Access and it doesn't recognize those particular functions, but I think I can replace them.