none
请指点有关open file dialog 的 读取mdb的代码? RRS feed

  • 问题

  • 我尝试且改篇写了file dialog read mdb file  的代。我遇到了下图问题

    以下是我的代码。

        Private Sub SelectTableButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SelectTableButton.Click
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then

                ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={OpenFileDialog1.FileName}"
                '"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={OpenFileDialog1.FileName}"

                Try
                    Using cn As New OleDbConnection(ConnectionString)

                        cn.Open()

                        Dim tableNames As List(Of String) =
                                cn.GetSchema("TABLES", {Nothing, Nothing, Nothing, "TABLE"}).AsEnumerable().
                                Select(Function(item) item.Field(Of String)("TABLE_NAME")).ToList

                        TableNamesListBox.DataSource = tableNames

                    End Using

                Catch ex As Exception
                    MessageBox.Show("Failed {ex.Message}")
                End Try
            End If

        End Sub

    Imports System.Data.OleDb
    Imports System.IO
    Imports System.Runtime.CompilerServices

    Public Module DataGridViewExtensions
        <Extension()>
        Public Sub ExpandColumns(ByVal sender As DataGridView)
            For Each col As DataGridViewColumn In sender.Columns
                col.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            Next
        End Sub
    End Module

    请指点


    2020年3月21日 10:00

答案

  • Hi christing,

    请参考以下更新后的代码:

    Public Property ConnectionString() As String Private bindingSource As New BindingSource Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click OpenFileDialog1.InitialDirectory = "D:\" OpenFileDialog1.Filter = "Access files (*.mdb)|*.mdb" OpenFileDialog1.FilterIndex = 0 OpenFileDialog1.RestoreDirectory = True If OpenFileDialog1.ShowDialog() = DialogResult.OK Then ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", {OpenFileDialog1.FileName}) Try Using cn As New OleDbConnection(ConnectionString) cn.Open() Dim tableNames As List(Of String) = cn.GetSchema("TABLES", {Nothing, Nothing, Nothing, "TABLE"}).AsEnumerable(). Select(Function(item) item.Field(Of String)("TABLE_NAME")).ToList TableNamesListBox.DataSource = tableNames End Using Catch ex As Exception MessageBox.Show(String.Format("Failed {0}", {ex.Message})) End Try End If End Sub Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click PopulateDataGridView() End Sub Private Sub PopulateDataGridView() If Not String.IsNullOrWhiteSpace(TableNamesListBox.Text) Then Using cn As New OleDbConnection(ConnectionString) Using cmd = New OleDbCommand With {.Connection = cn} Try cn.Open() cmd.CommandText = String.Format("SELECT * FROM [{0}]", {TableNamesListBox.Text}) Dim dt As New DataTable dt.Load(cmd.ExecuteReader()) bindingSource.DataSource = dt DataGridView1.DataSource = bindingSource DataGridView1.ExpandColumns() Text = String.Format("DB: {0}", Path.GetFileName(OpenFileDialog1.FileName)) & String.Format("Table: {0}", {TableNamesListBox.Text}) Catch ex As Exception MessageBox.Show(String.Format("Failed {0}", {ex.Message.ToString()})) End Try End Using End Using End If End Sub

    '...

    你无法使用‘$’的原因很可能是由于你的VB版本低于14,为了获得最新版本的最新功能,我建议你使用最新的visual studio进行编程。

    1. Visual Basic 的新增功能
    2. 内插字符串(Visual Basic 引用)

    希望可以帮助你解决问题。

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年3月24日 7:09
    版主

