ListView mit SqlDataAdapter, DataSet und und DataView befüllen

Question

Hallo zusammen,

ich bin hier dabei, eine ListView mit Daten aus einer MS-SQL-Datenbank zu befüllen, aber leider komme ich nicht weiter. Kann mir einer sagen, wo es hakt?

    Public Sub sub_publistview_select()
        Dim var_connectionstring As String
        Dim var_connection As SqlConnection
        Dim var_sql As String
        Dim var_sqldataadapter As SqlDataAdapter
        Dim var_dataset As DataSet
        Dim var_dataview As DataView
        var_connectionstring = "Server=" & global_variables.var_schroederstest_dbserver & ";Database=" & global_variables.var_schroederstest_db & ";" &
                               "User Id=" & global_variables.var_schroederstest_dbuser & ";Password=" & global_variables.var_schroederstest_dbpass & ""
        var_connection = New SqlConnection(var_connectionstring)
        var_connection.Open()
        var_sql = "SELECT                                               " &
                  "Nummer, GUID, MUID, USERNAME, PASSWORD, IP,          " &
                  "ACTIVE, DELETED, VALIDITYDATE_01, VALIDITYDATE_02,   " &
                  "LOGIN_EXTERN, LOGIN_TRACERS                          " &
                  "FROM                                                 " &
                  "tblHoflisteBenutzer                                  "
        var_dataset = New DataSet()
        var_sqldataadapter = New SqlDataAdapter(var_sql, var_connection)
        var_sqldataadapter.Fill(var_dataset)
        var_dataview = New DataView(var_dataset.Tables("test"))
        var_connection.Close()
        For i = 0 To (var_dataview.Table.Columns.Count - 1)
            frm_hauptmaske.listview.Items.Add(var_dataview.Table.Columns(i).Caption + " " + var_dataview.Table.Rows(0).Item(i).ToString)
        Next
    End Sub

Was mache ich falsch?

Gruß

Thomas

Answer 1

Hi Thomas,

schau dir mal das Beispiel an.

MFG Björn

Answer 2

Hallo Björn,

mit dem DataReader habe ich es schon heute vormittag hinbekommen. Ich hatte im zweiten Schritt das Ziel, es auch mit SqlAdapter, DataSet und DataTable zu schaffen. Es ist mir in der letzten halben Stunde auch gelungen, nachdem ich doch noch ein verwertbares Beispiel im Web gefunden habe.

Vielen Dank für Dein Feedback.

Gruß

Thomas

Hier der Code für die Nachwelt:

    Public Sub sub_publistview_select()
  
      frm_hauptmaske.listview.Clear()

        Dim var_connectionstring As String
        Dim var_connection As SqlConnection
        Dim var_sql As String
        Dim var_sqldataadapter As SqlDataAdapter
        Dim var_dataset As DataSet
        Dim var_datatable As DataTable
        Dim var_datarow As DataRow
        Dim var_row As ListViewItem
       
 var_connectionstring = "Server=" & global_variables.var_schroederstest_dbserver & ";Database=" & global_variables.var_schroederstest_db & ";" &
                               "User Id=" & global_variables.var_schroederstest_dbuser & ";Password=" & global_variables.var_schroederstest_dbpass & ""
        var_connection = New SqlConnection(var_connectionstring)
        var_connection.Open()
        var_sql = "SELECT                                                                                                                               " &
                  "GUID,                                                                                                                                " &
                  "ISNULL(Nummer, '')           AS Nummer           ,                                                                                   " &
                  "                                                                                                                                     " &
                  "CASE WHEN MUID IS NULL THEN '' ELSE MUID END AS MUID,                                                                                " &
                  "                                                                                                                                     " &
                  "ISNULL(USERNAME, '')         AS USERNAME         ,   ISNULL(PASSWORD, '')        AS PASSWORD         ,                               " &
                  "ISNULL(IP, '')               AS IP               ,                                                                                   " &
                  "ISNULL(ACTIVE, 0)            AS ACTIVE           ,   ISNULL(DELETED, 0)          AS DELETED          ,                               " &
                  "                                                                                                                                     " &
                  "CASE WHEN VALIDITYDATE_01 IS NULL OR VALIDITYDATE_01 = '01.01.1900 00:00:00' THEN '' ELSE VALIDITYDATE_01 END AS VALIDITYDATE_01,    " &
                  "                                                                                                                                     " &
                  "CASE WHEN VALIDITYDATE_02 IS NULL OR VALIDITYDATE_02 = '01.01.1900 00:00:00' THEN '' ELSE VALIDITYDATE_02 END AS VALIDITYDATE_02,    " &
                  "                                                                                                                                     " &
                  "ISNULL(LOGIN_EXTERN, 0)      AS LOGIN_EXTERN     ,   ISNULL(LOGIN_TRACERS, 0)    AS LOGIN_TRACERS                                    " &
                  "FROM                                                                                                                                 " &
                  "tblHoflisteBenutzer                                                                                                                  "
        var_dataset = New DataSet()
        var_sqldataadapter = New SqlDataAdapter(var_sql, var_connection)
        var_sqldataadapter.Fill(var_dataset, "tbl_test")
        var_connection.Close()
        frm_hauptmaske.listview.View = View.Details
        With frm_hauptmaske.listview
            .View = View.Details
            .AllowColumnReorder = True
            .FullRowSelect = True
            .TabIndex = 0
            Call .Items.Clear()
            .Columns.Add("Nummer", 80, HorizontalAlignment.Right)
            .Columns.Add("GUID", 50, HorizontalAlignment.Right)
            .Columns.Add("MUID", 50, HorizontalAlignment.Right)
            .Columns.Add("Benutzername", 100, HorizontalAlignment.Left)
            .Columns.Add("Passwort", 100, HorizontalAlignment.Left)
            .Columns.Add("IP-Adresse", 100, HorizontalAlignment.Left)
            .Columns.Add("Aktiv", 100, HorizontalAlignment.Left)
            .Columns.Add("Gelöscht", 100, HorizontalAlignment.Left)
            .Columns.Add("Gültig von", 120, HorizontalAlignment.Left)
            .Columns.Add("Gültig bis", 120, HorizontalAlignment.Left)
            .Columns.Add("Anmeldung Portal", 150, HorizontalAlignment.Left)
            .Columns.Add("Anmeldung Hofliste", 150, HorizontalAlignment.Left)
        End With
        var_datatable = var_dataset.Tables.Item("tbl_test")
        For Each var_datarow In var_datatable.Rows
            var_row = New ListViewItem(var_datarow.Item("Nummer").ToString)
            var_row.SubItems.Add(var_datarow.Item("GUID").ToString)
            var_row.SubItems.Add(var_datarow.Item("MUID").ToString)
            var_row.SubItems.Add(var_datarow.Item("USERNAME").ToString)
            var_row.SubItems.Add(var_datarow.Item("PASSWORD").ToString)
            var_row.SubItems.Add(var_datarow.Item("IP").ToString)
            var_row.SubItems.Add(var_datarow.Item("ACTIVE").ToString)
            var_row.SubItems.Add(var_datarow.Item("DELETED").ToString)
            var_row.SubItems.Add(var_datarow.Item("VALIDITYDATE_01").ToString)
            var_row.SubItems.Add(var_datarow.Item("VALIDITYDATE_02").ToString)
            var_row.SubItems.Add(var_datarow.Item("LOGIN_EXTERN").ToString)
            var_row.SubItems.Add(var_datarow.Item("LOGIN_TRACERS").ToString)
            Call frm_hauptmaske.listview.Items.Add(var_row)
        Next
    End Sub