Two ways to handle single quotes in SQL Server

server| techniques and database are frequently used in SQL statements, unless you are all bound by the control of the way, but the use of control binding the way there is a poor flexibility, inefficient, weak and so on shortcomings. As a result, most programmers rarely or rarely use this binding approach. In the unbound way, many programmers mostly ignore
The special handling of single quotes, once the query condition of the SQL statement of the variables have single quotes, the database engine will report errors to indicate that the SQL syntax is not correct, I found that there are two ways to solve and deal with this single quotation mark problem (in VB as an example).

----Method One: Use escape characters to process SQL statements. The following function can be invoked before executing the SQL statement, and the result of the processing will produce the correct knot
Fruit.

Function processstr (str as String)
Dim Pos as Integer
Dim Stedest as String
pos = InStr (str, "'")

While POS > 0
str = Mid (str, 1, POS) & "'" & Mid (str, POS + 1)
pos = INSTR (pos + 2, str, "'")
Wend
PROCESSSTR = str
End Function
----where the str parameter is your SQL string. Once the function finds a single quotation mark in the string, it is preceded by a single quote.

----Method Two: Use the parameters in the data object. You can use the Adodb.command object to pass strings that contain single quotes to the command, and then perform a search
Inquiries, etc. can be done.

----The above two methods are compared, the method increases the system processing time, the method is simple and efficient, if the stored procedure is adopted, then the parameter is passed to the stored
Process, stored procedures are precompiled so that the system is more efficient.

----The following examples to illustrate.

----Create a new project with a form (FORM1), two command buttons, and a MSFlexGrid name: Command1,
Command2,msflexgrid1, a Combox (COMBO1), whose contents are preset to "Paolo ' F", "Paolo ' F". Command1 Demo Method One,
Command2 demonstrates method two, MSFlexGrid1 storage method two query (SELECT) results. For other SQL operations (INSERT, Delter, Updatae) methods
Very similar, I will not repeat. This example uses the employee table in the pubs database in SQL Server and can use SQL syntax to put two records
The fname in is changed to "Paolo ' F", "Paolo ' F". The SQL syntax is as follows:

Update employee set fname= "Paolo '" ' F "
where emp_id= ' pma42628m '
Update employee set Fname= "Paolo ' F"
where emp_id= ' pma42628m '
The----procedures are as follows:

----first add the preceding function.

----Declare the following variables in the general purpose of the form:

Dim cnn1 as ADODB. Connection ' connection
Dim mycommand as Adodb.command ' command
Dim Rstbyquery as ADODB. Recordset ' result set
Dim strcnn As String ' connection string
Private Sub Form_Load ()
Set cnn1 = New ADODB. Connection ' generates a connection
strcnn = "Driver={sql Server};" & _
"Server=zyx_pc;uid=sa;pwd=pcdc;database=pubs" '
No system data source uses connection string

' strcnn = ' dsn=mydsn; Uid=sa; pwd=; "
' Database=pubs;driver={sql Server}; Server=gzl_pc "'
If the system data source MyDSN points to the pubs database, you can also use the
Cnn1. Open strcnn,,, 0 ' Turn on connection
End Sub
Private Sub Command1_Click () ' Demo character processing
Dim I as Integer
Dim J as Integer
Set parm = New ADODB. Parameter
Set mycommand = New Adodb.command

Dim Str as String
str = Combo1.text
str = PROCESSSTR (str)
mycommand. ActiveConnection = Cnn1 '
Specifies the current active connection for this command
myCommand.CommandText = "SELECT * FROM
Employee WHERE fname = ' "& Str &" ""
myCommand.CommandType = adCmdText ' indicates command type
Set rstbyquery = New ADODB. Recordset
Set rstbyquery = mycommand. Execute ()
i = 0
Do as not rstbyquery.eof
Number of records saved in i = i + 1 ' I
Rstbyquery.movenext
Loop
Msflexgrid1.rows = i + 1 ' dynamically set MSFlexGrid rows and columns
Msflexgrid1.cols = RstByQuery.Fields.count + 1
Msflexgrid1.row = 0
For i = 0 to Rstbyquery.fields.count-1
Msflexgrid1.col = i + 1
Msflexgrid1.text = RstByQuery.Fields.Item (i). Name
Next ' Sets the title of the first row and fills it with a domain name

i = 0
' Set rstbyquery = mycommand. Execute ()
Rstbyquery.requery
Do as not rstbyquery.eof
i = i + 1
Msflexgrid1.row = i ' OK line
For j = 0 to Rstbyquery.fields.count-1
Msflexgrid1.col = j + 1
Msflexgrid1.text = Rstbyquery (j) ' Fills all columns
Next
Rstbyquery.movenext

Loop ' This loop is used to populate the contents of MSFlexGrid
End Sub
Private Sub Command2_Click () ' parameter method
Dim I as Integer
Dim J as Integer

Set parm = New ADODB. Parameter
Set mycommand = New Adodb.command

' Parm_jobid. Name = "Name1" This line can be ommited
Parm. Type = Adchar ' parameter types
Parm. Size = 10 ' parameter length
Parm. Direction = adParamInput ' parameter direction, input or output
Parm. Value = Combo1.text ' parameter
mycommand. Parameters.Append parm ' Add parameter



mycommand. ActiveConnection = Cnn1 '
Specifies the current active connection for this command
myCommand.CommandText = "SELECT *
From employee where fname =? "
myCommand.CommandType = adCmdText ' indicates command type
Set rstbyquery = New ADODB. Recordset
Set rstbyquery = mycommand. Execute ()
i = 0
Do as not rstbyquery.eof
Number of records saved in i = i + 1 ' I
Rstbyquery.movenext
Loop
Msflexgrid1.rows = i + 1 ' dynamically set MSFlexGrid rows and columns
Msflexgrid1.cols = RstByQuery.Fields.count + 1
Msflexgrid1.row = 0
For i = 0 to Rstbyquery.fields.count-1
Msflexgrid1.col = i + 1
Msflexgrid1.text = RstByQuery.Fields.Item (i). Name
Next ' Sets the title of the first row and fills it with a domain name

i = 0
Rstbyquery.requery
Do as not rstbyquery.eof
i = i + 1
Msflexgrid1.row = i ' OK line
For j = 0 to Rstbyquery.fields.count-1
Msflexgrid1.col = j + 1
Msflexgrid1.text = Rstbyquery (j) ' Fills all columns
Next
Rstbyquery.movenext

Loop ' This loop is used to populate the contents of MSFlexGrid
End Sub
----Query section can use stored procedures to improve processing efficiency and reduce network traffic.

----This program is debugged on NT WORKSTATION 4.0 SP4, SQL SERVER 7.0.