469,609 Members | 1,681 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Seemingly random Concurrency violations updating Access database

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

Apr 5 '06 #1
13 5081
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

<mf******@columbus.rr.com> schreef in bericht
news:11**********************@u72g2000cwu.googlegr oups.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

Apr 5 '06 #2
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

Apr 5 '06 #3
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


<mf******@columbus.rr.com> schreef in bericht
news:11*********************@e56g2000cwe.googlegro ups.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

Apr 5 '06 #4
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

Apr 5 '06 #5
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

Apr 5 '06 #6
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

Apr 5 '06 #7
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.

Apr 6 '06 #8
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/de...terdataset.asp

I hope this helps,

Cor
Apr 6 '06 #9
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
Apr 6 '06 #10
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

Apr 6 '06 #11
Cor

you are full of shit 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

Apr 6 '06 #12
if you need to update records in a database run TSQL don't use
recordsets

Apr 13 '06 #13
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

"mf******@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.

May 28 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Robin Tucker | last post: by
4 posts views Thread by Bob | last post: by
7 posts views Thread by William E Voorhees | last post: by
1 post views Thread by John | last post: by
1 post views Thread by =?Utf-8?B?Qi4gQ2hlcm5pY2s=?= | last post: by
5 posts views Thread by John | last post: by
reply views Thread by Solution2021 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.