1. Forums
  2. Programming
  3. Visual Basic .NET
  4. Need help on viewing, inserting, and update picture in vb using access database?

Need help on viewing, inserting, and update picture in vb using access database?

Submitted by garyu87 on
Hi everyone, I am still new in visual basic; so, please go slow on me. I am actually having problem with the mentioned subject. I wanted to run a form that has a flowerID(text), FlowerName(text), FlowerMeaning(text), and FlowerPicture(PictureBox) that can be viewed, update, and delete. My problem is that I can't display the database out and have the correct syntax for the picturebox. By the way, I am not using any datasets,dataAdapter, and datagrid because I want to make it simple by using a direct connection. Here is my current code:
  1. Imports System.Data.OleDb
  2. Public Class FlowerData
  3.     Public connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FlowerDB.accdb"
  4.     Dim conn As New OleDbConnection
  5.     Dim myqry As String = Nothing
  6.     Dim mycmd As New OleDbCommand
  7.     Dim mydr As OleDbDataReader
  8.     Dim searching As Boolean
  9.     Dim str As String = ""
  10.  
  11.     Private Sub FlowerData_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  12.         Call ConnToDB()
  13.         Call FillData()
  14.         Call Set1()
  15.     End Sub
  16.  
  17. #Region "Connection"
  18.     Sub ConnToDB()
  19.         Try
  20.             With conn
  21.                 If .State = ConnectionState.Open Then .Close()
  22.                 .ConnectionString = connString
  23.                 .Open()
  24.             End With
  25.         Catch ex As Exception
  26.             MessageBox.Show("Unable to connect", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  27.             Application.Exit()
  28.         End Try
  29.     End Sub
  30. #End Region
  31.  
  32.  
  33. #Region "object Settings "
  34.  
  35.     Sub Set1()
  36.         btnAdd.Enabled = True
  37.         btnEdit.Enabled = True
  38.         btnDelete.Enabled = True
  39.         btnExit.Enabled = True
  40.         btnsearch.Enabled = True
  41.  
  42.         btnSave.Enabled = False
  43.         btnCancel.Enabled = False
  44.  
  45.         txtFlowerID.Enabled = False
  46.         txtFlowerName.Enabled = False
  47.         txtFlowerMeaning.Enabled = False
  48.         picBox.Enabled = False
  49.  
  50.         txtSearch.Enabled = True
  51.  
  52.     End Sub
  53.  
  54.     Sub Set2()
  55.         btnAdd.Enabled = False
  56.         btnEdit.Enabled = False
  57.         btnDelete.Enabled = False
  58.         btnExit.Enabled = False
  59.         btnsearch.Enabled = False
  60.  
  61.         btnSave.Enabled = True
  62.         btnCancel.Enabled = True
  63.  
  64.         txtFlowerID.Enabled = True
  65.         txtFlowerName.Enabled = True
  66.         txtFlowerMeaning.Enabled = True
  67.         picBox.Enabled = True
  68.  
  69.         txtSearch.Enabled = False
  70.  
  71.     End Sub
  72. #End Region
  73.  
  74. #Region "CLEAR TEXTBOX"
  75.     Sub ClearAlltextBox()
  76.         txtFlowerName.Text = ""
  77.         txtFlowerMeaning.Text = ""
  78.         picBox.Image = Nothing
  79.     End Sub
  80. #End Region
  81.  
  82. #Region "CLEAR TEXTBOX"
  83.     Sub FillData()
  84.         Dim LoadQry As String = "Select * from tblFlower Where FlowerID = " "'1001'"
  85.         mycmd = New OleDbCommand
  86.         mycmd = New OleDbCommand
  87.         With mycmd
  88.             .CommandText = LoadQry
  89.             .Connection = conn
  90.             .ExecuteNonQuery()
  91.         End With
  92.  
  93.         mydr.Read()
  94.         txtFlowerName.Text = mydr(1)
  95.         txtFlowerMeaning.Text = mydr(2)
  96.         picBox.Image = mydr(3)
  97.         conn.Close()
  98.  
  99.     End Sub
  100. #End Region
  101.  
  102.     Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
  103.         Me.Hide()
  104.     End Sub
  105.  
  106.     Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
  107.         If txtFlowerID.Text = "" Then
  108.             MessageBox.Show("Please Select Record to Update", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  109.         Else
  110.             str = "edit"
  111.             Call Set2()
  112.             txtFlowerID.Enabled = False
  113.             picBox.Image = Nothing
  114.         End If
  115.     End Sub
  116.  
  117.     Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
  118.         If txtFlowerID.Text = "" Then
  119.             MessageBox.Show("Please Select Record to Update", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  120.         Else
  121.             str = "edit"
  122.             Call Set2()
  123.             txtFlowerID.Enabled = False
  124.         End If
  125.     End Sub
  126.  
  127.     Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
  128.         Call Set1()
  129.         Call ClearAlltextBox()
  130.     End Sub
  131.  
  132.     Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
  133.         If str = "add" Then
  134.             ''''''ADD NEW RECORD'''''''
  135.             If txtFlowerName.Text = "" Or txtFlowerMeaning.Text = "" Or picBox.Visible = Nothing Then
  136.                 MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  137.             Else
  138.                 myqry = "INSERT INTO TblFlower(Flower Name,Flower Meaning,Flower image) "
  139.                 myqry = myqry + "VALUES('" & txtFlowerName.Text & "','" & txtFlowerMeaning.Text & "','" & picBox.Visible & "')"
  140.  
  141.                 mycmd = New OleDbCommand
  142.                 With mycmd
  143.                     .CommandText = myqry
  144.                     .Connection = conn
  145.                     .ExecuteNonQuery()
  146.                 End With
  147.                 Call Set1()
  148.             End If
  149.  
  150.         Else
  151.             ''''''''''UPDATE RECORD'''''''
  152.             If txtFlowerName.Text = "" Or txtFlowerMeaning.Text = "" Or picBox.Visible = Nothing Then
  153.                 MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  154.             Else
  155.                 myqry = "UPDATE TblFlower SET "
  156.                 myqry = myqry + " Flower Name = '" & txtFlowerName.Text & "',"
  157.                 myqry = myqry + " Flower Meaning = '" & txtFlowerMeaning.Text & "',"
  158.                 myqry = myqry + " Flower image = '" & picBox.Visible & "''"
  159.                 myqry = myqry + " WHERE "
  160.                 myqry = myqry + " FlowerID = " & txtFlowerID.Text
  161.  
  162.                 mycmd = New OleDbCommand(myqry, conn)
  163.                 mycmd.ExecuteNonQuery()
  164.                 Call Set1()
  165.             End If
  166.         End If
  167.  
  168.         Call ClearAlltextBox()
  169.     End Sub
  170. End Class
The attached file is my database that may probably need some adjustment. Please enlighten me.
  • 23 views