[RESOLVED] multiple data in Listview save to Database
I'm trying to insert/save data in the database from Listview:
The code I use:
Code:
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
Re: multiple data in Listview save to Database
I modified the code: I think this is the proper code:
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
Re: multiple data in Listview save to Database
You want the recordset to be saving the data not the connection. Try something like:
vb Code:
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:
vb Code:
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
Re: multiple data in Listview save to Database
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.
Code:
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
Re: multiple data in Listview save to Database
Quote:
Originally Posted by
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.
Code:
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.
Re: multiple data in Listview save to Database
How are you checking the contents of the table after performing the Inserts?
Re: multiple data in Listview save to Database
Quote:
Originally Posted by
Doogle
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. :D
here's the final code:
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
