[RESOLVED] Upadating a data base file after changing a value in a DataGridView control

[iliekater]

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 ?

[EntityX]

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.

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.

[iliekater]

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 ...

[EntityX]

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.