在VB Studio 2008和后端的MS Access中创建的Window窗体应用程序中的数据检索 [英] Data retrieval in Window form application created in VB Studio 2008 and MS Access at backend

问题描述

我在应用程序中有两种形式，即客户端策略和客户端策略.我想发生的是，当我从客户端策略表单上的组合框中选择client_no时，立即的client_name字段将自动填充为该所选对象保存的客户端名称 使用ms访问数据库的客户端形式的客户编号.请帮助我......我是初学者，只能从这里学习...您的回复可以帮助我完成我的项目...

I have two form namely client and client Policy in my application.What I want to happen is when I will select client_no from combobox on client policy form the immediate client_name field will get automatically filled with client name saved for that selected client no in client form using ms access database.Please help me......I am a beginner and learning only from here...Your reply can help me complete my project ...

推荐答案

我发布了一个回复，并删除了它，因为有更好的方法.

I posted a reply and removed it as there is a better way.

表格代码

Public Class ExampleForm
    WithEvents bsCustomers As New BindingSource
    Private Sub ExampleForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim ops As New Operations
        If ops.LoadCustomers Then
            bsCustomers.DataSource = ops.CustomerDataTable
            ComboBox1.DisplayMember = "CompanyName"
            ComboBox1.ValueMember = "Identifier"
            ComboBox1.DataSource = bsCustomers
            bsCustomers.Position = -1
            AddHandler bsCustomers.PositionChanged, AddressOf bsCustomers_PositionChanged
        End If
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ops As New Operations
        If ops.UpdateRecord(CType(bsCustomers.Current, DataRowView).Row.Field(Of Integer)("Identifier"), TextBox1.Text) Then
            CType(bsCustomers.Current, DataRowView).Row.SetField(Of String)("ContactName", TextBox1.Text)
            MessageBox.Show("Updated")
        Else
            MessageBox.Show("Updated failed")
        End If

    End Sub

    Private Sub bsCustomers_PositionChanged(sender As Object, e As EventArgs) 'Handles bsCustomers.PositionChanged
        If CType(bsCustomers.Current, DataRowView).Row.Field(Of Integer)("Identifier") > -1 Then
            TextBox1.Text = CType(bsCustomers.Current, DataRowView).Row.Field(Of String)("ContactName")
        Else
            TextBox1.Text = ""
        End If
    End Sub
End Class

用于读取和更新的类.

Public Class Operations
    Public Property HasErrors As Boolean
    Public Property ErrorMessage As String
    Public Property ExceptionThrown As Exception
    Public Property CustomerDataTable As DataTable

    Private ConnectionBuilder As New OleDb.OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb"))
        }

    Public Function LoadCustomers() As Boolean
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = ConnectionBuilder.ConnectionString}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT 
                            Identifier, 
                            CompanyName, 
                            ContactName, 
                            ContactTitle, 
                            Country 
                        FROM 
                            Customer
                    </SQL>.Value

                CustomerDataTable = New DataTable

                Try
                    cn.Open()

                    CustomerDataTable.Load(cmd.ExecuteReader)

                    CustomerDataTable.Columns("Identifier").ColumnMapping = MappingType.Hidden
                    CustomerDataTable.Columns("Country").ColumnMapping = MappingType.Hidden
                    Dim newrow As DataRow = CustomerDataTable.NewRow
                    newrow.SetField(Of String)("CompanyName", "Select one")
                    newrow.SetField(Of Integer)("Identifier", -1)
                    CustomerDataTable.Rows.InsertAt(newrow, 0)


                    Return True

                Catch ex As Exception
                    HasErrors = True
                    ExceptionThrown = ex
                End Try

                Return False

            End Using
        End Using
    End Function
    Public Function UpdateRecord(ByVal CustomerIdentifier As Integer, ByVal ContactName As String) As Boolean
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = ConnectionBuilder.ConnectionString}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText = "UPDATE Customer SET ContactName = @ContactName WHERE Identifier=@Identifier;"
                cmd.Parameters.AddWithValue("@ContactName", ContactName)
                cmd.Parameters.AddWithValue("@Identifier", CustomerIdentifier)
                cn.Open()
                Return cmd.ExecuteNonQuery = 1
            End Using
        End Using

    End Function

End Class

在SELECT语句中，我使用XML文字，如果您的Visual Studio版本不支持此文字，则对CommandText使用字符串.

In the SELECT statement I use XML literals, if your version of Visual Studio does not support this then use a string for the CommandText.

初始负载

选择后

请注意，在更新中，一旦数据库更新成功，我就会同时更新后端表和基础DataTable.

Note in the update I update the backend table along with the underlying DataTable upon success of the database update.

当然，如果您不使用OleDb，而是使用数据向导，则代码将不同，但逻辑相同.我不喜欢数据向导，因为它们会给您带来麻烦.对于简单的项目，向导只需要较少的代码，但是如果您具有中等或更高的水平，则向导会更少 项目向导很麻烦.

Of course if you are not using OleDb but instead data wizards the code will be different but the logic is the same. I don't favor data wizards as they can get in the way. Wizards requires less code for simple projects but when you have a medium or better project wizards are cumbersome. 

这篇关于在VB Studio 2008和后端的MS Access中创建的Window窗体应用程序中的数据检索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！