I think that I have stumbled across a bug with the SqlDataSource object. I am using the web control in a page and it is set up as follows.
<asp:SqlDataSource ID="dsFees" runat="server" ConnectionString="<%$ ConnectionStrings:LoanLevelConnectionString %>" UpdateCommand="update brokeredLoansFees set amount = CONVERT(money,@amount), feeDesc=@feeDesc where loannumber=@loannumber and feeDesc=@feeDesc" SelectCommand="SELECT * FROM [brokeredLoansFees] WHERE (([branchId] = @branchId) AND ([loanNumber] = @loanNumber))" DeleteCommand="delete from brokeredLoansFees where loannumber = @loannum and feedesc = @fdesc"> <SelectParameters> <asp:ControlParameter ControlID="txBranchNo" Name="branchId" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="ddLoans" Name="loanNumber" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> <DeleteParameters> <asp:Parameter name="loannum" Type="int32"/> <asp:Parameter Name="fdesc" Type="String" /> </DeleteParameters> </asp:SqlDataSource>
This control is the datasource for a GridView control. I have verified that the select command and the update command work perfectly. Better than I expected in fact 
The delete command though simplyu refuses to work. I had to manually put the parameters into the html, and that cleared up the whole undefined paramater error that I was getting, but it doesnt actually DO anything. I have noticed to little quirks to this. First, I had to change my paramater names in the delete command. If I used loannumber, and feedesc, as I do in the update command it would still give me the undefined parameter only by changing the parameter names was I able to get around this. Second, I notice that the explicit declaration of the parameters in HTML for the UpdateCommand are not required. It picks up the values from the GridView just fine.
Anyone have any idea whats going on here Oh and for what its worth theres no custom code being used for the updates or deletes. Its all the defaults that .Net creates.
Thanks in adavance for any and all advice!! 
Bug in 2.0 SqlDataSource Object ?
CodeDjinn
Ok. I did some more testing. I made a very simple web app. all it has is a sqlDataSource and a Gridview on it. Here is the code for it.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" style="left: 311px; position: absolute; top: 60px" Width="600px" AllowPaging="True" AllowSorting="True">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
SortExpression="id" />
<asp:BoundField DataField="text1" HeaderText="text1" SortExpression="text1" />
<asp:BoundField DataField="text2" HeaderText="text2" SortExpression="text2" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=xxxx;Initial Catalog=test;User ID=xxxxx;Password=xxxxx"
ProviderName="System.Data.SqlClient"
DeleteCommand="delete from [test1] where id = @id"
InsertCommand="insert into [test1] ( text1, text2 ) values (@text1, @text2)"
UpdateCommand="update [test1] set text1 = @text1, text2 = @text2"
SelectCommand="SELECT * FROM [test1] " >
<InsertParameters>
<asp:Parameter Name="@text1" Type="String" />
<asp:Parameter Name="@text2" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="@text1" Type="String" />
<asp:Parameter Name="@text2" Type="String" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="id" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
And here is the code behind page.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataBind();
}
}
There is 1 row in table test1. That row is displayed just fine. When I use the Edit link in the DatagridView I am able to edit the fields just fine. When I then click on update, nothing happens, and the values in text1 and text2 revert back to the values that they were prior to editing.
When I click on the delete link I get an error stating
Server Error in '/Test' Application.
--------------------------------------------------------------------------------
Must declare the variable '@id'. - along with a full stack trace.
These are the 2 errors that I am seeing in my project at work.
Somthing I noticed however. When I configure the data source after I specify the select SQL there is a displayed and this page has a button titled "Advanced" when I press this button I see that there are options to automatically create Insert Delete and Update commands. This option however is greyed out! The entire form is. According to the instructions at http://msdn2.microsoft.com/en-us/library/ms247242.aspx the advanced button is supposed to create these command for me. In part it states "If you want to support insert, update, and delete operations, click Advanced, and then select the option to generate INSERT, UPDATE, and DELETE statements for your SqlDataSource control. You can also specify whether you want the commands to use optimistic concurrency checks to determine whether the data has been modified before an update or delete operation is performed." Thats exactly what I want to do.
Im realy starting to think that either I have incorrectly installed VS 2005 or I need to get a patch. As far as I know though its installed correctly and I have all the latest patches. Frankly Im at a loss as to how to proceed with this.
RussP
Having the same problem but with the select command. Im building my query dynamically in code, not specifing anything in the the apsx file:
ParameterCollection collection = sqlDatasource.SelectParameters;
bool appendAND = false;
StringBuilder sb = new StringBuilder();
collection.Clear();
sb.Append("Select ID, FirstName, LastName from person where ");
if (!string.IsNullOrEmpty(fname))
{
sb.Append("FirstName LIKE @firstname");
collection.Add("@firstname", TypeCode.String, fname+'%');
}
if (!string.IsNullOrEmpty(lname))
{
sb.Append("LastName LIKE @lastname");
collection.Add("@lastname", TypeCode.String, lname + '%');
}
sqlDatasource.SelectCommand = sb.ToString();
kumarpavan
martona
Bumping this up. I was able to resolve the issue with the Advanced Button not creating the Insert, Delete and Update commands. Basically my test table did not have a primary key and that kept the check box to create those commands greyed out.
The basic problem still remains though. The update and delete commands either generate errors that a parameter is undefined, or they dont generate an error and simply dont do anything at all.
Has anyone had any luck trying to implement this functionality