Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
robcpettit
Starting Member
11 Posts |
 Posted - 2013-08-01 : 11:07:24
|
| Hi, im using vs2010 with sql compact ce. Please could someone explain why the following happensstring conString = ConfigurationManager.ConnectionStrings["DataTest1.Properties.Settings.ResultsTableConnectionString"].ConnectionString;SqlCeConnection con = new SqlCeConnection(conString);con.Open();SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'testTABLE'", con);int i = 0;i = int.Parse(cmd.ExecuteScalar().ToString());if (i > 0){MessageBox.Show("exists");SqlCeCommand drt = new SqlCeCommand("DROP TABLE [testTABLE]", con);drt.ExecuteNonQuery();}else{MessageBox.Show("doesn't exists");}Before i run the code, ive created a table called testTABLE with 1 column, its clearly there. Run and click button ounce, msgbox 'it exist', drop table executed. click button again, doesn't exist, as expected. End program. The table is still in the database. Why? Without making any alterations run the code and the same happens again. Any Ideas please. regards Robert |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-01 : 11:17:27
|
It just might be an artifact of the way you are looking at the table. For example, if you are looking in the SSMS object explorer, it will show the table even after you drop it. It does not automatically refresh. If you right-click the node above it and select refresh, it should refresh the tree and the table should disappear.Alternatively, run the same query that you are running from your C# code from an SSMS window and it should return 0SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'testTABLE' |
 |
|
|
robcpettit
Starting Member
11 Posts |
Posted - 2013-08-01 : 11:37:25
|
| Thank you for your reply, Ive tried refreshing and it still shows the table. The alternative shows the table still exists also.Regards Robert |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-01 : 11:54:34
|
| I can't think of anything that might cause this behavior, unless by some mistake you are connecting to one server and/or database in your client code, and looking at another server/database when looking via SSMS. |
|
|
|
robcpettit
Starting Member
11 Posts |
Posted - 2013-08-05 : 10:22:15
|
| hi, still playing around with this. Im not looking to see if it exists now just creating the table at design time and trying to drop ar run time.string conString = ConfigurationManager.ConnectionStrings["DataTest1.Properties.Settings.ResultsTableConnectionString"].ConnectionString; SqlCeConnection con = new SqlCeConnection(conString);con.Open();SqlCeCommand cmd = new SqlCeCommand("DROP TABLE [testTABLE]", con);cmd.ExecuteNonQuery();I get no error, but it will not drop the table. I can drop the the table at design time just not in code. Any Ideas?regerds robert |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-05 : 11:29:43
|
| Couple of things you might try:1. Run SQL Profiler and see what is coming into the database. Copy that T-SQL and run from an SSMS query window to see if that is successful.2. Return value of ExecuteNonQuery is somewhat strange (to my simple minded way of thinking). It returns the number of rows affected for update/insert/deletes, but -1 for anything else (even if there was some error). So unless an exception was thrown, you cannot rely on the return value of ExecuteNonQuery to determine success or failure.I know I am grasping at straws here because there is nothing else that comes to mind at the moment. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-05 : 11:47:07
|
INFORMATION_SCHEMA is dependant on the current database. Do all users attaching to the database server having the correct default database?If no, add a "USE [MyDatabase];" at the top of the query.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|
|
Drop Table