dtii
asked on
issue with DB inserts in .NET
Hello Experts..
I am fairly new to the .Net/C# sharp world and am having some issues getting Database interactivity to work properly.
Basically the issue is my insert statements do not seem to be "committing" a record into the database.
my Database connection works.. I am able to pull records back its just that i cant insert rows..
Additionally i can run the same exact code from the command line and succesfully insert a row...
so im thinking it has something to do with the Visual Studio Envinroment and data connectivity..
But i dont know why that would be the case or if it even is..
A peculiar issue is when i run the app in Visual Studio from the same "debugging session" It seems as if it does insert the record because i have a listobx that displays the results and the new row is there..
But this is only evident from the existing "debugging session" What i mean by this is.. If i view the Access database i do not see the new row but in the "debug session" i do. When i close the "debugging session" and start debugging againthe newly inserted row DOES NOT display within the resultset. It looks like a transaction commit issue.. But this is an Access database and the same code works from the command line??.. So im not sure what to think about that.
Here is the statement that will insert a row from the command line but not from visual studio
OleDbConnection conn = new OleDbConnection("Provider= Microsoft. Jet.OLEDB. 4.0;Data Source=myCampground.mdb");
String insertSQL = "insert into site_types (site_type, site_type_descr,base_rate, default_guests) values('Modern Thing','test',12,2)";
OleDbCommand cmd = new OleDbCommand(insertSQL, conn);
try {
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("it should have worked");
}
catch (Exception ex){
//MessageBox.Show("we have a DB error during the insert of the site type" + ex.Message);
Console.WriteLine(ex.Messa ge);
}
I hope this is enough information.. This seems very strange to me and im just hoping its some kind of
.net/visual studio intricacy im not aware of.
Thanks,
Dave
I am fairly new to the .Net/C# sharp world and am having some issues getting Database interactivity to work properly.
Basically the issue is my insert statements do not seem to be "committing" a record into the database.
my Database connection works.. I am able to pull records back its just that i cant insert rows..
Additionally i can run the same exact code from the command line and succesfully insert a row...
so im thinking it has something to do with the Visual Studio Envinroment and data connectivity..
But i dont know why that would be the case or if it even is..
A peculiar issue is when i run the app in Visual Studio from the same "debugging session" It seems as if it does insert the record because i have a listobx that displays the results and the new row is there..
But this is only evident from the existing "debugging session" What i mean by this is.. If i view the Access database i do not see the new row but in the "debug session" i do. When i close the "debugging session" and start debugging againthe newly inserted row DOES NOT display within the resultset. It looks like a transaction commit issue.. But this is an Access database and the same code works from the command line??.. So im not sure what to think about that.
Here is the statement that will insert a row from the command line but not from visual studio
OleDbConnection conn = new OleDbConnection("Provider=
String insertSQL = "insert into site_types (site_type, site_type_descr,base_rate,
OleDbCommand cmd = new OleDbCommand(insertSQL, conn);
try {
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("it should have worked");
}
catch (Exception ex){
//MessageBox.Show("we have a DB error during the insert of the site type" + ex.Message);
Console.WriteLine(ex.Messa
}
I hope this is enough information.. This seems very strange to me and im just hoping its some kind of
.net/visual studio intricacy im not aware of.
Thanks,
Dave
ASKER
That sucks .. i was hoping i was missing something simple..
OS: Windows XP
Im using Visual Studio 2005 so i think the .net framework version is 2.0
This is a desktop app im utilizing WinForms via C#
What would be the recommended method for database work utilizing a desktop app and and access database?..
OS: Windows XP
Im using Visual Studio 2005 so i think the .net framework version is 2.0
This is a desktop app im utilizing WinForms via C#
What would be the recommended method for database work utilizing a desktop app and and access database?..
Here is one more example of doing it. U are doing it the right way. U could check on two things
1. Have u referred Microsoft.Jet.OLEDB.4.0.dl l in your project
2. Have u given a correct path for your data source
3. Try executing this code directly from your C# code Windows or Web
4. After running the insert statement..check by using
select * from <tablename> with (nolock) : To avoid locks if any
string connectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=C:\\Samples\\Employ ee.mdb";
OleDbConnection myConnection = new OleDbConnection( connectionString );
myConnection.Open();
string query = "insert into EMPLOYEE_TABLE (EmployeeID, Name, Address) VALUES (101, 'John', '3960 CliffValley Way')";
OleDbCommand myCommand = new OleDbCommand();
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery( );
myConnection.Close();
1. Have u referred Microsoft.Jet.OLEDB.4.0.dl
2. Have u given a correct path for your data source
3. Try executing this code directly from your C# code Windows or Web
4. After running the insert statement..check by using
select * from <tablename> with (nolock) : To avoid locks if any
string connectionString = "Provider=Microsoft.Jet.OL
OleDbConnection myConnection = new OleDbConnection( connectionString );
myConnection.Open();
string query = "insert into EMPLOYEE_TABLE (EmployeeID, Name, Address) VALUES (101, 'John', '3960 CliffValley Way')";
OleDbCommand myCommand = new OleDbCommand();
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery(
myConnection.Close();
ASKER
OK.. i found the issue but i still dont know exactly why its happening
If you notice in my example.. in the connection string i am using the following as my data source
"Data Source=myCampground.mdb"
For some crazy reason when i "hardcode" the path.. meaning c:/whatever/myCampground.m db it works fine and inserts the row as expected.
Whats strange if i insert a row manually into the "relative" pathed database and then run a Visual Studio debug session .. The row shows up fine but yet when i attempt to insert a new one while within the debug session it acts as if it doesnt "commit" the record.. The Debug session indicates an insert and i can query up the new row from within the "Debug Session" but once im out of the "Debug Session" ... its like the row never existed..
Im thinking Visual Studio does some strange kind of temp database thing and copies the relatively pathed database into memory and then when the debug session is done for whatever reason it doesnt copy the database back down to the original.
Now i wanted the path datasource path to be relative because eventually the app will have to go to a dynamic location. So what exactly is the best way to implement a datasource in a .net desktop app?...
Any tips and/or adivce on implementing an Access DB into a standalone desktop app .. would be helpful and worth 125 points..
thanks,
Dave
If you notice in my example.. in the connection string i am using the following as my data source
"Data Source=myCampground.mdb"
For some crazy reason when i "hardcode" the path.. meaning c:/whatever/myCampground.m
Whats strange if i insert a row manually into the "relative" pathed database and then run a Visual Studio debug session .. The row shows up fine but yet when i attempt to insert a new one while within the debug session it acts as if it doesnt "commit" the record.. The Debug session indicates an insert and i can query up the new row from within the "Debug Session" but once im out of the "Debug Session" ... its like the row never existed..
Im thinking Visual Studio does some strange kind of temp database thing and copies the relatively pathed database into memory and then when the debug session is done for whatever reason it doesnt copy the database back down to the original.
Now i wanted the path datasource path to be relative because eventually the app will have to go to a dynamic location. So what exactly is the best way to implement a datasource in a .net desktop app?...
Any tips and/or adivce on implementing an Access DB into a standalone desktop app .. would be helpful and worth 125 points..
thanks,
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool thanks
What is the environment? Operating system? .NET version? Winforms or ASP.NET?
Bob