Insert Record/Data that Contain Quotes in Visual Basic

‘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 StringDim SQL_Str As StringDim Conn As ADODB.ConnectionDim ctl As Control‘ Get the data.DB_Name = App.PathIf Right$(DB_Name, 1) <> “” Then DB_Name = DB_Name & “”DB_Name = DB_Name & “Employee.mdb”‘ Open a connection.Set Conn = New ADODB.ConnectionConn.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 ControlsIf TypeOf ctl Is TextBox Thenctl.Text = “”End IfNext ctl

End Sub