[RESOLVED] multiple data in Listview save to Database

[micdansan]

I'm trying to insert/save data in the database from Listview:

The code I use:

For i = 0 To ListView3.ListItems.Count - 1
        With ListView3
        cn.Execute "insert into tempclientPurchases (baseID,invoiceno,pcode,ID,dateout,productdesc,qty,unit,discount,price,amt,salereport,type) values (" & .ListItems(i).Text & ",'" _
        & .ListItems(i).SubItems(1) & "'," & .ListItems(i).SubItems(2) & "," & .ListItems(i).SubItems(3) & ",#" _
        & .ListItems(i).SubItems(4) & "#,'" & .ListItems(i).SubItems(5) & "'," & .ListItems(i).SubItems(6) & ",'" _
        & .ListItems(i).SubItems(7) & "'," & .ListItems(i).SubItems(8) & "," & .ListItems(i).SubItems(9) & "," _
        & .ListItems(i).SubItems(10) & "," & .ListItems(i).SubItems(11) & "," & .ListItems(i).SubItems(12) & ")"
        End With
    Next i

I get Out of bounds error

[micdansan]

I modified the code: I think this is the proper code:

For i = 1 To ListView3.ListItems.Count - 1
        With ListView3
        cn.Execute "insert into tempclientPurchases (baseID,invoiceno,pcode,ID,dateout,productdesc,qty,unit,discount,price,amt,salereport,type) values (" & .ListItems.Item(i).SubItems(13) & ",'" _
        & .ListItems.Item(i).SubItems(1) & "'," & .ListItems.Item(i).SubItems(2) & "," & .ListItems.Item(i).SubItems(3) & ",#" _
        & .ListItems.Item(i).SubItems(4) & "#,'" & .ListItems.Item(i).SubItems(5) & "'," & .ListItems.Item(i).SubItems(6) & ",'" _
        & .ListItems.Item(i).SubItems(7) & "'," & .ListItems.Item(i).SubItems(8) & "," & .ListItems.Item(i).SubItems(9) & "," _
        & .ListItems.Item(i).SubItems(10) & "," & .ListItems.Item(i).SubItems(11) & "," & .ListItems.Item(i).SubItems(12) & ")"
        End With
    Next i

The program runs ok, no error... but in the database no data was save

[Nightwalker83]

You want the recordset to be saving the data not the connection. Try something like:

rs.Fields("baseID") = .ListItems.Item(i).SubItems(13) 'Do this for each item to be saved
rs.Update

Edit:

Don't forget to add error handling! Something such as:

On Error GoTo HandleSaveErrors 'if an error occurs, let "HandleSaveErrors"
'Put the above code here
 
HandleSaveErrors:
    
    Select Case Err.Number
         Case 3022 'duplicate primary key
         strMessage = "Duplicate Customer Field"
         MsgBox strMessage, vbExclamation, "Database Error"
         rs.cancelUpdate
        
        Case 3058       'Primary key left blank
            strMessage = "Field was left empty"
            MsgBox strMessage, vbExclamation, "Database Error"
            rs.cancelUpdate
        Case Else
            strMessage = "Record could not be saved. " & vbCrLf _
                            & Err.Description
            Resume Next
    End Select

[Doogle]

When building up an SQL query I normally put it into a String variable in some sort of structured fashion, so (1) I can print the contents to the debug.window to check for any errors and (2) make sure the SQL is complete and properly formed.

Dim strSQL As String
For i = 1 To ListView3.ListItems.Count - 1
    strSQL = "INSERT  INTO tempclientPurchases " _
        & "(baseID,invoiceno,pcode,ID,dateout,productdesc,qty,unit,discount,price,amt,salereport,type) " _
        & "VALUES("
    With ListView3.ListItems.Item(i)
        strSQL = strSQL & .SubItems(13) & ","           'baseID
        strSQL = strSQL & "'" & .SubItems(1) & "',"     'invoiceno
        strSQL = strSQL & .SubItems(2) & ","            'pcode
        strSQL = strSQL & .SubItems(3) & ","            'ID
        strSQL = strSQL & "#" & .SubItems(4) & "#,"     'dateout
        strSQL = strSQL & "'" & SubItems(5) & "',"      'productdesc
        strSQL = strSQL & .SubItems(6) & ","            'qty
        strSQL = strSQL & "'" & .SubItems(7) & "',"     'unit
        strSQL = strSQL & .SubItems(8) & ","            'discount
        strSQL = strSQL & .SubItems(9) & ","            'price
        strSQL = strSQL & .SubItems(10) & ","           'amt
        strSQL = strSQL & .SubItems(11) & ","           'salereport
        strSQL = strSQL & .SubItems(12)                 'type
        strSQL = strSQL & ")"
        cn.Execute strSQL
    End With
