Updating access table -- some fields "take", others don't

[Jeremy]

This is a variation on the last 2 unresolved questions I've posted. Having
removed the "required" attribute from a field that was causing trouble, I'm
finding that my dataAdapter update gets values into some fields, but not
others.

Inspecting the single row in my dataset, I see the correct values, but they
don't arrive at the table. Other columns, however, do. This is extremely
frustrating.

I considered the possibility that conditional case sensitivity was giving me
2 tables, but not the case.

 

[Ken Tucker [MVP]]

Hi,

Post some code

Ken
----------------------
This is a variation on the last 2 unresolved questions I've posted. Having
removed the "required" attribute from a field that was causing trouble, I'm
finding that my dataAdapter update gets values into some fields, but not
others.

Inspecting the single row in my dataset, I see the correct values, but they
don't arrive at the table. Other columns, however, do. This is extremely
frustrating.

I considered the possibility that conditional case sensitivity was giving me
2 tables, but not the case.

 

[Jeremy]

Hi,

Post some code

Ken
----------------------

Ken, here it is ..

Public Function newPanTixRow() As DataRow
mdsData.Tables("Cert").Clear()
newPanTixRow = mdsData.Tables("Cert").NewRow
mdsData.Tables("Cert").Rows.Add(newPanTixRow)
End Function

Public Sub New
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
With mdaCertByID
.MissingSchemaAction = MissingSchemaAction.AddWithKey
Try
.FillSchema(mdsData, SchemaType.Source, "Cert")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End With
mcbCertByID = New OleDbCommandBuilder(mdaCertByID)
End Sub

Public Function updatePanTixRow() As Boolean
Dim r As DataRow
Dim n As Integer 'jhg 02/25/2005
AddHandler mdaCertByID.RowUpdated, New
OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
Try
If Not dsData.HasErrors Then
mcbCertByID.RefreshSchema()
mdaCertByID.Update(mdsData.Tables("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
Catch ex As Exception
updatePanTixRow = False 'jhg 02/24/2005
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

Private Sub OnRowUpdated(ByVal sender As Object, ByVal args As
OleDbRowUpdatedEventArgs)
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY",
mdaCertByID.SelectCommand.Connection) 'mNQI.cnnData)
If args.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
args.Row("ID") = newID
End If
End Sub

 

[Ken Tucker [MVP]]

Hi,

Shouldn't you add some data to the new row before you add it to
the datatable.

Ken
-------------------

Hi,

Post some code

Ken
----------------------

Ken, here it is ..

Public Function newPanTixRow() As DataRow
mdsData.Tables("Cert").Clear()
newPanTixRow = mdsData.Tables("Cert").NewRow
mdsData.Tables("Cert").Rows.Add(newPanTixRow)
End Function

Public Sub New
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
With mdaCertByID
.MissingSchemaAction = MissingSchemaAction.AddWithKey
Try
.FillSchema(mdsData, SchemaType.Source, "Cert")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End With
mcbCertByID = New OleDbCommandBuilder(mdaCertByID)
End Sub

Public Function updatePanTixRow() As Boolean
Dim r As DataRow
Dim n As Integer 'jhg 02/25/2005
AddHandler mdaCertByID.RowUpdated, New
OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
Try
If Not dsData.HasErrors Then
mcbCertByID.RefreshSchema()
mdaCertByID.Update(mdsData.Tables("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
Catch ex As Exception
updatePanTixRow = False 'jhg 02/24/2005
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

Private Sub OnRowUpdated(ByVal sender As Object, ByVal args As
OleDbRowUpdatedEventArgs)
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY",
mdaCertByID.SelectCommand.Connection) 'mNQI.cnnData)
If args.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
args.Row("ID") = newID
End If
End Sub

 

[Jeremy]

Ken, of course, surely that goes without saying.

Do you have any ideas that could point me in a new direction to solve my
problem? I've been beating my head against the wall for days now. In fact,
I really begin to believe that oledbcommandbuilder is broken in some way. I
have to get beyond this, so tomorrow I will be making my own insert and
update commands, painful as that is with 73 columns.

Jeremy

 

[Cor Ligthert]

Jerymy,

In the code that you showed, I missed direct the fill command.

You do a fillschema and with that you can create the commands, however when
there is no row with a rowstate changed, than the update will not be done.
It is than an insert, and when there is than an already existing row, than
that new row will not be updated.

Can it be something like that? It is just a guess, trying to read your code
about 7 times.

What I do, if I have code as if you have now (It looks at knitting more and
more), and it won't go, than I start new with really deleting most of the
old stuff.

I make copy before, because important is to delete it and not to comment it
out, which gives in my even often more problems.

I hope this helps,

Cor




Cor