Updating Database through dataset OleDbDataAdapter [Solved]

Hi,

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

VB Code:

Private moDS As DataSet

VB Code:

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?

have you try instantiating your dataset?

VB Code:

Private moDS As New DataSet()

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

Quote:

---

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. ;)

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

because i already have one assigned in this piece

VB Code:

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

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?

Quote:

---

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...

Thanks That did it, now running smooth