By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Microsoft Access 2003 Deletes Records Automatically

Coldfire
100+
P: 289
hello ppl.

I am havin this minor problem that i have not yet figured out lately.

I am developing a windows application in VStudio.net 2005 that interacts with an Access DB (with latest MS Jet 4.0x Oledb engine).

On the user interface i have two buttons
1. Insert
2. View

Lets say i have inserted records and then closed the form & connection.
then i pressed View button to show the records in the datagridView control. It displays the records all fine. Now when i close the program and open the Access DB the tables are empty. How?
And when i run the application again to View the inserted-records, the gridview displays none (means records are not saved into the Access DB).
I have tried lots of things but not yet figured out completely the main source of problem.PLz help !
Mar 12 '07 #1
Share this Question
Share on Google+
24 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
What SQL code or VB code are you using to insert the records.

Mary
Mar 12 '07 #2

Coldfire
100+
P: 289
What SQL code or VB code are you using to insert the records.

Mary
simple sql Insert query.
INSERT INTO TableName(Field1,Field2)Values(txtBox1.text,textbo x2.text);

there is no problem in the query. But there could be the problem with the connection.
Whats the difference in creating a connection with the Access database in the main application folder and the Applications' Debug Folder
Mar 12 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Truthfully, I don't know as I don't use .NET. Let me post a pointer to this question in the .NET forum.

Mary
Mar 12 '07 #4

kenobewan
Expert 2.5K+
P: 4,871
Your insert is not working and I don't believe that it will not work as you have written it. So this is your first port of call. The debug folder is pre-release and won't affect your connection if you have the right path - this would normally generate an error anyway. Have you got your insert in a try/catch and write any errors. Please post your code. I am assuming that the database that you are using is irrelevant in this case, except for the connection.
Mar 13 '07 #5

Coldfire
100+
P: 289
Your insert is not working and I don't believe that it will not work as you have written it. So this is your first port of call. The debug folder is pre-release and won't affect your connection if you have the right path - this would normally generate an error anyway. Have you got your insert in a try/catch and write any errors. Please post your code. I am assuming that the database that you are using is irrelevant in this case, except for the connection.
my insert query is correct (since the datagridview is showing the inserted data)

[HTML]private void SaveInstituteInformation_Click(object sender, EventArgs e)
{
string InsertCmd = "INSERT INTO InstituteInformation(Country,InstituteName,Locatio n,Address,ContactDetails, PersontoContact,DetailPersontoContact,InstituteInt ro,DegreeOffered,CourseOffered,Fees,Scope)"+
"VALUES( \""+this.comboCountry.Text + "\", \"" + this.tbInstitutionName.Text + "\", \"" + this.tbLocation.Text + "\", \"" + this.tbAddress.Text + "\", \"" + this.tbContactDetails.Text + "\", \"" + this.tbPersonToContact.Text + "\", \"" + this.tbPersonContactDetail.Text + "\", \"" + this.tbInstituteIntro.Text + "\", \"" + this.comboDegreeOffered.Text + "\", \"" + this.comboCourseOffered.Text + "\", \"" + this.tbFees.Text + "\", \"" + this.tbScope.Text + "\" )";

oleDbConnection.Open();
oleDbInsertCommand.CommandText = InsertCmd;
oleDbInsertCommand.Connection = oleDbConnection;
oleDbDataAdapter.InsertCommand = oleDbInsertCommand;
try
{

oleDbInsertCommand.ExecuteNonQuery();

}
catch (OleDbException ex)
{
MessageBox.Show("OleDB Insert Exception{0}", ex.ToString());
}
}[/HTML]

The inserted-data remains in the AccessDB until the application is running but when i close the whole application the data is #Deleted and then afterwards the datagridview obiously show no records!
Mar 13 '07 #6

kenobewan
Expert 2.5K+
P: 4,871
Nearly saved myself by a double negative ;). Good work on your insert. Did you create using VS.NET, does your data adapter have a delete statement & is it executed somewhere and how do you bind the data to the gridview? So you have openned the db before exiting and the data is there. Failing something in the code or the way your application exits, we are back to looking at Access for clues in the way that it is storing the data.
Mar 14 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Nearly saved myself by a double negative ;). Good work on your insert. Did you create using VS.NET, does your data adapter have a delete statement & is it executed somewhere and how do you bind the data to the gridview? So you have openned the db before exiting and the data is there. Failing something in the code or the way your application exits, we are back to looking at Access for clues in the way that it is storing the data.
But the data was in the database according to coldfire. It wouldn't be deleted from the table unless prompted.

