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 Sub
The Qry_Categories has the following field names:
I would really appreciate the help as it is urgent i get this application up and running. Thanks in advance