Getting Supplier detail using listview

[firoz.raj]

Can anybody tell me why these statement is not working ?. I want to see supplier detail using listview.here is the code what i have written.

Private Sub Form_Load()
Set con = New ADODB.Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
con.CursorLocation = adUseClient
   Dim listitem As listitem
   ListView1.View = lvwReport
   ListView1.ColumnHeaders.Add , , "Supplier No."
   ListView1.ColumnHeaders.Add , , "Supplier Name"
   ListView1.ColumnHeaders.Add , , "Contact Person"
StrSql = "Select"
StrSql = StrSql & "[supplier_name],[type],[fax_no],[contact_person]"
StrSql = StrSql & "From  [Supplier]"
rs.Open StrSql, con, adOpenDynamic, adLockOptimistic
While (Not (rs.EOF))
    Set listitem = ListView1.ListItems.Add(suppliername)
    listitem.SubItems(1) = IIf(IsNull(rs!supplier_name), "", rs!supplier_name)
   [B] listitem.SubItems(2) = IIf(IsNull(rs!Type), "", rs!Type)
    listitem.SubItems(3) = IIf(IsNull(rs!fax_no), "", rs!fax_no)
    listitem.SubItems(4) = IIf(IsNull(rs!contact_person), "", rs![/B]contact_person)
Wend
End Sub

[choudhuryshouvi]

your code has some problems that I noticed....anyways this code can be improved...but could you explain what is happening actually when you running this?

what's d error msg you are getting....what's the output coming?

regards
Shouvik

[firoz.raj]

sir i am getting runtime error 91.kindly find the attachm
listview.doc

ent.

[jireh]

Well it's very clear as the sunshine. You have just used the "rs" but not declaring/setting it.

[firoz.raj]

Now i got invalid use of null value.when i uncomment the follwing
line get invalid property value.

'       listitem.SubItems(3) = IIf(IsNull(rs!fax_no), "", rs!fax_no)

here is the code :

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim listitem As listitem
Private Sub Form_Load()
Set con = New ADODB.Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
con.CursorLocation = adUseClient
   ListView1.View = lvwReport
   ListView1.ColumnHeaders.Add , , "Supplier No."
   ListView1.ColumnHeaders.Add , , "Supplier Name"
   ListView1.ColumnHeaders.Add , , "Contact Person"
StrSql = "Select"
StrSql = StrSql & "[supplier_name],[type],[fax_no],[contact_person] from [Supplier]"
Set rs = New ADODB.Recordset
rs.Open StrSql, con, adOpenDynamic, adLockOptimistic
   While (Not rs.EOF)
        Set listitem = ListView1.ListItems.Add(, , , supplier_name)
        listitem.SubItems(1) = rs!supplier_name
        listitem.SubItems(2) = rs!Type
'       listitem.SubItems(3) = IIf(IsNull(rs!fax_no), "", rs!fax_no)
        [B]listitem.SubItems(3) = rs!fax_no[/B]
        listitem.SubItems(4) = rs!contact_person
        Wend
        End Sub

[choudhuryshouvi]

hi....here is the modified code...just check this out....look for the BOLDED lines....these are where i made some changes....watch GREEN lines for comments....

the problem is within your listview manipulation code.....means when you are adding listitems.....you have mismatched total no. of columns and listitems.....total no. of columns in the listview should match with total no. of listitems you want to add....the problem is you adding 5 listitems to the control where you are having only 3 columns....so that's why the error is coming....

just follow this code and hope you will solve ur problem...

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim listitem As listitem

this is for adding the columns to the lv control
[B]Dim colx As Columnheader[/B]
this is for measuring column widths
[B]Dim colWidth As Double[/B]

Private Sub Form_Load()
Set con = New ADODB.Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")

Set rs = New ADODB.Recordset
[B]rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic[/B]

StrSql = "Select"
StrSql = StrSql & "[supplier_name],[type],[fax_no],[contact_person] from [Supplier]"

[B]rs.Open StrSql, con[/B]
   
ListView1.View = lvwReport

checking the width of listview control and divided by 5...so that all columns become of same width...here we used 5 because the control will have 5 columns
[B]colWidth = ListView1.Width / 5[/B]

here we are adding the columns
you need similar no. of columns as much no. of listitems you want to add to the listview. now if you want to hide any column from displaying, just set its width to 0.
[B]With ListView1
    .ColumnHeaders.Clear
    Set colx = .ColumnHeaders.ADD(, , ("Supplier No"), colWidth)
    Set colx = .ColumnHeaders.ADD(, , ("Supplier Name"), colWidth)
    Set colx = .ColumnHeaders.ADD(, , ("Type"), colWidth)
    Set colx = .ColumnHeaders.ADD(, , ("Fax No"), colWidth)
    Set colx = .ColumnHeaders.ADD(, , ("Contact Person"), colWidth)
End With[/B]
   
adding rows to listview
[B]With ListView1
    .ListItems.Clear
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        While Not rs.EOF()
            Set listitem = .ListItems.ADD(, , (supplier_no))
            listitem.SubItems(1) = rs!supplier_name
            listitem.SubItems(2) = rs!Type
            listitem.SubItems(3) = IIf(IsNull(rs!fax_no), "", rs!fax_no)
            listitem.SubItems(4) = rs!contact_person
            
            rs.MoveNext
        Wend
    End If
End With[/B]

closing the recordset so the memory can be utilized again and also releasing its current instance from the memory
[B]If rs.state = adStateOpen Then rs.Close
Set rs = Nothing[/B]
End Sub

