vb.net 如何在vb.net的列表框中填充数据库中的项目

how to populate items from database in a listbox in vb.net

提问by Nee-Z

I was developing an application using oop concept.I have a class that has 2 attributes and have Get and Set methods namely WorkItemNumber and Description.

我正在使用 oop 概念开发一个应用程序。我有一个具有 2 个属性的类，并具有 Get 和 Set 方法，即 WorkItemNumber 和 Description。

On the client side i have a list box used to populate the work items based on their description.Here's the code i wrote in the class o read items from the database.

在客户端，我有一个列表框，用于根据它们的描述填充工作项。这是我在类中编写的代码 o 从数据库中读取项目。

Public Sub LoadWorkItem()
    ' Load the data.
    ' Select records.
    Dim oWorkItem As WorkItem = New WorkItem()
    Dim conn As New OleDbConnection
    Dim data_reader As OleDbDataReader
    conn = oWorkItem.GetDbConnection()
    Dim cmd As New OleDbCommand("SELECT * FROM work_item ORDER BY [work item number]", conn)
    data_reader = cmd.ExecuteReader()
    'ListBox1.Items.Clear()
    If data_reader.HasRows = True Then
        Do While data_reader.Read()
            WorkItemNumber = data_reader.Item("work item number")
            Description = data_reader.Item("description")
        Loop
    End If
    data_reader.Close()
    data_reader = Nothing
    cmd.Dispose()
    cmd = Nothing
    conn.Close()
    conn.Dispose()
End Sub

How do i populate the listbox using the code,and if there's any improvement on the code please do tell me as well.Thank you

我如何使用代码填充列表框，如果代码有任何改进，也请告诉我。谢谢

回答by nunzabar

To poulate your ListBox, do this...

要填充您的列表框，请执行以下操作...

ListBox1.Item.Clear()
If data_reader.HasRows Then
    Do While data_reader.Read()
        WorkItemNumber = data_reader.Item("work item number")
        Description = data_reader.Item("description")
        ListBox1.Items.Add(New ListItem(Description, WorkItemNumber)
    Loop
End If

As far as improvements, start by using a Usingstatement for the DB connection. In your code, if there is an exception while the database connection is open, it will never get closed. This is better...

就改进而言，首先对数据库连接使用 Using语句。在您的代码中，如果在数据库连接打开时出现异常，它将永远不会关闭。这个更好...

Using conn As OleDbConnection = oWorkItem.GetDbConnection()
    ' Execute SQL and populate list... 
End Using

The above code assures that your connection will be closed.

上面的代码确保您的连接将被关闭。

Then, turn on Option Strict and Option Explicit. This will force you to declare the Type for Description and WorkItemNumber and cast them as Strings when adding a ListItem. This will reduce run-time errors.

然后，打开Option Strict 和 Option Explicit。这将强制您声明 Description 和 WorkItemNumber 的类型，并在添加 ListItem 时将它们转换为字符串。这将减少运行时错误。

Finally, if this is anything but a small app you are doing as a learning experiment, you should read up on tiered application design. Your code is mixing UI, business logic, and data access in the same method. This is generally frowned upon.

最后，如果这不是您作为学习实验做的一个小应用程序，您应该阅读分层应用程序设计。您的代码在同一方法中混合了 UI、业务逻辑和数据访问。这通常是不受欢迎的。

• Your "user interface" LoadWorkItem() method should ask a "core" method for a list of WorkItems.
• Your core method should then ask a "data access" method for data.
• The "data access" method should make the call to the database.

• 您的“用户界面” LoadWorkItem() 方法应该询问“核心”方法以获得 WorkItem 列表。
• 然后，您的核心方法应该询问数据的“数据访问”方法。
• “数据访问”方法应该调用数据库。

Happy coding.

快乐编码。

Update:You can find excellent info about n-Tier architecture on MSDN. A good book to read once you grasp the fundamentals and have some confidence in .NET is Visual Basic .NET Business Objects.

更新：您可以在MSDN上找到有关 n 层架构的优秀信息。一旦您掌握了基础知识并对 .NET 有一定的信心，就可以阅读一本好书是Visual Basic .NET Business Objects。

回答by Mark Tarver

    Imports System.Data.SqlClient 'Reference The Sql Client

    Public Class Form1
   ''Make sure to change the connection string below to your connection string this code only works for SQL DataBase. If Your connection String is wrong This will Not Work

    Dim connString As String = "Data         
    Source=NameofYourSQLServer\SQLEXPRESS;Initial Catalog=NameOfYourDataBase;Integrated Security=True"

Dim tblDIV As DataTable
Dim daDIV As SqlDataAdapter
Dim dsDIV As New DataSet
Dim oCon As SqlConnection

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

    Dim oCon = New SqlConnection
     oCon.ConnectionString = connString
     dsDIV = New DataSet

  ' Select all Fields and order by ID or Replace * with name of Field

   daDIV = New SqlDataAdapter("SELECT * FROM NameOfYourTable ORDER BY Id DESC", oCon)
    '*** Define command builder to generate the necessary SQL
    Dim builder As SqlCommandBuilder = New SqlCommandBuilder(daDIV)
    builder.QuotePrefix = "["
    builder.QuoteSuffix = "]"

    Try
        daDIV.FillSchema(dsDIV, SchemaType.Source, "DIV")
        daDIV.Fill(dsDIV, "DIV")
        tblDIV = dsDIV.Tables("DIV")
        ListBox1.DataSource = tblDIV
        ListBox1.DisplayMember = "NameOfTheFieldYouWanttoDisplay"
    Catch ex As Exception

        MsgBox("Encountered an Error;" & vbNewLine & ex.Message)

        oCon.Close()

    End Try
End Sub