locked
How to update a database from a DataSet RRS feed

  • Question

  • I have found a tutorial on Microsoft's site (http://support.microsoft.com/kb/301248) which I have modified slightly to add data into an Access 2000 database instead of a SQL server database. I cannot get the code to work though. It opens the database but no data is inserted. I am using the Biblio dtabase and the authors table.

    'Microsoft example of updating database

    Dim conn As New OleDb.OleDbConnection

    conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Temp\VB NET Dataentry\Data Entry With DataRow\Biblio.mdb"

    Conn.Open()

    ' Create an instance of a DataAdapter.

    Dim daAuthors As New OleDb.OleDbDataAdapter("Select * From Authors", conn)

    ' Create an instance of a DataSet, and retrieve data from the Authors table.

    Dim dsPubs As New DataSet("Pubs")

    daAuthors.FillSchema(dsPubs, SchemaType.Source, "Authors")

    daAuthors.Fill(dsPubs, "Authors")

     

    '*****************

    'BEGIN ADD CODE

    ' Create a new instance of a DataTable.

    Dim tblAuthors As DataTable

    tblAuthors = dsPubs.Tables("Authors")

    Dim drCurrent As DataRow

    ' Obtain a new DataRow object from the DataTable.

    drCurrent = tblAuthors.NewRow()

    ' Set the DataRow field values as necessary.

    drCurrent("au_id") = "16101"

    drCurrent("author") = "George"

    drCurrent("Year born") = "1980"

     'Pass that new object into the Add method of the DataTable.Rows collection.

    tblAuthors.Rows.Add(drCurrent)

    MsgBox("Add was successful.")

     

    Friday, February 2, 2007 7:15 AM

Answers

  • Hi,

    Yes, as was stated earlier, you also need an InsertCommand.

    You can also use a Command builder which will create these command objects using the Select command created from 1st statement below - Switch Sql to OleDb

    Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", nwindConn)
    Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(custDA)

    Dim custDS As DataSet = New DataSet

    nwindConn.Open()
    custDA.Fill(custDS, "Customers")

    ' Code to modify data in the DataSet here.

    ' Without the SqlCommandBuilder, this line would fail.
    custDA.Update(custDS, "Customers")
    nwindConn.Close()

    Above logic is from Microsoft help.

    If not using CommandBuilder then you would use ( or similar )

    Dim cmd As OledbCommand ' Could probably say New here and add in Connection and CommandText in same statement.

    ' Not sure on exact syntax

    cmd.CommandText = "INSERT INTO AUTHORS(au_id, author, 'Year born') VALUES('16101', 'George', '1980')

    cmd.Connection = conn

    da.InsertCommand = cmd

     Sorry,

    unfortunately also still learning so any experts out there feel free to advise.

     

     

    Monday, February 5, 2007 7:28 AM

