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:





