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:
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.
At this time we are ready for updating the data. To do this double click the update button and add the following code:
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.
The output of the code above when successfully executed will look like as shown below:
- 'the dataset and update the datasource
- Dim da As New OleDb.OleDbDataAdapter
- 'declare some SQL statements
- Dim sql As String = "SELECT ItemID as [ID], itemname as [Item Name],itemdescription as [Description],itemremarks," & _
- "itembarcodeno, itemcategory, itemnoqty FROM tblitemmaster;"
- Try
- 'open the connection
- con.Open()
- With cmd
- .Connection = con
- .CommandText = sql
- End With
- 'get the SQL statements used to select records in the datasource
- da.SelectCommand = cmd
- 'it fills the datatable
- da.Fill(publictable)
- 'populate the datagridview
- itemdatagrid.DataSource = publictable
- Catch ex As Exception
- End Try
- Private Sub itemdatagrid_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles itemdatagrid.CellClick
- lblitemkey.Text = itemdatagrid.CurrentRow.Cells(0).Value.ToString
- txtitemname.Text = itemdatagrid.CurrentRow.Cells(1).Value.ToString
- txtitemdescription.Text = itemdatagrid.CurrentRow.Cells(2).Value.ToString
- txtitemremarks.Text = itemdatagrid.CurrentRow.Cells(3).Value.ToString
- txtitembarcode.Text = itemdatagrid.CurrentRow.Cells(4).Value.ToString
- cbcategory.SelectedValue = itemdatagrid.CurrentRow.Cells(5).Value.ToString
- End Sub
- 'we declare cmd to represent an SQL Statements or a stored procedure
- 'to execute against a data source
- Dim cmd As New OleDb.OleDbCommand
- Dim sql As String = "UPDATE tblitemmaster SET itemname = '" & txtitemname.Text & "'," & _
- " itemdescription = '" & txtitemdescription.Text & "', " & _
- " itemremarks = '" & txtitemremarks.Text & "', itembarcodeno = '" & txtitembarcode.Text & "', " & _
- " itemcategory = '" & cbcategory.SelectedItem & "', itemnoqty = '" & Val(txtnumqty.Text) & "', " & _
- " where itemID = " & lblitemkey.Text & ""
- Dim result As Integer
- Try
- con.Open()
- With cmd
- .Connection = con
- .CommandText = Sql
- result = cmd.ExecuteNonQuery
- If result = 0 Then
- Else
- Call btnitemload_Click(sender, e)
- End If
- End With
- Catch ex As Exception
- End Try
- Dim cmd As New OleDb.OleDbCommand
- Dim sql As String = "Delete * from tblitemmaster where itemID= " & lblitemkey.Text & ""
- Dim result As Integer
- Try
- con.Open()
- With cmd
- .Connection = con
- .CommandText = sql
- result = cmd.ExecuteNonQuery
- If result = 0 Then
- Else
- Call btnitemload_Click(sender, e)
- End If
- End With
- Catch ex As Exception
- End Try
Comments
First Check whether database
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
Add new comment
- Add new comment
- 258 views