Stock Inventory System: Completing the Stock Master

Submitted by joken on Monday, November 18, 2013 - 16:28.
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:

Tags

Comments

Submitted bysaijadon Mon, 11/18/2013 - 17:58

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

it is inside the bin folder.example:Stockinven\Stockinven\bin\Debug\stocksdb.mdb
Submitted byiyaon Tue, 11/19/2013 - 10:34

How to run that application?
Submitted byiyaon Tue, 11/19/2013 - 10:43

Error: The connection was not closed. The connection's current state open... How to solve this?
Submitted bySystem Developer (not verified)on Wed, 03/26/2014 - 14:05

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 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" ' it should be on the top of class declared

Add new comment

About text formats