Update Access database from Listview in vb 6.0

[silversurf]

Hello,
Here is the scenario :
I have the follwoing sales return from in my software:

In the from I have tow listview control, the left is Listview and the right is Listview1. I have the following code in procedure :

Dim xsprice As String
    Dim xsprice1 As String
    Dim xsquantity As String
    Dim xcode As String
    Dim xprofit As String
    Dim xprofit1 As String
    Dim xtotalonhand As String
    Dim xtotalreturn As String
    Dim xdiscount As String
    Dim xsinvno As String
    Dim xsinvdt As String
    Dim xremark As String
    Dim xreturnprice As String
    Dim lvwItem As ListItem
    Dim mylist As Integer
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rs1 As ADODB.Recordset
With ListView
For Each lvwItem In ListView.ListItems
lvwItem.SubItems(1) = xsquantity
'lvwItem.SubItems(2) = xsprice
Next lvwItem
With ListView1
For Each lvwItem In ListView1.ListItems
lvwItem.Text = xcode
lvwItem.SubItems(1) = xtotalreturn
lvwItem.SubItems(2) = xreturnprice
Next lvwItem
Set cn = New ADODB.Connection
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
        cn.Open
        Set rs = New ADODB.Recordset
        rs.Open ("SELECT * FROM tblstock WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
        xtotalonhand = rs!on_hand
        rs!on_hand = Val(xtotalreturn) + Val(xtotalonhand)
        rs1.Open ("SELECT * FROM tblsales WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
        xprofit = rs1!profit
        xprofit1 = (Val(xprofit) / Val(xsquantity)) * (Val(xsquantity) - Val(xtotalreturn))
        xsprice = rs1!sprice
        xsprice1 = Val(xsprice) - Val(xreturnprice)
        rs1!profit = xprofit1
        rs1!sprice = xsprice1
        rs1.Update
        rs.Update
        End With
        End With

I have two tables in my Access database, tblstock and tblsales. what I am trying to do here is, if a customer return a certain purchased product of a certain invoice no.the two tables will be updated e.g. in tblstock the on_hand field will be added with the pcs of product returend and in tblsales. two fields, sprice and profit will be calculated as per the string.
Now when I run the code, I have a type mismatch error in the follwing line :

For Each lvwItem In ListView.ListItems

My question is, Is my coding is right and why the error is coming ?

Plz respond. Thanks in advance

[AndreRet]

The problem is because you are referring to only the first coloumn - ListItems only returns the first. For any other coloumns after that you will need to call the SubItem as well...

ListView1.ListItems(1).ListSubItems

[silversurf]

Thanks sir, I have chaged it as following :

With ListView
For Each lvwItem In ListView.ListItems(1).ListSubItems
lvwItem.SubItems(1) = xsquantity
'lvwItem.SubItems(2) = xsprice
Next lvwItem
With ListView1
For Each lvwItem In ListView1.ListItems(1).ListSubItems
lvwItem.Text = xcode
lvwItem.SubItems(1) = xtotalreturn
lvwItem.SubItems(2) = xreturnprice
Next lvwItem

but still having the same "Type Mismatch" error in the line

For Each lvwItem In ListView.ListItems(1).ListSubItems

don't know what to do

[AndreRet]

You need to define the lvwItem with a set value first...

Set lvwItem = ListView.ListItems.Add(, , "", , ) ''"" = empty value. add a value here to coloumn1 if you so wish...

Now run your for each loop...

[silversurf]

sorry, didn't get the code clearly. where should I use it ? after line 1 ? should I stick with the line 2 ? what value should I use in "empty value" ?? another thing I am not adding any value in the listview, just trying to pick up the data from listview and listview1. what will be here :

(, , "", , ) ''"" = empty value. 

[AndreRet]

didn't get the code clearly. where should I use it ? after line 1 ? should I stick with the line 2 ?

Set lvwItem = ListView.ListItems.Add(, , "", , ) ''"" = empty value. add a value here to coloumn1 if you so wish...
Now run your for each loop...

As my answer mentioned, first the set part, then run your code...

what value should I use in "empty value" ?

Whatever value needs to go into coloumn 1...

another thing I am not adding any value in the listview, just trying to pick up the data from listview and listview1. what will be here :

Now I am at a total loss. Your code that you use in the loop is trying to add a value to your coloumns...

lvwItem.SubItems(1) = xsquantity
lvwItem.SubItems(2) = xsprice

It says to add the value of xsquantity to coloumn 2 and so on. I am not sure anymore what you require. To answer your original question, the answer as above.

[silversurf]

Sorry, I did not get my question clear to you. What I want here is to put data back such as quantity, sale price and accordingly profit, to my database from the Listview1 according to the product code which is in the column 1 of listview1. did you remember that you give me this idea of declearing variable answering one of my question (update stock data) in this forum. Here I try to do the same thing, first declearing variable like xcode, xsprice etc, trying to put data from the listview and listview1 into those variables and calculate and update my database.
Am I going the wrong way ??

[silversurf]

no answer yet.

[tinstaafl]

if that is your goal then yes your code is backwards. your variables(xcode,xsprice, etc.) should be on the left side of the '=' and the subitems(?).text on the right side of the '='. By the same token it looks like you've repeated that mistake further on in your code. the database variables should be on the left side, if you want to transfer the amounts to the database.

[AndreRet]

Your declaring of variables etc is perfect. As tinstaafl pointed out, you had your logic the wrong way around. If you want to get to an answer of 2, you will be using 1+1 = 2. If you want the answer to 1+1, you will have 1+1 = 2

Hope this clarifies it a bit for you. This is the problem when users just copy code over and they do not understand exactly what it does. :)

[silversurf]

yes I admit it, as a new coder I just want to copy and do the work. Thanks for mentioning. I will correct the code. But the thing is, picking data from the listview, was that correct as per the vb code I tried ??

[AndreRet]

No, you were trying to add values to the listview. To grab values from your listview, do the following -

For Each lvwItem In ListView1.ListItems(1).ListSubItems
xcode = lvwItem.Text
xtotalreturn = lvwItem.SubItems(1)
xreturnprice = lvwItem.SubItems(2)
Next lvwItem

yes I admit it, as a new coder I just want to copy and do the work

We were all there once upon a time. :) As long as you learn from what you are copying over, you will be on the right track. ;)

