How to handle quatation in a string in SQL statement?? 2

[qajussi]

Hi
I am writing a simple sql statement and saw there are \"\' in the records. How can I handle it?

"Select Title From TblBook where BookID =" & BookID

BookID contains "1223.34sometext"title"andauthor's name"

Here is the actual code:
objRecordset.Open "SELECT [Company Data].[CompanyName] FROM [Company Data] ORDER BY [Company Data].[CompanyName] WHERE [Company Data].[MTCR ID] =" & CHAR(34) & VALUE & CHAR(34), objConnection, adOpenStatic, adLockOptimistic

VALUE IS "2.2.3.44B.THERE ARE SOME TEXT IN "STRING". i DON'T NOW HOW TO"

How do I hand this value with \"\'\.\, character??
Can you help me?
Thank you.

 

[PHV]

WHERE [Company Data].[MTCR ID]=[tt]'" & Replace(VALUE, "'", "''") & "'",[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[qajussi]

Hi PHV!

Sorry I don't understand what you did here with the REPLACE()...

Is that a vbscript??
so I am replaceing single quote with double quote??

My record has double and single quote and possibly period and coma.
ex)
value = "1.2.3.a.This title is called "Super book" by Jane's mother, Kim Smith"..

The Client sent me an access database and he used this field as a primary key and I am having a hard time writing a SQL queries..

Thanks for your help.

 

[PHV]

My code should works if acces 2000 or above.
Have you tried it ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[qajussi]

yes..
I am still getting same error..

 

[PHV]

I am still getting same error
Any chance you could post the whole error message and the highlighted code when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

I say OUCH.

If you could use DAO in stead, I think perhaps you should be able to do it with double quoting:

[tt]set rs=currentdb.openrecordset("SELECT [Company Data].[CompanyName] FROM [Company Data] ORDER BY [Company Data].[CompanyName] WHERE [Company Data].[MTCR ID] =""" & VALUE & """"[/tt]

Roy-Vidar

 

[RoyVidar]

Got it to work in ADO too, but I'm not sure this will give you an updateable recordset.

I used the command object and first one version using a stored query, with a defined parameter, looking something like this:

[tt]Parameters [MyBookID] Text (255)
SELECT [Company Data].[CompanyName] FROM [Company Data] ORDER BY [Company Data].[CompanyName] WHERE [Company Data].[MTCR ID] =[MyBookID][/tt]

Then:

[tt]dim rs as adodb.recordset
dim cmd as adodb.command
set cmd=new adodb.command
with cmd
.activeconnection = currentproject.connection ' or your conn
.properties("Jet OLEDB:Stored Query") = True
.commmandtext = "name of the query"
.parameters("[MyBookID]") = BookID
set rs = .execute
end with[/tt]

Using sql string, perhaps something like this

[tt]dim rs as adodb.recordset
dim cmd as adodb.command
dim strsql as string
set cmd=new adodb.command
strsql = "SELECT [Company Data].[CompanyName] FROM [Company Data] " & _
"ORDER BY [Company Data].[CompanyName] " & _
"WHERE [Company Data].[MTCR ID] = [MyBookID]"
with cmd
.activeconnection = currentproject.connection ' or your conn
.properties("Jet OLEDB:Stored Query") = False
.commmandtext = strsql
.parameters("[MyBookID]") = BookID
set rs = .execute
end with[/tt]

But again, this will provide a forwardonly, readonly recordset. I'm not sure how to make it updateable, should that be necessary.

Roy-Vidar

 

[RoyVidar]

To get an updateable recordset, one way can be to open the recordset without criterion, then use either the .Find method (a bit slow, cause it requeries the recordset and only allows one column find) or the .Filter property (faster). For both, one can do some equivalent of DAO's "doublequoting" thru using the hash (#) symbol.

[tt]objRecordset.Open "SELECT [Company Data].[CompanyName] FROM [Company Data] ORDER BY [Company Data].[CompanyName]", objConnection, adOpenStatic, adLockOptimistic

objRecordset.Find "[MTCR ID] = #" & VALUE & "#"
' or
objRecordset.Filter = "[MTCR ID] = #" & VALUE & "#"[/tt]

To remove the filter again, use
[tt]objRecordset.Filter = adFilterNone[/tt]

I see I missed the closing parenthesis at the DAO method above, and I think I'd also consider using a naming convention on the criterion. Value is a property of lot's of objects, and could create anomalities - strValue?

Roy-Vidar

 

[qajussi]

Hi PHV!
Thank you so much for your help.

objRecordset.Open "SELECT [Company Data].[CompanyName] FROM [Company Data] ORDER BY [Company Data].[CompanyName] WHERE [Company Data].[MTCR ID]= '" & Replace(OptionAListValue, "'", "''") & "'", objConnection, adOpenStatic, adLockOptimistic

OptionAListValue comes from the
Table which equals MTCR ID which contains the text like these:

I:01.A. some text, another text AND another text
I:02.D.1. ccccc e specially designed or modified for the "use" of "some textdfdf" specified in 2.B.1.
And I get the error from the above code.

Syntax error(missing operator in query expression'[Company Data].[CompanyName] WHERE [Company Data].[MTCR ID] = I:01.A. some text, another text AND another text"

I might have to clean up the client's table and get rid of those double quotes or something..
THank you again.

 

[PHV]

Seems you forgot the single quote just after equal sign if I trust the error message.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[qajussi]

No.
There is a single quote after the equal sign.
I can't figure out what wrong with the code.
Thank you very much for your time.

 

[qajussi]

Hi RoyVidar!

Thank you so much for your time and help.
I am sorry I am a newbie in VB.

I will try out your code later and let you know if it works.
Thanks million for your input.