List View Control Columns From 2 Tables???

[Vbbeginner07]

[code=vb]
Private Sub Form_Load()
ListView1.Refre sh
ListView2.Refre sh
ListView1.View = lvwReport
ListView2.View = lvwReport
ListView1.Colum nHeaders.Add , , "ID"
'ListView1.Colu mnHeaders.Add , , "NAME"
ListView2.Colum nHeaders.Add , , "DATE"
ListView2.Refre sh
rs.Open "select distinct id from saldetail", conn, adOpenStatic, adLockOptimisti c
ListView1.Refre sh
While Not rs.EOF

With ListView1
Set lvitem = ListView1.ListI tems.Add(, , rs!id)
'rs1.Open "select * from empdetail where id = '" & ListView1.ListI temsItem & "'", conn, adOpenStatic, adLockOptimisti c
'lvitem.SubItem s(1) = rs1!Name
End With
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
ListView1.Refre sh
ListView2.Refre sh
end sub
[/code]
the above code is to retrieve id from a table saldetail,where i have to retrieve the corresponding names of the ids from another table,is it possible in alist view control???
where i have gone wrong???
please help

[QVeen72]

Hi,

Open Recordset like this :

rs.Open "select distinct S.id , e.* from saldetail S, EmpMas E Where S.ID = E.ID ", conn, adOpenStatic, adLockOptimisti c

REgards
Veena

[Vbbeginner07]

Hi,
Open Recordset like this :
rs.Open "select distinct S.id , e.* from saldetail S, EmpMas E Where S.ID = E.ID ", conn, adOpenStatic, adLockOptimisti c
REgards
Veena

But Veena,
I cant understand that,can u please explain it once more,
even im getting errors in EmpMass
Please explain it

[Vbbeginner07]

Hi,
Open Recordset like this :
rs.Open "select distinct S.id , e.* from saldetail S, EmpMas E Where S.ID = E.ID ", conn, adOpenStatic, adLockOptimisti c
REgards
Veena

rs1.Open "select empdetail.id,sa ldetail.name where empdetail.id = saldetail.id", conn, adOpenStatic, adLockOptimisti c

'i have tried with this code too tat too error occurs

[QVeen72]

Hi,

try this :


rs1.Open "select empdetail.id,sa ldetail.name from empdetail , saldetail where empdetail.id = saldetail.id", conn, adOpenStatic, adLockOptimisti c

Regards
Veena

[Vbbeginner07]

Hi,
try this :
dim ivitem as Listitem
rs1.Open "select empdetail.id,sa ldetail.name from empdetail , saldetail where empdetail.id = saldetail.id", conn, adOpenStatic, adLockOptimisti c
Regards
Veena

rs.Open "select distinct id from saldetail", conn, adOpenStatic, adLockOptimisti c
rs1.Open "select empdetail.name, saldetail.id from empdetail,salde tail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimisti c
ListView1.Refre sh
While Not rs.EOF
While Not rs1.EOF
With ListView1
Set lvitem = ListView1.ListI tems.Add(, , rs!id)
lvitem.SubItems (1) = rs1!Name
End With
rs.MoveNext
rs1.MoveNext
Wend
Wend
rs1.Close
Set rs1 = Nothing
rs.Close
Set rs = Nothing
ListView1.Refre sh
ListView2.Refre sh


this codeis to add two coulmns to a listview where both are from different tables.
But the line
lvitem.subitems (1)=rs1!Nameis not working

[QVeen72]

Hi,

Before adding sub-items, you have to add Column Headers to the listview..

Regards
Veena

[Vbbeginner07]

Hi,
Before adding sub-items, you have to add Column Headers to the listview..

its added at first
but error showing invariable optional
Nick

[pureenhanoi]

[code=vb]
Private Sub Form_Load()
'ListView1.Refr esh ' the list view is blank on start-up, does not need any refresh
'ListView2.Refr esh ' donot refresh
ListView1.View = lvwReport
ListView2.View = lvwReport
ListView1.Colum nHeaders.Add , , "ID"
ListView1.Colum nHeaders.Add , , "NAME"
ListView2.Colum nHeaders.Add , , "DATE"
'ListView2.Refr esh 'again
rs.Open "select distinct id from saldetail", conn, adOpenStatic, adLockOptimisti c
'ListView1.Refr esh 'and again
While Not rs.EOF

