‘This visual basic shows how to use ADO to insert records into a database when fields contain quotes
‘The program creates a SQL INSERT statement. To protect against quotes in the
‘user-entered values, the program replaces each single quote with two single
‘quotes. The database replaces the pairs of quotes with single quotes when it
‘inserts the values. For example, the database treats the text “Tess’s Bakery” as
‘”Tess’s ” when it adds it to the database.
Private Sub cmdInsert_Click()
Dim DB_Name As String
Dim SQL_Str As String
Dim Conn As ADODB.Connection
Dim ctl As Control
‘ Get the data.
DB_Name = App.Path
If Right$(DB_Name, 1) <> “” Then DB_Name = DB_Name & “”
DB_Name = DB_Name & “Employee.mdb”
‘ Open a connection.
Set Conn = New ADODB.Connection
Conn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & DB_Name & _
“;” & “Persist Security Info=False”
Conn.Open
‘ Compose the INSERT statement.
SQL_Str = “INSERT INTO Addresses “ & “(Name, Street, City, State, Zip) “ & ” VALUES (“ & _
“‘” & Replace$(txtName.Text, “‘”, “””) & “‘, “ & “‘” & _
Replace$(txtStreet.Text, “‘”, “””) & “‘, “ & “‘” & _
Replace$(txtCity.Text, “‘”, “””) & “‘, “ & “‘” & _
Replace$(txtState.Text, “‘”, “””) & “‘, “ & “‘” & _
Replace$(txtZip.Text, “‘”, “””) & “‘” & “)”
‘ Execute the statement.
Conn.Execute SQL_Str, , adCmdText
‘ Close the connection.
Conn.Close
‘ Clear the TextBoxes.
For Each ctl In Controls
If TypeOf ctl Is TextBox Then
ctl.Text = “”
End If
Next ctl
End Sub
