SQLDatasource/StoredProc/Dataview Update Command Q..

[rob lynch]

I have been trying to get a grasp on how I can use a sqlserver storedproc as
the UpdateCommand of a Dataview.

The control (datview / SqlDatasource / Edit control) works great when you
supply it with an adhoc sql statement (somthing that I dont' like to do for
many reasons) and populating the select part with a storedproc works well
also, however I have been unable to find a way to get the update to work with
a paramitized storedproc.

Any clues would be welcome.! (if it just isn't possible could someone also
post that and save me hours of bashing my head against the wall!)

Thanks

Rob

[rob lynch]

Ok.. My big problem was that I was trying to do this without the wizard (damn
I hate having to use those things :)

End code (and it works!) - the big trick probably comes from having column
names and paramters that match.

In this case UpdateInvoiceDetail had 5 parameters and they all got picked up
perfectly!

Code below......

<asp:GridView ID="gridExistingItems" Runat="server" Width="718px"
AutoGenerateColumns="False"
datasourceid="DetailsDataSource"
datakeynames="InvoiceDetailID"
runat="server">
<Columns>
<asp:BoundField DataField="InvoiceDetailID"></asp:BoundField>
<asp:BoundField DataField="Description"></asp:BoundField>
<asp:BoundField DataField="Category"></asp:BoundField>
<asp:BoundField DataField="Quantity"></asp:BoundField>
<asp:BoundField DataField="DateOfCharge"></asp:BoundField>
<asp:BoundField DataField="Amount"></asp:BoundField>
<asp:CommandField ShowSelectButton="True"></asp:CommandField>
<asp:CommandField ShowEditButton="True"></asp:CommandField>
</Columns>
</asp:GridView>

<asp:sqldatasource id="DetailsDataSource" runat="server"
SelectCommand="ListInvoiceDetails" UpdateCommand="UpdateInvoiceDetail"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>">
<UpdateParameters>
<asp:Parameter Type="Int32"
Name="InvoiceDetailId"></asp:Parameter>
<asp:Parameter Type="String" Name="Description"></asp:Parameter>
<asp:Parameter Type="String" Name="Category"></asp:Parameter>
<asp:Parameter Type="Decimal" Name="Quantity"></asp:Parameter>
<asp:Parameter Type="Decimal" Name="Amount"></asp:Parameter>
<asp:Parameter Type="DateTime"
Name="DateOfCharge"></asp:Parameter>
</UpdateParameters>
<SelectParameters>
<asp:SessionParameter Name="orderID" Type="Int32"
SessionField="OrderID"></asp:SessionParameter>
<asp:Parameter Type="Int32" DefaultValue="5"
Name="OrderCol"></asp:Parameter>
</SelectParameters>
</asp:sqldatasource>

[rob lynch]

Well obviously didn't test enough..

Found something interesting when using storedprocs..

The whole process worked until I clicked on the UPDATE button (after an
edit) thenI got a rather surprising error that my parameters didn't match...

Turned on profier and found that the website was passing a parameter I just
wasn't expecting..

@original_InvoiceDetailID

All the other paramters existed.. Includint invoicedetailid, but due to the
fact that this is/was my primary key, the original and new are exactly the
same..

The fix. I created a new parameter for my storedproc (that does nothing)
called @original_InvoiceDetailID and Bam.. it works (completely this time)
HTH

Rob