By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,156 Members | 1,022 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,156 IT Pros & Developers. It's quick & easy.

SQLDatasource/StoredProc/Dataview Update Command Q..

P: n/a
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


Nov 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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>
Nov 19 '05 #2

P: n/a
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

Nov 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.