none
Re: Update Database from Dataset / Datagrid

    Question

  • I've been struggling with this as well.
    A lot of the documentation has to do with SQL and I am working with OLEDB from an Access DB.

    I want to import the DB to a dataset, populate the datagrid with the dataset.
    Updates will then be made to the datagrid programatically.
    Once verified they'll then be updated back to the dataset, then to the DB.

    I'm having an issue with sending the changes from the datagrid to the dataset and in turn, the DB.

    I modified the above code slightly to work with my adapter/datagrid and it looks like I need to convert my Datagrid to a datatable before attempting to update the dataset.
    I get the following error with the code above:

    Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.DataTable'

    I know I'm missing a vital piece.  I've got the general gist, but without finding an example that's using the same kind of adapter I can only attempt to fill in the blanks.

    I was going to include a code sample, but I've tried about 10 different ways according to MSDN, help, and forum examples... and it's nothing but goop.  Here's the general idea:

    datagrid: dgImport 

    adapter: tImportTableAdapter

    dataset: SurveyDataSet

    database: Survey.mdb
    (has 2 tables tImport and tSent)

    Monday, December 11, 2006 8:43 PM

Answers

All replies

  • what is your code? how are you creating the dataset/tableadapter? via wizard or are you doing this programmatically using the DataAdapter and DataSet?

    take a look at this in the time being:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=753872&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=728535&SiteID=1

    Tuesday, December 12, 2006 12:18 AM
  •  ahmedilyas wrote:

    what is your code? how are you creating the dataset/tableadapter? via wizard or are you doing this programmatically using the DataAdapter and DataSet?

    Well it looks like it was done via wizard when I chose via properties what to bind the datagrid to it created the adapter, etc.
    I removed a line from the onload that bound and loaded data into the grid and was able to do things with the code from that post. (2nd link)The code in It was a great help - exactly what I needed.  I was making it way too complicated.  I thought there was a command in addition to the datasource assignment.  Being "bound", it makes perfect sense now.

    With the code below, I was able to get a blank table to load, it sets the column names and sits there inviting input.
    Now for the complicated step I left out -> adding the values into the table from an excel spread sheet.

    I can't add rows to the datagrid when it's bound, and as soon as I remove the binding (set to nothing) the column names go away.
    (I know the answer would be to have it in ACCESS to begin with, but I've got to work with what I've got)

    I was thinking of extracting the column names to variables, but that still doesn't help me when I need to send the data from the datagrid to the DB.

    How can I
    1.) Import column names from Access to DataGrid
    2.) Import data from Excel to the same DataGrid
    3.) Re-bind to DataSource and update DB with changes

    (located within the main class)
    Public ImportDBCommand As New OleDbCommand
    Public ImportDBAdapter As New OleDbDataAdapter(ImportDBCommand)
    Public ImportDBDataset As New DataSet

    Private Sub ImportDB()
    Dim ImportDBConnectionString As String
    ImportDBConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Survey.mdb;"
    Me
    .ImportDBCommand = New OleDbCommand("SELECT * FROM tImport", New OleDbConnection(ImportDBConnectionString))
    Me.ImportDBAdapter = New OleDbDataAdapter(Me.ImportDBCommand)
    Me.ImportDBDataset = New DataSet()
    Me.ImportDBCommand.Connection.Open()
    Me.ImportDBAdapter.Fill(Me.ImportDBDataset)
    Me.ImportDBCommand.Connection.Close()

    'Bind Dataset to the Datagrid
    Me.dgImport.DataSource = Me.ImportDBDataset.Tables(0).DefaultView
    End Sub

    Private Sub ImportEX(ByVal day As Integer)

    Dim Type = "TEAM"
    Dim
    sndfile As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Type & ".xls;" & "Extended Properties=""Excel 8.0;HDR=YES"""
    Dim conn1 As New System.Data.OleDb.OleDbConnection(sndfile)

    Dim constring1 = "Select * From [" & "Oct 22$]" 'date to be replaced by variable
    Dim cmd1 As New System.Data.OleDb.OleDbCommand(constring1, conn1)
    Dim rdr As OleDbDataReader = cmd1.ExecuteReader
    Dim v = 0
    Dim w = ""
    Dim x = ""
    Dim y As String
    Dim ticket As String
    Dim z = ""

    Try
    conn1.Open()

    Do
    While rdr.Read() And Not rdr(0).ToString = ""
    'MsgBox(rdr.FieldCount)
    w = rdr(0).ToString
    x = rdr(1).ToString
    y = rdr(3).ToString
    ticket = rdr(4).ToString

    If w <> Nothing Then
    dgImport.DataSource = Nothing
    dgImport.Rows.Add(v - 1, w, x, y, ticket)
    End If

    v = v + 1
    Loop

    Catch ex As OleDbException
    Dim er As OleDbError
    For Each er In ex.Errors
    MsgBox(er.Message)
    Next
    Catch ex2 As System.InvalidOperationException
    MsgBox(ex2.Message)
    Catch ex3 As DataException
    MsgBox(ex3.Message)
    End Try

    End Sub

    Tuesday, December 12, 2006 6:31 PM
  • **update**
    Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
    Me.ImportDBCommand.Connection.Open()
    Me.ImportDBAdapter.Update(ImportDBDataset)
    Me.ImportDBCommand.Connection.Close()
    End Sub

    - took out the excel part just to verify the rest was working.  After I manually modified the table and clicked update, I get the following message:
    System.InvalidOperationException was unhandled
    "Update requires a valid InsertCommand when passed DataRow collection with new rows."
    Source="System.Data"

    I then attempted to make a "changed" datatable with ImportDBDataset.getchanges():
    Dim ChangesDT As DataTable
    ChangesDT = ImportDBDataset.tables(0).GetChanges()
    Me.ImportDBAdapter.Update(ChangesDT)
    but get the same error.

    It's acting like it's not getting the SELECT statement, do I have to send it an INSERT statement instead, or does it use the original adapter declaration to update the DB?

    I'm also using AUTONUMBER as the primary key in the DB, I'm going to remove the autonumber and go manual to see if that makes a difference.

    Tuesday, December 12, 2006 7:47 PM
  • you need to create a valid InsertCommand, DeleteCommand and UpdateCommand (even though it needs InsertCommand, better safe than sorry the next time) via the dataset designer I believe, in its properties of the dataAdapter.
    Tuesday, December 12, 2006 8:49 PM
  •  ahmedilyas wrote:
    you need to create a valid InsertCommand, DeleteCommand and UpdateCommand (even though it needs InsertCommand, better safe than sorry the next time) via the dataset designer I believe, in its properties of the dataAdapter.

    I appreciate your help on this, it's so close...

    Went to properties of dataadapter and created an InsertCommand, DeleteCommand, and UpdateCommand via the wizard. Testing the query works, and even adding items to the DB works through the dataset designer wizard but not via code.  When I run it with Me.ImportDBAdapter(ImportDBDataSet) it gives me the same error.  Curious - I hovered over the Me.ImportDBAdapter.UpdateCommand and it was "Nothing" so the commands are not making it from point A to point B.

    I then tried the below hoping the OleDBCommandBuilder would pick up the slack:

    Me.ImportDBCommand.Connection.Open()
    Dim newcmd As OleDbCommand
    newcmd = New OleDbCommand( _
    "SELECT ID, Name, Email, [SC NBR], [SC Date] FROM tImport", Me.ImportDBCommand.Connection)
    Me.ImportDBAdapter.SelectCommand = newcmd
    Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(Me.ImportDBAdapter)
    Me.ImportDBAdapter.Update(ImportDBDataset)
    Me.ImportDBCommand.Connection.Close()

    Get the error: Syntax error in INSERT INTO statement.

    So I've got the Commands created, they're sitting there and I can see them in the properties, but they're not getting through to the code when it executes.  Is there something I need to do to make them Public?  Copy them to a public variable?

    In the meanwhile I'm going to see if I can access the UpdateCommand from anywhere else in the class in order to pass to the other procedure from there.

     

    Wednesday, December 13, 2006 6:12 PM
  • Note to anyone following up - I got a book.

    ADO .NET 2.0 - Step by Step

    It helped a little, since it's all SQL based, it's hard to do the converting to OLE DB on the fly.

    However it was close enough that I could get some things clear.  I was confusing the Adapter commands with Table adapter commands, and the fact that a Table Adapater can't be created manually further aggrevated my situation.

    Once I dragged the tables into the form it created the Table Adapaters automatically and I was able to do the .fill and .update functions on the 2 separate tables.

    I still have to work out the details of working with 2 relational tables, but I got it to update to the child table, which is more then I could do before.

    Best of luck - you'll need it =)

    Tuesday, December 19, 2006 5:21 PM
  • Hi there, from reading the comments on your post, it sounds like you might have found the answer to my problem?! It would help if you could tell me why my database does not get updated with the changes I do on my datagrid?

    thanks in advance for your help...

    my code:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.OleDb;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;


    namespace AdminConsole
    {
        public partial class AdminConsole : Form
        {
            DataSet myDs;
            DataGrid myDg;
            OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter();
           
            public AdminConsole()
            {
                InitializeComponent();
            }
           
           
            void InitializeComponent()
            {
                this.ClientSize = new System.Drawing.Size(550, 1450);
                myDg = new DataGrid();
                myDg.Location = new Point(10, 10);
                myDg.Size = new Size(500, 400);
                myDg.CaptionText = "Data Grid";
                this.Text = "Administration Client";
                this.Controls.Add(myDg);
                ConnectToData();
                myDg.SetDataBinding(myDs, "Orders");
               
                // add a RowChanged event handler for the table.
                myDs.Tables["Orders"].RowChanged += new DataRowChangeEventHandler(Row_Changed);

             }
            
            //1.Build and fill each DataTable in a DataSet with data from a data source using a DataAdapter.
            void ConnectToData()
            {
                // New ConnectionString and new dataset
                string cString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=adminconsole.mdb";
                OleDbConnection myConnection = new OleDbConnection(cString);
                myConnection.Open();
                myDs = new DataSet("AdminConsoleDataSet");
               
                // Fill "ORDER" table into dataset
                myOleDbDataAdapter.TableMappings.Add("Orders", "Orders");
                OleDbCommand OrdersCmd = new OleDbCommand("SELECT * FROM Orders", myConnection);
                OrdersCmd.CommandType = CommandType.Text;
                myOleDbDataAdapter.SelectCommand = OrdersCmd;
                OleDbCommand OrdersCmd2 = new OleDbCommand("INSERT INTO Orders (Id,CustomerName) " + "VALUES (@Id, @CustomerName)" , myConnection);
                OrdersCmd2.CommandType = CommandType.Text;
                OrdersCmd2.Parameters.Add("@Id", OleDbType.Integer, 50, "Id");
                OrdersCmd2.Parameters.Add("@CustomerName", OleDbType.VarChar, 50, "CustomerName");
                myOleDbDataAdapter.InsertCommand = OrdersCmd2;
               
                // add db primary key constraints to dataset
                myOleDbDataAdapter.FillSchema(myDs, SchemaType.Source, "Orders");
                myOleDbDataAdapter.Fill(myDs);
                myDg.DataSource = myDs;
               
                // Fill "ORDERDETAILS" table into dataset
                myOleDbDataAdapter.TableMappings.Add("OrderDetails", "OrderDetails");
                OleDbCommand OrderDetailsCmd = new OleDbCommand("SELECT * FROM OrderDetails", myConnection);
                myOleDbDataAdapter.SelectCommand = OrderDetailsCmd;
                // add db primary key constraints to dataset
                myOleDbDataAdapter.FillSchema(myDs, SchemaType.Source, "OrderDetails");
                myOleDbDataAdapter.Fill(myDs);
               
                // Set order/ordersdetails relationship
                System.Data.DataRelation dr;
                System.Data.DataColumn dc1;
                System.Data.DataColumn dc2;
                dc1 = myDs.Tables["Orders"].Columns["Id"];
                dc2 = myDs.Tables["OrderDetails"].Columns["OrderId"];
                dr = new System.Data.DataRelation("OrdersOrderDetails", dc1, dc2);
                myDs.Relations.Add(dr);

                //Set Autoincrement for all the related columns
                myDs.Tables["Orders"].Columns["Id"].AutoIncrementStep = 1;
                myDs.Tables["OrderDetails"].Columns["Id"].AutoIncrementStep = 1;
                myDs.Tables["OrderDetails"].Columns["OrderId"].AutoIncrementStep = 1;
               
                myConnection.Close();
                Console.WriteLine("The connection is closed.");
            }

            private void Row_Changed(object sender, DataRowChangeEventArgs e)
            {
                myOleDbDataAdapter.Update((DataSet)myDg.DataSource);
            }       

        }
    }

     

    Friday, December 29, 2006 11:10 AM
  • Yannick,

    I'm not sure if the .Update() automatically opens/closes the connection like the .Fill() will do. (The .Fill() opens and closes a connection if it's not already open, but if it's already open, the .Fill() leaves it open). If not, try that. The only other thing I can think of is that perhaps the row needs an EndEdit() prior to the .Update().

    e.Row.EndEdit();

    Sunday, December 31, 2006 12:41 AM
  • Yannick,

    I'm not sure if the .Update() automatically opens/closes the connection like the .Fill() will do. (The .Fill() opens and closes a connection if it's not already open, but if it's already open, the .Fill() leaves it open). If not, try that. The only other thing I can think of is that perhaps the row needs an EndEdit() prior to the .Update().

    e.Row.EndEdit();

    what is e in that ...?
    Saturday, February 11, 2012 7:47 AM
  • The "e" is the DataRowChangeEventArgs from this line of code:

    private void Row_Changed(object sender, DataRowChangeEventArgs e)
    {
        myOleDbDataAdapter.Update((DataSet)myDg.DataSource);
    } 

    So, my suggestion was that it be coded like this:

    private void Row_Changed(object sender, DataRowChangeEventArgs e)
    {
        e.Row.EndEdit();
        myOleDbDataAdapter.Update((DataSet)myDg.DataSource);
    }


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, February 13, 2012 4:14 PM
  • Hello Bonnie,

    Thanks for your reply,Anyhow the Update() isn't good against the SQL Server...

    I'm currently using the SqlBulkCopy for my operations....

    Tuesday, February 14, 2012 5:02 AM
  • Ah, ok ... that's a totally different topic then ... I was just answering your question!! LOL!

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, February 19, 2012 5:30 PM