Scott posed the question below: "By chance is there a reference to when and

where you would use ' or " or &, etc?"

Here's my 2 sense worth...



With regards to ' and ", it really depends on what you are doing.

In ASP you generally put strings in "

	sString = "Hello World!"

If you want an actual " in the string you have to double the " otherwise VB

will stop at the next " and throw an error.

	sString = "The man said ""Hello"" to his friend."

Result: The man said "Hello" to his friend.

In SQL you generally put them in '

	select * from sometable where somecolumn='somestring';

& is really (as far as I have seen) only used in concatenating VB strings.

	sName = "Scott"

	sString = "Your name is " & sName & "."

Result: Your name is Scott.



If you were to build a SQL query from some ASP variables, you combine them

(as you have found out now! ;-)

	sSQLQuery = "select * from sometable where somecolumn='" & sString & "';"

Of course, you also need to modify SQL queries that contain strings that

contain strings, e.g:

When run, this query will break:

	sString = "Ain't gonna work"

	sSQLQuery = "insert into sometable(somecolumn) values('" & sString & "');"

Resulting query: insert into sometable(somecolumn) values('Ain't gonna

work');

Breaks on the ' in "Ain't".

Need to modify it for SQL.  SQL need to see 2 ' like VB wants 2 " in a

string to represent one.

Use a function like replace to take care of it.

This should take care of the problem...

	sString = "This'll work"

	sSQLQuery = "insert into sometable(somecolumn) values('" & Replace(sString,

"'", "''") & "');"

Resulting query: insert into sometable(somecolumn) values('This''ll work');

SQL will be happy with that.

One thing I have is a function that cleans up SQL strings. Something like

"FormatSQLString(sString, nLength)".  When I am building a SQL query I call

this function, passing in the string (let's say from a form input) and

desired string length. The function takes care of the special characters (')

and truncates the string at the passed length, among other things (more to

do with SQL problems). I can go into more detail about this type of funtion

if you are interested.



Hope this answered your question Scott.



(I wish I knew about this list while _I_ was learning ASP! :-)

-Peter L



-----Original Message-----

From: Scott.Taylor@E... [mailto:Scott.Taylor@E...]

Sent: Tuesday, January 23, 2001 9:34 AM

To: ASP Databases

Cc: ASP Databases; scott.taylor@E...

Subject: [asp_databases] Re: SQL Queries to Access using Session IDs???







Wow, out of all the combinations of characters I tried to make it work, I

did

not try that one. Thanks sooo much - it worked - obviously you knew that ;)



Thanks for helping me over a hurdle. By chance is there a reference to when

and

where you would use ' or " or &, etc? I am finishing up the "ASP in 21 Days"

but

it leaves me a bit confused on some topics.



BRgds,

Scott Taylor