DataAdapter.Update Error

[pfildes]

I am attempting to create new records in an MS Access database using ADO.NET. I have opened a connection, created a DataAdapter and added a new row to the table in the filled dataset. When the DataAdapter is updated I get the message;

"Update requires a valid InsertCommand when passed DataRow collection with new rows."

This is my source code;

    Private Sub AddPhotoRecord(ByVal theDataItems() As String, _
                               ByVal theColumnsCollection As Collection)

        Dim myOLEDBConnection As OleDb.OleDbConnection
        Dim myOLEDBCommand As OleDb.OleDbCommand
        Dim myDataAdapter As OleDb.OleDbDataAdapter
        Dim myDataSet As New System.Data.DataSet
        Dim myDataTable As New System.Data.DataTable
        Dim myRow As System.Data.DataRow
        Dim myColumn As System.Data.DataColumn
        Dim myConnectionString As String
        Dim Loop1 As Integer

        Try
            myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                 "Data Source=" & gProject.ProjectDatabase.Trim & ";" & _
                                 "User Id=admin;Password=;"

            'Instatiate the database connection
            myOLEDBConnection = New OleDb.OleDbConnection(myConnectionString)

            'Create the SQL command
            myOLEDBCommand = New OleDb.OleDbCommand("Select * From PHOTOS", _
                                                    myOLEDBConnection)

            'Open an OLEDB adapter based on the SQL command and use this to
            'create a dataset
            myOLEDBConnection.Open()
            myDataAdapter = New OleDb.OleDbDataAdapter(myOLEDBCommand)
            myDataAdapter.Fill(myDataSet)

            'Add the new record
            myDataTable = myDataSet.Tables(0)

                    'Add a new row to this table
            myRow = myDataTable.NewRow()

                    'Populate the fields in the row
            For Each myColumn In myDataTable.Columns
                For Loop1 = 1 To theColumnsCollection.Count
                    If (myColumn.ColumnName.Trim = theColumnsCollection(Loop1)) Then
                        myRow.Item(myColumn.Ordinal) = theDataItems(Loop1 - 1)
                        Exit For
                    End If
                Next
            Next

            'Add the new row to the DataTable rows collection
            myDataTable.Rows.Add(myRow)

            'Now update the dataset
            myDataAdapter.Update(myDataSet, myDataTable.TableName)

            'Close the connection and 
            myOLEDBConnection.Close()
            myOLEDBConnection.Dispose()
            myDataSet.Dispose()
            myDataAdapter.Dispose()

        Catch ex As Exception
            MsgBox("The following error occured whilst trying to insert new photograph data" & Chr(13) & _
            ex.Message, MsgBoxStyle.Exclamation)
        Finally
            If (Not myOLEDBConnection Is Nothing) Then
                If (myOLEDBConnection.State = _
                    ConnectionState.Open) Then myOLEDBConnection.Close()
                myOLEDBConnection.Dispose()
            End If
        End Try
    End Sub

Can anybody explain why this is happening?.

 

[ca8spo]

You need to specify the myDataAdapter.UpdateCommand

 

[ca8msm]

It looks as though your DataAdaptor hasn't got an InsertCommand (or if it does it is invalid). Has it got one?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ca8spo]

Actually its the .insertCommand for what you are doing.

 

[pfildes]

My current source code doesn't list a ".InsertCommand" and I didn't think I needed one, considering I was not using SQL to add new data.

I'm a little confused to be honest with how to add new rows to an Access database table using ADO.NET. I'd prefer to add a new record using the methods listed above in my source code as it ignores the type of fields (Text/Number, etc). If I use a SQL INSERT command it requires the inserted values to match the fields defined in the database.

What is the most ideal method of trying to add new records?

 

[chrissie1]

if you want the data to be written to the access database then you will need the insertcommand. Look at the commandbuilder it will create the sqlcommands for you. And yes it will need some work. But hey that's what we are paid for.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]