[RESOLVED] SqlDataSource Update and Delete commands fail

[Working.Net]

Can anyone tell me where I am going wrong? I have a simple gridview. I can view the data and I have gotten the insert to work (code behind).

Dim vRepairId As String = Request.QueryString("REPAIRID")
SqlDS_WOAccount.InsertParameters("WorkOrderID").DefaultValue = vRepairId
SqlDS_WOAccount.Insert()

But I can’t get the delete and update command commands to work. It is like the SQL fails at time of execution, and I am not sure how to trap the error that might tell me what is going on. Any suggestions would be much appreciated. I’ve tried finding the answer for about a day now. Code below:

<asp:GridView ID="GridViewAccount" runat="server" AutoGenerateColumns="False" 
	BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" 
	CellPadding="3" DataSourceID="SqlDS_WOAccount" Font-Size="8pt" Width="100%">
	<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
	<Columns>
		<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" >
		<ItemStyle HorizontalAlign="Center" Width="12%" />
		</asp:CommandField>
		<asp:BoundField DataField="WOAcctDesc" HeaderText="Account Description" 
			SortExpression="WOAcctDesc" />
		<asp:BoundField DataField="WOAcctNumber" HeaderText="Account Number" 
			SortExpression="WOAcctNumber">
		<ItemStyle Width="38%" HorizontalAlign="Right" />
		</asp:BoundField>
	</Columns>
	<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
	<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
	<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
	<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
	<AlternatingRowStyle BackColor="#F7F7F7" />
</asp:GridView>

<asp:SqlDataSource ID="SqlDS_WOAccount" runat="server" 
	ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
	SelectCommand="SELECT [WorkOrderAcctID], [WOAcctNumber], [WOAcctDesc], [WorkOrderID] FROM [WOAcct] WHERE ([WorkOrderID] = @WorkOrderID)" 
	DeleteCommand="DELETE FROM [WOAcct] WHERE [WorkOrderAcctID] = @WorkOrderAcctID" 
	InsertCommand="INSERT INTO [WOAcct] ([WorkOrderID], [WOAcctDesc]) VALUES (@WorkOrderID, 'Select Account')" 
	UpdateCommand="UPDATE [WOAcct] SET [WOAcctNumber] = @WOAcctNumber, [WOAcctDesc] = @WOAcctDesc WHERE [WorkOrderAcctID] = @WorkOrderAcctID">
	<SelectParameters>
		<asp:ControlParameter ControlID="txtWtrSysRepairID" Name="WorkOrderID" 
			PropertyName="Text" Type="Int32" />
	</SelectParameters>
	<DeleteParameters>
		<asp:Parameter Name="WorkOrderAcctID" Type="Int32" />
	</DeleteParameters>
	<UpdateParameters>
		<asp:Parameter Name="WOAcctNumber" Type="String" />
		<asp:Parameter Name="WOAcctDesc" Type="String" />
		<asp:Parameter Name="WorkOrderAcctID" Type="Int32" />
	</UpdateParameters>
	<InsertParameters>
		<asp:Parameter Name="WorkOrderID" Type="Int32" />
	</InsertParameters>
</asp:SqlDataSource>

Thanks

[motil]

did you tried to set breakpoint ?

and a little advice: do not use the datasource control, move to ADO.NET and write your own query's

[sapator]

Especially for deletecommand, sometimes it will carry all your other commandparameters with it.
My advice is to try to pass the parameters on sqlds_updating and deleting events and also review that the correct values are in there and no extra parameters have been carried in.
a little correction to motil (sorry beautiful ) Sqldatasource can take your "own queries" or sp's.
Also there is some things in asp.net that is very hard to accomplish without an sqldatasource or at least DAL. P.E. optimistic concurrency is a real pain in the...And believe me i'm the one that will always find excuses to go to sql server sp's and not lame DAL cuz sp's are da one () but in asp.net it's a pain.In standard winforms i'm in love with my sql server sp's, the destroyer of DAL,etc,etc.

[gep13]

First things first...

Have you taken the delete query that you are using and executed it directly against the database? Does it work?

This is always the first thing that you should check as you are then able to know whether there is a problem with the query, or whether there is a problem with the code.

