[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

vb.net update dataadapter through dataset

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
jackjohnson44
Asked:
jackjohnson44
3 Solutions
 
jjaquaCommented:
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
 
SanclerCommented:
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
 
Jeff CertainCommented:
Roger...

You also need
dataAdapter.updateCommand = cb.GetupdateCommand

no?
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Davidshc76Commented:
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
 
SanclerCommented:
>>
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
 
Jeff CertainCommented:
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
 
jackjohnson44Author Commented:
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
 
Jeff CertainCommented:
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
 
SanclerCommented:
Yes, that's true, but it looks like he wants to go down the "roll your own" route proposd by jjaqua

Roger
0
 
Jeff CertainCommented:
I was hoping he'd listen to sage advice... and was just confused about what he needed to know :)
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now