Next i

It's a bit of extra typing, but worth it in the end.

@Nightwalker: The Execute method of the Connection Object is the correct way to perform action queries, also there's no sign of a RecordSet being populated in OPs code

[micdansan]

When building up an SQL query I normally put it into a String variable in some sort of structured fashion, so (1) I can print the contents to the debug.window to check for any errors and (2) make sure the SQL is complete and properly formed.

Dim strSQL As String
For i = 1 To ListView3.ListItems.Count - 1
    strSQL = "INSERT  INTO tempclientPurchases " _
        & "(baseID,invoiceno,pcode,ID,dateout,productdesc,qty,unit,discount,price,amt,salereport,type) " _
        & "VALUES("
    With ListView3.ListItems.Item(i)
        strSQL = strSQL & .SubItems(13) & ","           'baseID
        strSQL = strSQL & "'" & .SubItems(1) & "',"     'invoiceno
        strSQL = strSQL & .SubItems(2) & ","            'pcode
        strSQL = strSQL & .SubItems(3) & ","            'ID
        strSQL = strSQL & "#" & .SubItems(4) & "#,"     'dateout
        strSQL = strSQL & "'" & SubItems(5) & "',"      'productdesc
        strSQL = strSQL & .SubItems(6) & ","            'qty
        strSQL = strSQL & "'" & .SubItems(7) & "',"     'unit
        strSQL = strSQL & .SubItems(8) & ","            'discount
        strSQL = strSQL & .SubItems(9) & ","            'price
        strSQL = strSQL & .SubItems(10) & ","           'amt
        strSQL = strSQL & .SubItems(11) & ","           'salereport
        strSQL = strSQL & .SubItems(12)                 'type
        strSQL = strSQL & ")"
        cn.Execute strSQL
    End With
Next i

It's a bit of extra typing, but worth it in the end.

@Nightwalker: The Execute method of the Connection Object is the correct way to perform action queries, also there's no sign of a RecordSet being populated in OPs code

@Doogle
Thanks for your reply... Tried your code, still the same runs ok, no errors, but no data was save in the database.

[Doogle]

How are you checking the contents of the table after performing the Inserts?

[micdansan]

How are you checking the contents of the table after performing the Inserts?

@Doogle
I run the code again and then returns this error error: no value given for one or more required parameters

I added trim() and val() and it works! Great Help Doogle.. Thanks a lot.

here's the final code:

For i = 1 To ListView3.ListItems.Count
        stringSQLT = "INSERT  INTO TempclientPurchases " _
            & "(baseID,invoiceno,pcode,ID,dateout,productdesc,qty,unit,discount,price,amt,salereport,type) " _
            & "VALUES("
        With ListView3.ListItems.Item(i)
            stringSQLT = stringSQLT & Val(.SubItems(13)) & ","          'baseid
            stringSQLT = stringSQLT & "'" & Trim(.SubItems(1)) & "',"   'invoiceno
            stringSQLT = stringSQLT & Val(.SubItems(2)) & ","            'pcode
            stringSQLT = stringSQLT & Val(.SubItems(3)) & ","            'ID
            stringSQLT = stringSQLT & "#" & .SubItems(4) & "#,"        'dateout
            stringSQLT = stringSQLT & "'" & Trim(.SubItems(5)) & "',"   'productdesc
            stringSQLT = stringSQLT & Val(.SubItems(6)) & ","            'qty
            stringSQLT = stringSQLT & "'" & Trim(.SubItems(7)) & "',"   'unit
            stringSQLT = stringSQLT & Val(.SubItems(8)) & ","            'discount
            stringSQLT = stringSQLT & Val(.SubItems(9)) & ","            'price
            stringSQLT = stringSQLT & Val(.SubItems(10)) & ","          'amt
            stringSQLT = stringSQLT & Val(.SubItems(11)) & ","          'salereport
            stringSQLT = stringSQLT & Val(.SubItems(12))                 'type
            stringSQLT = stringSQLT & ")"
            cn.Execute stringSQLT
        End With
    Next i