Two ways to deal with single quotes in Ms-sql Server

Use SQL statements frequently when dealing with databases, unless you are all bound by controls, but there are drawbacks to using control bindings in the form of poor flexibility, inefficiency, weak functionality, and so on. As a result, most programmers rarely or rarely use this binding approach. In the case of non binding, many programmers overlook the special handling of single quotes. Once the variables of the query condition of the SQL statement appear in single quotes, the database engine complains that the SQL syntax is incorrect, and I find that there are two ways to solve and deal with this single quote (in VB 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 result.

The code is as follows:

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 a string containing single quotes to the command, and then execute the query.

----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 and then passed the parameter to the stored procedure, the stored procedure is precompiled, so the system is more efficient.

----The following examples to illustrate.

----Create a new project with a form (FORM1), two command buttons, a MSFlexGrid, the name: Command1,command2,msflexgrid1, a Combox (COMBO1), and its contents are preset to " Paolo ' f ', ' Paolo ' f '. Command1 Demo Method One, Command2 demo method Two, MSFlexGrid1 storage method two query (SELECT) results. For other SQL operations (INSERT, Delter, Updatae) methods are very similar, I will not repeat. The example uses the employee table in the pubs database in SQL Server, and you can change the fname in two records to "Paolo ' F" and "Paolo ' F" in SQL syntax. 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 = adparamin put ' 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