dataadaptor.update problem

[Jeremy]

My app is throwing an exception regarding trying to post a null to a
required field. Examining the datarow in question, I see a valid date
value. The row's state is "added"

I'm relying on an oledbcommandbuilder to supply the missing insert or
update commands. The following shows how I'm creating my components:

Private mdaCertByID As New OleDbDataAdapter 'selects a single cert by ID.
....
mdaCertByID.SelectCommand = New OleDbCommand
With mdaCertByID.SelectCommand
.Connection = NewCnnData()
.CommandType = CommandType.Text
.CommandText = "select * from Cert where id=@certid"
.Parameters.Add("@certid", OleDbType.Integer)
End With
mcbCertByID = New OleDbCommandBuilder(mdaCertByID)

Here's where I execute the update:

Public Function updatePanTixRow() As Boolean
Dim r As DataRow
Dim n As IntegerAddHandler mdaCertByID.RowUpdated, New
OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
Try
If Not dsData.HasErrors Then
n = mdaCertByID.Update(dsData, "cert")
updatePanTixRow = True
Else
For Each r In dsData.Tables("cert").Rows
If r.HasErrors Then
MsgBox("Row " + r.Item("id") + _
" of " + dsData.Tables("cert").Rows.Count.ToString + " rows: " +
r.RowError)
End If
Next
updatePanTixRow = False
End If
'Debug.WriteLine(mcbCertByID.GetUpdateCommand.CommandText)
Catch ex As Exception
updatePanTixRow = False
Debug.WriteLine(mcbCertByID.GetInsertCommand.CommandText)
For Each r In dsData.Tables("cert").Rows
If r.HasErrors Then
MsgBox(r.RowError)
r.RowError = ""
Else
MsgBox(ex.Message)
End If
Next
End Try
End Function

 

[W.G. Ryan eMVP]

You're positive that the value is in fact there for every row? I've run
across a similar problem before and found that one of the last rows was the
culprit but i was checking the first 10 for instance which all had values.

The quick way to verify is to set a Default value for the DataColumn with a
good date (it probably won't be the real date you want, but for testing
it'll suit your needs). After you have a default value, hit update and see
if it still blows up.

If this isn't the problem,, then look at the column mapping w/ your dataset
and make sure the fields are mapped correctly, and verify the update command
that your commandbuilder is building. It' hard to tell right now without
knowing a little more... if you can narrow down the row (unless it's
happening to all of them) this would be helpful too.

 

[Jeremy]

Thanks, I really appreciate whatever thoughts you have.

Rowcount is 1, so I think I can conclusively say that all rows have a valid
value for this column. There are 2 tables in the dataset, with very
different names, neither of them the default. The error message is quite
clear on which table is the problem (I thought the pgm might somehow be
trying to update a wrong table somehow).

I'm not using the gui to hold my dataset, so the column mappings should be
computed based on the select statement, right? That is "select * where
id=@certid". I was thinking about looking at the dataset parameters
(probably the wrong term, but whatever thing the dataset uses to keep the
original value, the changed value, etc), but haven't figured out how to view
them yet.

The field in question does have a default value of date() (did I mention
this is MS Access?). Also, the field is actually the 2nd one
(left-to-right) that is required. The previous one is an int, and has a
default of 0.

Jeremy

 

[Jeremy]

Here is what VS says about the column in question. Curiously, the
fillschema did not apparently get all the answers right. Notice that
default value is {System.DBNull}, and AllowDBNull is True, contrary to
actual settings in the database (yes, I did triple check the path and
connection string). But aside from that, I don't know what this tells me.
Can anyone shed light?

? mdsdata.Tables("cert").Columns(6)
{System.Data.DataColumn}
AllowDBNull: True
AutoIncrement: False
AutoIncrementSeed: 0
AutoIncrementStep: 1
Caption: "DateofInspection"
ColumnMapping: Element
ColumnName: "DateofInspection"
Container: Nothing
DataType: {System.RuntimeType}
DefaultValue: {System.DBNull}
DesignMode: False
Expression: ""
ExtendedProperties: {System.Data.PropertyCollection}
MaxLength: -1
Namespace: ""
Ordinal: 6
Prefix: ""
ReadOnly: False
Site: Nothing
Table: {System.Data.DataTable}
Unique: False

 

[Jeremy]

Output from immediate:

?dsdata.Tables("cert").Rows(0).Item(6)
#5/10/2005# {Date}
[Date]: #5/10/2005#