Populating a ListView with SQL Server data

[jpower69]

I have the following code;
Dim conn As New SqlConnection()
Dim cmd As New SqlCommand
Dim myreader As SqlDataReader
Dim adapter As New SqlDataAdapter

lsql = "Select effect_dat, fxrate, currency from [sec_mast].[dbo].[sec_fxrt] where currency = 'EUR' order by effect_dat desc"

conn = New SqlConnection(strconnect)

Try
conn.Open()
Catch ex As Exception
MessageBox.Show("Connection To Server failed")
End Try

cmd.Connection = conn

Dim dm As New DataTable

Try
cmd = New SqlCommand(lsql, conn)
adapter.SelectCommand = cmd
adapter.Fill(dm)
adapter.Dispose()
cmd.Dispose()

Catch ex As Exception
MessageBox.Show("cannot open connection")

End Try

Dim myrow As DataRow
'
' following loads the data into the listview
'
For Each myrow In dm.Rows

ListView1.Items.Add(myrow.Item(0))
ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(myrow.Item(1))
ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(myrow.Item(2))
ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(myrow.Item(3))

Next
My problem is that the SQL statements returns 3898 rows of data but only the first row gets displayed in the listview
I have similar code that works using oledbdataadapter

can anyone point out what I am missing here..

thanks in advance for any comments/suggestions

 

[jebenson]

Try creating a ListViewItem object, populating that then adding it to the ListView:

[red]Dim lvi As ListViewItem[/red]

For Each myrow In dm.Rows

[red]lvi = New ListViewItem(myrow.Item(0))[/red]

[red]lvi[/red].SubItems.Add(myrow.Item(1))
[red]lvi[/red].SubItems.Add(myrow.Item(2))
[red]lvi[/red].SubItems.Add(myrow.Item(3))

[red]
ListView1.Items.Add(lvi)

lvi = Nothing[/red]

Next

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[jpower69]

Thanks for the info

will try it and let you know the results

again thanks

 

[jpower69]

I tried the statements
Dim myrow As DataRow
Dim lvi As New ListViewItem
'
' following loads the data into the listview
'
For Each myrow In dm.Rows

lvi = New ListViewItem(myrow.Item(0))

lvi.SubItems.Add(myrow.Item(1))
lvi.SubItems.Add(myrow.Item(2))
lvi.SubItems.Add(myrow.Item(3))

ListView1.Items.Add(lvi)

lvi = Nothing

Next
and I get
Error 1 Overload resolution failed because no accessible 'New' can be called without a narrowing conversion:
'Public Sub New(group As System.Windows.Forms.ListViewGroup)': Argument matching parameter 'group' narrows from 'Object' to 'System.Windows.Forms.ListViewGroup'.
'Public Sub New(items() As String)': Argument matching parameter 'items' narrows from 'Object' to '1-dimensional array of String'.
'Public Sub New(text As String)': Argument matching parameter 'text' narrows from 'Object' to 'String'. C:\Users\JPower\documents\visual studio 2012\Projects\WindowsApplication11\WindowsApplication11\Fxrates.vb 60 19 WindowsApplication11

not sure what this means

 

[jpower69]

Got it to work using the following statements
myreader = cmd.ExecuteReader
If myreader.HasRows Then
Do While myreader.Read()
Dim item As New ListViewItem
item.Text = myreader("effect_dat")
item.SubItems.Add(myreader("fxrate"))
item.SubItems.Add(myreader("currency"))
ListView1.Items.Add(item)
Loop
Else
MsgBox("no records")
End If
Thanks for your help...