[RESOLVED] [2005] Updating datagrid to database

:confused: Hi All,

I would like to have a feature on my program but i am unsure on how to do so.

I would like to query a database and pull the Data from the database into a datatable, no worries there.

Code:

---

    Sub LoadDataTable()
        'Using a Data adapter to load data into the DataTable..
        DA = New OleDb.OleDbDataAdapter("Select * from Employees WHERE Datey>= 26/07/07", CN)
        DT = New DataTable
        DA.Fill(DT)

        'initializing command builder, this will be used later..
        CMB = New OleDb.OleDbCommandBuilder(DA)
    End Sub

---

But i would like the user to be able to edit the data in the datagrid and click a button to save and it will update the database.

But if the row is a new row then it will insert the data into the database.


Any ideas on how to do that?

Many thanks

ken


However, if there is no

I am not using any relational database, so can i use a datatable instead of a dataset?

Ok got the additions and updating working...

how do i delete the rows?

Code:

---

  Dim dtt As New DataTable

        dtt = DT.GetChanges()

        'we are going to use the CommandBuilder to get the insert command, which the DataAdapter will use to update the Data Base
        DA.InsertCommand = CMB.GetInsertCommand 'the command builder is already created and initialized in Form1_Load()..

        'inserts the new row to the database
        DA.Update(dtt)

---

Argh, this is confusing,

Ok this is my database in access.

Tablename: "Employees"
EmpID Name Salary DeptID
1 Raja Edited 30000 5
2 Rani 20000 20
5 Vijay 24000 20
6 Siva 10000 10

I want to just "SELECT [Name], [Salary] FROM [Employees]" and put it into a datagrid (via a datatable)

Then The user can edit, insert or delete the entries and then click a button to write the changes to the database.

Is this possible?

the code above works when i "SELECT * From [Employees]", but when i run the same code the following error is thrown up
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

Please helppppp

Anyone?

You can't use a CommandBuilder to create your UpdateCommand and DeleteCommand if your query doesn't return PK information. How is each row to be updated or deleted to be identified if there's no PK? Follow the Data Access Examples link in my signature.

Hi jmcilhinney,

Have read your post about the Data Access Examples, and it is brilliant

I was wondering for your example:

Code:

---

Dim connection As New SqlConnection("connection string here")Dim adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", connection)Dim builder As New SqlCommandBuilder(adapter) adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey Dim table As New DataTable 'Retrieve the data.adapter.Fill(table) 'The table can be used here to display and edit the data.'That will most likely involve data-binding but that is not a data access issue. 'Save the changes.adapter.Update(table)

---

How can i make it that it displays a customised header in the datagridview? For example, instead of the field name in the database, i want to show a more descriptive column name.

is it using table.column(0).columnname = "Detailed title" ? coz it does not seem to work :(


i am using .net 2.0

Code:

---

Dim adapter As OleDbDataAdapter
    Dim dt As DataTable

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=;Data Source=" & Application.StartupPath & "\SNS.mdb")
        adapter = New OleDbDataAdapter("SELECT * FROM Wells", connection)
        Dim builder As New OleDbCommandBuilder(adapter)
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        dt = New DataTable
        dt.Clear()
        'Retrieve the data.
        adapter.Fill(dt)
        Me.DataGridView1.DataSource = dt

        'The table can be used here to display and edit the data.


        'That will most likely involve data-binding but that is not a data access issue.
        'Save the changes.

    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        adapter.Update(dt)
    End Sub

---

The DataGridViewColumn class has a HeaderText property that you can set if you don't want the name of the bound DataColumn used. You can either access the columns after setting the DataSource property, which is where columns are automatically generated, or you can create the columns at design time and specify the HeaderText then.

legend!, have sorted it out...and it updates back to the database too

amazing!

Code:

---

Dim dc As DataGridViewColumn
        dc = Me.DataGridView1.Columns(1)
        dc.HeaderText = "Woo"

---