Coldfire did you actually open the database during a live session to see if the data was stored in the database?

Mary
Mar 14 '07 #8

Coldfire
100+
P: 289
well, the data gets deleted when i Open the connection again after terminating the application.
e.g
1. Conn.Open
2. Insert Record

3. Checkd the data in access (OK its there!)
4. Terminate the applicaion (Connec.Close)

----------
5. Run the app again
6. conn.open
7.a checked the access db for the previous enterd data
7.b tables are empty, no data!
Mar 14 '07 #9

Coldfire
100+
P: 289
Nearly saved myself by a double negative ;). Good work on your insert. Did you create using VS.NET, does your data adapter have a delete statement & is it executed somewhere and how do you bind the data to the gridview? So you have openned the db before exiting and the data is there. Failing something in the code or the way your application exits, we are back to looking at Access for clues in the way that it is storing the data.
Yes i am using VS.net and my dataadapter has no delete statement.
I bind the data to gridview like
[HTML]private void btnShowAllRecords_Click(object sender, EventArgs e)
{
string SelectCmd = "SELECT EnterInstituteInformation.InstituteID,EnterInstitu teInformation.Country, EnterInstituteInformation.InstituteName, EnterInstituteInformation.DegreeHead, EnterInstituteInformation.CourseHead, EnterInstituteInformation.CourseName,EnterInstitut eInformation.StartingDate,EnterInstituteInformatio n.Fees, EnterInstituteInformation.EntryRequirements FROM EnterInstituteInformation ";

DataSet dsReportShowAll = new DataSet();
this.oleDbSelectCommand1.CommandText = SelectCmd;
oleDbSelectCommand1.Connection = oleDbConnection1;
oleDbDataAdapter1.SelectCommand = oleDbSelectCommand1;
oleDbDataAdapter1.Fill(dsReportShowAll);
this.dataGridView1.DataSource = dsReportShowAll.Tables["EnterInstituteInformation"].DefaultView;

}[/HTML]
but the problem doesnt concerns with datagridview..i think its the connection-issue....or may be the Ole Jet Engine Version issue ...who knows!
Mar 14 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes i am using VS.net and my dataadapter has no delete statement.
I bind the data to gridview like
[HTML]private void btnShowAllRecords_Click(object sender, EventArgs e)
{
string SelectCmd = "SELECT EnterInstituteInformation.InstituteID,EnterInstitu teInformation.Country, EnterInstituteInformation.InstituteName, EnterInstituteInformation.DegreeHead, EnterInstituteInformation.CourseHead, EnterInstituteInformation.CourseName,EnterInstitut eInformation.StartingDate,EnterInstituteInformatio n.Fees, EnterInstituteInformation.EntryRequirements FROM EnterInstituteInformation ";

DataSet dsReportShowAll = new DataSet();
this.oleDbSelectCommand1.CommandText = SelectCmd;
oleDbSelectCommand1.Connection = oleDbConnection1;
oleDbDataAdapter1.SelectCommand = oleDbSelectCommand1;
oleDbDataAdapter1.Fill(dsReportShowAll);
this.dataGridView1.DataSource = dsReportShowAll.Tables["EnterInstituteInformation"].DefaultView;

}[/HTML]
but the problem doesnt concerns with datagridview..i think its the connection-issue....or may be the Ole Jet Engine Version issue ...who knows!
I don't use oledb for connections so maybe I'm missing something obvious here. Your insert statement inserts the data into InstituteInformation table but your select statement selects the data from EnterInstituteInformation?

Mary
Mar 14 '07 #11

Coldfire
100+
P: 289
Please post your code. I am assuming that the database that you are using is irrelevant in this case, except for the connection.
Can DB be irrelevant ! .. wht do u mean by it ............i didnt understood
Mar 14 '07 #12

Coldfire
100+
P: 289
I don't use oledb for connections so maybe I'm missing something obvious here. Your insert statement inserts the data into InstituteInformation table but your select statement selects the data from EnterInstituteInformation?

