|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Not triggering a change to rowstate - Maybe!!!!!I'm facing some issues with the update/insert process. I have a datatable (dt), use oledbcommandbuilder, datarow (dr), OledbAccess connection (cn), dataAdapter (adapter). here's the logic I plan on using. (Ah, if it would only work!!!) ------------------------------------------------------------------------------------ Open a table in Access. read a transaction from flat file until eof Build a query ("Select * from purord where ponum = 'flat file ponumber' ") Build an Insert Query("insert into Access table ......") adapter.fill(dt) if dt.Rows.Count = 0 ' test if record found Dim dr As DataRow = dt.NewRow 'update dr with key fields only dr("PODate") = CDate(trnDate) dt.Rows.Add(dr) end if Try adapter.Update(dt) Catch ex As Exception 'An exception occurred MsgBox(ex.tostring()) End Try '----- Update with non-Key fields ------- dt.Rows(0).Item("POTrnAmt") = CDbl(trnAmt) Try adapter.Update(dt) Catch ex As Exception 'An exception occurred MsgBox(ex.tostring()) End Try Go read another transaction above. ----------------------------------------------------------------------------------------------- Now the problem I have is that if no record is found, the non-Key fields are never updated. It does create a new row in the datatable containing the key fields only. It doesn't catch any errors on the adapter.Update(). It performs the "dt.Rows(0).Item("POTrnAmt") = CDbl(trnAmt)" statements without errors and It doen't catch any errors on the second adapter.Update() command, but the non-Key fields don't update. My guess is that the update to non-key fields is not triggering a change to rowstate or I'm missing a refresh command to rowstate or just missing a command or two. Can anyone help me out? Hexman P.S. If I process a transaction for a records that IS on file, the update of non-Key fields works fine. Its only when a new record is to be added that the non-Key updates don't take. Access 2003, VB.net 2005, Hexman,
I would in your case look again to your logic. I find it strange that you selects rows depending on ponum. And I see nowhere in your code again ponum back. Cor Show quote "Hexman" <Hex***@Binary.com> schreef in bericht news:2ntv12lv053jgu519ls5nt82c66bs3h9bi@4ax.com... > Hello all, > > I'm facing some issues with the update/insert process. > > I have a datatable (dt), use oledbcommandbuilder, datarow (dr), > OledbAccess connection (cn), dataAdapter (adapter). here's the logic > I plan on using. (Ah, if it would only work!!!) > > ------------------------------------------------------------------------------------ > Open a table in Access. > read a transaction from flat file until eof > > Build a query ("Select * from purord where ponum = 'flat file > ponumber' ") > Build an Insert Query("insert into Access table ......") > adapter.fill(dt) > if dt.Rows.Count = 0 ' test if record found > Dim dr As DataRow = dt.NewRow > 'update dr with key fields only > dr("PODate") = CDate(trnDate) > dt.Rows.Add(dr) > end if > > Try > adapter.Update(dt) > Catch ex As Exception > 'An exception occurred > MsgBox(ex.tostring()) > End Try > > '----- Update with non-Key fields ------- > dt.Rows(0).Item("POTrnAmt") = CDbl(trnAmt) > > Try > adapter.Update(dt) > Catch ex As Exception > 'An exception occurred > MsgBox(ex.tostring()) > End Try > > Go read another transaction above. > ----------------------------------------------------------------------------------------------- > > Now the problem I have is that if no record is found, the non-Key > fields are never updated. It does create a new row in the datatable > containing the key fields only. It doesn't catch any errors on the > adapter.Update(). It performs the "dt.Rows(0).Item("POTrnAmt") = > CDbl(trnAmt)" statements without errors and It doen't catch any errors > on the second adapter.Update() command, but the non-Key fields don't > update. > > My guess is that the update to non-key fields is not triggering a > change to rowstate or I'm missing a refresh command to rowstate or > just missing a command or two. > > Can anyone help me out? > > Hexman > > P.S. If I process a transaction for a records that IS on file, the > update of non-Key fields works fine. Its only when a new record is to > be added that the non-Key updates don't take. > > Access 2003, VB.net 2005, ponum is one of the key fields, which does get updated during an add,
not modified during an update. Am I leaving out a command to refresh or re-query? On Wed, 22 Mar 2006 10:42:09 +0100, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote: Show quote >Hexman, dt("PONum") = cint(ponumber)> >I would in your case look again to your logic. I find it strange that you >selects rows depending on ponum. And I see nowhere in your code again ponum >back. > >Cor > >"Hexman" <Hex***@Binary.com> schreef in bericht >news:2ntv12lv053jgu519ls5nt82c66bs3h9bi@4ax.com... >> Hello all, >> >> I'm facing some issues with the update/insert process. >> >> I have a datatable (dt), use oledbcommandbuilder, datarow (dr), >> OledbAccess connection (cn), dataAdapter (adapter). here's the logic >> I plan on using. (Ah, if it would only work!!!) >> >> ------------------------------------------------------------------------------------ >> Open a table in Access. >> read a transaction from flat file until eof >> >> Build a query ("Select * from purord where ponum = 'flat file >> ponumber' ") >> Build an Insert Query("insert into Access table ......") >> adapter.fill(dt) >> if dt.Rows.Count = 0 ' test if record found >> Dim dr As DataRow = dt.NewRow >> 'update dr with key fields only Show quote >> dr("PODate") = CDate(trnDate) >> dt.Rows.Add(dr) >> end if >> >> Try >> adapter.Update(dt) >> Catch ex As Exception >> 'An exception occurred >> MsgBox(ex.tostring()) >> End Try >> >> '----- Update with non-Key fields ------- >> dt.Rows(0).Item("POTrnAmt") = CDbl(trnAmt) >> >> Try >> adapter.Update(dt) >> Catch ex As Exception >> 'An exception occurred >> MsgBox(ex.tostring()) >> End Try >> >> Go read another transaction above. >> ----------------------------------------------------------------------------------------------- >> >> Now the problem I have is that if no record is found, the non-Key >> fields are never updated. It does create a new row in the datatable >> containing the key fields only. It doesn't catch any errors on the >> adapter.Update(). It performs the "dt.Rows(0).Item("POTrnAmt") = >> CDbl(trnAmt)" statements without errors and It doen't catch any errors >> on the second adapter.Update() command, but the non-Key fields don't >> update. >> >> My guess is that the update to non-key fields is not triggering a >> change to rowstate or I'm missing a refresh command to rowstate or >> just missing a command or two. >> >> Can anyone help me out? >> >> Hexman >> >> P.S. If I process a transaction for a records that IS on file, the >> update of non-Key fields works fine. Its only when a new record is to >> be added that the non-Key updates don't take. >> >> Access 2003, VB.net 2005, > Hexman,
This assumes that your key is an autokey, is that true? Otherwise you have to set that before you add the row Be aware that with access and autokey you have normally forever to clean the dataset and do a complete fill again after updating that. (In SQLClient is the generated key automaticly set). I hope this helps,. Cor Show quote "Hexman" <Hex***@Binary.com> schreef in bericht news:q5k1221ps602l71n1rtosgtois4jqgo7e7@4ax.com... > ponum is one of the key fields, which does get updated during an add, > not modified during an update. > > Am I leaving out a command to refresh or re-query? > > > > On Wed, 22 Mar 2006 10:42:09 +0100, "Cor Ligthert [MVP]" > <notmyfirstn***@planet.nl> wrote: > >>Hexman, >> >>I would in your case look again to your logic. I find it strange that you >>selects rows depending on ponum. And I see nowhere in your code again >>ponum >>back. >> >>Cor >> >>"Hexman" <Hex***@Binary.com> schreef in bericht >>news:2ntv12lv053jgu519ls5nt82c66bs3h9bi@4ax.com... >>> Hello all, >>> >>> I'm facing some issues with the update/insert process. >>> >>> I have a datatable (dt), use oledbcommandbuilder, datarow (dr), >>> OledbAccess connection (cn), dataAdapter (adapter). here's the logic >>> I plan on using. (Ah, if it would only work!!!) >>> >>> ------------------------------------------------------------------------------------ >>> Open a table in Access. >>> read a transaction from flat file until eof >>> >>> Build a query ("Select * from purord where ponum = 'flat file >>> ponumber' ") >>> Build an Insert Query("insert into Access table ......") >>> adapter.fill(dt) >>> if dt.Rows.Count = 0 ' test if record found >>> Dim dr As DataRow = dt.NewRow >>> 'update dr with key fields only > dt("PONum") = cint(ponumber) >>> dr("PODate") = CDate(trnDate) >>> dt.Rows.Add(dr) >>> end if >>> >>> Try >>> adapter.Update(dt) >>> Catch ex As Exception >>> 'An exception occurred >>> MsgBox(ex.tostring()) >>> End Try >>> >>> '----- Update with non-Key fields ------- >>> dt.Rows(0).Item("POTrnAmt") = CDbl(trnAmt) >>> >>> Try >>> adapter.Update(dt) >>> Catch ex As Exception >>> 'An exception occurred >>> MsgBox(ex.tostring()) >>> End Try >>> >>> Go read another transaction above. >>> ----------------------------------------------------------------------------------------------- >>> >>> Now the problem I have is that if no record is found, the non-Key >>> fields are never updated. It does create a new row in the datatable >>> containing the key fields only. It doesn't catch any errors on the >>> adapter.Update(). It performs the "dt.Rows(0).Item("POTrnAmt") = >>> CDbl(trnAmt)" statements without errors and It doen't catch any errors >>> on the second adapter.Update() command, but the non-Key fields don't >>> update. >>> >>> My guess is that the update to non-key fields is not triggering a >>> change to rowstate or I'm missing a refresh command to rowstate or >>> just missing a command or two. >>> >>> Can anyone help me out? >>> >>> Hexman >>> >>> P.S. If I process a transaction for a records that IS on file, the >>> update of non-Key fields works fine. Its only when a new record is to >>> be added that the non-Key updates don't take. >>> >>> Access 2003, VB.net 2005, >> On Wed, 22 Mar 2006 18:05:38 +0100, "Cor Ligthert [MVP]"
<notmyfirstn***@planet.nl> wrote: >Hexman, No, it is not an autokey. I haven't put all the code in this thread,> >This assumes that your key is an autokey, is that true? thinking it may be a little too much to stomach. > See below where I've added actual code.>Otherwise you have to set that before you add the row > Sorry if I'm being thick with this. Are you saying that after I add a>Be aware that with access and autokey you have normally forever to clean the >dataset and do a complete fill again after updating that. (In SQLClient is >the generated key automaticly set). record to the datatable (which the program already does). That I have to update the datatable then do another select and datatable fill before I issue the update statements? Show quote > QryIns = "INSERT INTO Purord (PODate, PONum) " & _>I hope this helps,. > >Cor >"Hexman" <Hex***@Binary.com> schreef in bericht >news:q5k1221ps602l71n1rtosgtois4jqgo7e7@4ax.com... >> ponum is one of the key fields, which does get updated during an add, >> not modified during an update. >> >> Am I leaving out a command to refresh or re-query? >> >> >> >> On Wed, 22 Mar 2006 10:42:09 +0100, "Cor Ligthert [MVP]" >> <notmyfirstn***@planet.nl> wrote: >> >>>Hexman, >>> >>>I would in your case look again to your logic. I find it strange that you >>>selects rows depending on ponum. And I see nowhere in your code again >>>ponum >>>back. >>> >>>Cor >>> >>>"Hexman" <Hex***@Binary.com> schreef in bericht >>>news:2ntv12lv053jgu519ls5nt82c66bs3h9bi@4ax.com... >>>> Hello all, >>>> >>>> I'm facing some issues with the update/insert process. >>>> >>>> I have a datatable (dt), use oledbcommandbuilder, datarow (dr), >>>> OledbAccess connection (cn), dataAdapter (adapter). here's the logic >>>> I plan on using. (Ah, if it would only work!!!) >>>> >>>> ------------------------------------------------------------------------------------ >>>> Open a table in Access. >>>> read a transaction from flat file until eof >>>> >>>> Build a query ("Select * from purord where ponum = 'flat file >>>> ponumber' ") >>>> Build an Insert Query("insert into Access table ......") "VALUES (@PODate, @PONum)" adapter.InsertCommand = New OleDbCommand(QryIns, cn) adapter.InsertCommand.Parameters.Add("@PODate", OleDbType.Date, 6, "PODate") adapter.InsertCommand.Parameters.Add("@PONum", OleDbType.VarChar, 3, "PONum") Show quote >>>> adapter.fill(dt) >>>> if dt.Rows.Count = 0 ' test if record found >>>> Dim dr As DataRow = dt.NewRow >>>> 'update dr with key fields only >> dt("PONum") = cint(ponumber) >>>> dr("PODate") = CDate(trnDate) >>>> dt.Rows.Add(dr) >>>> end if >>>> >>>> Try >>>> adapter.Update(dt) >>>> Catch ex As Exception >>>> 'An exception occurred >>>> MsgBox(ex.tostring()) >>>> End Try >>>> >>>> '----- Update with non-Key fields ------- >>>> dt.Rows(0).Item("POTrnAmt") = CDbl(trnAmt) >>>> >>>> Try >>>> adapter.Update(dt) >>>> Catch ex As Exception >>>> 'An exception occurred >>>> MsgBox(ex.tostring()) >>>> End Try >>>> >>>> Go read another transaction above. >>>> ----------------------------------------------------------------------------------------------- >>>> >>>> Now the problem I have is that if no record is found, the non-Key >>>> fields are never updated. It does create a new row in the datatable >>>> containing the key fields only. It doesn't catch any errors on the >>>> adapter.Update(). It performs the "dt.Rows(0).Item("POTrnAmt") = >>>> CDbl(trnAmt)" statements without errors and It doen't catch any errors >>>> on the second adapter.Update() command, but the non-Key fields don't >>>> update. >>>> >>>> My guess is that the update to non-key fields is not triggering a >>>> change to rowstate or I'm missing a refresh command to rowstate or >>>> just missing a command or two. >>>> >>>> Can anyone help me out? >>>> >>>> Hexman >>>> >>>> P.S. If I process a transaction for a records that IS on file, the >>>> update of non-Key fields works fine. Its only when a new record is to >>>> be added that the non-Key updates don't take. >>>> >>>> Access 2003, VB.net 2005, >>> > Hexman,
If it is not an autokey, than I am supprised that it does not give an error when you add the row to the datatable. A datatable with a key will normally do that. dim dr as datarow = dt.newrow the key has to be set before the datarow is attached to the table dt.rows.add(dr) Cor Show quote "Hexman" <Hex***@Binary.com> schreef in bericht news:3sd422lnhri96agudo9a8lgjqafkm3maq9@4ax.com... > On Wed, 22 Mar 2006 18:05:38 +0100, "Cor Ligthert [MVP]" > <notmyfirstn***@planet.nl> wrote: > >>Hexman, >> >>This assumes that your key is an autokey, is that true? > > No, it is not an autokey. I haven't put all the code in this thread, > thinking it may be a little too much to stomach. > >> >>Otherwise you have to set that before you add the row > > See below where I've added actual code. > >> >>Be aware that with access and autokey you have normally forever to clean >>the >>dataset and do a complete fill again after updating that. (In SQLClient is >>the generated key automaticly set). > > Sorry if I'm being thick with this. Are you saying that after I add a > record to the datatable (which the program already does). That I have > to update the datatable then do another select and datatable fill > before I issue the update statements? >> >>I hope this helps,. >> >>Cor >>"Hexman" <Hex***@Binary.com> schreef in bericht >>news:q5k1221ps602l71n1rtosgtois4jqgo7e7@4ax.com... >>> ponum is one of the key fields, which does get updated during an add, >>> not modified during an update. >>> >>> Am I leaving out a command to refresh or re-query? >>> >>> >>> >>> On Wed, 22 Mar 2006 10:42:09 +0100, "Cor Ligthert [MVP]" >>> <notmyfirstn***@planet.nl> wrote: >>> >>>>Hexman, >>>> >>>>I would in your case look again to your logic. I find it strange that >>>>you >>>>selects rows depending on ponum. And I see nowhere in your code again >>>>ponum >>>>back. >>>> >>>>Cor >>>> >>>>"Hexman" <Hex***@Binary.com> schreef in bericht >>>>news:2ntv12lv053jgu519ls5nt82c66bs3h9bi@4ax.com... >>>>> Hello all, >>>>> >>>>> I'm facing some issues with the update/insert process. >>>>> >>>>> I have a datatable (dt), use oledbcommandbuilder, datarow (dr), >>>>> OledbAccess connection (cn), dataAdapter (adapter). here's the logic >>>>> I plan on using. (Ah, if it would only work!!!) >>>>> >>>>> ------------------------------------------------------------------------------------ >>>>> Open a table in Access. >>>>> read a transaction from flat file until eof >>>>> >>>>> Build a query ("Select * from purord where ponum = 'flat file >>>>> ponumber' ") >>>>> Build an Insert Query("insert into Access table ......") > > QryIns = "INSERT INTO Purord (PODate, PONum) " & _ > "VALUES (@PODate, @PONum)" > adapter.InsertCommand = New OleDbCommand(QryIns, cn) > > adapter.InsertCommand.Parameters.Add("@PODate", > OleDbType.Date, 6, "PODate") > adapter.InsertCommand.Parameters.Add("@PONum", > OleDbType.VarChar, 3, "PONum") > >>>>> adapter.fill(dt) >>>>> if dt.Rows.Count = 0 ' test if record found >>>>> Dim dr As DataRow = dt.NewRow >>>>> 'update dr with key fields only >>> dt("PONum") = cint(ponumber) >>>>> dr("PODate") = CDate(trnDate) >>>>> dt.Rows.Add(dr) >>>>> end if >>>>> >>>>> Try >>>>> adapter.Update(dt) >>>>> Catch ex As Exception >>>>> 'An exception occurred >>>>> MsgBox(ex.tostring()) >>>>> End Try >>>>> >>>>> '----- Update with non-Key fields ------- >>>>> dt.Rows(0).Item("POTrnAmt") = CDbl(trnAmt) >>>>> >>>>> Try >>>>> adapter.Update(dt) >>>>> Catch ex As Exception >>>>> 'An exception occurred >>>>> MsgBox(ex.tostring()) >>>>> End Try >>>>> >>>>> Go read another transaction above. >>>>> ----------------------------------------------------------------------------------------------- >>>>> >>>>> Now the problem I have is that if no record is found, the non-Key >>>>> fields are never updated. It does create a new row in the datatable >>>>> containing the key fields only. It doesn't catch any errors on the >>>>> adapter.Update(). It performs the "dt.Rows(0).Item("POTrnAmt") = >>>>> CDbl(trnAmt)" statements without errors and It doen't catch any errors >>>>> on the second adapter.Update() command, but the non-Key fields don't >>>>> update. >>>>> >>>>> My guess is that the update to non-key fields is not triggering a >>>>> change to rowstate or I'm missing a refresh command to rowstate or >>>>> just missing a command or two. >>>>> >>>>> Can anyone help me out? >>>>> >>>>> Hexman >>>>> >>>>> P.S. If I process a transaction for a records that IS on file, the >>>>> update of non-Key fields works fine. Its only when a new record is to >>>>> be added that the non-Key updates don't take. >>>>> >>>>> Access 2003, VB.net 2005, >>>> >>  |
|||||||||||||||||||||||
Other interesting topics