Handling string problem (') 2

[sonper]

Hi guys!
Does anybody here has an idea of what seem to be the problem with this code?


Private Sub txtSearch_Change()

Adodc1.RecordSource = "Select * From tblBusMast Where COMMNAME LIKE '" & txtSearch & "%';"
Adodc1.Refresh


End Sub

This code works pretty well but when I came across a string with an (') apostrophe e.g. CARL'S RESTAURANT, I am prompted by a syntax error. Anybody please help!

 

[takyan]

hi,

you should replace the string "CARL'S RESTAURANT" with
"CARL''S RESTAURANT" (2 single quote) by using,

replace(expression, "'", "''&quot

Yan

 

[zemp]

The apostrophie is a special character in SQL server. If it is a part of any string variable it will cause an error. You really only have two ways to deal with this. The first is to remove the character al together or as takyan mentioned, replace the character with something else. If you know what you replaced it with you can always return the apostrophie when you display the data in VB for your users. Thanks and Good Luck!

zemp

 

[strongm]

zemp, the advice that takyan provides is not quite what you think it is. A pair of single quotes in a quoted string tells the SQL interpreter that you actually mean a single quote. It's a special syntax.

 

[zemp]

Learn something new every day, that's why I like Tek-Tips.

Thanks strongm. Thanks and Good Luck!

zemp

 

[sunaj]

Which all sums up to that

Adodc1.RecordSource = "Select * From tblBusMast Where COMMNAME LIKE '" & replace(txtSearch.Text,"'","''&quot & "%';"

should do the trick...
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[zemp]

One more thing. I noticed that SQL Server accepts the double single quote with out a problem. However when returning the data to VB usign an ADO recordset the expression is returned with the double quote intact. Even using enterprise manager the double single quote is displayed.

Does this then mean that I need to re-replace the double single quote with a single single quote, or apostrophie. If this is true then the character I replace the single quote with really doesn't matter. As long as I know what it is then I can display the data properly. Although I am going to assume that using the replace methos mentioned by takyan is the acceptedor preferred method.

Am I missing something here? Why have this special syntax if it displays the added character anyway?
Thanks and Good Luck!

zemp

 

[strongm]

The special syntax is for when you are building SQL statements that are to be submitted for execution by SQL Server. If you directly update a table in Enterprise Manager then then it isn't necessary - EM quite happily sees and deals with a single quote as a single quote, and - more pertinent to your observation - sees two single quotes as two single quotes.

 

[sonper]

Thank you guys for all the info! Zemp is right here in Tek-Tips, you learn something new everyday! But of course I have to give credit to Takyan for his solution to my problem.

To sunaj, this is exactly the code the worked for me!
Thanks again and again for your support

God bless you all!

 

[bros]

that great idea, but when I use crystal report to print data have "'", it give the the error. can anyone help me!!!

thanks