I feel like my problem is trying to get different technologies to "play 

nice".  I have a web based application that uses ASP Pages, JavaScript, 

ADO Recordsets and SQL statements to function as a front end to a backend 

Access 97 database for Data entry only (no reports or calculations).  

Should be simple, right?  For 17 of the 19 fields it works like I would 

like.  The two fields that do not work contain single AND double quotes as 

part of the value of those fields (i.e. 10' Pipe or "A" Thread Type) and I 

am having trouble making all the different ways I need to present these 

two characters to the different technologies in ways they like while 

maintaing the validity of the data values.



Let's break it down.

From a blank form the employee has the option to lookup up valid values 

for each field by "onClicking" an icon next to that form field.  

This "event" opens a new window with a list of values filtered by search 

criteria (using a SQL Like statement here) entered by employee (values are 

an ADO Recordset of one of 16 lookup tables in the Access97 DB file.  

Which Recordset is determined by a Select Case on one ASP page.) that the 

employee can select one value and the form field is populated with the 

value selected.  My first error is in two of the fields there are values 

that have single AND double quotes and I receive a runtime 

error "unterminated string constant" for each instance and then all the 

records are displayed, even the ones with single and double quotes.



To solve this when I display the recordset I run:



'Page through Recordset

If Not pdRS.EOF Then

total = 0

x = 0

For intI = 1 to pdRS.PageSize

myVarPD = pdRS("ProdFam_Desc")

myVarPD = Replace(myVarPD,""","~")

myVarPD = Replace(myVarPD,"'","@")

'Response.Write(myVarPD)



    If x = 0 Then

    color = "#A6B9EA"

    font = "#483D8B"

    x = 1

    Else

    color = "#CFCDF5"

    font = "#483D8B"

    x = 0

    End If

    Response.Write "<TR>"

    Response.Write "<TD></TD>"

    Response.Write "<TD bgcolor=" & color & " onClick=""returnData('" & 

myVarPD & "')"";><A style=cursor:'hand'><U><FONT size=1 face=verdana 

color=" & font & ">" & pdRS("ProdFam_Desc") & "</FONT></U></A></TD>"

    Response.Write "<TD bgcolor=" & color & "></TD>"

    Response.Write "</TR>"

   pdRS.MoveNext

If pdRS.EOF Then Exit For

    Total = Total + 1

Next

End If



This works for the single quote but not the double quote when an employee 

enters for example 10' on the form and clicks on lookup then the recordset 

returns just values starting with 10'.  Nice, but when "A" is entered ALL 

the values of the recordset are returned.  Not what we want.



Now when the value is selected in the lookup table I do want the @ or ~ to 

display on the form so I need to convert it to something else more 

correct. The solution for this is I run an JavaScript onClick event before 

passing the values back to the form:



	function RepProFamDes(myVarPD)

	{

	var r, re;

	var ss = myVarPD;

	   re = /\@/g;

	   rt = /\~/g;

	   r = ss.replace(re, "''");

	   r = ss.replace(rt, "'");

	   //alert(r);

	   parent.opener.form1.ProFamDes.value = r;

	return(r);

	}



Now the form is completed and submitted. Before the data needs to be 

massaged before inserting or updating so that double quotes do not wind up 

as two single quotes as a field value and then doubled and the single 

quotes need to be doubled so that SQL does not choke.  So before the 

Insert or Update statement I take the QueryString and Replace the single 

AND double quote:



Function checkIT(strData)

    strData = Replace(strData, "''", """")

    strData = Replace(strData, "'", "''")

    checkIT = strData

End Function



ProFamDes = checkIT(Request.Form("ProFamDes"))

ProTit = checkIT(Request.Form("ProTit"))





This is the next problem area because the record is created and the values 

are entered but when I try to use a form to view the record all the data 

shows up except those values that have double quotes.  My Insert statement 

is:



		newSQL = "INSERT INTO tblProductFamily_Info "

		newSQL = newSQL & "("

		newSQL = newSQL & "ProdFam_No, "

		newSQL = newSQL & "Division, "

		newSQL = newSQL & "LabOffice_Code, "

		newSQL = newSQL & "Product_Family, "

		newSQL = newSQL & "ProdFam_Desc, "

		newSQL = newSQL & "ProdModel, "

		newSQL = newSQL & "ProdTitle, "

		newSQL = newSQL & "Prod_LineCode, "

		newSQL = newSQL & "Prod_Hierarchy, "

		newSQL = newSQL & "Project_DashNo, "

		newSQL = newSQL & "FunctionCode, "

		newSQL = newSQL & "Revenue_ClassCode, "

		newSQL = newSQL & "WHO_Rent_Sale, "

		newSQL = newSQL & "EHO_Rent_Sale, "

		newSQL = newSQL & "SAPClassification, "

		newSQL = newSQL & "CurrentTerminal_Digit, "

		newSQL = newSQL & "BasicMatl_Desc, "

		newSQL = newSQL & "tDate, "

		newSQL = newSQL & "LogonID"

		newSQL = newSQL & ")"

		newSQL = newSQL & " VALUES "

		newSQL = newSQL & "("

		newSQL = newSQL & "'" & Trim(Request("ProFamNum")) & "', "

		newSQL = newSQL & "'" & Trim(Request("Division1")) & "', "

		newSQL = newSQL & "'" & Trim(Request("LabOffCod")) & "', "

		newSQL = newSQL & "'" & Trim(Request("ProFam1")) & "', "

		newSQL = newSQL & "'" & Trim(ProFamDes) & "', "

		newSQL = newSQL & "'" & Trim(Request("ProMod")) & "', "

		newSQL = newSQL & "'" & Trim(ProTit) & "', "

		newSQL = newSQL & "'" & Trim(Request("ProLinCod")) & "', "

		newSQL = newSQL & "'" & Trim(Request("ProHie")) & "', "

		newSQL = newSQL & "'" & Trim(Request("EngProDasNum")) 

& "', "

		newSQL = newSQL & "'" & Trim(Request("FunSalCod")) & "', "

		newSQL = newSQL & "'" & Trim(Request("RevClaCod")) & "', "

		newSQL = newSQL & "'" & Trim(Request("WHORenSal")) & "', "

		newSQL = newSQL & "'" & Trim(Request("EHORenSal")) & "', "

		newSQL = newSQL & "'" & Trim(Request("CurTerDig")) & "', "

		newSQL = newSQL & "'" & Trim(Request("SapClaDes")) & "', "

		newSQL = newSQL & "'" & Trim(Request("BasMatDes")) & "', "

		newSQL = newSQL & "'" & Trim(Request("tDate")) & "', "

		newSQL = newSQL & "'" & Trim(Request("LogonID")) & "'"

		newSQL = newSQL & ")"

		'Response.Write("This is newSQL: " & "<br>" & newSQL 

& "<br>")

		conn.Execute(newSQL)



That is all I want it to do.  This may wind up being something simple but 

I have looked at this till my eyes bled and I am not afraid to ask if 

there may be a better way (I may be complicating a simple process but this 

is all I have come up with without asking for help).  That is why we are 

here, right?



I know this makes sense to me asking the question but if there is any gaps 

of code or logic needed to be explained please let me know and I will 

provide more information.  If anybody has any ideas or comments I would 

appreciate it (especially if anyone can help educate me on why it seems 

ADO when creating a recordset does not seem to like handling single or 

double quotes)...



Thanks,

Jerry Mounce

Intranet Developer

Baker Oil Tools

replace the double quotes with the chr equiv using replace command.....

what i mean is set a var to the chr equiv of double qoutes. Had similar

problem and it solved it.... ie vdblqoute = chr(34) (I thinks)

and chr(126) for  ~

vdblqoutes = chr(34)



vstrg = "DOGS"

vstrg = vdblqoutes & vstrg & vdblqoutes

 hope it helps ;-)