全部回复

  • Hi christing,

    我注意到你在VB的英文论坛也问了相同的问题,我根据Karen的建议做了相应的测试,并且发现测试过程没有出现其他问题。

    下面是我的完整测试的代码:

    Public Class Form1
        Public Property ConnectionString() As String
        Private bindingSource As New BindingSource
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            OpenFileDialog1.InitialDirectory = "D:\"
            OpenFileDialog1.Filter = "Access files (*.mdb)|*.mdb"
            OpenFileDialog1.FilterIndex = 0
            OpenFileDialog1.RestoreDirectory = True
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
    
                ConnectionString =
                    $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={OpenFileDialog1.FileName}"
    
                Try
                    Using cn As New OleDbConnection(ConnectionString)
    
                        cn.Open()
    
                        Dim tableNames As List(Of String) =
                                cn.GetSchema("TABLES", {Nothing, Nothing, Nothing, "TABLE"}).AsEnumerable().
                                Select(Function(item) item.Field(Of String)("TABLE_NAME")).ToList
    
                        TableNamesListBox.DataSource = tableNames
    
                    End Using
    
                Catch ex As Exception
                    MessageBox.Show($"Failed {ex.Message}")
                End Try
            End If
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            PopulateDataGridView()
        End Sub
    
        Private Sub PopulateDataGridView()
            If Not String.IsNullOrWhiteSpace(TableNamesListBox.Text) Then
                Using cn As New OleDbConnection(ConnectionString)
                    Using cmd = New OleDbCommand With {.Connection = cn}
    
                        Try
                            cn.Open()
    
                            cmd.CommandText = $"SELECT * FROM [{TableNamesListBox.Text}]"
    
                            Dim dt As New DataTable
                            dt.Load(cmd.ExecuteReader())
    
                            bindingSource.DataSource = dt
                            DataGridView1.DataSource = bindingSource
                            DataGridView1.ExpandColumns()
    
                            Text =
                                $"DB: {Path.GetFileName(OpenFileDialog1.FileName)} " &
                                $"Table: {TableNamesListBox.Text}"
    
                        Catch ex As Exception
                            MessageBox.Show($"Failed: {ex.Message.ToString()}")
                        End Try
    
                    End Using
                End Using
            End If
        End Sub
    End Class
    Public Module DataGridViewExtensions
        <Extension()>
        Public Sub ExpandColumns(sender As DataGridView)
            For Each col As DataGridViewColumn In sender.Columns
                col.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            Next
        End Sub
    End Module

    测试结果:

    如果这不能帮助你解决问题,为了重现你遇到的问题,我建议你提供完整的代码。

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年3月23日 7:05
    版主
  • @Xingyu Zhao

    感谢你的回复。我发现到我的microsoft visual studio 2010不能使用$。所有代码上出现$的字眼我都关了。也许是因为$所以我读取不到文件。 以下是我的代码。

    我是使用microsoft visual studio 2010来创建program

    Imports System.Data.OleDb
    Imports System.Runtime.CompilerServices

    Public Class Form3
        Public Property ConnectionString() As String
        Private bindingSource As New BindingSource
        Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        End Sub

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            OpenFileDialog1.InitialDirectory = "D:\"
            OpenFileDialog1.Filter = "Access files (*.mdb)|*.mdb"
            OpenFileDialog1.FilterIndex = 0
            OpenFileDialog1.RestoreDirectory = True
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then

                ConnectionString =
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={OpenFileDialog1.FileName}"

                Try
                    Using cn As New OleDbConnection(ConnectionString)

                        cn.Open()

                        Dim tableNames As List(Of String) =
                                cn.GetSchema("TABLES", {Nothing, Nothing, Nothing, "TABLE"}).AsEnumerable().
                                Select(Function(item) item.Field(Of String)("TABLE_NAME")).ToList

                        TableNamesListBox.DataSource = tableNames

                    End Using

                Catch ex As Exception
                    MessageBox.Show("Failed {ex.Message}")
                End Try
            End If
        End Sub

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            PopulateDataGridView()

        End Sub
        Private Sub PopulateDataGridView()
            If Not String.IsNullOrWhiteSpace(TableNamesListBox.Text) Then
                Using cn As New OleDbConnection(ConnectionString)
                    Using cmd = New OleDbCommand With {.Connection = cn}

                        Try
                            cn.Open()

                            cmd.CommandText = "SELECT * FROM [{TableNamesListBox.Text}]"

                            Dim dt As New DataTable
                            dt.Load(cmd.ExecuteReader())

                            bindingSource.DataSource = dt
                            DataGridView1.DataSource = bindingSource
                            DataGridView1.ExpandColumns()

                            Text =
                                "DB: {Path.GetFileName(OpenFileDialog1.FileName)} " &
                                "Table: {TableNamesListBox.Text}"

                        Catch ex As Exception
                            MessageBox.Show("Failed: {ex.Message.ToString()}")
                        End Try

                    End Using
                End Using
            End If
        End Sub
    End Class
    Public Module DataGridViewExtensions
        <Extension()>
        Public Sub ExpandColumns(ByVal sender As DataGridView)
            For Each col As DataGridViewColumn In sender.Columns
                col.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            Next
        End Sub
    End Module

     






    2020年3月23日 8:42
  • Hi christing,

    请参考以下更新后的代码:

    Public Property ConnectionString() As String Private bindingSource As New BindingSource Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click OpenFileDialog1.InitialDirectory = "D:\" OpenFileDialog1.Filter = "Access files (*.mdb)|*.mdb" OpenFileDialog1.FilterIndex = 0 OpenFileDialog1.RestoreDirectory = True If OpenFileDialog1.ShowDialog() = DialogResult.OK Then ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", {OpenFileDialog1.FileName}) Try Using cn As New OleDbConnection(ConnectionString) cn.Open() Dim tableNames As List(Of String) = cn.GetSchema("TABLES", {Nothing, Nothing, Nothing, "TABLE"}).AsEnumerable(). Select(Function(item) item.Field(Of String)("TABLE_NAME")).ToList TableNamesListBox.DataSource = tableNames End Using Catch ex As Exception MessageBox.Show(String.Format("Failed {0}", {ex.Message})) End Try End If End Sub Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click PopulateDataGridView() End Sub Private Sub PopulateDataGridView() If Not String.IsNullOrWhiteSpace(TableNamesListBox.Text) Then Using cn As New OleDbConnection(ConnectionString) Using cmd = New OleDbCommand With {.Connection = cn} Try cn.Open() cmd.CommandText = String.Format("SELECT * FROM [{0}]", {TableNamesListBox.Text}) Dim dt As New DataTable dt.Load(cmd.ExecuteReader()) bindingSource.DataSource = dt DataGridView1.DataSource = bindingSource DataGridView1.ExpandColumns() Text = String.Format("DB: {0}", Path.GetFileName(OpenFileDialog1.FileName)) & String.Format("Table: {0}", {TableNamesListBox.Text}) Catch ex As Exception MessageBox.Show(String.Format("Failed {0}", {ex.Message.ToString()})) End Try End Using End Using End If End Sub

    '...

    你无法使用‘$’的原因很可能是由于你的VB版本低于14,为了获得最新版本的最新功能,我建议你使用最新的visual studio进行编程。

    1. Visual Basic 的新增功能
    2. 内插字符串(Visual Basic 引用)

    希望可以帮助你解决问题。

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年3月24日 7:09
    版主