[tinstaafl]

One of the best things to learn, is about debugging your code with the VS debugger. With it you can step through the code line by line and examine exactly what values your variables are getting. Probably one of the best learning tools you can master.

[silversurf]

Thanks for the advice and codes. I will try my level best. Although I know it is offtopic but tinstaafi what is VS debugger ? How can I start and use it ?

[AndreRet]

Only a pleasure. Please mark as solved, thanx. :)

To use the debugger, go to this link from Microsoft. It covers everything you need to know.

[silversurf]

With ListView
'set lvwitem = listview.ListItems.Add(,,"",,)''""
For Each lvwItem In ListView.ListItems
xsquantity = lvwItem.SubItems(1)
'lvwItem.SubItems(2) = xsprice
Next lvwItem
With ListView1
For Each lvwItem In ListView1.ListItems(1).ListSubItems
xcode = lvwItem.Text
xtotalreturn = lvwItem.SubItems(1)
xreturnprice = lvwItem.SubItems(2)
Next lvwItem

have the above code now, still got the type mismatch error in the following line

For Each lvwItem In ListView.ListItems

i have tried it this way also, same result

For Each lvwItem In ListView.ListItems (1).ListSubItems

what should I do now.

Thanks andreret for the link

[AndreRet]

I have re-written your code to the following and tested it. Works fine...

Dim li As ListItem
    Dim xsquantity As Integer, xsprice As Integer

    xsquantity = 0

With ListView1
    .ColumnHeaders.Add , , "Id"
    .ColumnHeaders.Add , , "Quantity"
    .ColumnHeaders.Add , , "Price"

    Set li = .ListItems.Add(, , "1") ''This would be your record Id or similar when data was loaded...
    li.SubItems(1) = "2" ''Data would have been added before. You are now trying to return that data and calculate all totals...
    li.SubItems(2) = "3"

    Set li = .ListItems.Add(, , "11")
    li.SubItems(1) = "22"
    li.SubItems(2) = "33"
End With

For Each li In ListView1.ListItems
    xsquantity = xsquantity + li.SubItems(1)
    xsprice = xsprice + li.SubItems(2)
Next li

Text1.Text = xsquantity ''Display the totals...
Text2.Text = xsprice

[silversurf]

thanks andreret for trying out the code. But I think you missed out the thing that I want. I do not want to add item in the listview or listview1. I am trying to do some calculation on the basis of the datas that already in the listview and listview1 and put back the calculation result to my database. Please read my first post again.

I have two tables in my Access database, tblstock and tblsales. what I am trying to do here is, if a customer return a certain purchased product of a certain invoice no.the two tables will be updated e.g. in tblstock the on_hand field will be added with the pcs of product returend and in tblsales. two fields, sprice and profit will be calculated as per the string.

this is from my first post for your referrence. This whole calculation will be done as per the product code in the existing listview1 code column. I think I can make it clear.

[AndreRet]

:) Which is exactly what I gave you... In place of Text2.Text you need to add the code that will save the data to your database using the totals of xsquantity and xsprice.

[silversurf]

thanks again. but do I need this ?

`With ListView1
.ColumnHeaders.Add , , "Id"
.ColumnHeaders.Add , , "Quantity"
.ColumnHeaders.Add , , "Price"`

another question, where is the reference of the product code in line with which the database will be updated ??

[AndreRet]

No, not if you do not want to!! I have suppluied sample code. You need to modify it to suit your own needs.

another question, where is the reference of the product code in line with which the database will be updated ??

I am not sure what you are asking here.

[silversurf]

