jackjohnson44
asked on
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.Ins
Next
'this should update the dataset, but gets an error
updateDataSet(mySteps)
Public Function updateDataSet(ByVal dataSetToUpdate As DataSet) As Boolean
dataAdapter.Update(dataSet
End Function
Public Function getDataSet(ByVal selectString As String) As DataSet
Try
Dim myData As New DataSet()
Dim myConnection As New OleDb.OleDbConnection(conn
myConnection.Open()
dataAdapter.SelectCommand = New OleDbCommand(selectString,
dataAdapter.Fill(myData)
getDataSet = myData
Catch ex As Exception
MsgBox("Database Error")
getDataSet = Nothing
End Try
End Function
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.Ins
Next
'this should update the dataset, but gets an error
updateDataSet(mySteps)
Public Function updateDataSet(ByVal dataSetToUpdate As DataSet) As Boolean
dataAdapter.Update(dataSet
End Function
Public Function getDataSet(ByVal selectString As String) As DataSet
Try
Dim myData As New DataSet()
Dim myConnection As New OleDb.OleDbConnection(conn
myConnection.Open()
dataAdapter.SelectCommand = New OleDbCommand(selectString,
dataAdapter.Fill(myData)
getDataSet = myData
Catch ex As Exception
MsgBox("Database Error")
getDataSet = Nothing
End Try
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>
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
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
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.
ASKER
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.UpdateComm
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.UpdateComm
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)
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)
Yes, that's true, but it looks like he wants to go down the "roll your own" route proposd by jjaqua
Roger
Roger
I was hoping he'd listen to sage advice... and was just confused about what he needed to know :)
Dim yourDataAdapter As New SqlDataAdapter()
yourDataAdapter.UpdateComm