server| techniques----and database to frequently use SQL statements, unless you are all the way to bind the control, but the way the control is bound by the flexibility of the poor,
Low efficiency, weak function and so on shortcomings. As a result, most programmers rarely or rarely use this binding approach. In the case of non binding, many programmers suddenly
Slightly to the single quotation mark special processing, once the SQL statement query condition variable has the single quotation mark appearance, the database engine will report the error to indicate the SQL syntax is incorrect, I send
There are two ways to solve and deal with this single quotation mark problem (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 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
