Database - How can I add a record to a database?-VBForums
Results 1 to 1 of 1

Thread: Database - How can I add a record to a database?

  1. #1

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,706

    Arrow Database - How can I add a record to a database?

    There are a variety of ways that you can add a record to a database, each of which has advantages and disadvantages.

    In general, you should use whichever method is appropriate to your existing code and methods - so if you already have a recordset connected to the correct table, feel free to use the recordset to add the data.

    The examples here are for ADO, if you are using DAO then only the first two methods are relevant (but you must use a different object for .Execute).


    In each of the examples below we will add a single record to a table (called test), this table contains 4 fields (a, b, c, and d), each of a different data type so that you can see the code needed for each type. The values we will add have been defined like this:
    VB Code:
    1. Private Const Data1 As String = "dee-u sample"  ' text field
    2. Private Const Data2 As Boolean = True           ' boolean field
    3. Private Const Data3 As Integer = 1000           ' numeric field
    4. Private Const Data4 As Date = #14/1/2006#       ' date/time field
    note that in the examples below you can use values or variables instead (or values of controls, such as Text1.Text) in place of Data1, Data2, etc.


    Method 1: Using the AddNew method of the recordset object.
    If you already have a Recordset connected to the appropriate table, then it is quite easy to use the Recordset to add the data.

    Note that for this to work, the Recordset must be updatable - which means that it must be created using the "recordset.Open" method (rather than "connection.Execute"), and must have a lock type set to something other than adLockReadOnly (this can be set as a parameter of Open). Also, some database systems will not allow you to add data if you have fields from more than one table in your Recordset.

    Note that if you do not have a appropriate Recordset open already, it is quicker to use one of the other methods.
    VB Code:
    1. 'replace adoRecordset with the name of your Recordset object
    2.     With adoRecordset
    3.         .AddNew
    4.         .Fields("a").Value = Data1
    5.         .Fields("b").Value = Data2
    6.         .Fields("c").Value = Data3
    7.         .Fields("d").Value = Data4
    8.         .Update
    9.     End With

    Method 2: Using an SQL Insert statement, via a Connection object
    If you do not have an appropriate Recordset available, but do have a Connection linked to the right database, you can use an Insert statement to add the new record.

    One downside of this method is that you need to understand a little SQL, but the following example should be enough for you to modify to your needs.

    Another problem is that if your data contains any single quote characters it will stop this method working, but this can be fixed quite easily (see How do I put the ' character into an SQL string?).
    VB Code:
    1. Dim strSQL As String
    2.     strSQL = "INSERT INTO test (a,b,c,d)" & _
    3.              "VALUES('" & Data1 & "'," & _
    4.                           Data2 & "," & _
    5.                           Data3 & "," & _
    6.                     "#" & Data4 & "#)"
    7.  
    8.         'Replace adoConnection with the name of your connection object
    9.     adoConnection.Execute strSQL, , adCmdText + adExecuteNoRecords
    10.     'note: the last two arguments used for Execute here makes the execution of the command faster
    Note that the # signs are used around dates for an Access database, for other database systems use single quotes ( ' ) instead.


    Method 3: Using the execute method of the Command object and parameters
    Using the Command object is basically an alternative to using the Execute method of the Connection object, however it is slightly faster and does not have issues with quotes in your data.

    This method is even faster for adding multiple records, as you can just replace the values in the Parameters, instead of creating new ones for each record you are adding.
    VB Code:
    1. Dim strSQL As String
    2.     strSQL = "INSERT INTO test (a,b,c,d) VALUES(?,?,?,?)"
    3.  
    4. Dim adoCommand As ADODB.Command
    5.     Set adoCommand = New ADODB.Command
    6.     With adoCommand
    7.            'Replace adoConnection with the name of your connection object
    8.         .ActiveConnection = adoConnection
    9.         .CommandType = adCmdText
    10.         .CommandText = strSQL
    11.         .Prepared = True
    12.         .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, Data1)
    13.         .Parameters.Append .CreateParameter(, adBoolean, adParamInput, , Data2)
    14.         .Parameters.Append .CreateParameter(, adInteger, adParamInput, , Data3)
    15.         .Parameters.Append .CreateParameter(, adDate, adParamInput, , Data4)
    16.         .Execute , , adCmdText + adExecuteNoRecords
    17.           'note: the last two arguments used for Execute here makes the execution of the command faster
    18.     End With
    For a more in-depth discussion on using Command objects have a look at this FAQ article How do I use an ADO Command object?.


    Method 4: Passing values to a pre-defined action query (Access) or Stored Procedure (other database systems)
    This method uses similar code to the previous example, however most the work is now done on the database instead of in your program. This means that you can you can add extra rules to your query/SP that the program does not need to implement - which is useful if you are likely to work with the same database in multiple programs.

    For database systems which use a separate server (such as SQL Server or MySQL) this method can also be much faster, as they not only have better hardware than desktop PC's, but the database system can optimise the methods which are used behind the scenes.

    The disadvantages are that you need to know how to make the query/SP, and that you cannot see what is happening inside it while you debug your code (but this is arguably a good thing!).
    VB Code:
    1. Dim adoCommand As ADODB.Command
    2.     Set adoCommand = New ADODB.Command
    3.     With adoCommand
    4.            'Replace adoConnection with the name of your connection object
    5.         .ActiveConnection = adoConnection
    6.         .CommandType = adCmdStoredProc 'since it is a Query/SP
    7.            'Replace Query1 with the name of your Query/SP
    8.         .CommandText = "Query1"
    9.         .Prepared = True
    10.         .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, Data1)
    11.         .Parameters.Append .CreateParameter(, adBoolean, adParamInput, , Data2)
    12.         .Parameters.Append .CreateParameter(, adInteger, adParamInput, , Data3)
    13.         .Parameters.Append .CreateParameter(, adDate, adParamInput, , Data4)
    14.         .Execute
    15.     End With
    For a more in-depth discussion on using Command objects have a look at this FAQ article How do I use an ADO Command object?.


    The attached file has a program containing examples of each of these methods, and an Access database to add the data to.
    Attached Files Attached Files
    Last edited by si_the_geek; Jan 8th, 2009 at 07:59 AM. Reason: minor formatting changes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.