Please look at the picture that I posted in my first post. The first column of both listview and listview1 hold the data "code" which is the product code and whatever the update in the database will happen like sale price, quantity etc. that will be to that certain product code that I have in the listview1, thats why I thought there should be a line like

rs.Open ("SELECT * FROM tblstock WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic

by which I am trying to open that certain data to modify which is related to my product code, can I clearly explain the thing ??

[AndreRet]

:) I thought you would have grasped the concept of the listview getting values code by now. :)

Dim xCode As String

xCode = li.SubItems(1)

[silversurf]

thanks for the reply. but you are telling

   xCode = li.SubItems(1)

and again you are telling

xsquantity = xsquantity + li.SubItems(1)

isn't there something wrong ?

[AndreRet]

xsquantity is counting the total. xCode is the record number to update.

Please go through your code line by line so that you understand it and then ask your question. with all the posts given above your answer is there, you just need to put everything together. :(

[silversurf]

I am still scratching my head and looking for clue, what is going wrong. I have the following code now :

Dim xsprice As Integer
    Dim xsprice1 As Integer
    Dim xsquantity As Integer
    Dim xsquantity1 As Integer
    Dim xcode As String
    Dim xprofit As Integer
    Dim xprofit1 As Integer
    Dim xtotalonhand As Integer
    Dim xtotalreturn As String
    Dim xdiscount As String
    Dim xsinvno As String
    Dim xsinvdt As String
    Dim xremark As String
    Dim xreturnprice As String
    Dim li As ListItem
    Dim mylist As Integer
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rs1 As ADODB.Recordset
With ListView1
Set li = .ListItems.Add(, , "1")
li.SubItems(1) = "2"
li.SubItems(2) = "3"
li.SubItems(2) = "4"
End With
For Each li In ListView1.ListItems
xcode = li.SubItems(1)
xtotalreturn = li.SubItems(2)
xreturnprice = li.SubItems(3)
Next li
Set cn = New ADODB.Connection
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\main.mdb;Persist Security Info=False"
        cn.Open
        Set rs = New ADODB.Recordset
        rs.Open ("SELECT * FROM tblstock WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
        xtotalonhand = rs!on_hand
        rs!on_hand = Val(xtotalreturn) + Val(xtotalonhand)
        rs1.Open ("SELECT * FROM tblsales WHERE code = '" & xcode & "'"), con, adOpenDynamic, adLockOptimistic
        xprofit = rs1!profit
        xsquantity = rs1!squantity
        xsprice = rs1!sprice
        xsquantity1 = Val(xsquantity) - Val(xtotalreturn)
        xprofit1 = (Val(xprofit) / Val(xsquantity)) * (Val(xsquantity1))
        'xprofit1 = (Val(xprofit) / Val(xsquantity)) * (Val(xsquantity) - Val(xtotalreturn))
        xsprice1 = Val(xsprice) - Val(xreturnprice)
        rs1!profit = xprofit1
        rs1!squantity = xsquantity1
        rs1!sprice = xsprice1
        rs1.Update
        rs.Update
        'End With
        'End With

still having the "Type mismatch" error

Set li = .ListItems.Add(, , "1")

in the above line. Plz help.

[AndreRet]

Ouch.

Ok, lets try and find the problem...

Check if ListView1 spelling is the same as your control. Same with listview.

Put the following code into a seperate form and run and test it. If it works there our code is fine, something else is throwing the error...

Option Explicit

Private Sub Command1_Click()



    Dim li As ListItem
    Dim xsquantity As Integer, xsprice As Integer

    xsquantity = 0

With ListView1
    .ColumnHeaders.Add , , "Id"
    .ColumnHeaders.Add , , "Quantity"
    .ColumnHeaders.Add , , "Price"

    Set li = .ListItems.Add(, , "1") ''This would be your record Id or similar when data was loaded...
    li.SubItems(1) = "2" ''Data would have been added before. You are now trying to return that data and calculate all totals...
    li.SubItems(2) = "3"

    Set li = .ListItems.Add(, , "11")
    li.SubItems(1) = "22"
    li.SubItems(2) = "33"
End With

For Each li In ListView1.ListItems
    xsquantity = xsquantity + li.SubItems(1)
    xsprice = xsprice + li.SubItems(2)
Next li

Text1.Text = xsquantity ''Display the totals...
Text2.Text = xsprice


End Sub

Make sure you add all the controls. try and copy your listview over to this form. that way we will see that all namings is correct...

Let me know how it went.

[silversurf]

thanks for giving attention. I have put the codes in a new form as per you advice, here it is
still having the type mismatch error in following line

Set li = .ListItems.Add(, , "1") ''This would be your record Id or similar when data was loaded...

I have copy the listview1 from my program to this new form.
So what next ??

[silversurf]

I think i have discovered another interesting thing here , when I am using listview from the "Microsoft common control Sp-2 6.0" component, its giving errro, but when I am using the listview from "Microsoft Common Control Sp-2 5.0" component, your code working properly, is that a clue ?

[silversurf]

here is the interface that worked when I used listview from Microsoft common control 5.0 (SP) component