轉換AccessOLE物件影像以在Datagridviewvb.net中顯示

我正在嘗試將資料從 Access 資料庫加載到 DataGridView。

這是我的訪問資料庫 - 影像有很長的二進制資料

但是，當我從資料庫中檢索資料并嘗試將其加載到 DataGridView 中時，它顯示以下錯誤：

我有兩種形式，這個是用于添加到資料庫中的：

這是用于在 DataGridView 中顯示資料庫

這是我將上傳的影像添加到資料庫的代碼。

Dim fsreader As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
    Dim breader As New BinaryReader(fsreader)
    Dim imgbuffer(fsreader.Length) As Byte
    breader.Read(imgbuffer, 0, fsreader.Length)
    fsreader.Close()


    Dim create As New OleDbCommand("INSERT INTO Officials ([officialname] , [age] , [birthdate] , [position] , [term], [status], [image] ) VALUES ('" & TextBox1.Text & "' ,  '" & TextBox2.Text & "' , '" & DateTimePicker1.Value & "' , '" & cb1 & "' , '" & TextBox3.Text & "' , '" & status & "' , @img )", con)
    With create
        .Parameters.Add("@on", OleDb.OleDbType.VarChar).Value = TextBox1.Text.Trim
        .Parameters.Add("@age", OleDb.OleDbType.VarChar).Value = TextBox2.Text.Trim
        .Parameters.Add("@bd", OleDb.OleDbType.VarChar).Value = DateTimePicker1.Value
        .Parameters.Add("@pn", OleDb.OleDbType.VarChar).Value = cb1
        .Parameters.Add("@tm", OleDb.OleDbType.VarChar).Value = TextBox3.Text.Trim
        .Parameters.Add("@st", OleDb.OleDbType.VarChar).Value = status
        .Parameters.Add("@img", OleDb.OleDbType.LongVarBinary).Value = imgbuffer

uj5u.com熱心網友回復：

我可以舉一個例子，說明如何從 Access 將影像放在 DataGridView 上，但您需要適應您的現實。

只需添加一個 DataGridView 并創建 2 列，第一個作為 TextBoxColumn，第二個作為 ImageColumn。

下一步是從訪問資料庫加載資料，因此請使用您已經擁有但未在您的帖子中顯示的內容。它會是這樣的：

Dim GConn As New OleDbConnection("your connection string...")
Dim GCmd As New OleDbCommand()
Dim DtReader As OleDbDataReader

GCmd.Connection = GConn 
GCmd.CommandText = "SELECT PhotoDescription, PhotoOLE FROM MY_TABLE;"

DtReader = GCmd.ExecuteReader ' DtReader will have all the rows from database

' For this test you need to load less than 100 records
dim iLine as integer=0    
DataGridView1.rows.Add(100) ' add 100 rows to test

DtReader.Read ' read first record

Do
    DataGridView1.Rows(iLine).Cells(0).Value=DtReader("PhotoDescription").ToString
    DataGridView1.Rows(iLine).Cells(1).Value=CType(DtReader("PhotoOLE"), Byte())
    iLine =1  
Loop while DtReader.Read

uj5u.com熱心網友回復：

沒有必要同時存盤birthdateand age，因為可以計算其中一個和給另一個的值。

您沒有提供足夠的代碼來識別問題，但如果影像資料在存盤之前沒有正確轉換，則會導致問題。

下面顯示了如何插入和更新包含影像的資料，以及如何檢索資料。在下面的代碼中，您還將找到將創建 Access 資料庫和表的代碼。

添加參考 Microsoft ADO Ext. 6.0 for DDL and Security

注意：這是下面代碼中的“CreateDatabase”函式所必需的。

• 在 VS 選單中，單擊專案
• 選擇添加參考...
• 選擇COM
• 檢查Microsoft ADO Ext。6.0 用于 DDL 和安全性

代碼已經過測驗并且檔案齊全。以下功能/方法尤為重要：

• GetImageAsByteArray
• TblO??fficialsExecuteNonQuery
• TblO??fficials插入
• TblO??fficialsGetData

創建一個類（名稱：HelperAccess.vb）

Imports System.Data.OleDb
Imports System.IO

