Results 1 to 10 of 10

Thread: [RESOLVED] [2005] Updating datagrid to database

  1. #1

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Resolved [RESOLVED] [2005] Updating datagrid to database

    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
    Last edited by dinosaur_uk; Nov 15th, 2007 at 03:52 AM.
    If you find my thread helpful, please remember to rate me

  2. #2

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: [2005] Updating datagrid to database

    I am not using any relational database, so can i use a datatable instead of a dataset?
    If you find my thread helpful, please remember to rate me

  3. #3

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: [2005] Updating datagrid to database

    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)
    If you find my thread helpful, please remember to rate me

  4. #4

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: [2005] Updating datagrid to database

    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
    If you find my thread helpful, please remember to rate me

  5. #5

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: [2005] Updating datagrid to database

    Anyone?
    If you find my thread helpful, please remember to rate me

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: [2005] Updating datagrid to database

    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.

  7. #7

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: [2005] Updating datagrid to database

    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
    If you find my thread helpful, please remember to rate me

  8. #8

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: [2005] Updating datagrid to database

    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
    If you find my thread helpful, please remember to rate me

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: [2005] Updating datagrid to database

    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.

  10. #10

    Thread Starter
    Frenzied Member dinosaur_uk's Avatar
    Join Date
    Sep 2004
    Location
    Jurassic Park
    Posts
    1,098

    Re: [2005] Updating datagrid to database

    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"
    If you find my thread helpful, please remember to rate me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width