Mary
sorry for the syntax error, its "EnterInstituteInformation" everywhere. I pasted that code with "InstituteInformation" from the older version. Its not the issue.
Mar 14 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
sorry for the syntax error, its "EnterInstituteInformation" everywhere. I pasted that code with "InstituteInformation" from the older version. Its not the issue.
You don't have code anywhere running a create table procedure do you?
Mar 14 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
You don't have code anywhere running a create table procedure do you?
What code are you running to close the connection?
Mar 14 '07 #15

Coldfire
100+
P: 289
What code are you running to close the connection?
for closing connection i call it when the form is closed.

I think i have somewhat grabbed the problem. Since I am still developing the application. So, i used to run it in Debug mode and everything. But when i Run or close the application directly (running the exe from the debug folder) that data in access DB remains intact. Well, I am not 100% sure that "this" is the problem. but i think it should not be the problem. i.e. in development mode ur access DB tables lost the entered data and when u run it directly the data remains intact there !
Mar 14 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
for closing connection i call it when the form is closed.

I think i have somewhat grabbed the problem. Since I am still developing the application. So, i used to run it in Debug mode and everything. But when i Run or close the application directly (running the exe from the debug folder) that data in access DB remains intact. Well, I am not 100% sure that "this" is the problem. but i think it should not be the problem. i.e. in development mode ur access DB tables lost the entered data and when u run it directly the data remains intact there !
Doesn't make a lot of sense to me.

I've asked some experts with more experience of OLE DB connections to have a look at this but none of them are on line at the moment.

Mary
Mar 14 '07 #17

Coldfire
100+
P: 289
thats exactly the problem. i.e. when i rebuild solution than the data stored in the Access DB in Debug Folder also got erased. I think the inserted data should reside in the access DB in the main working folder (which is not happening, and the connection is also with this Db file) and it should not get erased.
Mar 14 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
thats exactly the problem. i.e. when i rebuild solution than the data stored in the Access DB in Debug Folder also got erased. I think the inserted data should reside in the access DB in the main working folder (which is not happening, and the connection is also with this Db file) and it should not get erased.
Hopefully some of the others will be able to help. We just have to wait for them to come online.
Mar 14 '07 #19

NeoPa
Expert Mod 15k+
P: 31,489
If you're saying 'Why does it work in real-time but not when I'm debugging?', then the answer may be (& I make no pretense to have experience of .NET or your OLE drivers) that the debugging environment shows you the state of the database as it would be at that time. Using the concept of 'Transactions', it possibly makes the changes as one, then rolls it back for you when it closes. Just a thought, but makes a certain amount of sense for debugging I would have thought.
Mar 14 '07 #20

Coldfire
100+
P: 289
If you're saying 'Why does it work in real-time but not when I'm debugging?', then the answer may be (& I make no pretense to have experience of .NET or your OLE drivers) that the debugging environment shows you the state of the database as it would be at that time. Using the concept of 'Transactions', it possibly makes the changes as one, then rolls it back for you when it closes. Just a thought, but makes a certain amount of sense for debugging I would have thought.
so i suppose that the matter is solved or not!!!
Mar 15 '07 #21

NeoPa
Expert Mod 15k+
P: 31,489
so i suppose that the matter is solved or not!!!
Only you can tell that my friend.
I'm only really hypothesizing. Does it make sense to you? Does it resolve your issue(s)?
As I said in my earlier post, I'm no expert in that area. i'm just drawing on experience of how things are done elsewhere.
Mar 15 '07 #22

Coldfire
100+
P: 289
Only you can tell that my friend.
I'm only really hypothesizing. Does it make sense to you? Does it resolve your issue(s)?
As I said in my earlier post, I'm no expert in that area. i'm just drawing on experience of how things are done elsewhere.
not really, well i have not found any reason of data getting erased from the Access Table on Rebuilding the Solution. Because it should not happen normally.
Mar 15 '07 #23

Coldfire
100+
P: 289
not really, well i have not found any reason of data getting erased from the Access Table on Rebuilding the Solution. Because it should not happen normally.
when i rebuild the entered fileds are replaced by #Error
and when i reopen DB the records get empty.
Mar 15 '07 #24

NeoPa
Expert Mod 15k+
P: 31,489
not really, well i have not found any reason of data getting erased from the Access Table on Rebuilding the Solution. Because it should not happen normally.
You didn't find my post (#20) very convincing then?
You're right, I could well be wrong. I'd be interested in your reasoning though?
Mar 15 '07 #25

Post your reply

Sign in to post your reply or Sign up for a free account.