Seemingly random Concurrency violations updating Access database

5 Apr 2006 3:36 AM

mfreeman

The minimal code (VB.NET 2003) needed to show this problem is shown
below. All I do is loop through the records in the table and update
them without making any changes. Out of 600 records, about 40 of them
throw Concurrency violation errors in the update, and my GetAllColErrs
function provides no output. In comparing the rows that throw errors
with those that do not, I could find no pattern.

It is obviously a bogus error, as the database is an Access 2003 MDB
file on my PC and there is no concurrent access taking place. After
doing much research as to possible causes, I changed all decimal
columns in the database and XSD file (also provided below) to doubles.
No difference.

Dim FundsDS As DataSets.FundsDataset
Dim FundRow As DataSets.FundsDataset.FundsRow
Dim myCommand As OleDbDataAdapter

FundsDS = New DataSets.FundsDataset
myConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strFileName & ";Persist Security Info=False")
myConnection.Open()

Try
   myCommand = New OleDbDataAdapter("SELECT * FROM Funds ORDER BY
TICKER", myConnection)
   myCommand.FillSchema(FundsDS, SchemaType.Source, "Funds")
   myCommand.Fill(FundsDS, "Funds")
   Dim myBuilder As OleDbCommandBuilder = New
OleDbCommandBuilder(myCommand)
   myBuilder.QuotePrefix = "["
   myBuilder.QuoteSuffix = "]"
   OpenDB = True
Catch e As StrongTypingException
   ErrorHandler(e.Source, e.Message)
Catch e As Exception
   ErrorHandler(e.Source, e.Message)
End Try

For Each FundRow In FundsDS.Funds
      'Code to do something useful would go here
      Try
         myCommand.Update(FundsDS.Tables(0))
         FundRow.AcceptChanges()
      Catch e As DBConcurrencyException
         ErrorHandler(e.Source, e.Message & GetAllColErrs(FundRow))
         FundRow.RejectChanges()
      Catch e As OleDbException
         ErrorHandler(e.Source, e.Message)
         FundRow.RejectChanges()
      Catch e As Exception
         ErrorHandler(e.Source, e.Message)
         FundRow.RejectChanges()
      End Try
Next

Private Function GetAllColErrs(ByVal myRow As DataRow) As String
   ' Declare an array variable for DataColumn objects.
   Dim colArr() As DataColumn
   Dim myRows() As DataRow
   Dim myCol As DataColumn
   Dim myTable As DataTable
   Dim myBuilder As New StringBuilder
   myBuilder.Append(" Column(s):")
   Dim intLoop As Integer
   For Each myTable In FundsDS.Tables
      If myTable.HasErrors Then
         myRows = myTable.GetErrors
         For Each myCol In myRow.GetColumnsInError
            If intLoop > 0 Then
               myBuilder.Append(vbCrLf)
            End If
            myBuilder.Append(myCol.ColumnName & " " &
myRows(intLoop).GetColumnError(myCol))
         Next
      End If
   Next
   GetAllColErrs = myBuilder.ToString
End Function

Here is the XSD file:

<?xml version="1.0" standalone="yes" ?>
<xs:schema id="FundsDataset"
xmlns:mstns="http://www.tempuri.org/FundsDataSet.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
   xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
   <xs:element name="FundsDataset" msdata:IsDataSet="true">
      <xs:complexType>
         <xs:choice maxOccurs="unbounded">
            <xs:element name="Funds">
               <xs:complexType>
                  <xs:sequence>
                     <xs:element name="_x0031_0YearReturn"
type="xs:double" minOccurs="0" />
                     <xs:element name="_x0031_0YearRisk"
type="xs:double" minOccurs="0" />
                     <xs:element name="_x0031_0Years" type="xs:double"
minOccurs="0" />
                     <xs:element name="_x0031_0YearStars"
type="xs:unsignedByte" minOccurs="0" />
                     <xs:element name="_x0031_0YearTaxEff"
type="xs:double" minOccurs="0" />
                     <xs:element name="_x0031_2b-1Fee" type="xs:double"
minOccurs="0" />
                     <xs:element name="_x0033_YearReturn"
type="xs:double" minOccurs="0" />
                     <xs:element name="_x0033_YearRisk"
type="xs:double" minOccurs="0" />
                     <xs:element name="_x0033_Years" type="xs:double"
minOccurs="0" />
                     <xs:element name="_x0033_YearStars"
type="xs:unsignedByte" minOccurs="0" />
                     <xs:element name="_x0033_YearTaxEff"
type="xs:double" minOccurs="0" />
                     <xs:element name="_x0035_yearReturn"
type="xs:double" minOccurs="0" />
                     <xs:element name="_x0035_yearRisk"
type="xs:double" minOccurs="0" />
                     <xs:element name="_x0035_Years" type="xs:double"
minOccurs="0" />
                     <xs:element name="_x0035_YearStars"
type="xs:unsignedByte" minOccurs="0" />
                     <xs:element name="_x0035_YearTaxEff"
type="xs:double" minOccurs="0" />
                     <xs:element name="AllReturn" type="xs:double"
minOccurs="0" />
                     <xs:element name="AllRisk" type="xs:double"
minOccurs="0" />
                     <xs:element name="AllStars" type="xs:unsignedByte"
minOccurs="0" />
                     <xs:element name="BearDecile" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestAvgReturn" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestAvgRisk" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestBear" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestCapExp" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestMean" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestReturn" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestReward" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestRisk" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestSharpe" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestStars" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestSTD" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestTax" type="xs:double"
minOccurs="0" />
                     <xs:element name="BestTaxEff" type="xs:double"
minOccurs="0" />
                     <xs:element name="Brokers" type="xs:string"
minOccurs="0" />
                     <xs:element name="CapGainsExp" type="xs:double"
minOccurs="0" />
                     <xs:element name="Category" type="xs:string"
minOccurs="0" />
                     <xs:element name="CurrentPrice" type="xs:double"
minOccurs="0" />
                     <xs:element name="DeferredLoad" type="xs:double"
minOccurs="0" />
                     <xs:element name="ErrorDesc" type="xs:string"
minOccurs="0" />
                     <xs:element name="ExpenseRatio" type="xs:double"
minOccurs="0" />
                     <xs:element name="Fund" type="xs:string"
minOccurs="0" />
                     <xs:element name="GeneralAdditional"
type="xs:double" minOccurs="0" />
                     <xs:element name="GeneralInitial" type="xs:double"
minOccurs="0" />
                     <xs:element name="Inception" type="xs:dateTime"
minOccurs="0" />
                     <xs:element name="InitialLoad" type="xs:double"
minOccurs="0" />
                     <xs:element name="IRAAdditional" type="xs:double"
minOccurs="0" />
                     <xs:element name="IRAInitial" type="xs:double"
minOccurs="0" />
                     <xs:element name="ManagerName" type="xs:string"
minOccurs="0" />
                     <xs:element name="Mean" type="xs:double"
minOccurs="0" />
                     <xs:element name="MgmtFee" type="xs:double"
minOccurs="0" />
                     <xs:element name="MgrDate" type="xs:dateTime"
minOccurs="0" />
                     <xs:element name="NetAssets" type="xs:double"
minOccurs="0" />
                     <xs:element name="Redemption" type="xs:double"
minOccurs="0" />
                     <xs:element name="RejectDate" type="xs:dateTime"
minOccurs="0" />
                     <xs:element name="RejectReason" type="xs:string"
minOccurs="0" />
                     <xs:element name="Sharpe" type="xs:double"
minOccurs="0" />
                     <xs:element name="SmallestFund" type="xs:double"
minOccurs="0" />
                     <xs:element name="STD" type="xs:double"
minOccurs="0" />
                     <xs:element name="TempRating" type="xs:string"
minOccurs="0" />
                     <xs:element name="Ticker" type="xs:string" />
                     <xs:element name="TotalFees" type="xs:double"
minOccurs="0" />
                     <xs:element name="Updated" type="xs:dateTime"
minOccurs="0" />
                     <xs:element name="UpdateError" type="xs:boolean"
minOccurs="0" />
                     <xs:element name="Year" type="xs:double"
minOccurs="0" />
                     <xs:element name="YTD" type="xs:double"
minOccurs="0" />
                  </xs:sequence>
               </xs:complexType>
            </xs:element>
         </xs:choice>
      </xs:complexType>
      <xs:unique name="Constraint1" msdata:PrimaryKey="true">
         <xs:selector xpath=".//mstns:Funds" />
         <xs:field xpath="mstns:Ticker" />
      </xs:unique>
   </xs:element>
</xs:schema>

Does anyone have any idea how to diagnose the cause of this problem?

Thanks,

Mark

5 Apr 2006 4:56 AM

Cor Ligthert [MVP]

Max,

You have only to do one update from a table one time, it takes than all the
rows in a table.

However, you can as well do (as an overloaded part) an update from a
seperate row as your code is build around. I would only do that if it was as
a seperate row and not in a loop for all.

There is a property to do everything without that there are errors thrown
ContinueUpdateOnError

They are than not processed but an string error is set in the datarow in the
property RowError

There is always done concurrency checking if you use a DataAdapter (or a
TableAdapter that uses that). In your case that should however of course
giving no result. (But your code is in my idea doing a kind of race where a
next record can throw an concurrencyerror)

I hope this helps,

Cor

<mfree***@columbus.rr.com> schreef in bericht
Show quote

news:1144208185.506172.146210@u72g2000cwu.googlegroups.com...
> The minimal code (VB.NET 2003) needed to show this problem is shown
> below. All I do is loop through the records in the table and update
> them without making any changes. Out of 600 records, about 40 of them
> throw Concurrency violation errors in the update, and my GetAllColErrs
> function provides no output. In comparing the rows that throw errors
> with those that do not, I could find no pattern.
>
> It is obviously a bogus error, as the database is an Access 2003 MDB
> file on my PC and there is no concurrent access taking place. After
> doing much research as to possible causes, I changed all decimal
> columns in the database and XSD file (also provided below) to doubles.
> No difference.
>
>
> Dim FundsDS As DataSets.FundsDataset
> Dim FundRow As DataSets.FundsDataset.FundsRow
> Dim myCommand As OleDbDataAdapter
>
> FundsDS = New DataSets.FundsDataset
> myConnection = New
> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> strFileName & ";Persist Security Info=False")
> myConnection.Open()
>
> Try
>   myCommand = New OleDbDataAdapter("SELECT * FROM Funds ORDER BY
> TICKER", myConnection)
>   myCommand.FillSchema(FundsDS, SchemaType.Source, "Funds")
>   myCommand.Fill(FundsDS, "Funds")
>   Dim myBuilder As OleDbCommandBuilder = New
> OleDbCommandBuilder(myCommand)
>   myBuilder.QuotePrefix = "["
>   myBuilder.QuoteSuffix = "]"
>   OpenDB = True
> Catch e As StrongTypingException
>   ErrorHandler(e.Source, e.Message)
> Catch e As Exception
>   ErrorHandler(e.Source, e.Message)
> End Try
>
> For Each FundRow In FundsDS.Funds
>      'Code to do something useful would go here
>      Try
>         myCommand.Update(FundsDS.Tables(0))
>         FundRow.AcceptChanges()
>      Catch e As DBConcurrencyException
>         ErrorHandler(e.Source, e.Message & GetAllColErrs(FundRow))
>         FundRow.RejectChanges()
>      Catch e As OleDbException
>         ErrorHandler(e.Source, e.Message)
>         FundRow.RejectChanges()
>      Catch e As Exception
>         ErrorHandler(e.Source, e.Message)
>         FundRow.RejectChanges()
>      End Try
> Next
>
> Private Function GetAllColErrs(ByVal myRow As DataRow) As String
>   ' Declare an array variable for DataColumn objects.
>   Dim colArr() As DataColumn
>   Dim myRows() As DataRow
>   Dim myCol As DataColumn
>   Dim myTable As DataTable
>   Dim myBuilder As New StringBuilder
>   myBuilder.Append(" Column(s):")
>   Dim intLoop As Integer
>   For Each myTable In FundsDS.Tables
>      If myTable.HasErrors Then
>         myRows = myTable.GetErrors
>         For Each myCol In myRow.GetColumnsInError
>            If intLoop > 0 Then
>               myBuilder.Append(vbCrLf)
>            End If
>            myBuilder.Append(myCol.ColumnName & " " &
> myRows(intLoop).GetColumnError(myCol))
>         Next
>      End If
>   Next
>   GetAllColErrs = myBuilder.ToString
> End Function
>
>
> Here is the XSD file:
>
> <?xml version="1.0" standalone="yes" ?>
> <xs:schema id="FundsDataset"
> xmlns:mstns="http://www.tempuri.org/FundsDataSet.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema"
>   xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
> attributeFormDefault="qualified" elementFormDefault="qualified">
>   <xs:element name="FundsDataset" msdata:IsDataSet="true">
>      <xs:complexType>
>         <xs:choice maxOccurs="unbounded">
>            <xs:element name="Funds">
>               <xs:complexType>
>                  <xs:sequence>
>                     <xs:element name="_x0031_0YearReturn"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="_x0031_0YearRisk"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="_x0031_0Years" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="_x0031_0YearStars"
> type="xs:unsignedByte" minOccurs="0" />
>                     <xs:element name="_x0031_0YearTaxEff"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="_x0031_2b-1Fee" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="_x0033_YearReturn"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="_x0033_YearRisk"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="_x0033_Years" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="_x0033_YearStars"
> type="xs:unsignedByte" minOccurs="0" />
>                     <xs:element name="_x0033_YearTaxEff"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="_x0035_yearReturn"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="_x0035_yearRisk"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="_x0035_Years" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="_x0035_YearStars"
> type="xs:unsignedByte" minOccurs="0" />
>                     <xs:element name="_x0035_YearTaxEff"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="AllReturn" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="AllRisk" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="AllStars" type="xs:unsignedByte"
> minOccurs="0" />
>                     <xs:element name="BearDecile" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestAvgReturn" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestAvgRisk" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestBear" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestCapExp" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestMean" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestReturn" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestReward" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestRisk" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestSharpe" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestStars" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestSTD" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestTax" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="BestTaxEff" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="Brokers" type="xs:string"
> minOccurs="0" />
>                     <xs:element name="CapGainsExp" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="Category" type="xs:string"
> minOccurs="0" />
>                     <xs:element name="CurrentPrice" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="DeferredLoad" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="ErrorDesc" type="xs:string"
> minOccurs="0" />
>                     <xs:element name="ExpenseRatio" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="Fund" type="xs:string"
> minOccurs="0" />
>                     <xs:element name="GeneralAdditional"
> type="xs:double" minOccurs="0" />
>                     <xs:element name="GeneralInitial" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="Inception" type="xs:dateTime"
> minOccurs="0" />
>                     <xs:element name="InitialLoad" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="IRAAdditional" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="IRAInitial" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="ManagerName" type="xs:string"
> minOccurs="0" />
>                     <xs:element name="Mean" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="MgmtFee" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="MgrDate" type="xs:dateTime"
> minOccurs="0" />
>                     <xs:element name="NetAssets" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="Redemption" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="RejectDate" type="xs:dateTime"
> minOccurs="0" />
>                     <xs:element name="RejectReason" type="xs:string"
> minOccurs="0" />
>                     <xs:element name="Sharpe" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="SmallestFund" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="STD" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="TempRating" type="xs:string"
> minOccurs="0" />
>                     <xs:element name="Ticker" type="xs:string" />
>                     <xs:element name="TotalFees" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="Updated" type="xs:dateTime"
> minOccurs="0" />
>                     <xs:element name="UpdateError" type="xs:boolean"
> minOccurs="0" />
>                     <xs:element name="Year" type="xs:double"
> minOccurs="0" />
>                     <xs:element name="YTD" type="xs:double"
> minOccurs="0" />
>                  </xs:sequence>
>               </xs:complexType>
>            </xs:element>
>         </xs:choice>
>      </xs:complexType>
>      <xs:unique name="Constraint1" msdata:PrimaryKey="true">
>         <xs:selector xpath=".//mstns:Funds" />
>         <xs:field xpath="mstns:Ticker" />
>      </xs:unique>
>   </xs:element>
> </xs:schema>
>
>
> Does anyone have any idea how to diagnose the cause of this problem?
>
> Thanks,
>
> Mark
>

5 Apr 2006 5:27 AM

mfreeman

I just tried move the Next statement to be before the Try so that it is
only executed once for the entire table and still received the
Concurrency violation error. What would you recommend I put in the
RecordUpdating or RecordUpdated handlers to try to better diagnose this
error?

I understand what you mean by a race condition, but the records that
throw the error are always the same ones, even if I change the ORDER BY
clause of the SELECT. Therefore it seems that there is something about
those specific records that are causing a problem.

I originally wrote the code using ADODB.Recordsets, which worked
perfectly. However, I am trying to use a DataAdapter and such to take
advantage of strong typing and other new capabilities. So far, I'm
finding it to far more difficult to debug given the lack of appropriate
error messages.

I appreciate your taking the time to respond. Any additional
suggestions would be appreciated.

Thanks,

Mark

5 Apr 2006 5:44 AM

Cor Ligthert [MVP]

Mark,

First you can start with removing those two

For Each FundRow In FundsDS.Funds
....
Next

I wrote more, because on this routine is now your error checking based.

I wrote about that x.RowError and ContinueOnError so that you can do a kind
of error checking as you did so you could keep that routine, (I did not
completely investigate what is happening there.

But start first with removing those two rows.

An alternative what I will not advice you is
myCommand.Update(FundsDS.Tables(0))
to change in
myCommand.Update(FundRow)

But you are than probably the only one in the world who does it this way and
everybody who sees it will probably think something but say nothign.

(It will have by the way probably no performance effects, you take a part
over from the functionality from the dataadapter).

I hope this helps,

Cor

<mfree***@columbus.rr.com> schreef in bericht
Show quote

news:1144214857.346539.46870@e56g2000cwe.googlegroups.com...
>I just tried move the Next statement to be before the Try so that it is
> only executed once for the entire table and still received the
> Concurrency violation error. What would you recommend I put in the
> RecordUpdating or RecordUpdated handlers to try to better diagnose this
> error?
>
> I understand what you mean by a race condition, but the records that
> throw the error are always the same ones, even if I change the ORDER BY
> clause of the SELECT. Therefore it seems that there is something about
> those specific records that are causing a problem.
>
> I originally wrote the code using ADODB.Recordsets, which worked
> perfectly. However, I am trying to use a DataAdapter and such to take
> advantage of strong typing and other new capabilities. So far, I'm
> finding it to far more difficult to debug given the lack of appropriate
> error messages.
>
> I appreciate your taking the time to respond. Any additional
> suggestions would be appreciated.
>
> Thanks,
>
> Mark
>

5 Apr 2006 7:02 PM

aaron.kempf

MDB is friggin crap don't listen to ANYONE that tries to tell you
otherwise

don't use MDB for a single record, a single user.

-Aaron

5 Apr 2006 10:26 PM

mfreeman

While a wonderful concept, I have found MDB files to be very fragile in
practice. However, this seems to be an illustration of an issue with
the .NET OLEDB drivers rather than the MDB files themselves.

This being a personal single-user application with a small data set,
using SQL Server (even the 2005 Express edition) seemed like massive
overkill. My app worked perfectly in VB6, and worked perfectly when
ported to VB.NET using Recordsets. However, there obviously seems to
be some horrible interaction issues between .NET DataAdapters/DataSets
and the Access file.

Judging from the posts I've seen on similar issues, there appear to be
known but unaknowledged bugs in this area. For example, it seems well
known that GetColumnsInError never provide any data for Concurrency
violations, even when used with SQL Server.

It also seems well known that the DBConcurrencyException is thrown for
issues having nothing to do with concurrency.

- Mark

6 Apr 2006 6:17 PM

aaron.kempf

SQL Server is never overkill.

if you care enough to build a database; use a database engine with a
FUTURE.

you can sit there and try to blame it on >NET providers; you can blame
it on odbc-- or oledb

oledb never gives me random problems in SQL Server i'll tell you that
much

MDB is just crap anyone that is writing it today should be unemployed

6 Apr 2006 6:08 AM

Cor Ligthert [MVP]

Aaron,

> MDB is friggin crap don't listen to ANYONE that tries to tell you
> otherwise
>
> don't use MDB for a single record, a single user.
>

What is the sense of this message.

There are plenty of situations that MDB is better than any other product, if
you don't know them than it says something about your overall knowledge.

Just my thought,

Cor

6 Apr 2006 6:19 PM

aaron.kempf

Cor

you are full of sh*t and obsolete.

Oh, but it's such a WONDERFUL database

i've written HUNDREDS of MDB solutions.

6 years ago i graduated to SQL Server and now i am adamant about the
FACT that mdb is NEVER superior to SQL Server.

Maybe you shoudl learn to write SQL, kid lol

5 Apr 2006 10:15 PM

mfreeman

Your most recent post has totally confused me.

If I don't use:
   For Each FundRow In FundsDS.Funds
   ...
   Next
how do I iterate through the rows? I need to update each one with
external data and then save the updated records.

>But you are than probably the only one in the world who does it this way and
>everybody who sees it will probably think something but say nothign.

I certainly want to adhere to standards and conventions wherever
possible. What do you see as unique about my approach?

Thanks,

Mark

6 Apr 2006 6:05 AM

Cor Ligthert [MVP]

Mark,

> If I don't use:
>   For Each FundRow In FundsDS.Funds
>   ...
>   Next
> how do I iterate through the rows? I need to update each one with
> external data and then save the updated records.
>

The dataadapter (that is used in your tableadapter) does this all for you.

Have a look at this page what it does.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconupdatingdatabasewithdataadapterdataset.asp

I hope this helps,

Cor

13 Apr 2006 10:52 PM

aaron.kempf@gmail.com

if you need to update records in a database run TSQL don't use
recordsets

6 Apr 2006 1:34 AM

mfreeman

I did some more research and added some more code.

I added this to the Update's Catch:

Catch dbcx As Data.DBConcurrencyException
   Dim response As Windows.Forms.DialogResult
   response = MessageBox.Show(CreateMessage(CType(dbcx.Row,
DataSets.FundsDataset.FundsRow)), _
    "Concurrency Exception", MessageBoxButtons.YesNo)
   ProcessDialogResult(response, ds)

And added this:

Private Function CreateMessage(ByVal myRow As
DataSets.FundsDataset.FundsRow) As String
   Dim myBuilder As New StringBuilder
   Dim myCol As New DataColumn
      For i As Integer = 0 To myRow.ItemArray.Length - 1
      If myRow.Item(i, Data.DataRowVersion.Original).ToString() <> _
       myRow.Item(i, Data.DataRowVersion.Current).ToString() Then
         myBuilder.Append("Change column " & CStr(i) & " from " & _
          myRow.Item(i, Data.DataRowVersion.Original).ToString() & _
         " to " & myRow.Item(i, Data.DataRowVersion.Current).ToString()
& "?" & vbCrLf)
      End If
   Next
   Debug.WriteLine(myBuilder.ToString)
   Return myBuilder.ToString
End Function

Now I get some more information when I get the DBConcurrencyException.
Here is a sample for one dialog:

Change column 7 from 0 to 3?
Change column 8 from 0.0555 to 0.0562?
Change column 13 from 0.0602 to 0.061?
Change column 34 from 3 to 2?
Change column 36 from 13.1 to 13.11?
Change column 55 from 0.91 to 0.78?
Change column 57 from 4.1 to 4.14?
Change column 61 from 4/3/2006 12:48:28 AM to 4/5/2006 9:16:41 PM?
Change column 63 from 0.0306 to 0.0318?
Change column 64 from 0.0007 to 0.0018?

Clearly there are no null values or anything else strange here, or
different in form from rows where no exception is thrown.

I am at a loss as to what I can do to fix this problem.

28 May 2006 11:52 PM

mdavidjohnson

I'm having a similar problem that seems unrelated to any programming but
rather to something different in the DateTime formats of VB.NET and Access.

I'm running VB.NET in Microsoft Development Environment 2003 Version
7.1.3088 with .NET Framework 1.1.4322.

I'm running Access 2003 (11.6566.6568) SP2.

I have created an update test database in Access which includes one table
with an ID AutoNumber field and a DateTime Field. I have made one entry into
the database, i.e. 10/27/2003 1:33:00 PM. I can update this in Access with no
problem.

I have also created an update test VB.NET project which connects to this
database by dragging the table onto the form to get OleDbConnection1 and
OleDbDataAdapter1. I have generated the dataset and filled it during the form
load event. I have added a datagrid and a button to the form. The datagrid
displays the table but only the date, not the time.

The button's code is:

OleDbConnection1.Open()
OleDbDataAdapter1.Update(Table11)
Table11.AcceptChanges()
OleDbConnection1.Close()

If I change the DateTime entry in the datagrid and then click the button, it
throws an unhandled exception of type 'System.Data.DBConcurrency Exception'.

Then I open the database in Access and empty the field. When I restart the
VB.NET program, the datagrid now shows (null) in the DateTime Field. I change
the field to 11/13/2005 10:47:00 AM and click the button. The update works
and does not throw any exception, but only the 11/13/2005 part appears in the
field: the time part has been lost.

When I open the database in Access again, only the 11/13/2005 part appears.

M. David Johnson
MSDN Professional Subscriber 16660003

Show quote

"mfree***@columbus.rr.com" wrote:

> I did some more research and added some more code.
>
> I added this to the Update's Catch:
>
> Catch dbcx As Data.DBConcurrencyException
>    Dim response As Windows.Forms.DialogResult
>    response = MessageBox.Show(CreateMessage(CType(dbcx.Row,
> DataSets.FundsDataset.FundsRow)), _
>     "Concurrency Exception", MessageBoxButtons.YesNo)
>    ProcessDialogResult(response, ds)
>
> And added this:
>
> Private Function CreateMessage(ByVal myRow As
> DataSets.FundsDataset.FundsRow) As String
>    Dim myBuilder As New StringBuilder
>    Dim myCol As New DataColumn
>       For i As Integer = 0 To myRow.ItemArray.Length - 1
>       If myRow.Item(i, Data.DataRowVersion.Original).ToString() <> _
>        myRow.Item(i, Data.DataRowVersion.Current).ToString() Then
>          myBuilder.Append("Change column " & CStr(i) & " from " & _
>           myRow.Item(i, Data.DataRowVersion.Original).ToString() & _
>          " to " & myRow.Item(i, Data.DataRowVersion.Current).ToString()
> & "?" & vbCrLf)
>       End If
>    Next
>    Debug.WriteLine(myBuilder.ToString)
>    Return myBuilder.ToString
> End Function
>
> Now I get some more information when I get the DBConcurrencyException.
> Here is a sample for one dialog:
>
> Change column 7 from 0 to 3?
> Change column 8 from 0.0555 to 0.0562?
> Change column 13 from 0.0602 to 0.061?
> Change column 34 from 3 to 2?
> Change column 36 from 13.1 to 13.11?
> Change column 55 from 0.91 to 0.78?
> Change column 57 from 4.1 to 4.14?
> Change column 61 from 4/3/2006 12:48:28 AM to 4/5/2006 9:16:41 PM?
> Change column 63 from 0.0306 to 0.0318?
> Change column 64 from 0.0007 to 0.0018?
>
> Clearly there are no null values or anything else strange here, or
> different in form from rows where no exception is thrown.
>
> I am at a loss as to what I can do to fix this problem.
>
>