How To Make a VB.Net Products Manager Project With MySQL Database Using Visual Studio
in this visual basic .net tutorial we will see how to Create a products manager project in one form using windowForm in vb.net programming language and mysql database.
tools:
- visual basic .net programming language.
- microsoft visual studio express 2013.
- mysql database.
- phpmyadmin.
- phpmyadmin.
- pixabay.com.
Watch The Full Tutorial
- The Project Source Code
Imports MySql.Data.MySqlClient
Public Class ManagementForm
' variables
Dim connection As MySqlConnection = New MySqlConnection("datasource = localhost;port = 3306; initial catalog = csharp_products; username = root; password =")
Dim table As DataTable = New DataTable()
Dim productIndex As Integer = 0
' form load
Private Sub ManagementForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' custom datagridview
dataGridViewProducts.RowTemplate.Height = 40
pictureBoxClose.Image = My.Resources.close
' populate datagridview
showProducts()
comboBoxCategories.SelectedIndex = 0
dataGridViewProducts.ColumnHeadersDefaultCellStyle.BackColor = Color.FromArgb(47, 54, 64)
dataGridViewProducts.ColumnHeadersDefaultCellStyle.ForeColor = Color.White
dataGridViewProducts.ColumnHeadersDefaultCellStyle.Font = New System.Drawing.Font("Verdana", 15, FontStyle.Italic)
dataGridViewProducts.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
dataGridViewProducts.GridColor = Color.DarkBlue
dataGridViewProducts.AllowUserToAddRows = False
dataGridViewProducts.EnableHeadersVisualStyles = False
End Sub
' picturebox close
Private Sub pictureBoxClose_Click(sender As Object, e As EventArgs) Handles pictureBoxClose.Click
' close the connection
If ConnectionState.Open = connection.State Then
connection.Close()
End If
System.Environment.[Exit](System.Environment.ExitCode)
End Sub
' button search
Private Sub buttonSearch_Click(sender As Object, e As EventArgs) Handles buttonSearch.Click
Try
Dim id As Integer = CInt(numericUpDownID.Value)
Dim row As DataRow() = table.[Select]("id = " & id)
If row.Length > 0 Then
textBoxName.Text = row(0)(1).ToString()
comboBoxCategories.Text = row(0)(2).ToString()
NumericUpDownQuantity.Text = row(0)(3).ToString()
textBoxPrice.Text = row(0)(4).ToString()
textBoxImagePath.Text = row(0)(5).ToString()
pictureBoxProduct.Image = Image.FromFile(row(0)(5).ToString())
Else
textBoxName.Text = ""
comboBoxCategories.SelectedIndex = 0
NumericUpDownQuantity.Text = ""
textBoxPrice.Text = ""
textBoxImagePath.Text = ""
pictureBoxProduct.Image = Nothing
MessageBox.Show("No Product With This ID, Enter a New One", "Product Not found", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
Catch ex As Exception
MessageBox.Show("Error -> " & ex.Message, "Product Not found", MessageBoxButtons.OK, MessageBoxIcon.[Error])
End Try
End Sub
' button browse image
Private Sub buttonBrowse_Click(sender As Object, e As EventArgs) Handles buttonBrowse.Click
Dim opf As OpenFileDialog = New OpenFileDialog()
opf.Filter = "Choose Image(*.jpg;*.png;*.gif)|*.jpg;*.png;*.gif"
If opf.ShowDialog() = DialogResult.OK Then
pictureBoxProduct.Image = Image.FromFile(opf.FileName)
textBoxImagePath.Text = opf.FileName
End If
End Sub
' button add product
Private Sub Button_Add_Click(sender As Object, e As EventArgs) Handles Button_Add.Click
Try
Dim name As String = textBoxName.Text
Dim category As String = comboBoxCategories.Text
Dim quantity As Integer = Integer.Parse(NumericUpDownQuantity.Text)
Dim price As Single = Single.Parse(textBoxPrice.Text)
Dim imagePath As String = textBoxImagePath.Text
Dim command As MySqlCommand = New MySqlCommand("INSERT INTO `product`(`name`, `category`, `quantity`, `price`, `picture`) VALUES (@nm,@ctg,@qty,@prc,@pic)", connection)
command.Parameters.Add("@nm", MySqlDbType.VarChar).Value = name
command.Parameters.Add("@ctg", MySqlDbType.VarChar).Value = category
command.Parameters.Add("@qty", MySqlDbType.Int16).Value = quantity
command.Parameters.Add("@prc", MySqlDbType.Float).Value = price
command.Parameters.Add("@pic", MySqlDbType.VarChar).Value = imagePath
If ConnectionState.Closed = connection.State Then
connection.Open()
End If
If command.ExecuteNonQuery() = 1 Then
showProducts()
MessageBox.Show("The Product Has Been Added Successfully", "Product Added", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("The Product Hasn't Been Added", "Product Not Added", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
Catch ex As Exception
MessageBox.Show("Enter The Product Info", "Empty Fileds", MessageBoxButtons.OK, MessageBoxIcon.[Stop])
End Try
End Sub
' button edit product
Private Sub Button_Edit_Click(sender As Object, e As EventArgs) Handles Button_Edit.Click
Try
Dim id As Integer = CInt(numericUpDownID.Value)
Dim name As String = textBoxName.Text
Dim category As String = comboBoxCategories.Text
Dim quantity As Integer = Integer.Parse(NumericUpDownQuantity.Text)
Dim price As Single = Single.Parse(textBoxPrice.Text)
Dim imagePath As String = textBoxImagePath.Text
Dim command As MySqlCommand = New MySqlCommand("UPDATE `product` SET `name`=@nm,`category`=@ctg,`quantity`=@qty,`price`=@prc,`picture`=@pic WHERE `id` = @id", connection)
command.Parameters.Add("@nm", MySqlDbType.VarChar).Value = name
command.Parameters.Add("@ctg", MySqlDbType.VarChar).Value = category
command.Parameters.Add("@qty", MySqlDbType.Int16).Value = quantity
command.Parameters.Add("@prc", MySqlDbType.Float).Value = price
command.Parameters.Add("@pic", MySqlDbType.VarChar).Value = imagePath
command.Parameters.Add("@id", MySqlDbType.Int16).Value = id
If ConnectionState.Closed = connection.State Then
connection.Open()
End If
If command.ExecuteNonQuery() = 1 Then
showProducts()
MessageBox.Show("The Product Info Has Been Edited Successfully", "Edit Product", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("The Product Info Hasn't Been Edited", "Product Not Edited", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
Catch ex As Exception
MessageBox.Show("Enter The Product Info", "Empty Fileds", MessageBoxButtons.OK, MessageBoxIcon.[Stop])
End Try
End Sub
' button remove product
Private Sub Button_Remove_Click(sender As Object, e As EventArgs) Handles Button_Remove.Click
Dim id As Integer = CInt(numericUpDownID.Value)
Dim command As MySqlCommand = New MySqlCommand("DELETE FROM `product` WHERE `id` = @id", connection)
command.Parameters.Add("@id", MySqlDbType.Int16).Value = id
If ConnectionState.Closed = connection.State Then
connection.Open()
End If
If MessageBox.Show("Are You Sure You Want To Remove This Product", "Remove Product", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = System.Windows.Forms.DialogResult.Yes Then
If command.ExecuteNonQuery() = 1 Then
numericUpDownID.Value = 0
textBoxName.Text = ""
comboBoxCategories.SelectedIndex = 0
NumericUpDownQuantity.Text = ""
textBoxPrice.Text = ""
textBoxImagePath.Text = ""
pictureBoxProduct.Image = Nothing
showProducts()
MessageBox.Show("Product Removed Successfully", "Remove Product", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Product NOT Removed", "Remove Product", MessageBoxButtons.OK, MessageBoxIcon.[Error])
End If
End If
End Sub
' button clear fields
Private Sub buttonClear_Click(sender As Object, e As EventArgs) Handles buttonClear.Click
textBoxName.Text = ""
comboBoxCategories.SelectedIndex = 0
NumericUpDownQuantity.Text = ""
textBoxPrice.Text = ""
textBoxImagePath.Text = ""
pictureBoxProduct.Image = Nothing
End Sub
' button display first product
Private Sub buttonFirst_Click(sender As Object, e As EventArgs) Handles buttonFirst.Click
productIndex = 0
displayInfo(productIndex)
End Sub
' button display next product
Private Sub buttonNext_Click(sender As Object, e As EventArgs) Handles buttonNext.Click
productIndex += 1
If productIndex > table.Rows.Count - 1 Then
productIndex = table.Rows.Count - 1
End If
displayInfo(productIndex)
End Sub
' button display previous product
Private Sub buttonPrevious_Click(sender As Object, e As EventArgs) Handles buttonPrevious.Click
productIndex -= 1
If productIndex < 0 Then
productIndex = 0
End If
displayInfo(productIndex)
End Sub
' button display last product
Private Sub buttonLast_Click(sender As Object, e As EventArgs) Handles buttonLast.Click
productIndex = table.Rows.Count - 1
displayInfo(productIndex)
End Sub
' display the selected product from datagridview
Private Sub dataGridViewProducts_Click(sender As Object, e As EventArgs) Handles dataGridViewProducts.Click
Dim selectedRow As DataGridViewRow = dataGridViewProducts.CurrentRow
numericUpDownID.Value = Integer.Parse(selectedRow.Cells(0).Value.ToString())
textBoxName.Text = selectedRow.Cells(1).Value.ToString()
comboBoxCategories.Text = selectedRow.Cells(2).Value.ToString()
NumericUpDownQuantity.Text = selectedRow.Cells(3).Value.ToString()
textBoxPrice.Text = selectedRow.Cells(4).Value.ToString()
textBoxImagePath.Text = selectedRow.Cells(5).Value.ToString()
pictureBoxProduct.Image = Image.FromFile(selectedRow.Cells(5).Value.ToString())
End Sub
' textBox Price KeyUp - check if the price is correct
Private Sub textBoxPrice_KeyUp(sender As Object, e As KeyEventArgs) Handles textBoxPrice.KeyUp
Try
Dim price As Single = Single.Parse(textBoxPrice.Text)
Catch ex As Exception
MessageBox.Show("Enter a Valid Price ( " & ex.Message & " )", "Invalid Price", MessageBoxButtons.OK, MessageBoxIcon.[Error])
textBoxPrice.Text = ""
End Try
End Sub
' create a function to display product in textboxes and picturebox
Public Sub displayInfo(ByVal index As Integer)
dataGridViewProducts.ClearSelection()
dataGridViewProducts.Rows(index).Selected = True
numericUpDownID.Value = CInt(table.Rows(index)(0))
textBoxName.Text = table.Rows(index)(1).ToString()
comboBoxCategories.Text = table.Rows(index)(2).ToString()
NumericUpDownQuantity.Text = table.Rows(index)(3).ToString()
textBoxPrice.Text = table.Rows(index)(4).ToString()
textBoxImagePath.Text = table.Rows(index)(5).ToString()
pictureBoxProduct.Image = Image.FromFile(table.Rows(index)(5).ToString())
End Sub
' create a function to show products in the datagridview
Public Sub showProducts()
table.Clear()
dataGridViewProducts.DataSource = table
Dim adapter As MySqlDataAdapter = New MySqlDataAdapter("SELECT * FROM `product`", connection)
adapter.Fill(table)
dataGridViewProducts.DataSource = table
label_ProductsCount.Text = table.Rows.Count.ToString() & " Product"
End Sub
End Class
OUTPUT:
Download Projects Source Code