Public Class HelperAccess
    Private _accessFilename As String = String.Empty
    Private _connectionStr As String = String.Empty

    Public ReadOnly Property AccessFilename
        Get
            Return _accessFilename
        End Get
    End Property

    Sub New(accessFilename As String, Optional dbPassword As String = "")
        'set value
        _accessFilename = accessFilename

        'create connection string
        If Not String.IsNullOrEmpty(dbPassword) Then
            _connectionStr = String.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0};Jet OLEDB:Database Password='{1}'", accessFilename, dbPassword)
        Else
            _connectionStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};", _accessFilename)
        End If
    End Sub

    Public Function CreateDatabase() As String
        Dim result As String = String.Empty

        Dim cat As ADOX.Catalog = Nothing

        Try
            'create New instance
            cat = New ADOX.Catalog()

            'create Access database
            cat.Create(_connectionStr)

            'set value
            result = String.Format("Status: Database created: '{0}'", _accessFilename)

            Return result
        Catch ex As Exception
            'set value
            result = String.Format("Error (CreateDatabase): {0}(Database: {1})", ex.Message, _accessFilename)
            Return result

        Finally
            If cat IsNot Nothing Then
                'close connection
                cat.ActiveConnection.Close()

                'release COM object
                System.Runtime.InteropServices.Marshal.ReleaseComObject(cat)

                cat = Nothing
            End If
        End Try
    End Function

    Public Function CreateTblOfficials() As String
        Dim result As String = String.Empty

        Dim tableName As String = "Officials"

        Dim sqlText = String.Empty
        sqlText = "CREATE TABLE Officials "
        sqlText  = "(ID AUTOINCREMENT not null primary key,"
        sqlText  = " [FullName] varchar(50) not null,"
        sqlText  = " [Birthdate] DateTime,"
        sqlText  = " [JobDescription] varchar(50) not null,"
        sqlText  = " [Term] varchar(50),"
        sqlText  = " [Status] varchar(50) not null,"
        sqlText  = " [Photo] Longbinary);"

        Try
            'create database table
            ExecuteNonQuery(sqlText)

            result = String.Format("Table created: '{0}'", tableName)

        Catch ex As OleDbException
            result = String.Format("Error (CreateTblOfficials - OleDbException): Table creation failed: '{0}'; {1}", tableName, ex.Message)
        Catch ex As Exception
            result = String.Format("Error (CreateTblOfficials): Table creation failed: '{0}'; {1}", tableName, ex.Message)
        End Try

        Return result
    End Function

    Private Function ExecuteNonQuery(sqlText As String) As Integer
        Dim rowsAffected As Integer = 0

        'used for insert/update

        'create new connection
        Using cn As OleDbConnection = New OleDbConnection(_connectionStr)
            'open
            cn.Open()

            'create new instance
            Using cmd As OleDbCommand = New OleDbCommand(sqlText, cn)
                'execute
                rowsAffected = cmd.ExecuteNonQuery()
            End Using
        End Using

        Return rowsAffected
    End Function

    Public Function GetImageAsByteArray(filename As String) As Byte()
        'read image from file and return as Byte()

        Try
            If Not String.IsNullOrEmpty(filename) AndAlso System.IO.File.Exists(filename) Then
                Using fs As FileStream = New FileStream(filename, FileMode.Open, FileAccess.Read)
                    Dim imageBytes(fs.Length) As Byte

                    'read image from file and put into Byte()
                    fs.Read(imageBytes, 0, fs.Length)

                    Return imageBytes
                End Using
            End If
        Catch ex As Exception
            Debug.WriteLine("Error (GetImageAsByteArray): "   ex.Message)
            Throw
        End Try

        Return Nothing
    End Function

    Public Function TblOfficialsExecuteNonQuery(sqlText As String, fullName As String, birthdate As Date, jobDescription As String, term As String, status As String, imageBytes As Byte()) As Integer
        Dim rowsAffected As Integer = 0

        'create new connection
        Using cn As OleDbConnection = New OleDbConnection(_connectionStr)
            'open
            cn.Open()

            'create new instance
            Using cmd As OleDbCommand = New OleDbCommand(sqlText, cn)

                'OLEDB doesn't use named parameters in SQL. Any names specified will be discarded and replaced with '?'
                'However, specifying names in the parameter 'Add' statement can be useful for debugging
                'Since OLEDB uses anonymous names, the order which the parameters are added is important
                'if a column is referenced more than once in the SQL, then it must be added as a parameter more than once
                'parameters must be added in the order that they are specified in the SQL
                'if a value is null, the value must be assigned as: DBNull.Value

                With cmd.Parameters
                    .Add("!fullName", OleDbType.VarChar).Value = If(String.IsNullOrEmpty(fullName), DBNull.Value, fullName)
                    .Add("!birthDate", OleDbType.Date).Value = birthdate
                    .Add("!jobDescription", OleDbType.VarChar).Value = If(String.IsNullOrEmpty(jobDescription), DBNull.Value, jobDescription)
                    .Add("!term", OleDbType.VarChar).Value = If(String.IsNullOrEmpty(term), DBNull.Value, term)
                    .Add("!status", OleDbType.VarChar).Value = If(String.IsNullOrEmpty(status), DBNull.Value, status)
                    .Add("!photo", OleDbType.VarBinary).Value = imageBytes
                End With

                'ToDo: remove the following code that is for debugging
                'For Each p As OleDbParameter In cmd.Parameters
                'Debug.WriteLine(p.ParameterName & ": " & p.Value.ToString())
                'Next

                'execute
                rowsAffected = cmd.ExecuteNonQuery()
            End Using
        End Using

        Return rowsAffected
    End Function

    Public Function TblOfficialsGetData() As DataTable
        Dim dt As DataTable = New DataTable()

        Dim sqlText As String = "SELECT * from Officials"

        Try
            'create new connection
            Using con As OleDbConnection = New OleDbConnection(_connectionStr)
                'open
                con.Open()

                'create new instance
                Using cmd As OleDbCommand = New OleDbCommand(sqlText, con)
                    Using da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
                        'fill DataTable from database
                        da.Fill(dt)
                    End Using
                End Using
            End Using

            Return dt
        Catch ex As OleDbException
            Debug.WriteLine("Error (TblOfficialsGetData - OleDbException) - " & ex.Message & "(" & sqlText & ")")
            Throw ex
        Catch ex As Exception
            Debug.WriteLine("Error (TblOfficialsGetData) - " & ex.Message & "(" & sqlText & ")")
            Throw ex
        End Try

    End Function

    Public Function TblOfficialsInsert(fullName As String, birthdate As Date, jobDescription As String, term As String, status As String, imageBytes As Byte()) As Integer
        Dim rowsAffected As Integer = 0

        Dim sqlText As String = String.Empty
        sqlText = "INSERT INTO Officials ([FullName], [BirthDate], [JobDescription], [Term], [Status], [Photo]) VALUES (?, ?, ?, ?, ?, ?);"

        Try
            'insert data to database
            Return TblOfficialsExecuteNonQuery(sqlText, fullName, birthdate, jobDescription, term, status, imageBytes)
        Catch ex As OleDbException
            Debug.WriteLine("Error (TblOfficialsInsert - OleDbException) - " & ex.Message & "(" & sqlText & ")")
            Throw ex
        Catch ex As Exception
            Debug.WriteLine("Error (TblOfficialsInsert) - " & ex.Message & "(" & sqlText & ")")
            Throw ex

        End Try

        Return rowsAffected
    End Function


    Public Function TblOfficialsUpdate(fullName As String, birthdate As Date, jobDescription As String, term As String, status As String, imageBytes As Byte()) As Integer
        Dim rowsAffected As Integer = 0

        Dim sqlText As String = String.Empty
        sqlText = "UPDATE Officials SET [FullName] = ?, [Birthdate] = ? , [JobDescription] = ?, [Term] = ?, [Status] = ?, [Photo] = ?;"

        Try
            'update data in database
            Return TblOfficialsExecuteNonQuery(sqlText, fullName, birthdate, jobDescription, term, status, imageBytes)
        Catch ex As OleDbException
            Debug.WriteLine("Error (TblOfficialsUpdate - OleDbException) - " & ex.Message & "(" & sqlText & ")")
            Throw ex
        Catch ex As Exception
            Debug.WriteLine("Error (TblOfficialsUpdate) - " & ex.Message & "(" & sqlText & ")")
            Throw ex
        End Try

        Return rowsAffected
    End Function
