What is wrong with this code?

[Abashai]

I am trying to do a simple update to a dataset, and I get an error on da.update.

Public Sub UpdateCLastTestDate(ByVal strConnect As String)

Dim da As New OleDbDataAdapter()
Dim cn As New OleDbConnection(strConnect)
Dim cmd As New OleDbCommand("SELECT * FROM mcTestingReport", cn)
Dim ds As New DataSet()
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
da.SelectCommand = cmd
da.Fill(ds, "mcTestingReport")

Dim i As Integer



For i = 0 To ds.Tables("mcTestingReport").Rows.Count - 1

If Not IsDBNull(ds.Tables("mcTestingReport").Rows(i).Item("cLastTestDate")) Then
If ds.Tables("mcTestingReport").Rows(i).Item("cLastTestDate") > ds.Tables("mcTestingReport").Rows(i).Item("NextTestDate") Then
ds.Tables("mcTestingReport").Rows(i).Item("NextTestDate") = #1/1/1900#

End If
End If

Next i
da.Update(ds, "mcTestingReport")


cn.Close()

End Sub

[Sascha]

Abashai,

maybe you should specify and run an UpdateCommand to get your data into the database,

best regards

Sascha

[Abashai]

Could you be a little more specifc? Are you talking about running my own SQL update? What is the code going to look like?

[pvb]

Can't tell if you're using Oracle or Access(since you're using the OleDb classes) so I'll assume Access.

Specifying just the Select command used in the OleDbDataAdapter and then creating a new OleDbCommandBuilder will automatically create the InsertCommand, UpdateCommand, and DeleteCommands.

The error wasn't posted so i'm not sure what was wrong with your code but here is some working code based on the Biblio database(I added a Date/Time field named LastUpdated to the Titles table in the Biblio database):

Private Function msAccessTest()
    Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        "C:\Program Files\Microsoft Visual Studio\VB98\BIBLIO2002.mdb"
    Dim cnBiblio As New OleDbConnection(sConnString)
    Dim dsTitles As New DataSet()
    Dim cmdSelect As New OleDbCommand("Select * From Titles", cnBiblio)
    Dim da As New OleDbDataAdapter()
    Dim cmdBuilder As New OleDbCommandBuilder(da)
    da.SelectCommand = cmdSelect
    Dim drowTitles As DataRow
    cnBiblio.Open()
    da.Fill(dsTitles, "Titles")
    For Each drowTitles In dsTitles.Tables.Item("Titles").Rows
        drowTitles.Item("LastUpdated") = Now()
    Next
    da.Update(dsTitles, "Titles")
    cnBiblio.Close()
    MessageBox.Show("Done!")
End Function

[Abashai]

The error is this:

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

It occures when the update method is called.

Man, .Net has some long error codes.