How do you execute a Insert statement?

brendan82 · December 16th, 2003, 06:40 AM

I have two forms, one for adding a new client, and for for calculating payments. These forms are linked via a drop down menu.
On the reservations form, I have labels: Surname, Firstname, Street, Town, Postcode, Phone, Email, and Date of Arrival. Opposite these labels I have text boxes. At the bottom of this page is a cmd botton, 'Add New Client.' The new person's details are then stored in a Clients table in Access.
I have been told that to execute this task, I need to use a Insert statement. But I don't know how to, or how to write one, or what it would look like. I'm not really sure what variables I need to declare, but I have been given a list of coding that I will list below. I'm not sure whether this piece of coding goes in this form or in the payments form, to calculate the person's bill after a period of stay. The coding is:

Private rsclients As ADODB.Recordset
Const connstr = "Provider=Microsoft.Jet.OLEDB.4.0:data source = f:(and then the location of the file)"
Private conn As ADODB.Connection
Private cmdaddnewclient As ADODB.Command
Private required As long
Private sql As string

Are these the variables that I need to declare? How do I write a Insert statement? What does it look like? Does the Insert statement go behind the cmd button? I'm grateful for all help, suggestions and examples. Thanks for reading and replying. Brendan

JackC · December 16th, 2003, 11:38 AM

Are these the variables that I need to declare? Yes, and I would skip the ADODB.Command object. Just call Execute() on the ADODB.Connection object like this:
Set rsclients = conn.Execute(sql)

The String sql will contain your INSERT INTO statement. INSERT INTO is not hard, go to http://msdn.microsoft.com search for INSERT INTO. In the results click on 'Creating Insert Into Queries'.

Does the Insert statement go behind the cmd button? Yes. But, all the initial connecting to the database goes in Form_Load() for example.
You need more help than I offered but it might get you going.

Bill Crawley · December 16th, 2003, 11:40 AM

do somthing like:
sql = "insert into mytable (field1,field2) values ('x',9)"
conn.execute sql

jstrong · December 29th, 2003, 12:26 PM

I realise this is way late for you but it might help others. here is an example that works:

Code:

Sub InsertNewCountryCriteria(iCriteriaId As Integer)

Dim Conn As New ADODB.Connection
Dim rsCountryCriteria As New ADODB.Recordset
Dim rsCriteriaCount As New ADODB.Recordset
Dim CmdInsertCriteria As New ADODB.Command
Dim CmdSelCriteria As New ADODB.Command
Dim iFldCriteriaCount As Integer

    'Update the CountryCriteria table with the new criteria
    'Establish Connection to Country Scan database

    Conn.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;" & _
                    "Persist Security info=False;" & _
                    "Data Source = " & sDataSourcePath & sDataBase
    Conn.Open


    'First check to see if country criteria exist for this criteria in the country criteria table

    sSQLCmd = "SELECT count(*) AS CriteriaCount " & _
                      "  FROM TblCountryCriteria " & _
                      " WHERE CriteriaId = " & iCriteriaId

    CmdSelCriteria.ActiveConnection = Conn
    CmdSelCriteria.CommandText = sSQLCmd
    CmdSelCriteria.CommandType = adCmdText
    Set rsCriteriaCount = CmdSelCriteria.Execute

    If Not rsCriteriaCount.EOF Then

        iFldCriteriaCount = rsCriteriaCount.Fields("CriteriaCount").Value

        If iFldCriteriaCount = 0 Then

            'Update the table TblCountryCriteria with the
            'new criterion entry for each country.

            'set up the SQL String

            iCategorySel = CategoryCombo.ListIndex
            iCategoryIdToSelect = iCategorySel + 1

            sSQLCmd = "INSERT INTO TblCountryCriteria " & _
                        " (CountryId " & _
                        " ,CriteriaId " & _
                        " ,CategoryId " & _
                        " ,CountryCriteriaScore " & _
                        " ) " & _
                        " SELECT a.CountryId " & _
                                " ,b.CriteriaId " & _
                                " ,b.CategoryId " & _
                                " ,0 " & _
                        "   FROM TblCountry as a " & _
                            "   ,TblCriteriaDefinition as b " & _
                        " WHERE b.CategoryId = " & iCategoryIdToSelect & _
                        "   AND b.CriteriaId = " & iCriteriaId

            CmdInsertCriteria.ActiveConnection = Conn
            CmdInsertCriteria.CommandText = sSQLCmd
            CmdInsertCriteria.CommandType = adCmdText

            'There is no need to use a recordset object for this execution
            'of the command object.
            CmdInsertCriteria.Execute

            'release ADO resources
            Set CmdInsertCriteria = Nothing
            Set rsCriteriaCount = Nothing

        End If


    End If

    'release ADO resources
    Set CmdSelCriteria = Nothing
    Set rsCriteriaCount = Nothing

End Sub