Updating Database through dataset OleDbDataAdapter [Solved]

[digita]

Hi,

I have a problem with updating my database with this code.

Private moDS As DataSet

Private Sub DataUpdate()
        Dim oAdapter As OleDbDataAdapter
        Dim oBuild As OleDbCommandBuilder
        Dim oDR As DataRow
        Dim strSQL As String
        Dim strID As String
        Dim strConn As String
 
        ' Get Primary Key From List Box
        strID = CType(lstLeden.SelectedItem, PDSAListItemNumeric).ID.ToString()
 
        ' Find Row To Update
        oDR = moDS.Tables("Gegevens").Rows.Find(CInt(strID))
 
        ' Begin the editing process
        oDR.BeginEdit()
 
        ' Load new data into row
        oDR("Voorletters") = txtVoorletters.Text
        oDR("Tussenvoegsel") = txtTussenvoegsel.Text
        oDR("Achternaam") = txtAchternaam.Text
        oDR("Aanspreeknaam") = txtAanspreeknaam.Text
        oDR("Geslacht") = txtGeslacht.Text
        oDR("Adres") = txtAdres.Text
        oDR("Postcode") = txtPostcode.Text
 
        ' End the editing process
        oDR.EndEdit()
 
        Try
            ' Get Connection String
            strConn = ConnectionString()
            ' Build SQL String
            strSQL = "SELECT *, Achternaam + ', ' + Voorletters AS FullName FROM Gegevens"
            ' Create New DataAdapter
            oAdapter = New OleDbDataAdapter(strSQL, strConn)
            ' Create CommandBuild from Adapter
            ' This will build INSERT, UPDATE and DELETE SQL
            oBuild = New OleDbCommandBuilder(oAdapter)
 
 
            ' Get Update Command Object
            oAdapter.UpdateCommand = oBuild.GetUpdateCommand()
 
            ' Submit UPDATE through Adapter
            oAdapter.Update(moDS, "Gegevens")
            ' Tell DataSet changes to data source are complete
            moDS.AcceptChanges()
 
            ' Reload the list box
            ListLoad()
 
        Catch oException As Exception
            MessageBox.Show(oException.Message)
 
        End Try
    End Sub

When i run this piece of code it says that "Automatically Generate an SQL Statement is not supported"

Why is that, and what can i do about it?

[mar_zim]

have you try instantiating your dataset?

Private moDS As New DataSet()

[digita]

yes, i did that but that didn't work,

I think that it is something that the selectcommand doesn't provide information about unique columns, or something like that

[mar_zim]

Originally posted by digita
yes, i did that but that didn't work,

I think that it is something that the selectcommand doesn't provide information about unique columns, or something like that

yah...that's it..

you should provide primary key to update your Gegevens table.

[digita]

where should i do that? i mean where in the code?

because i already have one assigned in this piece

Private Sub DataSetCreate()
        Dim oAdapter As OleDbDataAdapter
        Dim strSQL As String
        Dim strConn As String
 
        ' Get Connection String
        strConn = ConnectionString()
 
        ' Build SQL String
        strSQL = "SELECT *, Achternaam + ', ' + Voorletters AS FullName FROM Gegevens"
 
 
        moDS = New DataSet
        Try
            ' Create New Data Adapter
            oAdapter = New OleDbDataAdapter(strSQL, strConn)
            ' Fill DataSet From Adapter and give it a name
            oAdapter.Fill(moDS, "Gegevens")
            ' Create a Primary Key
            With moDS.Tables("Gegevens")
                .PrimaryKey = New DataColumn() _
                 {.Columns("Id")}
            End With
 
        Catch oExcept As Exception
            MessageBox.Show(oExcept.Message)
 
        End Try
    End Sub

And this code is executed on form load, so i guess tha there already is an primary key

[digita]

the actual errorcode is:

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

What can I to make that SelectCommand return key column information?

[mar_zim]

Originally posted by digita
the actual errorcode is:

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

What can I to make that SelectCommand return key column information?

edit your table in your SQL database. put the yellow key on your column you want to be unique. i.e. your id...

[digita]

Thanks That did it, now running smooth