Inserting to Dbase 3 database via ADO .Net Problem

[eboen]

Hi,

First for the setup:
I work for a museum, we have an exhibit hall that has a number of
interactiv
es that all store their data in a dbaseIII db. All of the exhibits are
earl
y to mid 1990's technology. We are in the process of replacing the
exhibits
(all the new exhibits are windows.net apps). There is a User Check in
stat
ion and a Check out station (the visitor uses a barcoded card), we are
repla
cing these first in order to allow for incremental replacement of the
exhibi
ts in between checkin and checkout. The new check in writes to both
dbaseII
I and the new SQL server db, checkout reads from both.

Problem:
We are able to write/insert into the dbaseIII db (we can see the rows
using
a dbase viewer) but the legacy exhibits are only able to find the
inserted r
ecord some of the time. We can't
really find a pattern. Test 1: I checked in sixteen times each time
going d
irectly to a legacy app after checking in, 4 out of 16 where not found.
Te
st 2: I checked in sixteen times consecutively, then went to a legacy
statio
n, none were found of the 16. If we write a record using a legacy app
We are
able to update records in DBase 100% of the time. We do not have the
sourc
e code for any of the legacy apps. The dbf file sits on a shared
drive. See
ms like some sort of locking issue or something like that.

Our connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strConnection +
";Extended
Properties=dBASE III;User ID=Admin;Password="

Here is our Insert Statement: //we've tried the below insert using odbc
and
dsn - no transaction - no difference in outcome

System.Data.OleDb.OleDbConnection cnn = new
System.Data.OleDb.OleDbConnectio
n(conn);
cnn.Open();
System.Data.OleDb.OleDbCommand oleCommand = cnn.CreateCommand();
System.Data.OleDb.OleDbTransaction trans;
trans = cnn.BeginTransaction(IsolationLevel.ReadCommitted);
oleCommand.Transaction = trans;

oleCommand.CommandText = "INSERT INTO VISITORS(CHECKIN, REVISION, ID,
NAME,
BIRTHDAY, SEX, DISABLED, VISIT_DATE, VISIT_TIME, PARQ_PASS, ACTIVITY1,
ACTIV
ITY2, ACTIVITY3, ETHNIC, EVER_SMOKE, ZIPCODE, KEPTCARD, AG_RESV2,
SYSTOLIC0,
DIASTOLIC0, ACTIVE_P0, VO20, CV_FIT0, QUIET_P0, WEIGHT0, HEIGHT0,
FLEX0, FL
EX_RAW0, GRIP0, GRIP_RAW0, STRESS0, DATE0, SMOKER0, FAT0, FIBER0,
RESERVED0,
SYSTOLIC1, DIASTOLIC1, ACTIVE_P1, VO21, CV_FIT1, QUIET_P1, WEIGHT1,
HEIGHT1
, FLEX1, FLEX_RAW1, GRIP1, GRIP_RAW1, STRESS1, DATE1, SMOKER1, FAT1,
FIBER1,
RESERVED1, SYSTOLIC2, DIASTOLIC2, ACTIVE_P2, VO22, CV_FIT2, QUIET_P2,
WEIGH
T2, HEIGHT2, FLEX2, FLEX_RAW2, GRIP2, GRIP_RAW2, STRESS2, DATE2,
SMOKER2, FA
T2, FIBER2, RESERVED2, SYSTOLIC3, DIASTOLIC3, ACTIVE_P3, VO23, CV_FIT3,
QUIE
T_P3, WEIGHT3, HEIGHT3, FLEX3, FLEX_RAW3, GRIP3, GRIP_RAW3, STRESS3,
DATE3,
SMOKER3, FAT3, FIBER3, RESERVED3) ";
oleCommand.CommandText += "VALUES ('+', '1','" +
PadLeftSpaces(7,_CardNumber
String) + "','" + PadRightSpaces(10,clsData._FirstName) + "','" +
clsData._B
irthDate + "','" + PadLeftSpaces(2,clsData._Sex.ToString()) + "','" +
PadLef
tSpaces(2,clsData._Disabled.ToString()) + "','" + clsData._CheckInDate
+ "',
'" + clsData._VisitTime + "'," + parqpass + ","+ nullNumeric2 + "," +
nullN
umeric2 + "," + nullNumeric2 + ","+ nullNumeric2 + ","+ nullNumeric2 +
","+
nullNumeric5 + ","+ nullNumeric2 + "," + nullNumeric5 + ","+
nullNumeric3 +
","+ nullNumeric3 + "," + nullNumeric3 + "," + "' -1.0'" + "," +
nullNumeri
c2 + ","+ nullNumeric3 + ","+ nullNumeric3 + ","+ nullNumeric3 + ",";
oleCommand.CommandText += nullNumeric3 + ","+ nullNumeric3 + ","+
nullNumeri
c3 + ","+ "' -1.0'" + ", "+ nullNumeric2 + ","+ nullNumeric3 + ","+
nullNu
meric2 + ","+ nullNumeric3 + ","+ nullNumeric3 + ","+ nullNumeric3 +
","+ nu
llNumeric3 + ","+ nullNumeric3 + ","+ nullNumeric3 + ","+ "' -1.0'" +
","+
nullNumeric2 + ","+ nullNumeric3 + ","+ nullNumeric3 + ","+
nullNumeric3 + "
,"+ nullNumeric3 + ","+ nullNumeric3 + ","+ nullNumeric3 + ","+ "'
-1.0'"
+ ","+ nullNumeric2 + ","+ nullNumeric3;
oleCommand.CommandText += ","+ nullNumeric2 + ","+ nullNumeric3 + ","+
null
Numeric3 + ","+ nullNumeric3 + ","+ nullNumeric3 + ","+ nullNumeric3 +
","+
nullNumeric3 + ","+ "' -1.0'" + ","+ nullNumeric2 + ","+ nullNumeric3
+ ","
+ nullNumeric3 + ","+ nullNumeric3 + ","+ nullNumeric3 + ","+
nullNumeric3 +
", "+ nullNumeric3 + ","+ "' -1.0'" + ","+ nullNumeric2 + ","+
nullNumeri
c3 + ","+ nullNumeric2 + "," + nullNumeric3 + ","+ nullNumeric3 + ","+
nullN
umeric3 + ","+ nullNumeric3 + ", "+ nullNumeric3 + ","+ nullNumeric3 +
","+
"' -1.0'" + ","+ nullNumeric2 + "," + nullNumeric3 + ",'" +
PadLeftSpaces(
3,clsData._Weight.ToString()) + "','" +
PadLeftSpaces(3,clsData._HeightInche
s.ToString()) + "'," + nullNumeric3 + ","+ nullNumeric3 + ","+
nullNumeric3
+ ","+ "' -1.0'" + "," + nullNumeric2 + ", '"+ "188" + "',"+
nullNumeric
2 + ","+ nullNumeric3 + ","+ nullNumeric3 + ","+ nullNumeric3 + ")";



