I have a question related to the one I posted at http://www.vbdotnetforums.com/ms-access/47948-update-database-using-dgv.html, but as it is a different question I thought it best to start a new thread.
I can update the database via the DGV perfectly when the DGV has the same columns as the database table.
I need to update a database field using a combobox column that I programatically add at run time. For the purposes of testing, I made a small application and database.
The database has one table called "Test" and 3 fields, NameId (key), FirstName, Result.
I populate the DGV with NameId and FirstName, and programatically add a combobox column called ResultList. I want to update the values in the Result field with the text selected in the comboboxes. However, I'm getting an error message when I run the SaveData function: "Parameter @Result has no default value". It seems to be unable to recognise that I've added the third column.
My code for the entire test application is as follows.
Once again, any help will be greatly appreciated. Sadly, my lack of experience with the DGV control is an issue here.
I can update the database via the DGV perfectly when the DGV has the same columns as the database table.
I need to update a database field using a combobox column that I programatically add at run time. For the purposes of testing, I made a small application and database.
The database has one table called "Test" and 3 fields, NameId (key), FirstName, Result.
I populate the DGV with NameId and FirstName, and programatically add a combobox column called ResultList. I want to update the values in the Result field with the text selected in the comboboxes. However, I'm getting an error message when I run the SaveData function: "Parameter @Result has no default value". It seems to be unable to recognise that I've added the third column.
My code for the entire test application is as follows.
VB.NET:
Imports System.Data.OleDb
Public Class Form1
Private connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Damien Fenton\Desktop\SQL Test.mdb;User Id=admin;Password=;")
Private adapter As New OleDbDataAdapter("SELECT NameId, FirstName FROM Test", connection)
Private table As New DataTable
Private Sub InitialiseDataAdapter()
Dim update As New OleDbCommand("UPDATE Test SET FirstName = @FName, Result = @Result WHERE NameId = @NameId", Me.connection)
update.Parameters.Add("@FName", OleDbType.VarChar, 4, "FirstName")
update.Parameters.Add("@Result", OleDbType.VarChar, 4, "ResultList")
update.Parameters.Add("@NameId", OleDbType.Integer, 4, "NameId")
Me.adapter.UpdateCommand = update
Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
Private Sub GetData()
'Retrieve the data.
Me.adapter.Fill(Me.table)
DataGridView1.DataSource = table
Dim NewColumn As New DataGridViewComboBoxColumn() 'Declare new DGV CC
With NewColumn 'Set Properties
.DataPropertyName = "ResultList" 'Name
.HeaderText = "Result List" 'Heading
.DropDownWidth = 160 'Width Of DropDown Box
.Width = 90 'Display Width
.MaxDropDownItems = 5 'How Many Items To Drop Down At A Time
.ReadOnly = False
.Items.Add("Attended") 'Add Some Text Items
.Items.Add("Did not attend")
.Items.Add("Left early")
.Items.Add("Arrived Late")
End With
DataGridView1.Columns.Add(NewColumn) 'Add The Column
End Sub
Private Sub SaveData()
InitialiseDataAdapter()
Me.adapter.Update(Me.table)
MsgBox("done")
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
GetData()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
SaveData()
End Sub
End Class
