class DataManager { private static SqlConnection myConnection; private static SqlDataAdapter workcenterAdapter; private static SqlCommandBuilder workcenterCB; private static String conString = "..." private static DataSet dataSet = new DataSet(); private static void downloadWorkcenter() { workcenterAdapter = new SqlDataAdapter(); workcenterAdapter.TableMappings.Add("Table", "Workcenter"); workcenterCommandBuilder = new SqlCommandBuilder(workcenterAdapter); SqlCommand mycommand = new SqlCommand("SELECT * FROM Workcenter", myConnection); myCommand.CommandType = CommandType.Text; workcenterAdapter.SelectCommand = myCommand; workcenterAdapter.Fill(dataSet); } public static void uploadWorkcenter() { workcenterAdapter.Update(dataSet, "Workcenter"); } } |
Ok... pretty straight forward. I can get all the data I want downloading. I have no problems connecting there. I can add new rows to the "Workcenter" table in the Dataset, but absolutely no change to the SQL datasource. Even though I'm new to .Net, I've done a lot of vbscript connecting to databases using just SQL syntax. This .net stuff is killing me.
Now, I looked in the debug, my local dataset shows an additional row when I create a newrow and add it to the table. There is one anomaly that I've seen and that is that the three SQL collumns are WCIndex, Name and Symbol and BigInt, nvarchar(20) and nvarchar(5) respecively. The WCIndex is set to (Is Identity)=yes in SQL, but if I look at the table directly in debug and look at that specific collumn, it shows false... but I get all the names with the schema so I'm not sure what is going on here.
I have tried all combinations including hardcoding a SqlCommand and manually executing it... no dice
public static void uploadWorkcenter() { SqlCommand test = new SqlCommand("INSERT INTO Workcenter " + "(Name, Symbol) VALUES ('VIDDS', 'SCMV')", myConnection); int test1 = test.ExecuteNonQuery(); } |
This code should insert a new record regardless of anything else I'm doing, but it doesn't. The integer test1 even reports back 1 record modified (inserted). I even did an SQL Update to all 2 of my records I manually inserted and test1 came back 2 so I know it THINKS it's updating.
Unable to insert or update into SQL2005Express from C# 2005
frohar
I hope this will help you (read the last answer)!
http://forums.microsoft.com/msdn/ShowPost.aspx postid=62925&isthread=true&authhash=efd9aaf9c8d16c2c1179a9e0719f857231bb0cdc&ticks=632579465331716556
Regards,
Nick :o)
siddharth_V
Anthony
IMIT
Hi,
Did you have a chance of looking at my comments from the previous post May be if I can get some more details on the issue I may be able to help.
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation
Lanex
A plain INSERT failing is really surprising. A couple of questions to try to narrow the issue:
- Do you have any active transaction in this connection before you execute this command
- Did you check the results by executing a SELECT right after the INSERT using the same connection object
- Did you check querying with a different tool (e.g. sqlcmd or Management Studio)
- Do you have any triggers in that table Why kind (e.g. any INSTEAD OF triggers )
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.
SauloMachado
To answer your questions here.
- Do you have any active transaction in this connection before you execute this command
No, even the hard coded SQL insert statement was the first and only transaction I would perform and it would fail.
- Did you check the results by executing a SELECT right after the INSERT using the same connection object
Yes, that is where I originally found the problem. At first I didn't know that I could use a stored procedure to return autogenerated index values so I inserted the record then would select it right after. Bad, but I was new to true SQL operations (only used Access up to this point).
- Did you check querying with a different tool (e.g. sqlcmd or Management Studio)
Yes. The Management Studio didn't even have my DB listed. I also attached the DB through the db management tab in VC# to check for updates or inserts. Nothing there either.
- Do you have any triggers in that table Why kind (e.g. any INSTEAD OF triggers )
No triggers.
Anthony
r17158
Great, I'm glad to see that you got the problem sorted out, and also glad to hear that the RTM versions of the product are not causing any problems :)
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation
BulletCoderHope
My apologies for the lateness. I was able to circumvent the problem. Apperantely with one of the beta versions, when I created a new database within C# and then programatically designed my own data adapters and datasets, I could only select rows, not insert or update them (didn't try to delete) either through dataadapter.update() methods or through hard coded SQL statements like I did above. I would have to open the table through the data management tab and manually enter information.
The weirdness didn't stop there though. I could make a connection with the data manager tab to the database, to rapidly check if data had propogated. But if I actually connected to the DB and left it open then my program would throw an SQL exception (can't remember which one) and the only way to fix the issue was to close the connection with Visual C#, then restart the SQL Server service.
To fix the main problem though I had to unattach the DB and create one using the SQL Management CTP interface. Then using a connection string that I generated manually, I was able to attach and perform all normal SQL operations. I think the underlying problem was that when I generated the database with Visual C#, it didn't actually create the DB in SQL 2005. When I used the management software, it was not listed (which is why I created a new DB and was able to fix the issue).
So far I have had no problems with the express production releases of Visual C# and SQL 2005. I did have to redo my connection string again, but a minor inconvenience.
Anthony