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.
Copy Code code 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 = 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
