SQL Update problem I'm still having.
-
This is the error I get when I try to update. Some extra information I didn't include when I posted about this a while back is that I've got 2 forms, a main form with a dataset(this is mostly a "view" of what is returned by what the user clicks, no edit features), and I've got a simple edit form that lets the user change stuff in the table they want to edit. Each form uses its own dataset, but I've tried it with a shared dataset. If I make the 2nd form in its own project and run it as a stand alone to edit the table, it works just fine. ****************************************************************************** The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time. ****************************************************************************** Sample code that causes this error. private void button1_Click(object sender, EventArgs e) { this.Validate(); this.tb.EndEdit(); this.tb.Update(this.DataSet.tb); } Form was created by using the drag and drop abilities of the data source. simple form, only 4 items in the table.
-
This is the error I get when I try to update. Some extra information I didn't include when I posted about this a while back is that I've got 2 forms, a main form with a dataset(this is mostly a "view" of what is returned by what the user clicks, no edit features), and I've got a simple edit form that lets the user change stuff in the table they want to edit. Each form uses its own dataset, but I've tried it with a shared dataset. If I make the 2nd form in its own project and run it as a stand alone to edit the table, it works just fine. ****************************************************************************** The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time. ****************************************************************************** Sample code that causes this error. private void button1_Click(object sender, EventArgs e) { this.Validate(); this.tb.EndEdit(); this.tb.Update(this.DataSet.tb); } Form was created by using the drag and drop abilities of the data source. simple form, only 4 items in the table.
I don't think that there is a problem with the C# code that you've posted. From the error message, it sounds like it is the way you have set up your table that is the culprit. The call to
Updatewill cause an update to the table which causes the error message you cited - not because of theUpdatecall, but because of the columns and setup of the table you update. I have not read the previous post you mentioned, but could you post some information about the way you have setup your table? Sincerely, Alexander Wiseman -
I don't think that there is a problem with the C# code that you've posted. From the error message, it sounds like it is the way you have set up your table that is the culprit. The call to
Updatewill cause an update to the table which causes the error message you cited - not because of theUpdatecall, but because of the columns and setup of the table you update. I have not read the previous post you mentioned, but could you post some information about the way you have setup your table? Sincerely, Alexander WisemanThe other post was the same, with less information and I posted it on the C# forum. As far as the table is concerned, ID, Name, Notes PK INT, Char[10], Char[50] No Null, Nulls, Nulls No foreign keys, but another table does use ID as an FK. Also, I click the buttons on the BindingNavigator for New, and Delete and they work fine, I just can't make a change to the current record displayed and click the save button. I've made both forms (stand alone project, and the edit form for the parent application) identically, same datasets, same connection strings. All code on both forms is auto generated by the IDE. For some reason, the standalone works fine, but this one doesn't.
-
The other post was the same, with less information and I posted it on the C# forum. As far as the table is concerned, ID, Name, Notes PK INT, Char[10], Char[50] No Null, Nulls, Nulls No foreign keys, but another table does use ID as an FK. Also, I click the buttons on the BindingNavigator for New, and Delete and they work fine, I just can't make a change to the current record displayed and click the save button. I've made both forms (stand alone project, and the edit form for the parent application) identically, same datasets, same connection strings. All code on both forms is auto generated by the IDE. For some reason, the standalone works fine, but this one doesn't.
It sounds more like the problem is in Visual Studio's auto-generated code. I am not familiar with this error, but I did a few Google searches and here is what I came up with: First, on a Weblog I found a post concerning this issue, the solution to which was to remove the default "Create as CLUSTERED" option on the database. You can view that solution here[^]. This does not sound like your problem, since you said it works if you create the application as its own form, meaning that the problem is probably not so much with the table structure as with Visual Studio's code generation. Concerning that: second, I found this thread which suggests deleting and recreating the data-adapter as a solution to the problem. You can see that here[^]. I hope one of these links gets you going in the right direction. I apologize that I have not been able to be of more assistance. Sincerely, Alexander Wiseman [EDIT] For the second link, see post number 3 on the page - that is the one to which I was referring. [/EDIT]
-
It sounds more like the problem is in Visual Studio's auto-generated code. I am not familiar with this error, but I did a few Google searches and here is what I came up with: First, on a Weblog I found a post concerning this issue, the solution to which was to remove the default "Create as CLUSTERED" option on the database. You can view that solution here[^]. This does not sound like your problem, since you said it works if you create the application as its own form, meaning that the problem is probably not so much with the table structure as with Visual Studio's code generation. Concerning that: second, I found this thread which suggests deleting and recreating the data-adapter as a solution to the problem. You can see that here[^]. I hope one of these links gets you going in the right direction. I apologize that I have not been able to be of more assistance. Sincerely, Alexander Wiseman [EDIT] For the second link, see post number 3 on the page - that is the one to which I was referring. [/EDIT]
YOU ARE MY HERO! I removed the clustered keys option from the tables I'm trying to manipulate and viola! THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!! You dont understand how long I've had this problem and nobody in my department has been able to even point me in the right direction. I can finally get this project turned in this quarter! Woohoo!
-
YOU ARE MY HERO! I removed the clustered keys option from the tables I'm trying to manipulate and viola! THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!! You dont understand how long I've had this problem and nobody in my department has been able to even point me in the right direction. I can finally get this project turned in this quarter! Woohoo!
Excellent, I'm glad it worked! I know how you feel about being stuck on a problem for awhile :cool: Sincerely, Alexander Wiseman