Two solutions to single quotes in MS-SQL Server

SQL statements are frequently used when dealing with databases. Unless you bind all controls, the method of binding controls has disadvantages such as poor flexibility, low efficiency, and weak functions. Therefore, most programmers seldom or seldom use this binding method. When a non-binding method is adopted, many programmers ignore the special processing of single quotes. Once the variables in the SQL statement query condition appear in single quotes, the database engine reports an error indicating that the SQL syntax is incorrect, I have found that there are two ways to solve and solve this single quotation mark problem (in the example of VB ).

---- Method 1: Use escape characters to process SQL statements. The following functions can be called before the SQL statement is executed. The correct results can be generated after the processed results are executed.

Copy codeThe 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

---- The str parameter is your SQL string. Once a single quotation mark is found in a string, the function adds a single quotation mark.
---- Method 2: Use the parameters in the data object. You can use the ADODB. COMMAND object to pass strings containing single quotes to the COMMAND, and then execute the query and other operations.

---- Compared with the above two methods, method 1 adds the system processing time and method 2 is concise and efficient. If a stored procedure is used, then the parameters are passed to the stored procedure, the stored procedure is pre-compiled, so that the system is more efficient.

---- The following is an example.

---- Create a project with a form (Form1), two command buttons, one MSFlexGrid, named Command1, Command2, MSFlexGrid1, and one COMBOX (COMBO1 ), its content is pre-set to "Paolo ''f" and "Paolo ''f". Command1 demonstration method 1, Command2 demonstration method 2, MSFlexGrid1 storage method 2 query (SELECT) results. Other SQL operations (INSERT, DELTER, and UPDATAE) are very similar. I will not repeat them here. In this example, we use the SQL server pubs database's EMPLOYEE table. At the same time, we can use SQL syntax to change the FNAME in the two records to "Paolo ''f" and "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 program is as follows:
---- First, add the previous function.

---- Declare the following variables in the common form:

Dim cnn1 As ADODB. Connection 'Connection
Dim mycommand As ADODB. Command
Dim rstByQuery As ADODB. Recordset
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 uses a 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
Cnn1.Open strCnn, 0' open connection
End Sub
Private Sub commandateclick () 'demonstrate 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 of the command
Mycommand. CommandText = "select * from
Employee where fname = '& str &'
Mycommand. CommandType = ad1_text 'indicates the command type
Set rstByQuery = New ADODB. Recordset
Set rstByQuery = mycommand. Execute ()
I = 0
Do While Not rstByQuery. EOF
I = I + 1' number of records saved in I
RstByQuery. MoveNext
Loop
MSFlexGrid1.Rows = I + 1 'dynamically sets the rows and columns of 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 title of the first line and fill it with the domain name

I = 0
'Set rstByQuery = mycommand. Execute ()
RstByQuery. Requery
Do While Not rstByQuery. EOF
I = I + 1
MSFlexGrid1.Row = I 'OK
For j = 0 To rstByQuery. Fields. count-1
MSFlexGrid1.Col = j + 1
MSFlexGrid1.Text = rstByQuery (j) add all columns
Next
RstByQuery. MoveNext

Loop 'this Loop is used to fill the content 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 Value
Add Parameters to mycommand. Parameters. Append parm '.
Mycommand. ActiveConnection = cnn1'
Specifies the current active connection of the command
Mycommand. CommandText = "select *
From employee where fname =? "
Mycommand. CommandType = ad1_text 'indicates the command type
Set rstByQuery = New ADODB. Recordset
Set rstByQuery = mycommand. Execute ()
I = 0
Do While Not rstByQuery. EOF
I = I + 1' number of records saved in I
RstByQuery. MoveNext
Loop
MSFlexGrid1.Rows = I + 1 'dynamically sets the rows and columns of 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 title of the first line and fill it with the domain name

I = 0
RstByQuery. Requery
Do While Not rstByQuery. EOF
I = I + 1
MSFlexGrid1.Row = I 'OK
For j = 0 To rstByQuery. Fields. count-1
MSFlexGrid1.Col = j + 1
MSFlexGrid1.Text = rstByQuery (j) add all columns
Next
RstByQuery. MoveNext

Loop 'this Loop is used to fill the content of MSFlexGrid
End Sub

---- Stored procedures can be used for query to improve processing efficiency and reduce network traffic.
---- This program passes debugging on nt workstation 4.0 SP4 and SQL SERVER 7.0