All replies

  • Hi,

    All the code you wrote is fine. However u must know that a dataset is a disconnected data container. Adding/updating/deleting rows in a dataset does not affect database.

    To update the database add this line in the end:

    daAuthors.Update(dsPubs)

    I am not sure, but i think u must also provide the Insert/update/delete commands to the dataadapter so it can run the appropiate command on the database. The best way to have the full code is to use the Visual Studio designer to create a dataadapter and the u can see the generated code. 

     

    HTH

     

    Friday, February 2, 2007 12:06 PM
  • Adding :

    daAuthors.Update(dsPubs)

    to the end of the code returns the following error:

    Update unable to find TableMapping['Table'] or DataTable 'Table'.

     

     

    Friday, February 2, 2007 2:19 PM
  • he got it backwards.

    dsPubs.Update(daAuthors)

    Friday, February 2, 2007 5:52 PM
  • This still porduces an error message:

    Update is not a member of 'System.Data.DataSet'

    Perhaps I have not declared the dsPub dataset correctly.

     

     

     

    Saturday, February 3, 2007 1:06 AM
  • Hi, it is da.Fill(ds,"Authors"), not ds.Fill as was mentioned.

    Here is code that I tried out. Originally used a DataTable but modified to use DataSet.

    Dim cn As New SqlClient.SqlConnection()

    cn.ConnectionString = My.Settings.Golf_DataConnectionString

    Dim ds As New DataSet()

    Dim dt As New DataTable("Course")

    ds.Tables.Add(dt)

    cn.Open()

    da.Fill(ds, "Course")

    ' add new row code goes here.

    da.Update(ds, "Course")

     

    I believe that you need to add tblAuthors data table or dt in my case to your DataSet.

    then you use da.Fill(ds,"Authors"), add your new row and use da.Update(ds,"Authors")

    I think that this line adds "Authors" table to dsPubs in the Microsoft example.

    daAuthors.FillSchema(dsPubs, SchemaType.Source, "Authors")

    Sunday, February 4, 2007 9:56 AM
  • I have tried to use the above methodology to add a new row to the authors database but still get an error message.

    Update requires a valid InsertCommand when passed DataRow collection with new rows

    I am trying to convert a program I developed in VB6 to VB .NET. But unfortunately VB .NET is quite different and I am struggling. What I want do is just add and edit data in a table of an Access 2000 database that resides on my hard drive(not a server). Currently I have been using the Authors table from Biblio.msd whilst coming to grips with VB .NET. If anyone has a link or a document to a good tutorial I would greatly appreciate it. Here is the code that gave me the above error:

    *****************************************************************************************************

    Dim conn As New OleDb.OleDbConnection

    conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = _

    C:\Temp\VB NET Dataentry\Data Entry With DataRow\Biblio.mdb"

    Dim da As New OleDb.OleDbDataAdapter("Select * From Authors", conn)

    Dim ds As New DataSet()

    Dim dt As New DataTable("authors")

    Dim drCurrent As DataRow

    ds.Tables.Add(dt)

    conn.Open()

    da.Fill(ds, "authors")

    drCurrent = ds.Tables("authors").NewRow()

    ' Set the DataRow field values as necessary.

    drCurrent("au_id") = "16101"

    drCurrent("author") = "George"

    drCurrent("Year born") = "1980"

     'Pass that new object into the Add method of the DataTable.Rows collection.

    dt.Rows.Add(drCurrent)

    MsgBox("Add was successful.")

    da.Update(ds, "authors")

    conn.Close()

    End Sub

     

    Monday, February 5, 2007 4:18 AM
  • Hi,

    Yes, as was stated earlier, you also need an InsertCommand.

    You can also use a Command builder which will create these command objects using the Select command created from 1st statement below - Switch Sql to OleDb

    Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", nwindConn)
    Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(custDA)

    Dim custDS As DataSet = New DataSet

    nwindConn.Open()
    custDA.Fill(custDS, "Customers")

    ' Code to modify data in the DataSet here.

    ' Without the SqlCommandBuilder, this line would fail.
    custDA.Update(custDS, "Customers")
    nwindConn.Close()

    Above logic is from Microsoft help.

    If not using CommandBuilder then you would use ( or similar )

    Dim cmd As OledbCommand ' Could probably say New here and add in Connection and CommandText in same statement.

    ' Not sure on exact syntax

    cmd.CommandText = "INSERT INTO AUTHORS(au_id, author, 'Year born') VALUES('16101', 'George', '1980')

    cmd.Connection = conn

    da.InsertCommand = cmd

     Sorry,

    unfortunately also still learning so any experts out there feel free to advise.

     

     

    Monday, February 5, 2007 7:28 AM
  • Ok, here is the full code to include with prior code, it is not tried as I always have used Sql.

    Dim cmd As New OleDb.OleDbCommand("INSERT INTO AUTHORS( au_id, author, 'Year born')" & _

    "VALUES (?, ?, ?)", conn)

    You require a Parameter for each value in your Insert statement.

    cmd.Parameters.AddWithValue("@AuthorId", "auid")

    cmd.Parameters.AddWithValue("@Author", "author")

    cmd.Parameters.AddWithValue("@Year", "Year born")

     

    'Can also use below- will require a parameter for each ? in the Insert statement with different data types and lengths.

    cmd.Parameters.Add("Author", OleDbType.VarChar, 20, "Author")

    da.InsertCommand = cmd

     

    Monday, February 5, 2007 9:07 AM
  • Hi,

    Just had a look at the  link from Microsoft mentioned in startup thread.

    If you change anything that says Sql to OleDb, you should be able to get it to work.

    Ensure that you use the CommandBuilder.

    There are so many ways to do the same thing in this language and it takes a long time to learn.

    Signing off...

    Monday, February 5, 2007 9:25 AM