Dealing with single quotes text fields and SQL

CodeCharge Studio

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

Dealing with single quotes text fields and SQL

Author

Message

RChampagne

Posted: 09/30/2005, 1:41 PM

How can I deal with embedded single quotes in an SQL? Looking at the ToSql()
function in the Common.asp, I notice that this case isn't considered.

Yet, the preview version 3.0 has added code to replace single quotes with 2
single quotes.

If needEscape And CStr(mValue) <> "" Then mValue =
EscapeChars(mValue)
ToSQL = "'" & mValue & "'"

Can I simply add this 'case' to the nested If statements in Common.asp?

dhodgdon

Posts: 80

Posted: 09/30/2005, 2:27 PM

I get around this by first assigning the SQL to a variable and then use the Execute method on the variable.

dim davgfuciton  
davgfunction = "UPDATE Sessions INNER JOIN Results ON Sessions.SessionID = Results.SessionID SET " &_  
"Sessions.OverallQuality = DAvg(""[ovrallsesquality]"",""results"",""[sessionid]='" & urlsession & "'""), " &_  
"Sessions.OverallClarity = DAvg(""[ovrallclarity]"",""results"",""[sessionid]='" & urlsession & "'""), " &_  
"Sessions.OverallRefMaterials = DAvg(""[ovrallrefmaterials]"",""results"",""[sessionid]='" & urlsession & "'""), " &_  
"Sessions.OverallPresMaterials = DAvg(""[ovrallpresmaterials]"",""results"",""[sessionid]='" & urlsession & "'""), " &_  
"Sessions.OverallPrework = DAvg(""[ovrallprework]"",""results"",""[sessionid]='" & urlsession & "'""), " &_  
"Sessions.OverallSpeaker = DAvg(""[ovrallspkrquality]"",""results"",""[sessionid]='" & urlsession & "'""), " &_  
"Sessions.OverallUsefulness = DAvg(""[ovrallusefulness]"",""results"",""[sessionid]='" & urlsession & "'""), " &_  
"Sessions.NumOfResponses = DCount(""[ovrallsesquality]"",""results"",""[sessionid]='" & urlsession & "'"") " &_  
"WHERE (((Sessions.SessionID)=""" & urlsession & """));"  
  
dbconnection.open  
	dbconnection.Execute davgfunction

_________________
Regards,
David Hodgdon

Benjamin Krajmalnik

Posted: 09/30/2005, 3:34 PM

Check common.asp. There is a CCToSQL function.

Target = CCToSQL(Source, "text")

The way you pass a single quote to SQL (such as O'Brien) is 'O''Brien'

RChampagne

Posted: 10/03/2005, 4:59 PM

Benjamin,

Thank you ... It did the trick.

Regards,
Roger.

"Benjamin Krajmalnik" <kraj@illumen.com> wrote in message
news:dhkeh9$ogs$1@news.codecharge.com...
> Check common.asp. There is a CCToSQL function.
>
> Target = CCToSQL(Source, "text")
>
>
> The way you pass a single quote to SQL (such as O'Brien) is 'O''Brien'
>
>

These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home | Search | Members | Register | Login