Stock Inventory System: Completing the Stock Master

Submitted by: 
Visitors have accessed this post 4549 times.

This tutorial is a continuation of our last topic called “Creating the Stock Master”. This time we're going to focus on loading, Updating and Deleting of an Item. To start in this lesson, open first our project named “Stockinven”. Then open the Stock master form and double click the load button and add the following code:

  1. 'the dataset and update the datasource
  2. Dim da As New OleDb.OleDbDataAdapter
  3. 'declare some SQL statements
  4. Dim sql As String = "SELECT ItemID as [ID], itemname as [Item Name],itemdescription as [Description],itemremarks," & _
  5. "itembarcodeno, itemcategory, itemnoqty FROM tblitemmaster;"
  6.  
  7.  
  8. Try
  9. 'open the connection
  10. con.Open()
  11. With cmd
  12. .Connection = con
  13. .CommandText = sql
  14. End With
  15. 'get the SQL statements used to select records in the datasource
  16. da.SelectCommand = cmd
  17. 'it fills the datatable
  18. da.Fill(publictable)
  19. 'populate the datagridview
  20. itemdatagrid.DataSource = publictable
  21. Catch ex As Exception
  22. MsgBox(ex.Message)
  23. End Try

The output of the code above when successfully executed will look like as shown below:

To update the data or an item the user must click first a specific from a datagridview. So we need to create a code when the datagrid cell is clicked. And here’s the following code:
This code below will triggered if the user clicked on the specific entry in the datagridview and display the data to every text field provided.

  1. Private Sub itemdatagrid_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles itemdatagrid.CellClick
  2.  
  3. lblitemkey.Text = itemdatagrid.CurrentRow.Cells(0).Value.ToString
  4. txtitemname.Text = itemdatagrid.CurrentRow.Cells(1).Value.ToString
  5. txtitemdescription.Text = itemdatagrid.CurrentRow.Cells(2).Value.ToString
  6. txtitemremarks.Text = itemdatagrid.CurrentRow.Cells(3).Value.ToString
  7. txtitembarcode.Text = itemdatagrid.CurrentRow.Cells(4).Value.ToString
  8. cbcategory.SelectedValue = itemdatagrid.CurrentRow.Cells(5).Value.ToString
  9.  
  10. End Sub

At this time we are ready for updating the data. To do this double click the update button and add the following code:

  1. 'we declare cmd to represent an SQL Statements or a stored procedure
  2. 'to execute against a data source
  3. Dim cmd As New OleDb.OleDbCommand
  4. Dim sql As String = "UPDATE tblitemmaster SET itemname = '" & txtitemname.Text & "'," & _
  5. " itemdescription = '" & txtitemdescription.Text & "', " & _
  6. " itemremarks = '" & txtitemremarks.Text & "', itembarcodeno = '" & txtitembarcode.Text & "', " & _
  7. " itemcategory = '" & cbcategory.SelectedItem & "', itemnoqty = '" & Val(txtnumqty.Text) & "', " & _
  8. " itemqty = '" & cbunitqty.SelectedItem & "', itempricce = '" & Val(txtprice.Text) & "', " & _
  9. " itemtotalprice = '" & Val(txttotprice.Text) & "', dateencoded= #" & DateValue(itemdate.Value) & "# " & _
  10. " where itemID = " & lblitemkey.Text & ""
  11. Dim result As Integer
  12.  
  13. Try
  14. con.Open()
  15. With cmd
  16. .Connection = con
  17. .CommandText = Sql
  18. result = cmd.ExecuteNonQuery
  19. If result = 0 Then
  20. MsgBox("No Data has been Updated!")
  21. Else
  22. MsgBox("New Data is updated succesfully!")
  23. Call btnitemload_Click(sender, e)
  24. End If
  25. End With
  26. Catch ex As Exception
  27. MsgBox(ex.Message, MsgBoxStyle.Information)
  28.  
  29. End Try
  30. con.Close()

The output of the code above when successfully executed will look like as shown below:

Next, add functionality to our Delete button. Double click the Delete button and add the following code:
The code below will function when the user click the selected item in the datagidview before the click the Delete button.

  1. Dim cmd As New OleDb.OleDbCommand
  2. Dim sql As String = "Delete * from tblitemmaster where itemID= " & lblitemkey.Text & ""
  3. Dim result As Integer
  4.  
  5. Try
  6. con.Open()
  7. With cmd
  8. .Connection = con
  9. .CommandText = sql
  10. result = cmd.ExecuteNonQuery
  11. If result = 0 Then
  12. MsgBox("No Data has been Deleted!")
  13. Else
  14. MsgBox("New Data is deleted succesfully!")
  15. Call btnitemload_Click(sender, e)
  16. End If
  17. End With
  18. Catch ex As Exception
  19. MsgBox(ex.Message, MsgBoxStyle.Information)
  20.  
  21. End Try
  22. con.Close()

The output of the code above when successfully executed will look like as shown below:


Comments

< con.Open() >

First Check whether database is opened or not using following codes
if con.State = connectionState.Close Then
con.open()
End If

be sure your database table structure should not be opened in any other software like Ms Access or any other database utility.

Regarding < ask about load error with cmd and public table not declared>

Check What is your database table name. Your database table name must be similar with the name which is used in database functions.
Always "import data namespace before using database utilities"

< Imports System.Data.Ole > ' it should be on the top of class declared

ask about load error with cmd and public table not declared

Error: The connection was not closed. The connection's current state open...

How to solve this?

How to run that application?

Where is the database file......?

it is inside the bin folder.example:Stockinven\Stockinven\bin\Debug\stocksdb.mdb

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.