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

14 May 2005 6:40 PM

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.

14 May 2005 10:15 PM

Ken Tucker [MVP]

Hi,

Post some code

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

"Jeremy" <jer***@ninprodata.com> wrote in message
news:OBPj4RLWFHA.2572@TK2MSFTNGP14.phx.gbl...

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.

15 May 2005 12:42 AM

Jeremy

"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%23mcQrJNWFHA.3864@TK2MSFTNGP10.phx.gbl...
> 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

Show quoteHide quote

"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%23mcQrJNWFHA.3864@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Post some code
>
> Ken
> ----------------------

16 May 2005 1:21 AM

Ken Tucker [MVP]

Hi,

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

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

"Jeremy" <jer***@ninprodata.com> wrote in message
news:%23KMOHcOWFHA.132@TK2MSFTNGP14.phx.gbl...
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%23mcQrJNWFHA.3864@TK2MSFTNGP10.phx.gbl...
> 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

Show quoteHide quote

"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%23mcQrJNWFHA.3864@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Post some code
>
> Ken
> ----------------------

16 May 2005 1:45 AM

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

Show quoteHide quote

"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:O2p7pWbWFHA.3840@tk2msftngp13.phx.gbl...
> Hi,
>
>            Shouldn't you add some data to the new row before you add it to
> the datatable.
>
> Ken
> -------------------
> "Jeremy" <jer***@ninprodata.com> wrote in message
> news:%23KMOHcOWFHA.132@TK2MSFTNGP14.phx.gbl...
> "Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
> news:%23mcQrJNWFHA.3864@TK2MSFTNGP10.phx.gbl...
>> 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]" <vb***@bellsouth.net> wrote in message
> news:%23mcQrJNWFHA.3864@TK2MSFTNGP10.phx.gbl...
>> Hi,
>>
>>        Post some code
>>
>> Ken
>> ----------------------
>
>
>

16 May 2005 6:26 AM

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