Secondly, I fully agree with motil (), SqlDataSources are fine for quick, proof of concept applications, but in my opinion, they have no place in a real world application. If you can, I would suggest that you move away from using them as quickly as possible.

Gary

[Working.Net]

If I plug in the SQL used in the data source and substitute the parameter with an appropriate record ID (in SQL Server) it deletes the record. ("DELETE FROM [WOAcct] WHERE [WorkOrderAcctID] = @WorkOrderAcctID" - where @WorkOrderAcctID would be substituted with 401) The thing I realized in this discussion is that both the DELETE and UPDATE commands rely on this @WorkOrderAcctID parameter which "I think" is managed internally in the SQL DataSource. Quite possibly I have missed something and this record_ID is not being captured properly.

On to the ODO.Net. "Oh Master Po, why does it always seem that when I think I have mastered technique you tell me that I must forget what I have learned and start over?" In any case, based on the comments of motil and Gary I might have to succumb to the forces of ADO.Net. Would either of you be able to recommend a good tutorial?

[gep13]

There are plenty out there on the interweb, a couple starting points you will find in my signature.

Bottom line is, the SqlDataSource is easy to use, so it is easy to write tutorials for it, however, that doesn't mean that it is best practice.

Gary

[sapator]

Fine, fine don't listen to little ol sapator
But i would not suggest simple ADO.NET (meaning not using DAL) for internet apps that relay on source controls and concurrency.
And if your app is a simple one use the sqldatasource.Yes use it.I wouldn't use it to wash my feet in a winforms app but here if you want something simple i don't see any harm at all.

[gep13]

To an extent, I agree with you Sap, but the question is, where do you draw the line in the sand?

Your website might start out small, and the SqlDataSource is a nice fit, however, what happens if your website explodes, and over night you get 10,000 users?!? Your site won't scale, and you will then be fighting trying to get things back up and running.

If you plan things correctly for the outset, you will be in a better position moving forward.

Gary

[motil]

Hi, SqlDataSoruce is great form small websites but nothing more then that, for medium to large site i always go with the n-layer approach + SP (thanks Mend & Gary )

[sapator]

Yep Gary you have a point.I guess sqldatasource would be easy to use on apps inside large companies for internal project(you will not get more that 10-20 users at a time).
But,i guess if i get 10000 users then there are other parameters to consider.
Possibly the site will not be saved by DAL or even by direct SQL "server-like" design.You have to consider cpu,memory,raid,net speed access,expanding the databases,cutting the db's according to user credentials etc.
I haven't got to an extent of manipulating something that requires 10000 at a time and if i get that far then probably i would require and expert sql creator and expert .net creator and an expert admin-hardware manipulator.
So in theory yes don't use sqldatasource if you are planning to expand but in practice it really doesn't make any difference for most of the apps.
Of course i may be wrong cuz i'm new in all the asp.net stuff but if i take it to winform wise then probably 10000 users would require pure sp's and DAL would be a major disaster.
So,yes, plan ahead but be realistic.

[Working.Net]

OK, I have spent an afternoon familiarizing myself with ADO, and though I probably have a really long way to go I find myself spending a lot of time fiddling with record indexes and the like, and programmatically populating form fields. I am hoping that this more a function of the exercise that I am going through than a full ADO.NET experience.

None the less, looking at your above discussion and visualizing our user’s needs which are: Few users, I am thinking on average less than 100 at a time – probably a lot less; lots of data tables with relatively few records – usually no more then, let’s say 10,000 to 15,000 most often 1,000 to 4,000. Now I am looking at this and thinking if I have to program all that functionality: go to next record; go to previous record; Insert Record; Delete Record; record counters; well you get the picture; for each of those tables the SqlDataSources are beginning to look awfully attractive.

So for the sake of hopefully ending this debate, motil, Gary and sapator, given the above mentioned application constraints should I be working with ADO.NET or SQL Data Sources?

[motil]

In my opinion for this type of project you can go with SqlDataSource but if you looking for best practice and doing things right I suggest you still stick to ADO.NET, ADO.NET combined with custom classes for representing your data tables will make your code much more readable. again, if you just want to get it done and move on just choose the quickest way, but if what you looking is to improve your skills I even suggest you to take a break from your project and practice ADO.NET and the related subjects, it might looks kinda hard at first but in the long run its really worth it.

