recordset vs. SQL execution

[ahmun]

Hi all,

I've heard a lot of advice from the gurus of this forum that executing SQL runs much quicker than opening a recordset, setting the field values, and updating.

I was wondering if anybody follows a standard on how to access/manipulate database info.

I'd like to find a way where I don't have to do any major string replacing/formatting to capture user input.

For example: a Last Name field may contain "O'Hare"

If I write a SQL statment there would be a problem because of the ' mark in the name.

sSQL = "UPDATE Table SET strLast = '" & Request.Form("strLast") & "' Where critera..."

Wheras if I used a recordset:

sSQL = "SELECT * From Table Where [some criteria...]"
myRS.open
myRS("strLast") = Request.Form("strLast")
etc...

I don't have any problems with whatever content. What if the user accidentally enters double-quotes when I thought I'd be clever and ""escape my doublequote in the sSQL string?

Earnie Eng

 

[Tarwn]

Doublequotes (?) don't need to be escaped in an SQL string, only single quotes needs to be. If the user accidentally typed two single quotes and you double that up to four, it still goes into the database as two single quotes.

I generally create some sort of function to handle the replace for me just because I am lazy. I don't like typing Replace(..."'","''") every time. So what I'll do is createa function like:

Function SqlQ(str)
   SqlQ = Replace(str,"'","''")
End Function

Then after I finish my code I will use search and replace to make the function name more understandable (ie, like SqlCleanQuotes). While I'm coding I have a rewally short function to type though

-T

 

[ahmun]

I've seen reference to that method of replacing single quotes with two single quotes, but I've always glanced over it.

Just to verify:
in a SQL statement, putting two single quotes is equavelent of escaping that character so that the sql engine doesn't misinterpret the info?

Earnie Eng

 

[Tarwn]

Yep. basically when you double them up like that the SQL Engine goes "Oh, a single quote character" instead of saying "oh, thats the beginning or end of a string/date/etc...where's the rest?"