Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse ADO to insert records into a database when fields contain quotes
DescriptionThis example shows how to use ADO to insert records into a database when fields contain quotes in Visual Basic 6.
KeywordsADO, database, insert, quotes
CategoriesDatabase
 
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 "O''Toole" as "O'Toole" when it adds it to the database.
 
Private Sub Command1_Click()
Dim db_file As String
Dim statement As String
Dim conn As ADODB.Connection
Dim ctl As Control

    ' Get the data.
    db_file = App.Path
    If Right$(db_file, 1) <> "\" Then db_file = db_file & _
        "\"
    db_file = db_file & "people.mdb"

    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & db_file & ";" & _
        "Persist Security Info=False"
    conn.Open

    ' Compose the INSERT statement.
    statement = "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 statement, , 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
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated