Insert into sql server from excel userform

[bhalbach]

I am using excel Userform and MS Server.

I have a Userform (OrderFrm) that I populate 2 multi column combo boxes with lists from my database (Customers & Items) there is also text box for inputting quantity and a text box that is populated with Price on a change event of the Items combo box. I have a textbox to input quantity and a change event on quantity textbox to calculate Item Total textbox.

I hav a Sub “AddItem” that adds the combo boxes and textboxes values to a “Order” Listbox on the Userform. The columns of the listbox are OrderId, CustomerId, ItemId, ItemName, Quantity, Price, ItemTotal. I have worked out getting the CustomerId and ItemId from the multi column comboboxes to the Order listbox along with ItemName, Quantity, Price and ItemTotal. At this point I have not Inserted into the Database yet.

In the Database I have 2 tables (tblOrder, tblOrderItem) that I want to insert the data from the Order listbox. The database is a relational db. The fields in the tblOrder are OrderId, CustomerId, OrderTotal. The fields in the tblOrderItem are OrderId, ItemId, Quantity, ItemTotal.

My hangup is at this point….
I have not inserted the items from the Order listbox to the 2 tables in db and Do NOT have a OrderId yet…so each line of the Order listbox is missing the OrderId which needs to come from the tblOrder table (auto increments) . I’m not sure how to sort this out, getting 1 Unique OrderId to use with every line of the Order listbox (because they all belong to 1 Order) so I can insert the records to the db tables.

The result I’m trying to get is the tblOrder gets 1 record inserted with OrderId (generated by db automatic) CustomerId and OrderTotal. TblOrderItem gets multiple records inserted from the Order listbox with a Unique OrderId for the inserted Items

I hope I have explained this clearly.

I have all the connection to the db sorted out already.

If I am approaching this inefficiently by using the Order listbox for staging the items before inserting to the db, alternative suggestions would be great.

Any assistance would be greatly appreciated.

 

[bhalbach]

I am using excel Userform and MS Server.

I have a Userform (OrderFrm) that I populate 2 multi column combo boxes with lists from my database (Customers & Items) there is also text box for inputting quantity and a text box that is populated with Price on a change event of the Items combo box. I have a textbox to input quantity and a change event on quantity textbox to calculate Item Total textbox.

I hav a Sub “AddItem” that adds the combo boxes and textboxes values to a “Order” Listbox on the Userform. The columns of the listbox are OrderId, CustomerId, ItemId, ItemName, Quantity, Price, ItemTotal. I have worked out getting the CustomerId and ItemId from the multi column comboboxes to the Order listbox along with ItemName, Quantity, Price and ItemTotal. At this point I have not Inserted into the Database yet.

In the Database I have 2 tables (tblOrder, tblOrderItem) that I want to insert the data from the Order listbox. The database is a relational db. The fields in the tblOrder are OrderId, CustomerId, OrderTotal. The fields in the tblOrderItem are OrderId, ItemId, Quantity, ItemTotal.

My hangup is at this point….
I have not inserted the items from the Order listbox to the 2 tables in db and Do NOT have a OrderId yet…so each line of the Order listbox is missing the OrderId which needs to come from the tblOrder table (auto increments) . I’m not sure how to sort this out, getting 1 Unique OrderId to use with every line of the Order listbox (because they all belong to 1 Order) so I can insert the records to the db tables.

The result I’m trying to get is the tblOrder gets 1 record inserted with OrderId (generated by db automatic) CustomerId and OrderTotal. TblOrderItem gets multiple records inserted from the Order listbox with a Unique OrderId for the inserted Items

I hope I have explained this clearly.

I have all the connection to the db sorted out already.

If I am approaching this inefficiently by using the Order listbox for staging the items before inserting to the db, alternative suggestions would be great.

Any assistance would be greatly appreciated.

Any assistance would be really appreciated appreciated....

I have tried the following to retrieve the OrderID from the sql database...I get a FALSE in the me.txtOrderId textbox when I run through the code.
If I run the SQL statement SELECT max(OrderId) FROM tblOrder i get the correct answer returned in the sql server query.

Me.txtOrderID = (cmd.CommandText = "SELECT max(OrderID) FROM tblOrder")

Thanks in advance.

 

[bhalbach]

This is the code I have come up with...its likely very poor but I am getting it to do everything except return the max OrderId from the database to the userform textbox Me.txtOrderID.
I am getting a FALSE in txtOrderId textbox and I need the max OrderId number.

I am open to all suggestions and assistance.....

Sub Insert_OrderData()

    Dim Conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim r As Long
    
    Set Conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    Conn.ConnectionString = SQLConStr
    Conn.Open
    
    cmd.ActiveConnection = Conn

    Conn.BeginTrans 'Safety to reverse if the error handler catches
    
        On Error GoTo ErrorHandler
        
            'This uses the GetInsertCustomerDataSQL Function
        cmd.CommandText = _
            GetInsertOrderDataSQL(Me.cboCustomer.Value, Me.txtOrderTotal.Value)

        On Error GoTo 0
        cmd.Execute


        On Error GoTo ErrorHandler
        Dim id As String
        
            'This will retrieve the OrderID from the database and put in userform
           Me.txtOrderID = (cmd.CommandText = "SELECT max(OrderID) FROM tblOrder")
        
        On Error GoTo 0

        cmd.Execute
        
        On Error GoTo ErrorHandler

        
        For r = 1 To Me.lstboxOrderItems.ListCount - 1
            'Me.lstboxOrderItems.List(r, 0),
            
            cmd.CommandText = _
                GetInsertOrderItemDataSQL( _
                    Me.txtOrderID.Value, _
                    Me.cboCustomer, _
                    Me.lstboxOrderItems.List(r, 0), _
                    Me.lstboxOrderItems.List(r, 2), _
                    Me.lstboxOrderItems.List(r, 3), _
                    Me.lstboxOrderItems.List(r, 4))

        On Error GoTo 0
        
            cmd.Execute
            
       Next r
        
    Conn.CommitTrans
    Conn.Close
    
    Set Conn = Nothing

    Exit Sub
    
ErrorHandler:
    MsgBox "Error number = " & (Err.Number - vbObjectError) & vbNewLine & Err.Description
    Conn.RollbackTrans 'Safety to reverse if the error handler catches bad like "DROP"
    Conn.Close
    
End Sub

 

[MoshiM]

You would need to store the output of the command in an ADODB.RecordSet object and then get the data from that object.

 

[bhalbach]

You would need to store the output of the command in an ADODB.RecordSet object and then get the data from that object.

Thanks for the direction...

I made this work with your help.

Is this code that I have used efficient or is this a really messy way of trying to accomplish what I need? I'm fairly new to vba and really new to SQL and could use any pointers you would be willing to offer.

Sub Insert_OrderData()

    Dim Conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim sqlString As String
    Dim r As Long
    
    Set Conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    Conn.ConnectionString = SQLConStr
    Conn.Open
    
    cmd.ActiveConnection = Conn

    Conn.BeginTrans 'Safety to reverse if the error handler catches
    
    'This uses the GetInsertOrderDataSQL Function
        On Error GoTo ErrorHandler
        cmd.CommandText = GetInsertOrderDataSQL(Me.cboCustomer.Value, Me.txtOrderTotal.Value)

        On Error GoTo 0
        cmd.Execute

    'This will retrieve the OrderID from the database and put in userform
        On Error GoTo ErrorHandler
        sqlString = "SELECT max(OrderID) FROM tblOrder;"
        Set rs = New ADODB.Recordset
        rs.Open sqlString, Conn, adOpenForwardOnly, adLockReadOnly
        Me.txtOrderID.Value = rs.Fields(0).Value
      
        On Error GoTo 0
        
        On Error GoTo ErrorHandler
        For r = 1 To Me.lstboxOrderItems.ListCount - 1
            cmd.CommandText = _
                GetInsertOrderItemDataSQL( _
                    Me.txtOrderID.Value, _
                    Me.cboCustomer, _
                    Me.lstboxOrderItems.List(r, 0), _
                    Me.lstboxOrderItems.List(r, 2), _
                    Me.lstboxOrderItems.List(r, 3), _
                    Me.lstboxOrderItems.List(r, 4))

        On Error GoTo 0
            cmd.Execute
            
       Next r
        
    Conn.CommitTrans
    Conn.Close
    
    Set Conn = Nothing
    Set rs = Nothing

    Exit Sub
    
ErrorHandler:
    MsgBox "Error number = " & (Err.Number - vbObjectError) & vbNewLine & Err.Description
    Conn.RollbackTrans 'Safety to reverse if the error handler catches bad like "DROP"
    Conn.Close
    
End Sub

 

[MoshiM]

Thanks for the direction...

I made this work with your help.

Is this code that I have used efficient or is this a really messy way of trying to accomplish what I need? I'm fairly new to vba and really new to SQL and could use any pointers you would be willing to offer.

Sub Insert_OrderData()

    Dim Conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim sqlString As String
    Dim r As Long
   
    Set Conn = New ADODB.Connection
    Set cmd = New ADODB.Command
   
    Conn.ConnectionString = SQLConStr
    Conn.Open
   
    cmd.ActiveConnection = Conn

    Conn.BeginTrans 'Safety to reverse if the error handler catches
   
    'This uses the GetInsertOrderDataSQL Function
        On Error GoTo ErrorHandler
        cmd.CommandText = GetInsertOrderDataSQL(Me.cboCustomer.Value, Me.txtOrderTotal.Value)

        On Error GoTo 0
        cmd.Execute

    'This will retrieve the OrderID from the database and put in userform
        On Error GoTo ErrorHandler
        sqlString = "SELECT max(OrderID) FROM tblOrder;"
        Set rs = New ADODB.Recordset
        rs.Open sqlString, Conn, adOpenForwardOnly, adLockReadOnly
        Me.txtOrderID.Value = rs.Fields(0).Value
     
        On Error GoTo 0
       
        On Error GoTo ErrorHandler
        For r = 1 To Me.lstboxOrderItems.ListCount - 1
            cmd.CommandText = _
                GetInsertOrderItemDataSQL( _
                    Me.txtOrderID.Value, _
                    Me.cboCustomer, _
                    Me.lstboxOrderItems.List(r, 0), _
                    Me.lstboxOrderItems.List(r, 2), _
                    Me.lstboxOrderItems.List(r, 3), _
                    Me.lstboxOrderItems.List(r, 4))

        On Error GoTo 0
            cmd.Execute
           
       Next r
       
    Conn.CommitTrans
    Conn.Close
   
    Set Conn = Nothing
    Set rs = Nothing

    Exit Sub
   
ErrorHandler:
    MsgBox "Error number = " & (Err.Number - vbObjectError) & vbNewLine & Err.Description
    Conn.RollbackTrans 'Safety to reverse if the error handler catches bad like "DROP"
    Conn.Close
   
End Sub

I'm not sure how necessary it is but you are missing an rs.close. Also I don't know what you are generating for your cmd.commandtext but you may be able to use the command object like this example:

    With number_of_records_command
        'Command will be used to ensure that there aren't duplicate entries in the database
        .ActiveConnection = cn
        .CommandText = "SELECT Count([Report_Date_as_YYYY-MM-DD]) FROM " & table_name & " WHERE [Report_Date_as_YYYY-MM-DD] = ? AND [CFTC_Contract_Market_Code] = ?;"
        .CommandType = adCmdText
        .Prepared = True
        
        With .Parameters
            .Append number_of_records_command.CreateParameter("YYYY-MM-DD", adDate, adParamInput)
            .Append number_of_records_command.CreateParameter("Contract_Code", adVarWChar, adParamInput, 6)
        End With

    End With

        For X = LBound(data_array, 1) To UBound(data_array, 1)

            contract_code = data_array(X, contract_code_column)
            row_date = data_array(X, yyyy_mm_dd_column)
            
            number_of_records_command.Parameters("Contract_Code").value = contract_code
            number_of_records_command.Parameters("YYYY-MM-DD").value = row_date

            Set number_of_records_returned = number_of_records_command.Execute

     Next X