I'm trying to load data from an Access database into a DataGridView.
This is my access database - Image has long binary data
However, when I retrieve the data from the database and try to load it into the DataGridView, it shows this error:
I have 2 forms, this one is for adding to database:
This one is for showing the database in the DataGridView
Here's my code to add my uploaded image to database.
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
CodePudding user response：
I can give you an example how to put images on a DataGridView from Access but you'll need to adapt to your reality.
Just add a DataGridView and create 2 columns, first as TextBoxColumn and second as ImageColumn.
The next step is to load data from access database, so use what you already have that is not shown in your post. It will be something like:
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
CodePudding user response：
It's not necessary to store both birthdate and age, because one of them can be computed given a value for the other one.
You haven't provided enough code to identify the issue, but if the image data wasn't properly converted before storing it, that would cause an issue.
Below shows how to both insert and update data that contains an image, as well as how to retrieve the data. In the code below, you'll also find code that will create an Access database and a table.
Add a reference to Microsoft ADO Ext. 6.0 for DDL and Security
Note: This is required for the "CreateDatabase" function in the code below.
- In VS menu, click Project
- Select Add Reference...
- Select COM
- Check Microsoft ADO Ext. 6.0 for DDL and Security
The code is tested and fairly well-documented. Of particular importance are the following functions/methods:
- GetImageAsByteArray
- TblOfficialsExecuteNonQuery
- TblOfficialsInsert
- TblOfficialsGetData
Create a class (name: 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
Usage
Create Access Database:
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
Create Table
Private _helper As HelperAccess = Nothing
...
Dim result As String = _helper.CreateTblOfficials()
Insert data to database:
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