'With ListView1 'donot need "With",
Set lvitem = ListView1.ListI tems.Add(, , rs!id)
'this code seem good, except ListView1.ListI temsItem ??? what does it mean
'rs1.Open "select * from empdetail where id = '" & ListView1.ListI temsItem & "'", conn, adOpenStatic, adLockOptimisti c
'Try this
rs1.Open "select * from empdetail where id = '" & rs!id & "'", conn, adOpenStatic, adLockOptimisti c
lvitem.SubItems (1) = rs1!Name
'End With
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
'ListView1.Refr esh
'ListView2.Refr esh
end sub
[/code]
the above code is to retrieve id from a table saldetail,where i have to retrieve the corresponding names of the ids from another table,is it possible in alist view control???
where i have gone wrong???
please help

Your actual code can do things you need, except it has lot of extra statement

[Vbbeginner07]

Your actual code can do things you need, except it has lot of extra statement

but that doesnt work,now i have used the code
[code=vb]

ListView2.Refre sh
rs1.Open "select empdetail.name, saldetail.id from empdetail,salde tail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimisti c
While Not rs1.EOF
With ListView1
vitem.SubItems( 1) = rs1!Name
End With
rs1.MoveNext
Wend
rs1.Close
Set rs1 = Nothing
[/code]
that adds only one record,ie the last record,please help...........

[pureenhanoi]

but that doesnt work,now i have used the code
[code=vb]

ListView2.Refre sh
rs1.Open "select empdetail.name, saldetail.id from empdetail,salde tail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimisti c
While Not rs1.EOF
With ListView1
vitem.SubItems( 1) = rs1!Name
End With
rs1.MoveNext
Wend
rs1.Close
Set rs1 = Nothing
[/code]
that adds only one record,ie the last record,please help...........

No no, not that SQL state ment. See my reply above. you can copy that code and paste into your program. After that, you can delete all comment line. Try that code.
One more else, the saldetail table, as i think it has more than one field (ID), so, why do you SELECT only ID on salDetail

[QVeen72]

Hi,

Try this :

[code=vb]
Private Sub Form_Load()
ListView1.Refre sh
ListView2.Refre sh
ListView1.View = lvwReport
ListView2.View = lvwReport
ListView1.Colum nHeaders.Add , , "ID"
ListView1.Colum nHeaders.Add , , "NAME"
ListView2.Colum nHeaders.Add , , "DATE"
ListView2.Refre sh
rs.Open "select empdetail.name, saldetail.id from empdetail,salde tail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimisti c
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
With ListView1
Set lvitem = ListView1.ListI tems.Add(, , rs!id)
lvitem.SubItems (1) = rs!Name
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
ListView1.Refre sh
ListView2.Refre sh
end sub

[/code]

Regards
Veena

[Vbbeginner07]

Hi,

Try this :

[code=vb]
Private Sub Form_Load()
ListView1.Refre sh
ListView2.Refre sh
ListView1.View = lvwReport
ListView2.View = lvwReport
ListView1.Colum nHeaders.Add , , "ID"
ListView1.Colum nHeaders.Add , , "NAME"
ListView2.Colum nHeaders.Add , , "DATE"
ListView2.Refre sh
rs.Open "select empdetail.name, saldetail.id from empdetail,salde tail where saldetail.id = empdetail.id", conn, adOpenStatic, adLockOptimisti c
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
With ListView1
Set lvitem = ListView1.ListI tems.Add(, , rs!id)
lvitem.SubItems (1) = rs!Name
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
ListView1.Refre sh
ListView2.Refre sh
end sub

[/code]

Regards
Veena

do loop has a pblm???

[QVeen72]

Hi,

Tried the Code...?
Copy the entire code and run..
What error you are getting...?

Regards
Veena

[Vbbeginner07]

Hi,
Tried the Code...?
Copy the entire code and run..
What error you are getting...?
Regards
Veena

yes,
Loop without do
i tried with while that too wend wthout while........

Nick