Why I am getting System.Data.DataRowView” instead

问题:

Could someone help here?

I need to extract data from a Database into a combolistbox in VB.net. I have got the data, but now find that The first and the 'x' line need to be removed from the combolistbox (they are validation entries for another software) and shouldn't be selected for this application.

I tried to simply remove the offending entries from lists by using :- cbCubeARivet.Items.RemoveAt(index), but had an error letting me know I cannot use "Items" with a DataSource.

I decided to send the data to a listbox, and then try to transfer the entries to the combolistbox. This then lead me to getting multiple entries of System.Data.DataRowView in the combolist box. To demonstrate my problem I include an example code modified from MSDN.

Imports System
Imports System.Windows.Forms
Imports System.Drawing
Imports System.Collections
Imports System.Data.SqlClient

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ' Populate the list box using an array as DataSource. 
        Dim SQLConnectionString As String = "Data Source=HL605\RIVWARE;Database=RIVWARE;Integrated Security=true;"
        Dim mySQLConnection As New SqlConnection(SQLConnectionString)
        mySQLConnection.Open()
        Dim SQLDataTable As New System.Data.DataTable

        'Create new DataAdapter
        'Use DataAdapter to fill DataTable
        Dim mySQLDataAdapter = New SqlDataAdapter("SELECT * FROM [Rivware].[dbo].[RivetTypes]", mySQLConnection)
        mySQLDataAdapter.Fill(SQLDataTable)
        ListBox1.DataSource = SQLDataTable
        ListBox1.DisplayMember = "RivetType"


        'original code from MSDN
        'Dim USStates As New ArrayList()
        'USStates.Add(New USState("Alabama", "AL"))
        'USStates.Add(New USState("Washington", "WA"))
        'USStates.Add(New USState("West Virginia", "WV"))
        'USStates.Add(New USState("Wisconsin", "WI"))
        'USStates.Add(New USState("Wyoming", "WY"))
        'ListBox1.DataSource = USStates

        ' Set the long name as the property to be displayed and the short
        ' name as the value to be returned when a row is selected.  Here
        ' these are properties; if we were binding to a database table or
        ' query these could be column names.

        ' Bind the SelectedValueChanged event to our handler for it.
        AddHandler ListBox1.SelectedValueChanged, AddressOf ListBox1_SelectedValueChanged

        ' Ensure the form opens with no rows selected.
        ListBox1.ClearSelected()
    End Sub 'NewNew

    Private Sub ListBox1_SelectedValueChanged(ByVal sender As Object, ByVal e As EventArgs)
        If ListBox1.SelectedIndex <> -1 Then
            TextBox1.Text = ListBox1.SelectedValue.ToString()
            ' If we also wanted to get the displayed text we could use
            ' the SelectedItem item property:
            ' Dim s = CType(ListBox1.SelectedItem, USState).LongName
        End If

    End Sub
End Class 'ListBoxSample3

Public Class USState
    Private myShortName As String
    Private myLongName As String

    Public Sub New(ByVal strLongName As String, ByVal strShortName As String)
         Me.myShortName = strShortName
         Me.myLongName = strLongName
    End Sub 'NewNew

    Public ReadOnly Property ShortName() As String
        Get
            Return myShortName
        End Get
    End Property
    Public ReadOnly Property LongName() As String
        Get
            Return myLongName
        End Get
    End Property
End Class 'USState

回答1:

I may not get this correct so here goes, the following uses mocked up data to display a string in both a ListBox and ComboBox where a integer is available by casting the current item as a DataRowView, access Row then access the data via Row.Field(Of Integer)("ID").

In the code I use a copy of the underlying DataTable for the ComboBox as using the same data table for listbox and combobox will cause one to traverse when is generally unwanted. The cast aspect would be done in another event but wanted to stay simple. Again I may not be on track here, let me know and can adjust to better suit your question.

Code using Option Infer On for the Linq anonymous statement which could be strongly typed too.

Dim dt As New DataTable
dt.Columns.Add(New DataColumn With {.ColumnName = "ID", .DataType = GetType(Integer)})
dt.Columns.Add(New DataColumn With {.ColumnName = "Name", .DataType = GetType(String)})

Dim data =
    (
        From M In System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthNames
        Where Not String.IsNullOrEmpty(M)).ToList.Select(
        Function(monthName, index) New With
            {
                .ID = index, .Name = monthName
            }
    ).ToList
For Each item In data
    dt.Rows.Add(New Object() {item.ID, item.Name})
Next

ListBox1.DataSource = dt
ListBox1.DisplayMember = "Name"

ComboBox1.DataSource = dt.Copy
ComboBox1.DisplayMember = "Name"

Dim theTable As DataTable = CType(ComboBox1.DataSource, DataTable)
Dim theRow As DataRow = theTable.AsEnumerable _
        .Where(
            Function(row) row.Field(Of String)("Name") = "September") _
        .FirstOrDefault()

If theRow IsNot Nothing Then
    theTable.Rows.Remove(theRow)
End If

回答2:

Thanks again @Karen Payne,

I used your code to lead me in the right direction.

I created a new application and added a textbox, and two Listboxes, then paste the code. To run you will need to point to your own Server, Database, and Table.

This is what I came up with. It is useful as this will give you the actual data in a useable form:-

Imports System
Imports System.Windows.Forms
Imports System.Drawing
Imports System.Collections
Imports System.Data.SqlClient

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' get the data
        Dim SQLConnectionString As String = "Data Source=HL605\RIVWARE;Database=RIVWARE;Integrated Security=true;"
        Dim mySQLConnection As New SqlConnection(SQLConnectionString)

        ' Populate the list box using an array as DataSource. 
        mySQLConnection.Open()
        Dim SQLDataTable As New System.Data.DataTable

        'Create new DataAdapter
        Dim mySQLDataAdapter = New SqlDataAdapter("SELECT * FROM [Rivware].[dbo].[RivetTypes]", mySQLConnection)
        mySQLDataAdapter.Fill(SQLDataTable)
        ListBox1.DataSource = SQLDataTable
        ListBox1.DisplayMember = "RivetType"

        ' remove validation data from list
        Dim Count As Integer
        Dim X As Integer
        'get the column of data to search for
        For Count = 0 To ListBox1.DataSource.Columns.Count - 1
            X = InStr(ListBox1.DataSource.Columns.Item(Count).ToString, "Name")
            If X <> 0 Then Exit For
        Next
        ' now search for any invalid rows, in that column. those containing "---"
        Dim TheTable As DataTable = CType(ListBox1.DataSource, DataTable)
        Dim theRow As DataRow() = TheTable.Select()
        Dim RowNumber As Integer
        For RowNumber = 0 To UBound(theRow) - 1
            If theRow(RowNumber).Item(Count).ToString <> "---" Then
                ' data is OK so transer it to the other listbox
                ListBox2.Items.Add(theRow(RowNumber).Item(Count - 1).ToString)
            End If
        Next

        ListBox2.ClearSelected()
    End Sub 'NewNew

    Private Sub ListBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox2.SelectedIndexChanged
        TextBox1.Text = ListBox2.SelectedItem.ToString()
    End Sub
End Class 'ListBoxSample3