Jerry,



I got lost about half way through your post, but did pick up a few points.



a) Only single quotes need to be replaced when using SQL statements - double

quotes are fine. Single quotes are *delimiters* in SQL statements, ie they

are reserved for a particular purpose. When you want to use a literal single

quote, you need to double it. Double quotes ", have no special purpose in

SQL statements, hence you don't need to do anything with them



b) You can replace single quotes using a function like this:

www.adopenstatic.com.resources/code/SafeSQL.asp



strVar = "Some variable with ' and "" in it'

' strVar = Some variable with ' and " in it

' note that I doubled the " above to put the value

' into strVar, because " is a delimiter in VBScript

' strVar only contains a single " though!



strSQL _

    "INSERT INTO table1 " & _

    "(field1) " & _

    "VALUES ( " & _

    "'" & SafeSQL(strVar) & "'" & _

    ")"



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Jerry Mounce" <jerry_mounce@m...>

To: "ASP Web HowTo" <asp_web_howto@p...>

Sent: Saturday, November 10, 2001 11:48 PM

Subject: [asp_web_howto] single AND double quotes in DB fields





: I feel like my problem is trying to get different technologies to "play

: nice".  I have a web based application that uses ASP Pages, JavaScript,

: ADO Recordsets and SQL statements to function as a front end to a backend

: Access 97 database for Data entry only (no reports or calculations).

: Should be simple, right?  For 17 of the 19 fields it works like I would

: like.  The two fields that do not work contain single AND double quotes as

: part of the value of those fields (i.e. 10' Pipe or "A" Thread Type) and I

: am having trouble making all the different ways I need to present these

: two characters to the different technologies in ways they like while

: maintaing the validity of the data values.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~