Solved

vb.net update dataadapter through dataset

Posted on 2006-07-19
10
668 Views
Last Modified: 2012-08-13
I am trying to select a blank dataset add some rows, then update it.
I don't know how.
I am getting an error in the updateDataSet function.
"Update requires a valid InsertCommand when passed DataRow collection with new rows."

dataAdapter and connectionString are global

'this fills the dataset
        Dim mySteps As DataSet = getDataSet("SELECT * FROM Steps WHERE 1=0")
        Dim i%
        Dim dr As DataRow = mySteps.Tables(0).NewRow
        For i = 1 To 10
            dr = mySteps.Tables(0).NewRow
            tmpStep = testToSave.Item(CStr(i))
            dr("stepNumber") = i
            mySteps.Tables(0).Rows.InsertAt(dr, i)
        Next

'this should update the dataset, but gets an error
        updateDataSet(mySteps)


    Public Function updateDataSet(ByVal dataSetToUpdate As DataSet) As Boolean
        dataAdapter.Update(dataSetToUpdate)
    End Function

    Public Function getDataSet(ByVal selectString As String) As DataSet
        Try
            Dim myData As New DataSet()
            Dim myConnection As New OleDb.OleDbConnection(connectionString)

            myConnection.Open()

            dataAdapter.SelectCommand = New OleDbCommand(selectString, myConnection)

            dataAdapter.Fill(myData)

            getDataSet = myData

        Catch ex As Exception

            MsgBox("Database Error")
            getDataSet = Nothing

        End Try
    End Function
0
Comment
Question by:jackjohnson44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 5

Expert Comment

by:jjaqua
ID: 17142169
It's been a while because I'm using ADO.Net 2.0. Does your DataAdapter have a valid UpdateCommand?

Dim yourDataAdapter As New SqlDataAdapter()
yourDataAdapter.UpdateCommand = "<SQLto update your rows>"
0
 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 100 total points
ID: 17142310
jjaqua's code should work but if you don't want to "roll your own" you could use a commandbuilder.  Try putting this

            Dim cb As New OleDbCommandBuilder(dataAdapter)

immediately after this

            dataAdapter.SelectCommand = New OleDbCommand(selectString, myConnection)

And, as an aside, you should get rid of this line

            myConnection.Open()

You don't need explicitly to open the connection when you use a dataadapter, it opens and closes it automatically when it needs it.  More importantly, perhaps, it leaves it as it finds it.  And here we have a connection which is opened and never closed.

Roger
0
 
LVL 24

Assisted Solution

by:Jeff Certain
Jeff Certain earned 100 total points
ID: 17142768
Roger...

You also need
dataAdapter.updateCommand = cb.GetupdateCommand

no?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Accepted Solution

by:
Davidshc76 earned 300 total points
ID: 17142868
You need to make command builder for insert, update or delete ...

Please try it .............

Public Function getDataSet(ByVal selectString As String) As DataSet
        Try
            Dim myData As New DataSet()
            Dim myConnection As New OleDb.OleDbConnection(connectionString)

            myConnection.Open()

            dataAdapter.SelectCommand = New OleDbCommand(selectString, myConnection)

            Dim CB As OleDbCommandBuilder
            CB = New OleDbCommandBuilder(dataAdapter)
            dataAdapter.InsertCommand = CB.GetInsertCommand()
            dataAdapter.UpdateCommand = CB.GetUpdateCommand()
            dataAdapter.DeleteCommand = CB.GetDeleteCommand()

            dataAdapter.Fill(myData)

            getDataSet = myData

        Catch ex As Exception

            MsgBox("Database Error")
            getDataSet = Nothing

        End Try
End Function



0
 
LVL 34

Expert Comment

by:Sancler
ID: 17144362
>>
You also need
dataAdapter.updateCommand = cb.GetupdateCommand
<<

No.  You would need it if your code wanted to do something specific to (e.g. alter a name by putting [] round it) or explicitly use (e.g. add specific values to a parameter) the command autogenerated by the command builder.  But if you are going straight from the use of command builder to the use of the dataadapter.Update, you don't.  Or that's both my understanding and how I'm seeing it actually happen.

Roger
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17145321
Cool. I've always explicitly assigned the update command from the builder to the adapter, and it has worked. Guess I could have saved myself some typing.
0
 

Author Comment

by:jackjohnson44
ID: 17145848
thanks, what does this mean?
can you give an example?
would it be like "update mytable set x=5, y='some string' where id=5"
How would that work with an update command since I don't know what the values are?


yourDataAdapter.UpdateCommand = "<SQLto update your rows>"
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17146369
The dataAdapter takes care of all that for you. The datatable has additional data associated with each row that indicates the row state (original, removed, etc).

When you call the Update method of the dataAdapter, it handles he required updates to make your database match your datatable (at least in an ideal world with no concurrency issues)
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17146445
Yes, that's true, but it looks like he wants to go down the "roll your own" route proposd by jjaqua

Roger
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17146479
I was hoping he'd listen to sage advice... and was just confused about what he needed to know :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question