I am trying to create an Inventory/Stock Control system as a windows application using vb.net with an Access database. This is my first time trying to use vb.net and im having difficulty making the application interactive. I am unable to update records to the database from the front end.
I am creating this application in Visual Studio 2008. The first problem that i would like to resolve is trying to update records to the database. My front-end consists of a datagrid at the top of the screen, which brings back all of the records from Qry_Categories. I then have text boxes below the datagrid that replicate the data in the grid above it. So which ever row is highlighted in the datagrid, that data is then automatically filled in the text boxes below it.
I then have an update button. To update a record the user will highlight the row in the datagrid, this information will then get filled in the text boxes. The user will make changes to any of the text boxes and then click on the 'Update' button. This is where I am having the problems. Please can someone provide me with the update button code that will work, as i have tried so many and none seem to work.
This is my code so far:
Public Class Form1
Dim con As New OleDb.OleDbConnection
Dim adp As OleDb.OleDbDataAdapter
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(adp)
Dim ds As New DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'StockControlDataSet.LK_Categories' table. You can move, or remove it, as needed.
Me.LK_CategoriesTableAdapter.Fill(Me.StockControlDataSet.LK_Categories)
con.ConnectionString = ConfigurationManager.ConnectionStrings _
("WRC_SMS_1.My.MySettings.StockControlConnectionString").ConnectionString.ToString()
' Fill the data grid viewer
con.Open()
cmd = New OleDb.OleDbCommand("Select * from Qry_Categories", con)
adp = New OleDb.OleDbDataAdapter(cmd)
adp.Fill(ds, "Qry_Categories")
Me.LK_CategoriesDataGridView.DataSource = ds
Me.LK_CategoriesDataGridView.DataMember = "Qry_Categories"
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
'open(connection)
con.Open()
cmd.Connection = con
Try
cmd.CommandText = "UPDATE LK_Categories SET CategoryName = @CategoryName WHERE PK_CategoryID = @CatID"
'"UPDATE LK_Categories SET CategoryName = @CategoryName WHERE PK_CategoryID = 16"
Dim param As New OleDb.OleDbParameter
Dim param2 As New OleDb.OleDbParameter
'add content txtProdName as parameter
param.ParameterName = "@CategoryName"
param.Value = txtbxCatName.Text()
cmd.Parameters.Add(param)
param2.ParameterName = "@CatID"
param2.Value = txtCatID.Text
cmd.Parameters.Add(param2)
cmd.ExecuteNonQuery()
MessageBox.Show("customer data successfully updated")
Catch ex As Exception
MessageBox.Show("cust data not updated because " _
& ex.Message)
Finally
'close connection if it is open
If (con.State = ConnectionState.Open) Then
con.Close()
End If
End Try
con.Close()
End SubThe Qry_Categories has the following field names:
PK_CategoryID
CategoryName
Description
I would really appreciate the help as it is urgent i get this application up and running. Thanks in advance
