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;
Can anybody explain why this is happening?.
"Update requires a valid InsertCommand when passed DataRow collection with new rows."
This is my source code;
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?.