try
{
oleCommand.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
try
{
trans.Rollback();
}
catch (Exception e)
{
if (trans.Connection != null)
{
}
}
finally
{
}
finally{
cnn.Close();
}
}catch(Exception exc)
{
}

Here is our Update Statement (this works and uses DSN)

OdbcConnection cnn = new OdbcConnection(strConnection);
OdbcCommand odbcUpdateCommand = cnn.CreateCommand();
try
{
odbcUpdateCommand.CommandText = @"UPDATE VISITORS SET NAME = '" +
PadRightS
paces(10,FirstName) + "', SEX = " +
PadLeftSpaces(2,clsData._Sex.ToString())
+ ", DISABLED = " + PadLeftSpaces(2,clsData._Disabled.ToString()) + ",
BI
RTHDAY = '" + clsData._BirthDate + "' WHERE ID = " +
PadLeftSpaces(7,_Card
NumberString);

odbcUpdateCommand.Connection.Open();
odbcUpdateCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("UpdateUserInfoDbase in clsData", ex);
}
finally
{
if (odbcUpdateCommand != null)
{
odbcUpdateCommand.Dispose();
}
if(cnn.State == ConnectionState.Open)
{
cnn.Close();
}
}

Thank you in advance
sincerely
eric

 

[William \(Bill\) Vaughn]

First, I'm glad to hear that Mars is not going to crash into the earth.
Next, I would suggest that you first get that data out of Dbase if at all
possible. Use DTS or BCP to import the tables into SQL Server. Once there
your update problems (which I suspect are caused by JET) will go away.
Next, I would think about normalization for your database.
Next, I would build a Parameter-driven Command to eliminate SQL injection
issues. The concatenated query approach is very dangerous and fraught with
issues.

hth


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[eboen]

Thanks for the prompt response Bill.
Are you referring to
http://www.dmns.org/main/en/General/AboutTheMuseum/News/Story/marsAboutUs
if so thanks for visiting our web site!!No can do, we don't have the resources to replace all the exhibits at
one time so until we can get the older exhibits replaced they will be
writing to the dbase db since we do not have the source. We had the
same problem when using a DSN using the DBaseIII driver.Once all exhibits are updated the apps will be using Stored Procedures
and access them using the Enterprise Data Access Application Block. The
new exhbit apps already use this.

Thanks for your input

Eric

 

[Kevin Yu [MSFT]]

Hi Eric

Do you mean that when you have inserted records into the dBase database,
the inserted records cannot be seen?

If so, I suppose it might have something to do with the Jet engine. Because
Jet will cache data before real update. Will it work fine if you close the
client side app after updating?

In this case, I strongly suggest you use SQL Server instead as Bill
suggested.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."