double quotation mark problem with SQL2000

[xenomage]

Hi all,

having a major headache with this problem.

Inside my SQL2000 table, there's a field with the value of "21" colour monitor". This row has an index of 3. Note that there is a double quotation mark in it.

when i want to search for this record, i use the following codes.

strSQL = "SELECT fielddesc FROM tblMonitior WHERE iIndex = 3"
Set rstChk = objConn.Execute(strSQL)

partDesc = rstChk("fielddesc&quot

'****this would give me the value of the description*****'
'*but when i want to use the description with the WHERE clause, like this*****'

strSQL = "SELECT * FROM tblMonitiors WHERE fielddesc = '" & partDesc & "'"
Set rstChkAgain = objConn.Execute(strSQL)


'*****no records is found*****'


does anybody has any idea why?? Any help is appreciated. Thanks.

xeno

 

[codestorm]

Given you are using SQL Server and ADO why not use
stored procedures and ADO Parameters and Command objects.
When you use a Parameter object it doesn't matter what the content is wrt quotes. <insert witticism here>
codestorm

 

[gsc1ugs]

Can i have your connection string... cause access is fine Regards gsc1ugs
&quot;Cant see wood for tree's...!&quot;

 

[gsc1ugs]

Meaning some driver info to connect via SQL server Regards gsc1ugs
&quot;Cant see wood for tree's...!&quot;

 

[gsc1ugs]

Hi... just managed get it going.... dont know what your connection is but here's the code i used

<%
DIM objConn
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot

Application(&quot;DBConnect&quot=whateveryoursis&quot;

objConn.Open(Application(&quot;DBConnect&quot)

strSQL = &quot;SELECT fielddesc FROM cart WHERE iIndex = 3&quot;
Set rstChk = objConn.Execute(strSQL)

partDesc = rstChk(&quot;fielddesc&quot

strSQL = &quot;SELECT * FROM cart WHERE fielddesc = '&quot; & partDesc & &quot;'&quot;
Set rstChkAgain = objConn.Execute(strSQL)

if not rstChkAgain.EOF then
Response.write &quot;results &quot;&rstChkAgain(&quot;fielddesc&quot
else
Response.write &quot;Nothing found&quot;
End if


%> Regards gsc1ugs
&quot;Cant see wood for tree's...!&quot;

 

[xenomage]

Hi,

this is my connection string.

&quot;Driver={SQL Server};UID=mySQLID;PWD=password;DATABASE=dbMyData;SERVER=myServerName&quot;

the problem is that the value that is taken out from the database with the double quotation marks cannot be used to search for the same record again.

Thanks.

xeno

 

[gsc1ugs]

It worked fine with me... try the above code Regards gsc1ugs
&quot;Cant see wood for tree's...!&quot;

 

[cfk]

SQLServer Database Functions

CHAR(x)

The character code for ' is 39
and for &quot; is 34

value = [search parameter]

value = REPLACE(value,CHR(39),&quot;'+CHAR(39)+'&quot

value = REPLACE(value,CHR(34)m&quot;'+CHAR(34)+'&quot

sql = &quot;UPDATE/SELECT FROM ........&quot; & _
&quot;WHERE Field = '&quot; & value & &quot;'&quot;

If your where clause is using '21&quot; colour monitor'
the value will be '21' + CHR(34) + ' colour monitor'.

Always replace single quotes first.

 

[codestorm]

create procedure findMonitorByDesc(@Desc nvarchar(10))
as select * from tblMonitior where fielddesc= @Desc
go
-------------------------------------------------------
dim obj_Command, obj_Parameter
set obj_Command= Server.CreateObject(&quot;ADODB.Command&quot
set obj_Parameter= Server.CreateObject(&quot;ADODB.Parameter&quot
set rstChkAgain= Server.CreateObject(&quot;ADODB.Recordset&quot
obj_Command.ActiveConnection= objConn
obj_Command.CommandText= &quot;findMonitorByDesc&quot;
obj_Command.CommandType= 4'adCmdStoredProc
set obj_Parameter = obj_Command.CreateParameter(&quot;Desc&quot;, 129, 1, len(partDesc), partDesc)
obj_Command.Parameters.Append obj_Parameter
rstChkAgain.Open obj_Command
<insert witticism here>
codestorm