How to insert picture in the datagridview and converted it into the database VB.NET without using parameter

Question

1.00/5 (1 vote)

See more:

, +

Hi! I have a code here which can add,search,delete,clear and insert a picture of users information. The problem is I don't know how and what syntax should I make the picture add in the add button to save it in the database and show the picture in the data grid view along with the users informations...I hope and advance thanks to people that can help me ...Thanks

P.S this is the only thing that i'm missing in my code, all of them is executable. It can already appear in the picture box but I haven't connected yet in my database which that is the problem. I will paste here under all the codes i already made, hope this will help.

What I have tried:

VB

'Imports Microsoft.Win32
Imports System.Data.OleDb
Imports System.IO
'Imports System
'Imports System.Data
'Imports System.Drawing
'Imports System.Drawing.Imaging
'Imports System.Windows.Forms
'Imports Microsoft.VisualBasic
Imports System.DBNull

Public Class ProjectTwo
    Dim cnn As New OleDb.OleDbConnection
    Dim imgName As String
    Dim cmd As New OleDb.OleDbCommand
    Private Sub ProjectTwo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cnn = New OleDb.OleDbConnection
        cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\ProjectTwo\ProjectTwoEmployment.accdbProvider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\ProjectTwo\ProjectTwoEmployment.accdb"
        '
        'get data into list
        Me.RefreshData()
    End Sub

    Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
        Dim cmd As New OleDb.OleDbCommand
        If Not cnn.State = ConnectionState.Open Then
            'open connection if it is not yet open
            cnn.Open()
        End If

        cmd.Connection = cnn
        'check whether add new or update
        If Me.TxtEID.Tag & "" = "" Then
            'add new 
            'add data to table
            cmd.CommandText = "INSERT INTO PersonalInfo (EmploymentID, EmploymentName, DateOfBirth, PlaceOfBirth, Address , Phone , Sex) " & _
                            " VALUES(" & Me.TxtEID.Text & ",'" & Me.TxtEName.Text & "','" & Me.TxtEDate.Text & "','" & _
                            Me.TxtEPlace.Text & "','" & Me.TxtEAdd.Text & "','" & Me.TxtEPhone.Text & "','" & _
                            Me.TxtESex.Text & "')"
            cmd.ExecuteNonQuery()
        Else
            'update data in table
            cmd.CommandText = "UPDATE PersonalInfo " & _
                        " SET EmploymentID =" & Me.TxtEID.Text & _
                        ", EmploymentName='" & Me.TxtEName.Text & "'" & _
                        ", DateOfBirth='" & Me.TxtEDate.Text & "'" & _
                        ", PlaceOfBirth='" & Me.TxtEPlace.Text & "'" & _
                        ", Address='" & Me.TxtEAdd.Text & "'" & _
                        ", Phone='" & Me.TxtEPhone.Text & "'" & _
                        ", Sex='" & Me.TxtESex.Text & "'" & _
                        " WHERE EmploymentID=" & Me.TxtEID.Tag
            cmd.ExecuteNonQuery()
        End If

        'refresh data in list
        Me.RefreshData()
        'clear form
        Me.BtnClear.PerformClick()

        'close connection
        cnn.Close()
    End Sub

    Private Sub RefreshData()
        If Not cnn.State = ConnectionState.Open Then
            'open connection
            cnn.Open()
        End If

        Dim da As New OleDb.OleDbDataAdapter("SELECT EmploymentID as [EmploymentID], " & _
                                             "EmploymentName as [EmploymentName], DateOfBirth, PlaceOfBirth, Address, Phone , Sex , Photo" & _
                                             " FROM PersonalInfo ORDER BY EmploymentID", cnn)

        Dim dt As New DataTable
        'fill data to datatable
        da.Fill(dt)

        'offer data in data table into datagridview
        Me.DataGridView1.DataSource = dt

        'close connection
        cnn.Close()
    End Sub

    Private Sub BtnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClear.Click
        Me.TxtEID.Text = ""
        Me.TxtEName.Text = ""
        Me.TxtEDate.Text = ""
        Me.TxtEPlace.Text = ""
        Me.TxtEAdd.Text = ""
        Me.TxtEPhone.Text = ""
        Me.TxtESex.Text = ""
        Me.PictureBox1.Image = Nothing

        Me.RefreshData()

        'enable button edit
        Me.BtnUpdate.Enabled = True
        'set button add to add label
        Me.BtnAdd.Text = "Add"
        '
        Me.TxtEID.Focus()
    End Sub

    Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
        'check for the selected item in list
        If Me.DataGridView1.Rows.Count > 0 Then
            If Me.DataGridView1.SelectedRows.Count > 0 Then
                Dim intEID As Integer = Me.DataGridView1.SelectedRows(0).Cells("EmploymentID").Value
                'get data from database followed by employment id
                'open connection
                If Not cnn.State = ConnectionState.Open Then
                    cnn.Open()
                End If
                'get data into datatable
                Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM PersonalInfo " & _
                                                     " WHERE EmploymentID=" & intEID, cnn)
                Dim dt As New DataTable
                da.Fill(dt)

                Me.TxtEID.Text = intEID
                Me.TxtEName.Text = dt.Rows(0).Item("EmploymentName")
                Me.TxtEDate.Text = dt.Rows(0).Item("DateOfBirth")
                Me.TxtEPlace.Text = dt.Rows(0).Item("PlaceOfBirth")
                Me.TxtEAdd.Text = dt.Rows(0).Item("Address")
                Me.TxtEPhone.Text = dt.Rows(0).Item("Phone")
                Me.TxtESex.Text = dt.Rows(0).Item("Sex")

                '
                'hide the id to be edited in TAG of TxtEID in case id is changed
                Me.TxtEID.Tag = intEID
                'change button add to update
                Me.BtnAdd.Text = "UPDATE"
                'disable button edit
                Me.BtnUpdate.Enabled = False
                'close connection
                cnn.Close()
            End If
        End If
    End Sub

    Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
        'check for the selected item in list
        If Me.DataGridView1.Rows.Count > 0 Then
            If Me.DataGridView1.SelectedRows.Count > 0 Then
                Dim intEID As Integer = Me.DataGridView1.SelectedRows(0).Cells("EmploymentID").Value
                'open connection
                If Not cnn.State = ConnectionState.Open Then
                    cnn.Open()
                End If

                'delete data
                Dim cmd As New OleDb.OleDbCommand
                cmd.Connection = cnn
                cmd.CommandText = "DELETE FROM PersonalInfo WHERE EmploymentID=" & intEID
                cmd.ExecuteNonQuery()
                'refresh data
                Me.RefreshData()

                'close connection
                cnn.Close()
            End If
        End If
    End Sub

    Private Sub BtnPicture_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPicture.Click
     
        Dim opf As New OpenFileDialog
        opf.Filter = "Choose Image(*.jpg;*.png;*.gif)|*.jpg;*.png;*.gif"

        If opf.ShowDialog = DialogResult.OK Then
            PictureBox1.Image = Image.FromFile(opf.FileName)
        End If
    End Sub

    Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click

        'for searching the record in the database 
        Try
            'open the connection
            cnn.Open()
            Dim data = New DataTable
            Dim dataT As New OleDbDataAdapter
            'set your commands for holding the data
            With cmd
                .Connection = cnn
                .CommandText = "Select * from PersonalInfo where EmploymentID like '" & TxtEID.Text & "%'"
            End With
            'filling the table in the database.
            dataT.SelectCommand = cmd
            dataT.Fill(data)
            'put your datasource in the datagridview
            DataGridView1.DataSource = data

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'close the connection
        cnn.Close()

    End Sub

