VB.NET + PreStatement (By Shuja Ali)

Or instead of using a direct SQL Statement,use Preparaed SQL Statements. When you execute a prepared SQL statement,you don't have to worry about special characters in your Text. Prepared Statements will automatically take care of the Single Quote and other symbols.

Moreover Prepared Statements are better that the SQL Statements that are insertted in the code,they are faster and reduce the chances of SQL Injection attacks.

Here is a simple example of how to use Command Object and Preparaed SQL Statements

CODE:
??????Dim cmdSQLInsert As ADODB.Command
Set cmdSQLInsert = New ADODB.Command

'Create the query
cmdSQLInsert.CommandText = "Insert Into Table1(ID,NAME,AGE) Values(?,?,?)"
cmdSQLInsert.CommandType = adCmdText
cmdSQLInsert.Prepared = True

'Create the parameters
'in this case we will create three parameters
'-----Param 1 (for Field ID)-------------
Dim gParam As ADODB.Parameter
Set gParam = New ADODB.Parameter
With gParam
.Name = "ID"
.Direction = adParamInput
.Type = adChar
.Size = 10
.Value = "xxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam

'-----Param 2 (for Field Name)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
.Name = "NAME"
.Direction = adParamInput
.Type = adVarChar
.Size = 50
.Value = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam

'-----Param 3 (for Field AGE)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
.Name = "AGE"
.Direction = adParamInput
.Type = adChar
.Size = 2
.Value = "xx"
End With
cmdSQLInsert.Parameters.Append gParam

'Set the connection property of the command object
Set cmdSQLInsert.ActiveConnection = mySQLConnection
'pass the values that need to be inserted to specific parameters that we created above
cmdSQLInsert("ID") = txtID.Text
cmdSQLInsert("NAME") = txtName.Text
cmdSQLInsert("AGE") = txtAge.Text

'Execute the command
cmdSQLInsert.Execute

Remember once the Prepared Statement is built,next time you just need to pass on the values for the Parameters and execute the statement. This makes code look more handsome and easily maintainable. You could also look in MSDN for more about Preparaed Statements and search this forum too.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!