VB.Net Products Manager Project With MySQL Database

How To Make a VB.Net Products Manager Project With MySQL Database Using Visual Studio

VB.Net Products Manager Project With MySQL Database


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.
- 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:

VB.Net Project Source Code With MySQL Database

VB.Net Project Source Code With MySQL Database






Share this

Related Posts

Previous
Next Post »