End Class

Posted 27-Aug-17 3:38am

User 13348345

Updated 27-Aug-17 8:11am

RickZeeland

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2017-08-27T08:11:00

Solution 2

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]

Quote:
is there possible way that it can be inserted a picture? without using parameters?

No, parameters is the only solution.

Quote:
Then can I just make a special parameter for the photo and the rest is not in parameters?

Your choice ! Your know the danger, you know the solution.

Posted 27-Aug-17 8:11am

Patrice T

Updated 27-Aug-17 20:04pm

v3

Comments

[no name] 27-Aug-17 15:23pm

Hi! I do know the consequences but is there possible way that it can be inserted a picture? without using parameters?

[no name] 28-Aug-17 0:41am

Then can I just make a special parameter for the photo and the rest is not in parameters?

[no name] 28-Aug-17 3:27am

Hi! I follow your advice but there was a warning notes. I can solve it..It said:

Variable 'ArrayImage' is used before it has been assigned a value. A null reference exception could result at runtime.

Here is the code:

Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
Dim cmd As New OleDb.OleDbCommand
Dim Photo As New DataGridViewImageColumn
Dim MS As New MemoryStream
Dim ArrayImage() As Byte
Dim i As Short

If (PictureBox1.Image IsNot Nothing) Then
PictureBox1.Image.Save(MS, PictureBox1.Image.RawFormat)
ArrayImage = MS.GetBuffer
MS.Close()