End Class

用法

創建訪問資料庫：

Private _helper As HelperAccess = Nothing
            ...
Dim sfd As SaveFileDialog = New SaveFileDialog()

sfd.Filter = "Access Database (*.accdb)|*.accdb|Access Database (*.mdb)|*.mdb"

If sfd.ShowDialog() = DialogResult.OK Then
    'create new instance
    _helper = New HelperAccess(sfd.FileName)

    Dim result As String = _helper.CreateDatabase()
End If

創建表

Private _helper As HelperAccess = Nothing
               ...
Dim result As String = _helper.CreateTblOfficials()

向資料庫插入資料：

Private _helper As HelperAccess = Nothing
               ...
Dim imageBytes As Byte() = Nothing
imageBytes = System.IO.File.ReadAllBytes("C:\Temp\Images\Test1.jpg")
_helper.TblOfficialsInsert("Joe Smith", New Date(1986, 5, 20), "Captain", "2016-2030", "Active", imageBytes)

Get data from database:

Add a DataGridView to your form from the Toolbox (don't add any columns)

Private _dt As DataTable = New DataTable()
Private _helper As HelperAccess = Nothing
Private _source As BindingSource = New BindingSource()
               ...

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'set properties
    DataGridView1.AllowUserToAddRows = False
    DataGridView1.AllowUserToDeleteRows = False

    'set data source
    DataGridView1.DataSource = _source
End Sub

Private Sub GetData()
     'get data from database
     _dt = _helper.TblOfficialsGetData()

     'set value
     _source.DataSource = _dt
     _source.ResetBindings(True)
 End Sub

Resources

• CREATE TABLE statement (Microsoft Access SQL)
• How can I refresh c# dataGridView after update?
• Getting binary data using SqlDataReader

標籤：网络 ms-access 奥德

上一篇：SQLWHERE子句從一個欄位中搜索多個引數

下一篇：MSAccessSQL：帶復選框的選擇串列的左連接