Writing to Access database

[SnoopFrogg]

I am writing data values to an Access database. For every set of values, I am creating a new row in the database. I've noticed that occasionally some previously added rows are overwritten by new data that I am writing to the database. Is there a way to specify that new data should only be added after all other records (i.e., can I specify that the "pointer" should always be moved after the last record)?

The following is the code that inserts the row into the database:

			myAccessCommand = new OleDbCommand(sqlInsertStatement, conn);
			myAccessCommand.CommandType = CommandType.Text;

			try
			{
				int numRows = myAccessCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				Console.WriteLine("Failed to insert into database.  The error is:  " + ex);
			}

I've looked through the MSDN API briefly but didn't see anything that caught my eye.

[purple]
SnoopFrogg
MCSA+Security - Windows Server 2003
[/purple]

 

[litton1]

does the table where the data is inserted have an auto number?
I must confess that I am slightly confused by what you are saying, are you saying that you are performing an insert and an update is taking place?

by the way i dont think the line

myAccessCommand.CommandType = CommandType.Text;

is needed as you have already made the command

Age is a consequence of experience

 

[SnoopFrogg]

Thanks for the quick reply.

The table does not have an autonumber.

I must confess that I am slightly confused by what you are saying, are you saying that you are performing an insert and an update is taking place?
I am inserting rows of data into the table. After the rows- say records 29 through 56- are inserted, I look back at rows 1 through 28 and notice that the contents of some fields are modified.



[purple]
SnoopFrogg
MCSA+Security - Windows Server 2003
[/purple]

 

[litton1]

Is it possible that the data being inserted was modified before the data actually went into the Database?

Age is a consequence of experience

 

[ddiamond]

Under normal circumstances, there is no way an insert statement can modify existing data. With MS Access there are only 2 exceptions I can think of:
1. You have a corrupt database. The repair tool should fix this.
2. You don't have a primary key defined on the table (this really shouldn't matter, but I have seen the jet engine do strange things when it doesn't have a primary key).

Most likely though, the data modification is not occurring in the code you listed above.

 

[SnoopFrogg]

Is it possible that the data being inserted was modified before the data actually went into the Database?
Even if the data is being modified prior to insertion, I'm still curious why previously written rows are modified by the current insertion.

2. You don't have a primary key defined on the table (this really shouldn't matter, but I have seen the jet engine do strange things when it doesn't have a primary key).
You're right- I don't have a primary key. Doh! I'll create one and see what the results are. Thanks!

[purple]
SnoopFrogg
MCSA+Security - Windows Server 2003
[/purple]

 

[damber]

An INSERT statement is just that. It will insert NEW rows into an existing table and SHOULD NOT (in normal circumstances) UPDATE any existing rows.. that's what the UPDATE statment is for.

For this very reason I think ddiamond's first point may be worth investigating if bug-crunching doesn't get you anywhere. However, it may be worthwhile posting the SQL Statement that you store in sqlInsertStatement - this may be your problem - but as we can't see it, we've little chance of knowing!

If it is the PK thing, then I would be very surprised - mind you, Access is a bit of a backward db engine full of bugs, so you never know.. ;o)

A smile is worth a thousand kind words. So smile, it's easy!