End If

With cmd
.Connection = cnn
.CommandText = "INSERT INTO PersonalInfo ([EmploymentID], [EmploymentName], [DateOfBirth], [PlaceOfBirth], [Address] , [Phone] , [Sex] , [Photo]) " & _
" VALUES(@EID,@EName,,@EDate,@EPlace,@EAdd,@EPhone,@ESex,@EPhoto)"

.Parameters.Add(",@EID", OleDbType.Integer).Value = TxtEID.Text
.Parameters.Add(",@EName", OleDbType.VarChar).Value = TxtEName.Text
.Parameters.Add(",@EDate", OleDbType.Date).Value = TxtEDate.Text
.Parameters.Add(",@EPlace", OleDbType.VarChar).Value = TxtEPlace.Text
.Parameters.Add(",@EAdd", OleDbType.VarChar).Value = TxtEAdd.Text
.Parameters.Add(",@EPhone", OleDbType.VarChar).Value = TxtEPhone.Text
.Parameters.Add(",@ESex", OleDbType.VarChar).Value = TxtESex.Text
.Parameters.Add(",@EPhoto", OleDbType.Binary).Value = IIf(PictureBox1.Image IsNot Nothing, ArrayImage, DBNull.Value)

i = .ExecuteNonQuery
.Dispose()

End With

If i >= 1 Then
MsgBox("ADD SUCCESSFULLY")
PictureBox1.Image = Nothing
Call Clear()
Call RefreshData()
End If

End Sub

Patrice T 28-Aug-17 3:54am

Use Improve question to update your question.
So that everyone can pay attention to this information.

[no name] 28-Aug-17 4:05am

How?

Patrice T 28-Aug-17 4:07am

button at bottom of question.

Dave Kreskowiak · Answer 2 · 2017-08-27T05:25:00

Solution 1

USE PARAMETERS! There is no excuse not to.

This string concatenation garbage you're using is prone to SQL Injection Attacks, be it intentional or not.

I can break your SQL statements you're building just by putting a ' character in any one of those fields.

Posted 27-Aug-17 5:25am

Dave Kreskowiak

Comments

[no name] 27-Aug-17 15:19pm

Yes, Thanks for the tips but no big deal tho, I just want to try there's any way that i insert pictures in my database. It's OK that it may crash but i was finding a code for it...If you do, let me know...:)

Dave Kreskowiak 27-Aug-17 20:09pm

Seriously, forget this noise and USE PARMAETERS! DO NOT get into this habit.

Image img = picturebox1.Image();
byte[] arr;
ImageConverter converter = new ImageConverter();
arr=(byte[])converter.ConvertTo(img, typeof(byte[]));

command.CommandText = "INSERT INTO ImagesTable (Image) VALUES('" + arr + "')";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

[no name] 28-Aug-17 0:45am

Thank you for understanding but I would like to ask if where should i put your code? is it in the browse button or in the add button? should I put it also in the update button?

Dave Kreskowiak 28-Aug-17 0:52am

What do you think an SQL INSERT does?

[no name] 28-Aug-17 2:10am

Sorry for having you a hard time ...I just decided to change my code using parameter although i don't much understand the logic behind it..Thanks for the time and I will this as a reference.

[no name] 28-Aug-17 2:19am

Hi! I almost finish my code in adding button but there is a error. Here :
Warning 1 Variable 'ArrayImage' is used before it has been assigned a value. A null reference exception could result at runtime.

Here's the code:

Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
Dim cmd As New OleDb.OleDbCommand
Dim Photo As New DataGridViewImageColumn
Dim MS As New MemoryStream
Dim ArrayImage() As Byte
Dim i As Short

If (PictureBox1.Image IsNot Nothing) Then
PictureBox1.Image.Save(MS, PictureBox1.Image.RawFormat)
ArrayImage = MS.GetBuffer
MS.Close()

'open connection if it is not yet open
cnn.Open()
End If

With cmd
.Connection = cnn
.CommandText = "INSERT INTO PersonalInfo ([EmploymentID], [EmploymentName], [DateOfBirth], [PlaceOfBirth], [Address] , [Phone] , [Sex] , [Photo]) " & _
" VALUES(@EID,@EName,,@EDate,@EPlace,@EAdd,@EPhone,@ESex,@EPhoto)"

