Skip to content

Instantly share code, notes, and snippets.

@Nelson-Chinedu Nelson-Chinedu/vb.net 2010
Last active May 22, 2017

Embed
What would you like to do?
i'm having a problem on how to retrieve saved picture from mysql db to picture box and datagridview using vb.net 2010
Imports MySql.Data.MySqlClient
Imports System.IO
Imports System.Drawing
Public Class children
Dim conString As String = "server=localhost;database=foursquaredb;uid=root;password=;"
Dim con As New MySqlConnection(conString)
Dim cmd As MySqlCommand
Dim adapter As MySqlDataAdapter
Dim dt As New DataTable()
Private Sub children_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DataGridView1.ColumnCount = 13
DataGridView1.Columns(0).Name = "ID"
DataGridView1.Columns(1).Name = "SurName"
DataGridView1.Columns(2).Name = "FirstName"
DataGridView1.Columns(3).Name = "LastName"
DataGridView1.Columns(4).Name = "Sex"
DataGridView1.Columns(5).Name = "Date Of Birth"
DataGridView1.Columns(6).Name = "State Of Origin"
DataGridView1.Columns(7).Name = "Residential Address"
DataGridView1.Columns(8).Name = "Occupation"
DataGridView1.Columns(9).Name = "Nationality"
DataGridView1.Columns(10).Name = "Phone Number"
DataGridView1.Columns(11).Name = "E-mail Address"
DataGridView1.Columns(12).Name = "picture"
DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
End Sub
Private Sub cleartxt()
surTxt.Text = ""
firstTxt.Text = ""
lastTxt.Text = ""
sexTxt.Text = ""
dobTxt.Text = ""
stateTxt.Text = ""
addressTxt.Text = ""
occTxt.Text = ""
natTxt.Text = ""
phoneTxt.Text = ""
emailTxt.Text = ""
End Sub
Private Sub populate(ByVal id As String, ByVal sur As String, ByVal first As String, ByVal last As String, ByVal sex As String, ByVal dob As String, ByVal state As String, ByVal address As String, ByVal occ As String, ByVal nat As String, ByVal phone As String, ByVal email As String, ByVal picture As String)
Dim row As String() = New String() {id, sur, first, last, sex, dob, state, address, occ, nat, phone, email, picture}
DataGridView1.Rows.Add(row)
End Sub
Private Sub retrieve()
DataGridView1.Rows.Clear()
Dim sql As String = "SELECT * FROM childrentb"
cmd = New MySqlCommand(sql, con)
Try
con.Open()
adapter = New MySqlDataAdapter(cmd)
adapter.Fill(dt)
For Each row In dt.Rows
populate(row(0), row(1), row(2), row(3), row(4), row(5), row(6), row(7), row(8), row(9), row(10), row(11), row(12))
Next
con.Close()
dt.Rows.Clear()
Catch ex As Exception
MsgBox(ex.Message)
con.Close()
End Try
End Sub
Private Sub add()
'
Dim mstream As New System.IO.MemoryStream()
PictureBox1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim arrImage() As Byte = mstream.GetBuffer()
mstream.Close()
'
Dim sql As String = "INSERT INTO childrentb(Surname,Firstname,Lastname,Sex,Date_Of_Birth,State_Of_Origin,Residential_Address,Occupation,Nationality,Phone_Number,Email_Address,picture) VALUES(@surname,@firstname,@lastname,@sex,@dob,@state,@address,@occ,@nat,@phone,@email,@picture)"
cmd = New MySqlCommand(sql, con)
cmd.Parameters.AddWithValue("@surname", surTxt.Text)
cmd.Parameters.AddWithValue("@firstname", firstTxt.Text)
cmd.Parameters.AddWithValue("@lastname", lastTxt.Text)
cmd.Parameters.AddWithValue("@sex", sexTxt.Text)
cmd.Parameters.AddWithValue("@dob", dobTxt.Text)
cmd.Parameters.AddWithValue("@state", stateTxt.Text)
cmd.Parameters.AddWithValue("@address", addressTxt.Text)
cmd.Parameters.AddWithValue("@occ", occTxt.Text)
cmd.Parameters.AddWithValue("@nat", natTxt.Text)
cmd.Parameters.AddWithValue("@phone", phoneTxt.Text)
cmd.Parameters.AddWithValue("@email", emailTxt.Text)
'
cmd.Parameters.AddWithValue("@picture", arrImage)
'
Try
con.Open()
If cmd.ExecuteNonQuery() > 0 Then
MsgBox("RECORD SAVED", MsgBoxStyle.OkOnly, "MESSAGE")
cleartxt()
End If
con.Close()
retrieve()
Catch ex As Exception
MsgBox(ex.Message)
con.Close()
End Try
End Sub
Private Sub updateDG(ByVal id As String)
Dim sql As String = "UPDATE childrentb SET Surname='" + surTxt.Text + "' ,Firstname='" + firstTxt.Text + "', Lastname='" + lastTxt.Text + "',Sex='" + sexTxt.Text + "', Date_Of_Birth='" + dobTxt.Text + "', State_Of_Origin='" + stateTxt.Text + "', Residential_Address='" + addressTxt.Text + "', Occupation='" + occTxt.Text + "', Nationality='" + natTxt.Text + "', Phone_Number='" + phoneTxt.Text + "', Email_Address='" + emailTxt.Text + "' WHERE ID='" + id + "'"
Try
con.Open()
adapter.UpdateCommand = con.CreateCommand
adapter.UpdateCommand.CommandText = sql
If adapter.UpdateCommand.ExecuteNonQuery() > 0 Then
MsgBox("successfully updated")
cleartxt()
End If
con.Close()
retrieve()
Catch ex As Exception
MsgBox(ex.Message)
con.Close()
End Try
End Sub
Private Sub delete(ByVal id As String)
Dim sql As String = "DELETE FROM childrentb WHERE ID='" + id + "'"
cmd = New MySqlCommand(sql, con)
Try
con.Open()
adapter.DeleteCommand = con.CreateCommand()
adapter.DeleteCommand.CommandText = sql
If MessageBox.Show("YOU ARE ABOUT TO REMOVE A RECORD", "Delete", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.OK Then
If cmd.ExecuteNonQuery() > 0 Then
MsgBox("successfully Deleted")
cleartxt()
End If
End If
con.Close()
retrieve()
Catch ex As Exception
MsgBox(ex.Message)
con.Close()
End Try
End Sub
Private Sub DataGridView1_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
Dim surname As String = DataGridView1.SelectedRows(0).Cells(1).Value
Dim firstname As String = DataGridView1.SelectedRows(0).Cells(2).Value
Dim lastname As String = DataGridView1.SelectedRows(0).Cells(3).Value
Dim sex As String = DataGridView1.SelectedRows(0).Cells(4).Value
Dim dob As String = DataGridView1.SelectedRows(0).Cells(5).Value
Dim state As String = DataGridView1.SelectedRows(0).Cells(6).Value
Dim address As String = DataGridView1.SelectedRows(0).Cells(7).Value
Dim occ As String = DataGridView1.SelectedRows(0).Cells(8).Value
Dim nat As String = DataGridView1.SelectedRows(0).Cells(9).Value
Dim phone As String = DataGridView1.SelectedRows(0).Cells(10).Value
Dim email As String = DataGridView1.SelectedRows(0).Cells(11).Value
Dim picture As Image = DataGridView1.SelectedRows(0).Cells(12).Value
surTxt.Text = surname
firstTxt.Text = firstname
lastTxt.Text = lastname
sexTxt.Text = sex
dobTxt.Text = dob
stateTxt.Text = state
addressTxt.Text = address
occTxt.Text = occ
natTxt.Text = nat
phoneTxt.Text = phone
emailTxt.Text = email
'PictureBox1.Image = picture
End Sub
Private Sub AddNewRecordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNewRecordToolStripMenuItem.Click
add()
End Sub
Private Sub DeleteRecordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteRecordToolStripMenuItem.Click
Dim id As String = DataGridView1.SelectedRows(0).Cells(0).Value
delete(id)
End Sub
Private Sub UpdateRecordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateRecordToolStripMenuItem.Click
Dim id As String = DataGridView1.SelectedRows(0).Cells(0).Value
updateDG(id)
End Sub
Private Sub RetrieveRecordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RetrieveRecordToolStripMenuItem.Click
retrieve()
End Sub
Private Sub ChildrenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChildrenToolStripMenuItem.Click
Me.Show()
End Sub
Private Sub TeenagersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TeenagersToolStripMenuItem.Click
teenagers.Show()
Me.Hide()
End Sub
Private Sub YouthToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles YouthToolStripMenuItem.Click
youth.Show()
Me.Hide()
End Sub
Private Sub AdultToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AdultToolStripMenuItem.Click
adults.Show()
Me.Hide()
End Sub
Private Sub OpenSavedRecordsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenSavedRecordsToolStripMenuItem.Click
GroupBox2.Show()
End Sub
Private Sub CloseSavedRecordsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseSavedRecordsToolStripMenuItem.Click
GroupBox2.Hide()
End Sub
Private Sub LogoutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LogoutToolStripMenuItem.Click
home.Show()
Me.Hide()
home.txtpass.Text = ""
home.txtuser.Text = ""
End Sub
Private Sub phoneTxt_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles phoneTxt.KeyPress
If Asc(e.KeyChar) <> 13 AndAlso Asc(e.KeyChar) <> 8 AndAlso Not IsNumeric(e.KeyChar) Then
MsgBox("please enter numbers only from 0-9 and must not exceed 11 digits", MsgBoxStyle.Information, "MESSAGE")
e.Handled = True
End If
End Sub
Private Sub ChangePasswordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChangePasswordToolStripMenuItem.Click
change_password.Show()
Me.Enabled = False
End Sub
Private Sub CloseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseToolStripMenuItem.Click
Dim resp As String
resp = MsgBox("YOU ARE ABOUT TO CLOSE THIS PROGRAM", vbYesNo, "ALERT MESSAGE")
If resp = vbYes Then
End
Else
Exit Sub
End If
End Sub
Private Sub HelpToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles HelpToolStripMenuItem.Click
help.Visible = True
Me.Enabled = False
End Sub
Private Sub AboutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AboutToolStripMenuItem.Click
about.Visible = True
Me.Enabled = False
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
OpenFileDialog1.Title = "please choose an image"
OpenFileDialog1.InitialDirectory = "c:"
OpenFileDialog1.ShowDialog()
PictureBox1.ImageLocation = OpenFileDialog1.FileName.ToString
PictureBox1.Visible = True
End Sub
End Class
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.