help with fuctions [RESOLVED]

[Andy]

Has this been done before?
I want to take a datareader and send that to a function along with a listview control as params. I would like the function to take the data in the reader and create a listview item and subitems in the listview parmeter. I thought making it as a function would allow returning true or false easy.

is this a feasable function? where would i start?

I figure something like this:

[vbode]
public function FillListView(lst as ListView, items as Collection) as boolean

end function
[/Highlight]

that's as far as i got.

[scavenger]

Yellow!


I would like to suggest this code.
If you want Andy, you can examine it...
Hope this one helps.... I used this one in my code.
ahehe...

Public Function FillListView(ByRef lst As ListView) As Boolean
        Dim SQLComm As New SqlCommand()
        Dim SQLConn As New SqlConnection("<your connection string here>")
        Dim DatReader As SqlDataReader
        Dim i, index As Integer
        index = 0
        SQLComm.CommandText = "<your command text here>"
        SQLConn.Open()
        SQLComm.Connection = SQLConn
        With SQLComm
            .Parameters.Add("<Parameter Name here>")
            'You can add as many parameters as you desire. But it must match the parameters in your commandtext
            DatReader = .ExecuteReader
            While DatReader.Read
                lst.Items.Add(IsDataNull(DatReader, 0)) 'First Item
                if DatReader.FieldCount > 0 then
 
                For i = 1 To DatReader.FieldCount - 1 'If your commandtext has many rows to fetch
                    ' we deducted 1 to the fieldcount becoz it is base-1 not zero-based
                    ' we started at 1 becoz we already fetch the first data
                    lst.Items(index).SubItems(i).Text = IsDataNull(DatReader, i)
                Next
                End If
                index += 1
                'move on the next fetch
            End While
            DatReader.Close()
        End With
        If lst.Items.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
 
    Private Function IsDataNull(ByVal DataReader As SqlDataReader, ByVal index As Integer) As String
        If IsDBNull(DataReader.GetValue(index)) Then
            Return " "
            'since listviewitems does not allow nulls to be displayed only strings
        Else
            Return DataReader.GetValue(index)
            'just return the original value 
        End If
    End Function

Hope this helps...if not...my apologies....

[brown monkey]

got this also

Option Strict On
 
Imports System.Data.SqlClient
Public Class Sample
   Public Shared Function fillListView(ByVal lv As ListView, ByVal s As SqlDataReader) As Boolean
      Try
         If Not s.Read Then
            Return False
         End If
         Do
            Dim i As Integer
            Dim li As ListViewItem = lv.Items.Add(s(0).ToString)
            For i = 1 To s.FieldCount - 1
               li.SubItems.Add(s(i).ToString)
            Next
         Loop While s.Read
         s.Close()
         Return True
      Catch ex As Exception
         Return False
      End Try
   End Function
End Class

sample usage

Dim cn As New SqlConnection("user id=sa;password=password;initial catalog=northwind")
   Dim da As New SqlDataAdapter()
   Dim dt As New DataTable()
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      cn.Open()
      da.SelectCommand = New SqlCommand("select * from territories ", cn)
      If (Sample.fillListView(ListView1, da.SelectCommand.ExecuteReader)) Then
         MessageBox.Show("there is")
      Else
         MessageBox.Show("none")
      End If
   End Sub

i know this isn't efficient enough, hope someone else share their ideas.

[Andy]

Here's what I came up with:

Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim con As New SqlClient.SqlConnection("Initial Catalog=Northwind;Data Source=laptop;user id=sa;Workstation ID=LAPTOP")
            Dim cmd As New SqlClient.SqlCommand("select * from employees", con)
 
            cmd.CommandType = CommandType.Text
            cmd.Connection.Open()
            Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader
            FillListView(ListView1, dr)
            dr.Close()
            cmd.Connection.Close()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
 
    End Sub
    Shared Function FillListView(ByVal lst As ListView, ByVal dr As SqlClient.SqlDataReader) As Boolean
        'Purpose :  Fills  lines of a listview control .
        Try
            Dim col As Integer
            While dr.Read
                'Create the first item.
                col = 0
                lst.Columns.Add(dr.GetName(col), 102, HorizontalAlignment.Left)
                lst.Items.Add(dr.Item(col).ToString)
                'create the column names.
                'don't include binaries or nulls.
                While col < dr.FieldCount - 1
                    col += 1
                    If Not (dr.Item(col).GetType.ToString = "System.Byte[]") And _
                    Not dr.IsDBNull(col) Then
                        'add the sub items and their column names.
                        lst.Columns.Add(dr.GetName(col), 102, HorizontalAlignment.Left)
                        lst.Items(0).SubItems.Add(CStr(dr.Item(col)))
                    End If
                End While
            End While
 
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
 
    End Function

only problem is, the first row ends up with two records, and then that's it. the other rows only show the first item.

[Andy]

ok, I added an integer variable called 'row' under the 'col' variable and increase that by one after the 'while' that is adding columns and that helps but the data is still off kilter.

slowly and surely

[Andy]

Got it all figured out:

Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim con As New SqlClient.SqlConnection("Initial Catalog=Northwind;Data Source=laptop;user id=sa")
            Dim cmd As New SqlClient.SqlCommand("select * from employees", con)
 
            cmd.CommandType = CommandType.Text
            cmd.Connection.Open()
            Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader
            FillListView(ListView1, dr)
            dr.Close()
            cmd.Connection.Close()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
 
    End Sub
    Shared Function FillListView(ByVal lst As ListView, ByVal dr As SqlClient.SqlDataReader) As Boolean
        'Purpose :  Fills  lines of a listview control .
        Try
            Dim col As Integer
            Dim row As Integer
            While dr.Read
                'Create the first item.
                col = 0
                lst.Items.Add(dr.Item(col).ToString)
                While col < dr.FieldCount - 1
                    'add the sub items and their column names.
                    'filter out anything that may cause an exception.
                    col += 1
                    Select Case True
                        Case dr.Item(col).GetType.ToString = "System.Byte[]"
                            lst.Items.Item(row).SubItems.Add("NA")
                        Case dr.IsDBNull(col)
                            lst.Items.Item(row).SubItems.Add("NA")
                        Case Else
                            lst.Items.Item(row).SubItems.Add(CStr(dr.Item(col)))
                    End Select
                End While
                row += 1
            End While
            'add the column headers.
            For x As Integer = 0 To dr.FieldCount - 1
                lst.Columns.Add(dr.GetName(x), 100, HorizontalAlignment.Left)
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
 
        Return True
    End Function