.Parameters.Add(",@EID", OleDbType.Integer).Value = TxtEID.Text
.Parameters.Add(",@EName", OleDbType.VarChar).Value = TxtEName.Text
.Parameters.Add(",@EDate", OleDbType.VarChar).Value = TxtEDate.Text
.Parameters.Add(",@EPlace", OleDbType.VarChar).Value = TxtEPlace.Text
.Parameters.Add(",@EAdd", OleDbType.VarChar).Value = TxtEAdd.Text
.Parameters.Add(",@EPhone", OleDbType.VarChar).Value = TxtEPhone.Text
.Parameters.Add(",@ESex", OleDbType.VarChar).Value = TxtESex.Text
.Parameters.Add(",@EPhoto", OleDbType.Binary).Value = IIf(PictureBox1.Image IsNot Nothing, ArrayImage, DBNull.Value)

i = .ExecuteNonQuery
.Dispose()

End With

If i >= 1 Then
MsgBox("ADD Success")
PictureBox1.Image = Nothing
Call Clear()
Call RefreshData()
End If

End Sub

Dave Kreskowiak 28-Aug-17 9:08am

You're getting that warning because the only time you assigned a value to ArrayImage is inside an "if" block. You then used ArrayImage in a block of code outside that "if" block. The rest of the database code below the "End If" should have been placed inside the "if" block.

[no name] 28-Aug-17 21:59pm

Hi! I place the database code inside the if block but no luck...Same output but there's no warning note anymore instead there's a DataGridView Default Error Dialog warning.. I can't paste here the printscreen that i capture but it says that i have to replace this default dialog and please handle the dataerror event.

P.S This occur everytime i execute the program..Please help me

Dave Kreskowiak 28-Aug-17 22:30pm

The DataError event of the DGV needs to be handled so you get the error for the cell it's trying to render.

[no name] 28-Aug-17 23:57pm

The problem is in the cell? Sorry but I can actually don't know where to change/edit my code cause there's no even error code.Here's the code please:

Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
Dim cmd As New OleDb.OleDbCommand
Dim EPhoto As New DataGridViewImageColumn
Dim MS As New MemoryStream
Dim ArrayImage() As Byte = Nothing
Dim i As Short

If (PictureBox1.Image IsNot Nothing) Then
PictureBox1.Image.Save(MS, PictureBox1.Image.RawFormat)
ArrayImage = MS.GetBuffer
MS.Close()

With cmd
.Connection = cnn
.CommandText = "INSERT INTO PersonalInfo ([EmploymentID], [EmploymentName], [DateOfBirth], [PlaceOfBirth], [Address], [Phone] , [Sex] ,[Photo]) VALUES (@EID,@EName,@EDate,@EPlace,@EAdd,@EPhone,@ESex,@EPhoto)"

.Parameters.Add("@EID", OleDbType.Integer).Value = TxtEID.Text
.Parameters.Add("@EName", OleDbType.VarChar).Value = TxtEName.Text
.Parameters.Add("@EDate", OleDbType.VarChar).Value = TxtEDate.Text
.Parameters.Add("@EPlace", OleDbType.VarChar).Value = TxtEPlace.Text
.Parameters.Add("@EAdd", OleDbType.VarChar).Value = TxtEAdd.Text
.Parameters.Add("@EPhone", OleDbType.VarChar).Value = TxtEPhone.Text
.Parameters.Add("@ESex", OleDbType.VarChar).Value = TxtESex.Text
.Parameters.Add("@EPhoto", OleDbType.Binary).Value = If(PictureBox1.Image IsNot Nothing, ArrayImage, CObj(DBNull.Value))

i = .ExecuteNonQuery
.Dispose()

End With

If i >= 1 Then
MsgBox("ADD SUCCESSFULLY")
PictureBox1.Image = Nothing
Call Clear()
Call RefreshData()
End If

End If

End Sub

Dave Kreskowiak 29-Aug-17 8:48am

You've got a LOT to learn...

Click on the DGV in your form once. In the Properties windows, there is a small icon that looks like a lightning bolt. Click that. You'll now see a list of all the events that the DGV exposes. Find the line that says "DataError" and double click it. It'll add an empty DataError event handler method. That's where you're going to put your code to log any errors, such as

    Debug.WriteLine(e.Exception.ToString());

[no name] 29-Aug-17 21:22pm

I tried but when i click the lightning bolt there's no "DataError" line. I search for it but there is none.