Query SQL Server Table Error

[fvlmasl2]

If in a VB Application i pass a search string to query a SQL Server table, lets say the search string is Bob's Texeco. To test this i ran SQL Server Enterprise Manager and ran a simple SQL Query...

SELECT *
FROM admin.customer_table
WHERE (NAME = 'Bob's Texaco')

When i run this i get the following error....

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:Incorrect syntax near 's'

the ' in between Bob and the s seems to be the issue. How would i correct this.

Thanks
Frank

[Split]

It should be 'Bob''s Texaco', all apostrophes should be doubled

[MartinG242]

You should use SQL Stored Procedures for that (assuming you have SQL Server and not Access).

SP's are designed to handle parameters containing special characters like the '.

This is how your SP should look like:

CREATE PROCEDURE SP_ADMIN_NAME @NAME varchar(50)

AS

set nocount on

BEGIN TRANSACTION

SELECT *
FROM admin.customer_table
WHERE NAME = @NAME

If @@Error <> 0
Begin
Rollback
End
Else
Begin
Commit
End

set nocount off
GO


Then use the following VB code to call the SP:

Public Sub Get_Admin_Name (NM as string)
Dim cmGN As ADODB.Command
Dim ParInput As ADODB.Parameter
Dim rsGN As ADODB.Recordset

Set cmGN = New ADODB.Command
Set ParInput = New ADODB.Parameter
Set rsGN = New ADODB.Recordset

With cmGN
Set .ActiveConnection = Connection
.CommandText = "SP_ADMIN_NAME"
.CommandType = adCmdStoredProc
End With

Set ParInput = cmGN.CreateParameter("Name", adVarChar, adParamInput, 50, NM)
cmGN.Parameters.Append ParInput

Set rsGN = cmGN.Execute
rsGN.MoveFirst
<Variable> = rsGN(0)
<Variable> = rsGN(1)
etc...

TheEnd:
Set rsGN = Nothing
Set cmGN = Nothing
End Sub


Hope this helps

MartinLG

[Leather]

Martins solution seems a bit extreme... I wouldn't create a stored procedure for every query you are ever going to run.


Just remeber that all search strings need to be checked for single quotes. This is pretty simple to handle:


sSearchString = Replace(sSearchString,"'","''")

[Leather]

OR:

sSearchString = Replace(sSearchString, Chr(39), Chr(39) & Chr(39))



Chr(39) is the single quote character.

[MartinG242]

And why not, Leather? Seems to me it has nothing but advantages...

* SP can handle those characters

* SP are much faster than embedded SQL queries (no recompiling)

* writing the query on SQL Server means that your app will contain less code, especially when complex queries.

* If you need to change something in your query, you can do it on the server side, so you do not need to change/recompile/distribute your app... very handy when it is used by multiple users

[fvlmasl2]

Thanks for the input. We had a function (i think) and it was something like this.

str_trans(testffield, "'", "''") this worked great.


Frank