[RESOLVED] Upadating a data base file after changing a value in a DataGridView control
I have been trying desperately to update a data base file (Microsoft Access file) through a DataGridView control . Unfortunately there is something I am doing wrong . Maybe you can help me out ; after all , it's about a tiny code , so I guess the error should be very easy to spot .
As you can see , the form is very simple , containing only a button and the DataGridView control . Whenever I change anything on the DataGridView control and press the Update button , I get an error saying ""No value given for one or more parameters" .
Below there's a picture showing both my form and the whole code , along with the error message .
In case it is not possible to update a data base file through a DataGridView control , could you please tell how could I do it by code ?
http://img89.imageshack.us/img89/416...aaaaaaaaac.png
1 Attachment(s)
Re: Upadating a data base file after changing a value in a DataGridView control
I run into the same problem you do and I'm not sure what the solution is though I've never really tried to dig into it. I do know how to programmatically do update and deletions, etc. Here's code from an ADO.NET tutorial application that was in the ASP.NET forum. I'm not sure if it's still there or not and this code here works in Visual Basic 2010. The original code came from .NET 2003 or something like that and I had to do some updating of it to get it to work in Visual Basic 2008/2010.
Code:
Imports System.Data.OleDb
Public Class Form1
Dim ds As New DataSet()
Dim intCurrentIndex As Integer = 0
Dim da As New OleDbDataAdapter()
Dim conn As New OleDbConnection()
Private Sub FillFields()
FirstNameTextBox.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
LastNameTextBox.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
LocationTextBox.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=K:\Stuff\ADONetTutorial1\ADONetTutorial1\sample.mdb;User Id=admin;Password=;"
da.SelectCommand = New OleDbCommand("SELECT EmployeeID, FirstName, LastName, Location FROM tbl_Master")
'
da.SelectCommand.Connection = conn
da.UpdateCommand = New OleDbCommand("UPDATE tbl_Master SET FirstName = @FirstName, LastName = @LastName, Location = @Location WHERE EmployeeID = @EmployeeID")
da.UpdateCommand.Connection = conn
da.UpdateCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 40, "FirstName")
da.UpdateCommand.Parameters.Add("@LastName", OleDbType.VarChar, 40, "LastName")
da.UpdateCommand.Parameters.Add("@Location", OleDbType.VarChar, 40, "Location")
da.UpdateCommand.Parameters.Add("@EmployeeID", OleDbType.Integer, 5, "EmployeeID")
da.InsertCommand = New OleDbCommand("INSERT INTO tbl_Master(FirstName, LastName, Location) VALUES(@FirstName,@LastName,@Location)")
da.InsertCommand.Connection = conn
da.InsertCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 40, "FirstName")
da.InsertCommand.Parameters.Add("@LastName", OleDbType.VarChar, 40, "LastName")
da.InsertCommand.Parameters.Add("@Location", OleDbType.VarChar, 40, "Location")
da.DeleteCommand = New OleDbCommand("DELETE FROM tbl_Master WHERE EmployeeID = @EmployeeID")
da.DeleteCommand.Connection = conn
da.DeleteCommand.Parameters.Add("@EmployeeID", OleDbType.Integer, 5, "EmployeeID")
da.Fill(ds)
If ds.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
FillFields()
End If
End Sub
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
'Since 0 is the first row
intCurrentIndex = 0
FillFields()
End Sub
Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
If intCurrentIndex > 0 Then 'We move back only if we're not at the first row.
intCurrentIndex = intCurrentIndex - 1 'Subtract one from the current index.
FillFields()
Else
MessageBox.Show("You're already at the first record.")
End If
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then 'ds.Tables(0).Rows.Count - 1 is the index for the last row
intCurrentIndex = intCurrentIndex + 1 'Add one to the current index.
FillFields()
Else
MessageBox.Show("You're already at the last record.")
End If
End Sub
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
intCurrentIndex = ds.Tables(0).Rows.Count - 1 'ds.Tables(0).Rows.Count - 1 is the index for the last row
FillFields()
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim dr As DataRow
dr = ds.Tables(0).Rows(intCurrentIndex) 'This gets a reference to the row currently being edited
dr.BeginEdit()
dr("FirstName") = FirstNameTextBox.Text
dr("LastName") = LastNameTextBox.Text
dr("Location") = LocationTextBox.Text
dr.EndEdit()
da.Update(ds) 'Ask the dataadapter to call the UpdateCommand and update the database
ds.AcceptChanges() 'Commits the change to the dataset.
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim dr As DataRow
dr = ds.Tables(0).NewRow() 'Gets a reference to a new row.
dr("FirstName") = FirstNameTextBox.Text
dr("LastName") = LastNameTextBox.Text
dr("Location") = LocationTextBox.Text
ds.Tables(0).Rows.Add(dr)
da.Update(ds)
ds.AcceptChanges()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim dr As DataRow
dr = ds.Tables(0).Rows(intCurrentIndex)
dr.Delete() 'Delete the row
da.Update(ds)
ds.AcceptChanges()
End Sub
End Class
I attached a zip file with the Visual Studio 2010 solution file and other necessary files all in one folder. You're using Visual Basic 2008 but I think it will probably work ok for you. You'd have to adjust the connection string if you want this to work for you so that the sample database is found.
Re: Upadating a data base file after changing a value in a DataGridView control
Thank you EntityX for the reply . However , after so much time , i FINALLY made it ! You won't believe what the problem was ! You see , you must take care not use spaces or other characters in the names of the fields of your data base file . For example a field called "E-mail 1" should be written as something like "E_mail_1" ! I was using those names for years because the Classic ADO worked fine with such names (and still works , if you use it on VB .NET) and therefore I couldn't imagine that this could be a problem in the modern ADO .NET ...
Re: [RESOLVED] Upadating a data base file after changing a value in a DataGridView co
I recently learned that you shouldn't have any spaces in the names of your tables or databases either. If you put brackets around the names that will solve the problem. The problem I have when using a DataGridView is a little different. I already don't have any spaces in the names but I get a problem but then my code is a bit different. Every situation is unique.
I had a table name that had spaces in it and when using a SELECT statement I had problems but putting brackets [] around the name fixed the problem.
