473,320 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Microsoft Access 2003 Deletes Records Automatically

Coldfire
289 100+
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
24 2558
MMcCarthy
14,534 Expert Mod 8TB
What SQL code or VB code are you using to insert the records.

Mary
Mar 12 '07 #2
Coldfire
289 100+
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
14,534 Expert Mod 8TB
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
4,871 Expert 4TB
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
289 100+
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
4,871 Expert 4TB
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
14,534 Expert Mod 8TB
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
289 100+
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
289 100+
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
14,534 Expert Mod 8TB
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
289 100+
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
289 100+
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
14,534 Expert Mod 8TB
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
14,534 Expert Mod 8TB
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
289 100+
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
14,534 Expert Mod 8TB
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
289 100+
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
14,534 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
289 100+
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
32,556 Expert Mod 16PB
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
289 100+
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
289 100+
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
32,556 Expert Mod 16PB
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

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

Similar topics

9
by: (Pete Cresswell) | last post by:
Seems like when there's a 1:1 relationship, the order of referential integrity enforcement depends on which way you drag the mouse pointer when drawing the relationship line. If you drag from...
49
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The...
14
by: Darin | last post by:
I have a table that I want to delete specific records from based on data in other tables. I'm more familiar with Access '97, but am now using 2003, but the database is in 2000 format. In '97, I...
6
by: Null Reference | last post by:
Anybody here who can explain or point me to a link ? I wish to create a blank MS Access DB file programmatically using C# . Thanks, nfs
0
by: Phil Haddock | last post by:
Hi, I'm converting an existing Access application to vb.net 2003. The application allows users to select a number of tables from an ODBC data source, and nominate names for each table to use...
3
by: RayPower | last post by:
I'm having a system using Access 2000 as both front-end (queries, forms, reports & temp tables for reports) & back-end (data) with back-end running on the server. The application runs on the...
11
bhcob1
by: bhcob1 | last post by:
Hi, Whenever I delete a record my command button, the record deletes, a list displaying all records is updated and then a message box appears: Microsoft Access can't find the field 'I' referred to...
8
by: Briansmi1116 | last post by:
Hi all, I am using Access 2003 as well as Excel 2003. I have an Access database that has an output to Excel button I created. This outputs a form to an Excel file A. I then have Excel...
1
by: Bobby | last post by:
Hi I am using Access 2003 mdb as a front end to an application which uses SQL Server 2000 as the backend. The two are connected using ODBC. On one particular table (the Stock table), I have a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.