I had really big project that i worked on for almost a year and in one of my posts (http://www.vbforums.com/showthread.php?t=595038) Gary and Mend mentioned the n-tier approach which i really liked, i pushed my project and Refactor the all thing (few months of work) to match the n-tier approach. it was hard work and a big delay for my project release, but it was 100% worth it.

just my thoughts

Good Luck

[sapator]

15,000 rows is nothing.
Sqldatasource as i've said would be ok for your project.
Leave n-tier for the end.I rarely use it so i cannot comment much.
If you are not only on asp.net but in winforms also....Well correction, in any approach learn to program on the sql server because any method of data manipulation DAL,n-tier lists, sqldatasource, in general, ado.net will became easier if you create a good sp,view,trigger whatever on the sql.Then there much less need for .net programming.Well except n-tier .Also any new method that may come it will still relay on sql language,so....Of course i would suggest mysql (for companies that don't want to pay sql server licenses) and i would not comment on Oracle as i haven't used it much.

[gep13]

I had really big project that i worked on for almost a year and in one of my posts (http://www.vbforums.com/showthread.php?t=595038) Gary and Mend mentioned the n-tier approach which i really liked, i pushed my project and Refactor the all thing (few months of work) to match the n-tier approach. it was hard work and a big delay for my project release, but it was 100% worth it.

OMG, I totally remember that thread!!

[gep13]

So for the sake of hopefully ending this debate, motil, Gary and sapator, given the above mentioned application constraints should I be working with ADO.NET or SQL Data Sources?

Hmm, I think I am going to have to throw a spanner in the works, and say that I would still be inclined to leave SqlDataSources on the bench. This dislike (some would call it hatred) of SqlDataSources, stems from the fact that I have used them in the past, and become more and more frustrated by what they can and can't do. I am trying to impart on you the hard lesson that I have learnt, and the realization that the approaches described in the thread that motil linked to, will serve you far better in the long run.

That is just my opinion, I am not forcing you to do anything, at the end of the day, the decision is still yours.

Gary

[Working.Net]

Thanks, this has been very interesting and helpful. Thanks also due to your input, this is what I decided to do with this issue. Since I only have 3 weeks left to complete this project, and I think I am well within reach of that goal, and since, based on your comments, it may take a while to wrap my head around the ADO.NET animal I am going to try to solve the problem as posted. I will take your advice under serious advisement and at a minimum learn to work with ADO.NET. But that will have to wait a few weeks.

Back to the original question. I have been able to determine that the problem lies with the @WorkOrderAcctID variable. If I hard code a record id in the SQL statements for both the DELETE and the UPDATE commands like so:

<asp:SqlDataSource ID="SqlDS_WOAccount" runat="server" 
	ConnectionString="<&#37;$ ConnectionStrings:MyConnectionString %>" 
	SelectCommand="SELECT [WorkOrderAcctID], [WOAcctNumber],
		[WOAcctDesc], [WorkOrderID] 
		FROM [WOAcct] WHERE ([WorkOrderID] = @WorkOrderID)" 
	DeleteCommand="DELETE FROM [WOAcct] 
		WHERE ([WorkOrderAcctID] = 400)" 
	InsertCommand="INSERT INTO [WOAcct] ( [WOAcctDesc],[WorkOrderID])
		VALUES ('Select Account', @WorkOrderID)" 
	UpdateCommand="UPDATE [WOAcct] SET 
		[WOAcctNumber] = @WOAcctNumber, 
		[WOAcctDesc] = @WOAcctDesc 
		WHERE ([WorkOrderAcctID] = 401)">

it works. So somewhere I am loosing the record reference. If any of you could take a quick look at the original code posed in this thread and see if I have missed anything obvious that would be great. Thanks so much for your help and comments.

[motil]

can you post the update and delete code ?

one thing that might wroth mention is that in the SELECT and INSERT you use this parameter:

@WorkOrderID

and in the UPDATE and DELETE you use this :

@WorkOrderAcctID

I'm not sure this is the problem but i thought you might want to check it.

[Working.Net]

motil,

This is a relational setup, thus I want to see all the records in the Account table where the WorkOrderID matches the current WorkOrder record. Same with insert, since the WorkOrderAcctID field has Identity Specification turned on, when I create a new record all I need to do is provide the appropriate WorkOrder record ID. When I am performing a DELETE or an UPDATE I am dealing specifically with the Account record, thus we have the WorkOrderAcctID or account record ID

As far as the Update and Delete code goes, that you requested, I am assuming that you would like to see what is under the hood (or the code behind the scenes) Unfortunately I am not sure how to get to that since it is all part of the SqlDataObject. I guess that that is both the beauty and curse of using objects you don't really understand.

[motil]

i meant the code where you execute the update/delete, like you showed us the insert code

Dim vRepairId As String = Request.QueryString("REPAIRID")
SqlDS_WOAccount.InsertParameters("WorkOrderID").DefaultValue = vRepairId
SqlDS_WOAccount.Insert()

did you set breakpoint and make sure that the value is correct?

[Working.Net]

Yes,
Like I said, that is code that comse with the ASP:SqlDataSource Object. I do not know how to get to that. I have included the client side ASP so you can see what that looks like, but the Java Script functions __doPostBack('GridViewAccount$ctl03$ctl00','') and __doPostBack('GridViewAccount', 'Cancel$1')" were generated by ASP.NET

<td align="center" style="width:12&#37;;">
	<a href="javascript:__doPostBack('GridViewAccount$ctl03$ctl00','')" 
		style="color:#4A3C8C;">Update</a>&nbsp;
	<a href="javascript:__doPostBack('GridViewAccount','Cancel$1')" 
		style="color:#4A3C8C;">Cancel</a>
</td>

[motil]

Well i'm sorry, i have very little experience working with SqlDataSource (as might already understand)
but I'm sure you need to set somewhere in your code the parameter of the record id you want to update / delete (most chances this is the reason things not working for you).

Gary might be able help you more then me.

[Working.Net]

Thanks motil,

It is interesting because I have three other sqlData sources pointing to different tables but set up almost identically, and 3 other gridview linked to these data sources and they work just fine. I have compared all the elements, the data sources, the SQL tables and I have not been able to find any discernable differences. I have completely replaced the Account Gridview, and SqlDataSource and I still get the same problem. I am thinking perhaps I should try recreating the table and try again. My fear is that I do something, inadvertently, find that "all of a sudden" it works and then not knowing what it is that fixed the problem. I'll see if Gary or someone else might have any ideas. Have a great night (should be after work for you)

[motil]

Ok, Just remember the Record ID parameter must be coming from somewhere try to find out where did you set it in the other working SqlDataSources, oh and don't forget move to ADO.NET

[Working.Net]

I will, LOL

[gep13]

I seem to have missed a few posts here...

Let me try and have a look at this tomorrow morning, and I will post back if I find anything.

Gary

[Working.Net]

Thanks

[sapator]

"Just remember the Record ID parameter must be coming from somewhere"
That is the crucial thing as motil said.
Where is the value that you want to set is coming from?
The gridview?Another function?A combobox?
What i can say to you is that if you have the value then you have 2 main options.
1)Pass it on the sqldatasource page automatically.
2)Use SqlDS_WOAccount_Updating or SqlDS_WOAccount_deleting events to pass the parameters (with "e").
But the basic thing is to know where and how you get the value.

[gep13]

Okay, this could be going off on a tangent, but it is something that is worth looking into...

Can you check your other GridViews in your other applications and check for the DataKeys property in the GridView definition. Is there one? You don't have one in the definition that you have posted in your first post.

This could be the critical item that you are missing.

Gary

[Working.Net]

Finally, that was it. The DataKeyName property in all of the other Gridviews was set to the appropriate record ID, but that of the problem GridView was left blank. I should have caught that.

Thanks for your help. Thanks also to motil and sapator for theirs

[gep13]

Woo hoo, I was hoping that was it, because if it wasn't, I didn't have any other ideas

Gary

[Working.Net]

I am finding more and more that the solutions in VB.NET, to what sometimes seem to be insurmountable challenges, are often dead simple and are solved with a couple of lines of code or, as is the case here, a single property. It is disappointing that it often takes days to find such a simple solution. But thanks to your help and this forum I need not search any longer - On to the next quest.

[gep13]

And it is these little problems that keep things interesting