Hope this helps.....please make a feedback here...

regards
Shouvik

[choudhuryshouvi]

Well it's very clear as the sunshine. You have just used the "rs" but not declaring/setting it.

that's true....he forgot to create an instance....but already fixed that...
he..he.

[firoz.raj]

Now i got item cannot be found in the collection corresponding to the requested name or ordinal.kindly help me anybody.

With ListView1
    .ListItems.Clear
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        While Not rs.EOF()
            Set listitem = .ListItems.Add(, , (supplier_no))
'           listitem.SubItems(1) = rs!supplier_name
            listitem.SubItems(1) = IIf(IsNull(rs!supplier_name), "", rs!supplier!Name)
            [B]listitem.SubItems(3) = IIf(IsNull(rs_Type), "", rs!Type)
            listitem.SubItems(3) = IIf(IsNull(rs_fax_no), "", rs!fax_no)
            listitem.SubItems(4) = IIf(IsNull(rs!contact_person), "", rs![/B]contact_person)
'           listitem.SubItems(4) = rs!contact_person
            
            rs.MoveNext
        Wend
    End If
End With

[choudhuryshouvi]

item cannot be found in the collection corresponding to the requested name or ordinal --> this means you donot have a column with name "supplier_name" in the table...

check the sql statement...whether you are opening the correct table or not...
the table must have the column "supplier_name" in it....

for better observation please upload your full code here....

regards
Shouvik

[firoz.raj]

Supplier.zip

kindly find the attachment.

[firoz.raj]

error.doc

i am getting invalid property value.can anyone tell me .how should
i resolve it ? Any help would be greately appreciated.listitem.SubItems(4) = IIf(IsNull(rs!contact_person), "", rs!contact_person)

Dim colx As ColumnHeader
'this is for measuring column widths
Dim colWidth As Double
Private Sub Form_Load()
Set con = New ADODB.Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\asfserver\itp$\Product_tabletest.mdb")
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
StrSql = "Select"
StrSql = StrSql & "[supplier_name],[type],[fax_no],[contact_person] from [Supplier]"

rs.Open StrSql, con

ListView1.View = lvwReport

'checking the width of listview control and divided by 5...so that all columns become of same width...here we used 5 because the control will have 5 columns
colWidth = ListView1.Width / 5

'here we are adding the columns
'you need similar no. of columns as much no. of listitems you want to add to the listview. now if you want to hide any column from displaying, just set its width to 0.
With ListView1
.ColumnHeaders.Clear
' Set colx = .ColumnHeaders.Add(, , ("Supplier No"), colWidth)
Set colx = .ColumnHeaders.Add(, , ("Supplier Name"), colWidth)
Set colx = .ColumnHeaders.Add(, , ("Type"), colWidth)
Set colx = .ColumnHeaders.Add(, , ("Fax No"), colWidth)
Set colx = .ColumnHeaders.Add(, , ("Contact_Person"), colWidth)
End With
'adding rows to listview
With ListView1
.ListItems.Clear
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF()
Set listitem = .ListItems.Add(, , , (supplier_no))
' listitem.SubItems(1) = rs!supplier_name
listitem.SubItems(1) = IIf(IsNull(rs!supplier_Name), "", rs!supplier_Name)
listitem.SubItems(2) = IIf(IsNull(rs_Type), "", rs!Type)
listitem.SubItems(3) = IIf(IsNull(rs!fax_no), "", rs!fax_no)
listitem.SubItems(4) = IIf(IsNull(rs!contact_person), "", rs!contact_person)
rs.MoveNext
Wend
End If
End With

[choudhuryshouvi]

are you out of your mind?
what else someone understand from just a scratch.....you have just post a form...what could someone understand from this....

if you really want some solutions....if u really need...then post your fullcode..

[firoz.raj]

i am getting invalid property value.here is the following code.

Private Sub Form_Load()
Set con = New ADODB.Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic

StrSql = "Select"
StrSql = StrSql & "[supplier_name],[type],[fax_no],[contact_person] from [Supplier]"

rs.Open StrSql, con
   
ListView1.View = lvwReport

'checking the width of listview control and divided by 5...so that all columns become of same width...here we used 5 because the control will have 5 columns
colWidth = ListView1.Width / 5

'here we are adding the columns
'you need similar no. of columns as much no. of listitems you want to add to the listview. now if you want to hide any column from displaying, just set its width to 0.
With ListView1
    .ColumnHeaders.Clear
'    Set colx = .ColumnHeaders.Add(, , ("Supplier No"), colWidth)
    Set colx = .ColumnHeaders.Add(, , ("Supplier Name"), colWidth)
    Set colx = .ColumnHeaders.Add(, , ("Type"), colWidth)
    Set colx = .ColumnHeaders.Add(, , ("Fax No"), colWidth)
    Set colx = .ColumnHeaders.Add(, , ("Contact Person"), colWidth)
End With
'adding rows to listview
 With ListView1
   .ListItems.Clear
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        While Not rs.EOF()
            Set listitem = ListView1.ListItems.Add(, , supplier_name)
            listitem.SubItems(1) = IIf(IsNull(rs!supplier_name), "", rs!supplier_name)
            listitem.SubItems(2) = IIf(IsNull(rs!Type), "", rs!Type)
            listitem.SubItems(3) = IIf(IsNull(rs!fax_no), "", rs!fax_no)
          [B]  listitem.SubItems(4) = IIf(IsNull(rs!contact_person), "", rs![/B]contact_person)
          rs.MoveNext
        Wend
    End If
End With
End Sub