And databases frequently used to deal with SQL statement, unless you are bound all the way with the control, but control is bound by the existence of flexibility in the way poor, inefficient, weak features, shortcomings. Therefore, most of the programmers with little or less the way in this binding. The non-binding way that many programmers have largely ignored the special treatment of single quotation marks, if the query SQL statement variables that appear in single quotation marks, the database engine that SQL syntax error will not, I found there are two ways can be solved and issues to deal with this single quote (in VB as an example).
Method 1: Use the escape character processing SQL statements. The following functions can be invoked in the implementation of the SQL statement before the implementation of the results of treatment can produce the correct statement
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
Str parameter which is your SQL string. Once the string function in single quotes appear, added something in front of single quotes.
Method 2: Using the data object parameters. Can ADODB.COMMAND object to a string containing single quotation marks passed to COMMAND, then run check
Exercise and other operations can.
Comparing these two methods, one method to increase the system processing time, method two simple, efficient, and if the use of stored procedures, and then pass parameters to stored procedures, stored procedure is precompiled, so the system more efficient.
The following examples illustrate the.
Create a new project, the project has a form (Form1), two command buttons, a MSFlexGrid, names were: Command1,
Command2, MSFlexGrid1, a COMBOX (COMBO1), its content pre-set to "Paolo''f", "Paolo'f". Demonstration of a Command1, Command2 presentation methods 2, MSFlexGrid1 storage method two query (SELECT) results. For other SQL operations (INSERT, DELTER, UPDATAE) is extremely similar, I will not repeat them. Examples used in the PUBS database in SQL SERVER in the EMPLOYEE table, and can use SQL syntax to two of the record FNAME to "Paolo''f", "Paolo'f". 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'
Procedures are as follows:
First of all, to join the previous function. In the form of common variable in the following statement:
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 'to generate a connection
strCnn = "driver = (SQL Server);" & _
"Server = ZYX_pc; uid = sa; pwd = PCDC; database = pubs" '
No system data source using the connection string
'StrCnn = "DSN = mydsn; UID = sa; PWD =;"
'DATABASE = pubs; Driver = (SQL Server); SERVER = gzl_pc "'
If the data source MYDSN point PUBS database, also can be used
cnn1.Open strCnn,,, 0 'Open the 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 '
The command specifies the current active connections
mycommand.CommandText = "select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from
employee where fname = '"& str &"' "
mycommand.CommandType = adCmdText 'that type of command
Set rstByQuery = New ADODB.Recordset
Set rstByQuery = mycommand.Execute ()
i = 0
Do While Not rstByQuery.EOF
i = i + 1 'i in the number of record-keeping
rstByQuery.MoveNext
Loop
MSFlexGrid1.Rows = i + 1 'dynamic set of rows and columns MSFlexGrid
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 'set the first line of the title, filled with a domain name
i = 0
'Set rstByQuery = mycommand.Execute ()
rstByQuery.Requery
Do While Not rstByQuery.EOF
i = i + 1
MSFlexGrid1.Row = i 'OK OK
For j = 0 To rstByQuery.Fields.count - 1
MSFlexGrid1.Col = j + 1
MSFlexGrid1.Text = rstByQuery (j) 'add all the listed charge
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 type
parm.Size = 10 'parameter length
parm.Direction = adParamInput 'parameter direction, input or output
parm.Value = Combo1.Text 'parameter
mycommand.Parameters.Append parm 'adding parameters
mycommand.ActiveConnection = cnn1 '
The command specifies the current active connections
mycommand.CommandText = "select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp
from employee where fname =? "
mycommand.CommandType = adCmdText 'that type of command
Set rstByQuery = New ADODB.Recordset
Set rstByQuery = mycommand.Execute ()
i = 0
Do While Not rstByQuery.EOF
i = i + 1 'i in the number of record-keeping
rstByQuery.MoveNext
Loop
MSFlexGrid1.Rows = i + 1 'dynamic set of rows and columns MSFlexGrid
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 'set the first line of the title, filled with a domain name
i = 0
rstByQuery.Requery
Do While Not rstByQuery.EOF
i = i + 1
MSFlexGrid1.Row = i 'OK OK
For j = 0 To rstByQuery.Fields.count - 1
MSFlexGrid1.Col = j + 1
MSFlexGrid1.Text = rstByQuery (j) 'add all the listed charge
Next
rstByQuery.MoveNext
Loop 'This loop is used to populate the contents of MSFlexGrid
End Sub
Enquiries can be viewed using stored procedures to improve processing efficiency, reduce network traffic. This procedure in NT WORKSTATION 4.0 SP4, SQL SERVER 7.0 on debugging through.