I had this error for my insert command stored procedure, and followed the directions in another thread on how to fix it, and it worked. I made sure to have the parameter names the same as the fields returned from my selectcommand stored procedure, and after fixing that, it worked great. I then created a stored procedure for the update command, and followed the same rules in having the parameter name the same as the fields returned from the select stored procedure, and I am getting this error about too many arguments specified. I am attaching my aspx code and also the code for the stored procedure. I'd appreciate any insights.
ASPX:
<telerik:RadFormDecorator ID="formDecorator1" runat="server" DecoratedControls="All" Skin="Office2007" />
<telerik:RadScriptManager ID="ScriptManager1" runat="server" EnableTheming="True" OutputCompression="AutoDetect">
</telerik:RadScriptManager>
<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server" >
<ClientEvents OnRequestStart="centerUpdatePanel();"></ClientEvents>
<AjaxSettings>
<telerik:AjaxSetting AjaxControlID="usersGrid">
<UpdatedControls>
<telerik:AjaxUpdatedControl ControlID="usersGrid" LoadingPanelID="RadAjaxLoadingPanel1" />
</UpdatedControls>
</telerik:AjaxSetting>
</AjaxSettings>
</telerik:RadAjaxManager>
<telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" Runat="server" Transparency="20" MinDisplayTime="1000" IsSticky="true" height="200" width="400px" >
<img alt="Loading..." src='<%= RadAjaxLoadingPanel.GetWebResourceUrl(Page, "Telerik.Web.UI.Skins.Default.Ajax.loading.gif") %>' style="border: 0;"/>
</telerik:RadAjaxLoadingPanel>
<asp:TextBox ID="package_cd" runat="server" Visible="False"></asp:TextBox>
<asp:TextBox ID="TextBoxAdditionalItemsPackageCdAla" runat="server" Visible="False"></asp:TextBox>
<asp:TextBox ID="TextBoxActualPackage" runat="server" Visible="False" ></asp:TextBox>
<asp:TextBox ID="temptablename" runat="server" Visible="False"></asp:TextBox>
<asp:TextBox ID="TextBoxPackagePrice" runat="server" Visible="false"></asp:TextBox>
<asp:TextBox ID="TextBoxTotalAlaCartePrice" runat="server" Visible="false"></asp:TextBox>
<asp:TextBox ID="TextBoxUserID" runat="server" Visible="false"></asp:TextBox>
<asp:TextBox ID="TextBoxInInsert" runat="server" Visible="false"></asp:TextBox>
<telerik:RadGrid ID="usersGrid" runat="server"
DataSourceID="ContentCentralUsers" GridLines="None" Skin="Office2007"
AutoGenerateColumns="False"
ondatabound="usersGrid_DataBound" onitemdatabound="usersGrid_ItemDataBound"
onitemdeleted="usersGrid_ItemDeleted" onitemevent="usersGrid_ItemDataBound"
oniteminserted="usersGrid_ItemInserted" onitemupdated="usersGrid_ItemUpdated"
onload="usersGrid_Load" ondatabinding="usersGrid_DataBinding" ShowStatusBar="True"
ondeletecommand="usersGrid_DeleteCommand" OnPreRender="usersGrid_PreRender"
oninsertcommand="usersGrid_InsertCommand1" onitemcreated="usersGrid_ItemCreated"
onpageindexchanged="usersGrid_PageIndexChanged" OnItemCommand="usersGrid_ItemCommand"
onupdatecommand="usersGrid_UpdateCommand" commanditemdisplay="TopAndBottom"
onselectedindexchanged="usersGrid_SelectedIndexChanged"
AllowAutomaticDeletes="True" AllowAutomaticInserts="True" AllowAutomaticUpdates="True" >
<MasterTableView DataSourceID="ContentCentralUsers"
AllowFilteringByColumn="True" AllowSorting="True"
InsertItemPageIndexAction="ShowItemOnFirstPage" CommandItemDisplay="Top"
NoMasterRecordsText="No users to display.">
<CommandItemSettings AddNewRecordText="Add new user" />
<RowIndicatorColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</RowIndicatorColumn>
<ExpandCollapseColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</ExpandCollapseColumn>
<Columns>
<telerik:GridEditCommandColumn ButtonType="ImageButton" UniqueName="EditCommandColumn1">
<HeaderStyle Width="20px" />
</telerik:GridEditCommandColumn>
<telerik:GridBoundColumn DataField="user_id" UniqueName="user_id" Visible="false" ReadOnly="true"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="login_name" HeaderText="Seat(User)"
SortExpression="login_name" UniqueName="login_name" >
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="password"
HeaderText="Password" SortExpression="password" AllowFiltering="false"
UniqueName="password" EditFormColumnIndex="1">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="email" HeaderText="Email Address"
SortExpression="email" UniqueName="email" >
</telerik:GridBoundColumn>
<telerik:GridTemplateColumn HeaderText="Package" UniqueName="RadComboPackage" EditFormColumnIndex="1" >
<HeaderStyle Width="100%" />
<ItemTemplate>
<asp:Label runat="server" ID="Label1" Text='<%#Eval("package_name") %>'></asp:Label>
<telerik:RadToolTip runat="server" ID="RadToolTip1" TargetControlID="Label1"
Skin="Office2007" ShowDelay="1000" AutoCloseDelay="200000" Font-Names="arial"
Font-Size="XX-Large" BorderStyle="Inset" HideDelay="100000" ManualClose="False"
Position="BottomRight" Sticky="True" Width="300">
<asp:Label runat="server" ID="Label3" Text='<%#Eval("descriptions") %>'></asp:Label><br/>
</telerik:RadToolTip>
</ItemTemplate>
<EditItemTemplate>
<telerik:RadComboBox ID="RadComboBox1" Width="220px" runat="server" OnSelectedIndexChanged="RadComboBoxSelectedIndexChanged"
MarkFirstMatch="True" AllowCustomText="True" DataSourceID="ContentCentralPackages" EmptyMessage="Select a package"
ExpandAnimation-Type="None" CollapseAnimation-Type="None" AutoPostBack="true" AppendDataBoundItems="false"
DataTextField="package_name" DataValueField="package_cd" DropDownWidth="650px"
Skin="Office2007" SelectedValue='<%# Bind("package_cd") %>'
EnableVirtualScrolling="True" HighlightTemplatedItems="True">
<ExpandAnimation Type="None" />
<CollapseAnimation Duration="200" Type="None" />
<HeaderTemplate>
<table style="width: 620px; text-align: left">
<tr>
<td style="width: 170px;">
Package Name
</td>
<td style="width: 450px;" >
Description
</td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table style="width: 620px; text-align: left">
<tr>
<td style="width: 170px;" valign="top">
<%# DataBinder.Eval(Container.DataItem, "package_name") %>
</td>
<td style="width: 450px;">
<%# DataBinder.Eval(Container.DataItem, "descriptions") %>
</td>
</tr>
</table>
</ItemTemplate>
</telerik:RadComboBox>
</EditItemTemplate>
</telerik:GridTemplateColumn>
<telerik:GridMaskedColumn DataField="packageprice" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Package Price" Display="true" UniqueName="packageprice"
readonly="true" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
<telerik:GridMaskedColumn DataField="packagealaprice" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Ala Carte Price" Display="true" UniqueName="packagepriceala"
readonly="true" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
<telerik:GridButtonColumn ButtonType="ImageButton" CommandName="Delete"
ConfirmDialogType="RadWindow" Text="Delete" UniqueName="column"></telerik:GridButtonColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" HeaderStyle-VerticalAlign="Top" Visible="false">
<EditItemTemplate>
<telerik:RadGrid ID="PackagePriceGrid" runat="server" AutoGenerateColumns="False"
DataSourceID="spAddNewsTradeProduct" GridLines="None" Skin="Office2007"
ondatabound="PackagePriceGrid_DataBound" onitemdatabound="PackagePriceGrid_ItemDataBound"
onload="PackagePriceGrid_Load" ondatabinding="PackagePriceGrid_DataBinding" ShowStatusBar="True"
OnPreRender="PackagePriceGrid_PreRender" >
<MasterTableView CommandItemDisplay="None" >
<Columns>
<telerik:GridBoundColumn HeaderText="Service ID" UniqueName="service_id" DataField="service_id"
Visible="false" Display="false"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="service_name" HeaderText="Default Package" Display="true"
UniqueName="service_name" ></telerik:GridBoundColumn>
<telerik:GridTemplateColumn HeaderText="Entitled" UniqueName="TemplateColumn">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Eval("Checked") %>' AutoPostBack="true"
EditFormColumnIndex="1" OnCheckedChanged="cbCheckedChangedDefaultPackage"/>
</ItemTemplate>
</telerik:GridTemplateColumn >
<telerik:GridMaskedColumn DataField="Price" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Price" Display="false" UniqueName="Price"
EditFormColumnIndex="2" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
</Columns>
<EditFormSettings ColumnNumber="5">
</EditFormSettings>
<PagerStyle AlwaysVisible="True" />
</MasterTableView>
</telerik:RadGrid>
</EditItemTemplate>
<HeaderStyle VerticalAlign="Top"></HeaderStyle>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" HeaderStyle-VerticalAlign="Top" Visible="false">
<EditItemTemplate>
<telerik:RadGrid ID="PackageReadOnlyGrid" runat="server" AutoGenerateColumns="False" Visible="false"
DataSourceID="spAddNewsTradeProduct" GridLines="None" Skin="Office2007"
ondatabound="PackagePriceGrid_DataBound" onitemdatabound="PackageReadOnlyGrid_ItemDataBound"
onload="PackagePriceGrid_Load" ondatabinding="PackagePriceGrid_DataBinding" ShowStatusBar="True"
OnPreRender="PackagePriceGrid_PreRender" >
<MasterTableView CommandItemDisplay="None" >
<Columns>
<telerik:GridBoundColumn HeaderText="Service ID" UniqueName="service_id" DataField="service_id"
Visible="false" Display="false"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="service_name" HeaderText="Default Package" Display="true"
UniqueName="service_name" ></telerik:GridBoundColumn>
<telerik:GridCheckBoxColumn HeaderText="Entitled" UniqueName="CHECKED" DataField="Checked" EditFormColumnIndex="1">
</telerik:GridCheckboxColumn >
<telerik:GridMaskedColumn DataField="Price" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Price" Display="false" UniqueName="Price"
EditFormColumnIndex="2" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
</Columns>
<EditFormSettings ColumnNumber="5">
</EditFormSettings>
<PagerStyle AlwaysVisible="True" />
</MasterTableView>
</telerik:RadGrid>
</EditItemTemplate>
<HeaderStyle VerticalAlign="Top"></HeaderStyle>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" HeaderStyle-VerticalAlign="Top" Visible="false" EditFormColumnIndex="1">
<EditItemTemplate>
<telerik:RadGrid ID="AlaCartePriceGrid" runat="server" AutoGenerateColumns="False"
DataSourceID="spAddNewsTradeProductAlaCarte" GridLines="None" Skin="Office2007"
ondatabound="AlaCartePriceGrid_DataBound" onitemdatabound="AlaCartePriceGrid_ItemDataBound"
onload="AlaCartePriceGrid_Load" ondatabinding="AlaCartePriceGrid_DataBinding" ShowStatusBar="True"
OnPreRender="AlaCartePriceGrid_PreRender" >
<MasterTableView CommandItemDisplay="None" >
<Columns>
<telerik:GridBoundColumn HeaderText="Service ID" UniqueName="service_id" DataField="service_id"
Visible="false" Display="false"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="service_name" HeaderText="Available a la carte Content" Display="true"
UniqueName="service_name" ReadOnly="true" ></telerik:GridBoundColumn>
<telerik:GridTemplateColumn HeaderText="Entitled" UniqueName="CHECKED">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Eval("Checked") %>' AutoPostBack="true" OnCheckedChanged="cbCheckChangedAlaCarteEntitled"
EditFormColumnIndex="1" />
</ItemTemplate>
</telerik:GridTemplateColumn >
<telerik:GridTemplateColumn HeaderText="Price" UniqueName="Price" >
<ItemTemplate>
<telerik:RadNumericTextBox ID="RadNumericTextBox1" runat="server" AutoPostBack="true" DbValue='<%#Eval("Price") %>'
Culture="English (United States)" Type="Currency" Value="0" Width="125px" OnTextChanged="RadNumericTextBox1_TextChanged"
EditFormColumnIndex="2" >
</telerik:RadNumericTextBox>
</ItemTemplate>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn HeaderText="Incl. Inv" UniqueName="InclInv">
<ItemTemplate>
<asp:CheckBox ID="InclInv" runat="server" Checked='<%# Eval("InvoiceChecked") %>' AutoPostBack="true" OnCheckedChanged="cbCheckedChangedAlaCarteInvoice"
EditFormColumnIndex="3" />
</ItemTemplate>
</telerik:GridTemplateColumn >
</Columns>
<EditFormSettings ColumnNumber="5">
</EditFormSettings>
<PagerStyle AlwaysVisible="True" />
</MasterTableView>
</telerik:RadGrid>
</EditItemTemplate>
<HeaderStyle VerticalAlign="Top"></HeaderStyle>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" HeaderStyle-VerticalAlign="Top" Visible="false" EditFormColumnIndex="1">
<EditItemTemplate>
<telerik:RadGrid ID="AlaCarteReadOnlyGrid" runat="server" AutoGenerateColumns="False" Visible="false"
DataSourceID="spAddNewsTradeProductAlaCarte" GridLines="None" Skin="Office2007"
ondatabound="AlaCartePriceGrid_DataBound" onitemdatabound="AlaCarteReadOnlyGrid_ItemDataBound"
onload="AlaCartePriceGrid_Load" ondatabinding="AlaCartePriceGrid_DataBinding" ShowStatusBar="True"
OnPreRender="AlaCartePriceGrid_PreRender" >
<MasterTableView CommandItemDisplay="None" >
<Columns>
<telerik:GridBoundColumn HeaderText="Service ID" UniqueName="service_id" DataField="service_id"
Visible="false" Display="false"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="service_name" HeaderText="Available a la carte Content" Display="true"
UniqueName="service_name" ReadOnly="true" ></telerik:GridBoundColumn>
<telerik:GridCheckBoxColumn HeaderText="Entitled" UniqueName="CHECKED" DataField="Checked" EditFormColumnIndex="1">
</telerik:GridCheckboxColumn >
<telerik:GridMaskedColumn DataField="Price" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Price" Display="true" UniqueName="Price"
EditFormColumnIndex="2" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
</Columns>
<EditFormSettings ColumnNumber="5">
</EditFormSettings>
<PagerStyle AlwaysVisible="True" />
</MasterTableView>
</telerik:RadGrid>
</EditItemTemplate>
<HeaderStyle VerticalAlign="Top"></HeaderStyle>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" Visible="false" >
<EditItemTemplate>
<asp:Label runat="server" Text="* Prices are monthly"></asp:Label>
</EditItemTemplate>
</telerik:GridTemplateColumn>
</Columns>
<EditFormSettings ColumnNumber="5">
<EditColumn ButtonType="ImageButton" UniqueName="EditCommandColumn1" EditFormColumnIndex="5">
</EditColumn>
</EditFormSettings>
<CommandItemStyle BorderStyle="Solid" />
</MasterTableView>
<FilterMenu EnableTheming="True">
<CollapseAnimation Type="OutQuint" Duration="200"></CollapseAnimation>
</FilterMenu>
</telerik:RadGrid>
<asp:SqlDataSource ID="ContentCentralUsers" runat="server"
ConnectionString="<%$ ConnectionStrings:ContentCentralConnectionString %>"
SelectCommand="isp_sbLoadUserData"
InsertCommand="isp_sbInsertNewsTradePkgs"
UpdateCommand="isp_sbUpdateNewsTradePkgsSave"
SelectCommandType="StoredProcedure"
InsertCommandType="StoredProcedure"
UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="SAPID" QueryStringField="SAPID" Type="String" />
<asp:QueryStringParameter Name="sbODID" QueryStringField="sbODID" Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:ControlParameter ControlID="temptablename" Name="temptablename" PropertyName="Text" Type="String" />
<asp:QueryStringParameter Name="SAPID" QueryStringField="SAPID" Type="String" />
<asp:ControlParameter ControlID="package_cd" Name="package_cd" PropertyName="Text" Type="String" />
<asp:Parameter Name="login_name" Type="String" />
<asp:Parameter Name="password" Type="String" />
<asp:Parameter Name="email" Type="String" />
<asp:QueryStringParameter Name="sbOrderID" QueryStringField="sbOID" Type="Int32" />
<asp:QueryStringParameter Name="sbProductID" QueryStringField="sbproductid" Type="Int32" />
</InsertParameters>
<UpdateParameters>
<asp:ControlParameter ControlID="temptablename" Name="temptablename" PropertyName="Text" Type="String" />
<asp:QueryStringParameter QueryStringField="sbODID" Name="sbODID" Type="Int32" />
<asp:Parameter Name="user_id" Type="Int32" />
<asp:ControlParameter ControlID="package_cd" Name="package_cd" PropertyName="Text" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="ContentCentralPackages" runat="server"
ConnectionString="<%$ ConnectionStrings:ContentCentralConnectionString %>"
SelectCommand="SELECT package_name, descriptions, package_id, package_cd, mid_id FROM PACKAGES WHERE (pkg_type = 'NT') OR (pkg_type = 'NEV8')" >
</asp:SqlDataSource>
<asp:SqlDataSource ID="spAddNewsTradeProduct" runat="server"
ConnectionString="<%$ ConnectionStrings:ContentCentralConnectionString %>"
SelectCommand="isp_sbUpdateNewsTradeProduct"
SelectCommandType="StoredProcedure" >
<SelectParameters>
<asp:ControlParameter ControlID="temptablename" DefaultValue=""
Name="temptablename" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="package_cd" DefaultValue="NTB"
Name="package_cd" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="spAddNewsTradeProductAlaCarte" runat="server"
ConnectionString="<%$ ConnectionStrings:ContentCentralConnectionString %>"
SelectCommand="isp_sbUpdateNewsTradeProduct"
SelectCommandType="StoredProcedure" >
<SelectParameters>
<asp:ControlParameter ControlID="temptablename" Name="temptablename"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxAdditionalItemsPackageCdAla"
DefaultValue="NTBALA" Name="package_cd" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
Stored Procedure:
USE [ContentCentral]
GO
/****** Object: StoredProcedure [dbo].[isp_sbUpdateNewsTradePkgsSave] Script Date: 10/04/2008 19:41:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[isp_sbUpdateNewsTradePkgsSave]
@temptablename nvarchar(200),
@sbODID int,
@user_id int,
@package_cd nvarchar(20)
AS
set nocount on
declare
@sqlstring1 nvarchar(1000),
@sqlstring2 nvarchar(1000),
@database nvarchar(20),
@usercount int,
@quote nvarchar(1),
@ODIDOrig int,
@ODIDPkg int,
@ODIDPkgALA int,
@strODIDPkg nvarchar(12),
@strODIDPkgALA nvarchar(12),
@package_id int,
@package_name nvarchar(100),
@package_cdala nvarchar(20), --NTBALA etc
@package_price numeric(8,2),
@package_mid_id int,
@mid_type_id int,
@mid_type_name nvarchar(50),
@defaultenddate varchar(10),
@billing_freq char(1),
@billing_freqala char(1),
@sumpriceodid numeric(8,2),
@sumpricealaodid numeric(8,2),
@sumpriceuser numeric(8,2),
@sumpricealauser numeric(8,2),
@acvprice numeric(8,2),
@acvpriceala numeric(8,2)
select @defaultenddate = '01/01/1900'
select @database = 'Temporary.dbo.'
select @quote = char(39)
select @package_cdala = @package_cd + 'ALA'
select @temptablename = @database + @temptablename
/************************************************************************************************************/
select @ODIDPkg = @sbODID
select @ODIDPkgALA = isnull(convert(int,rtrim(CUSTOMFLD4)),0) from SPSData.dbo.sbOrderDetail where sbOrderDetailID = @ODIDPkg
select @strODIDPkg = convert(nvarchar,@ODIDPkg)
select @strODIDPkgALA = convert(nvarchar,@ODIDPkgALA)
select @database = 'Temporary.dbo.'
-- Step 2b for package_cd data..Get come local vars next .. may not need all of these but get them for now...
select @package_mid_id = mid_id from ContentCentral.dbo.MODULE_IDS where short_name = @package_cd
select @mid_type_id = mid_type_id from ContentCentral.dbo.MODULE_IDS where short_name = @package_cd
select @mid_type_name = mid_type_name from ContentCentral.dbo.MID_TYPES where mid_type_id = @mid_type_id
select @package_id = package_id from ContentCentral.dbo.PACKAGES where package_cd = @package_cd
select @package_name = package_name from ContentCentral.dbo.PACKAGES where package_cd = @package_cd
select @package_price = asking_price from ContentCentral.dbo.PRICES where mid_id = @package_mid_id
select @billing_freq = billing_freq from ContentCentral.dbo.PRICES where mid_id = @package_mid_id
-- First get the NT_NEV8_USER_MIDS package header information
update n
set n.mid_id = @package_mid_id,
n.mid_type_name = @mid_type_name,
n.start_date = getdate(),
n.end_date = @defaultenddate,
n.price = @package_price,
n.last_changed_by = 'tbd',
n.date_last_changed = getdate(),
n.DisplayonInvoice = 1
from ContentCentral.dbo.NT_NEV8_USER_MIDS n
where n.sbOrderDetailID = @ODIDPkg and n.[user_id] = @user_id
delete from ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent where sbOrderDetailID = @ODIDPkg and [user_id] = @user_id
select @sqlstring1 = 'insert into ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent(mid_id,package_mid_id,sbOrderDetailID,[user_id],price,date_created,end_date,date_last_changed,DisplayonInvoice) select mid_id,' + str(@package_mid_id) + ',' + @strODIDPkg + ',' + str(@user_id) + ',Price,getdate(),' + @quote + @defaultenddate + @quote + ',getdate(),InvoiceChecked from ' + @temptablename + ' where Checked > 0 and package_cd = ' + @quote + @package_cd + @quote
exec(@sqlstring1)
-- Step 2b for package_cd data..Get come local vars next .. may not need all of these but get them for now...
select @package_mid_id = mid_id from ContentCentral.dbo.MODULE_IDS where short_name = @package_cdala
select @mid_type_id = mid_type_id from ContentCentral.dbo.MODULE_IDS where short_name = @package_cdala
select @mid_type_name = mid_type_name from ContentCentral.dbo.MID_TYPES where mid_type_id = @mid_type_id
select @package_id = package_id from ContentCentral.dbo.PACKAGES where package_cd = @package_cdala
select @package_name = package_name from ContentCentral.dbo.PACKAGES where package_cd = @package_cdala
select @package_price = asking_price from ContentCentral.dbo.PRICES where mid_id = @package_mid_id
select @billing_freqala = billing_freq from ContentCentral.dbo.PRICES where mid_id = @package_mid_id
update n
set n.mid_id = @package_mid_id,
n.mid_type_name = @mid_type_name,
n.start_date = getdate(),
n.end_date = @defaultenddate,
n.price = @package_price,
n.last_changed_by = 'tbd',
n.date_last_changed = getdate(),
n.DisplayonInvoice = 1
from ContentCentral.dbo.NT_NEV8_USER_MIDS n
where n.sbOrderDetailID = @ODIDPkgALA and n.[user_id] = @user_id
delete from ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent where sbOrderDetailID = @ODIDPkgALA and [user_id] = @user_id
select @sqlstring1 = 'insert into ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent(mid_id,package_mid_id,sbOrderDetailID,[user_id],price,date_created,end_date,date_last_changed,DisplayonInvoice) select mid_id,' + str(@package_mid_id) + ',' + @strODIDPkgALA + ',' + str(@user_id) + ',Price,getdate(),' + @quote + @defaultenddate + @quote + ',getdate(),InvoiceChecked from ' + @temptablename + ' where Checked > 0 and package_cd = ' + @quote + @package_cdala + @quote
exec(@sqlstring1)
/***********************************************************************************************************/
-- Next update the sbOrderDetail and NT_NEV8_USER_MIDS table with summary price information
select @sumpriceuser = isnull(price,0) from ContentCentral.dbo.NT_NEV8_USER_MIDS where user_id = @user_id and sbOrderDetailID = @ODIDPkg
select @sumpricealauser = isnull(sum(price),0) from ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent where user_id = @user_id and sbOrderDetailID = @ODIDPkgALA
select @sumpriceodid = isnull(sum(price),0) from ContentCentral.dbo.NT_NEV8_USER_MIDS where sbOrderDetailID = @ODIDPkg
select @sumpricealaodid = isnull(sum(price),0) from ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent where sbOrderDetailID = @ODIDPkgALA
if @billing_freq = 'm' select @acvprice = (@sumpriceodid * 12)
if @billing_freqala = 'm' select @acvpriceala = (@sumpricealaodid * 12)
update ContentCentral.dbo.NT_NEV8_USER_MIDS set Price = @sumpriceuser where user_id = @user_id and sbOrderDetailID = @ODIDPkg
update ContentCentral.dbo.NT_NEV8_USER_MIDS set Price = @sumpricealauser where user_id = @user_id and sbOrderDetailID = @ODIDPkgALA
update SPSData.dbo.sbOrderDetail set ACV = @acvprice where sbOrderDetailID = @ODIDPkg
update SPSData.dbo.sbOrderDetail set ACV = @acvpriceala where sbOrderDetailID = @ODIDPkgALA
/************************************************************************************************************/
--END PROC
ASPX:
<telerik:RadFormDecorator ID="formDecorator1" runat="server" DecoratedControls="All" Skin="Office2007" />
<telerik:RadScriptManager ID="ScriptManager1" runat="server" EnableTheming="True" OutputCompression="AutoDetect">
</telerik:RadScriptManager>
<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server" >
<ClientEvents OnRequestStart="centerUpdatePanel();"></ClientEvents>
<AjaxSettings>
<telerik:AjaxSetting AjaxControlID="usersGrid">
<UpdatedControls>
<telerik:AjaxUpdatedControl ControlID="usersGrid" LoadingPanelID="RadAjaxLoadingPanel1" />
</UpdatedControls>
</telerik:AjaxSetting>
</AjaxSettings>
</telerik:RadAjaxManager>
<telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" Runat="server" Transparency="20" MinDisplayTime="1000" IsSticky="true" height="200" width="400px" >
<img alt="Loading..." src='<%= RadAjaxLoadingPanel.GetWebResourceUrl(Page, "Telerik.Web.UI.Skins.Default.Ajax.loading.gif") %>' style="border: 0;"/>
</telerik:RadAjaxLoadingPanel>
<asp:TextBox ID="package_cd" runat="server" Visible="False"></asp:TextBox>
<asp:TextBox ID="TextBoxAdditionalItemsPackageCdAla" runat="server" Visible="False"></asp:TextBox>
<asp:TextBox ID="TextBoxActualPackage" runat="server" Visible="False" ></asp:TextBox>
<asp:TextBox ID="temptablename" runat="server" Visible="False"></asp:TextBox>
<asp:TextBox ID="TextBoxPackagePrice" runat="server" Visible="false"></asp:TextBox>
<asp:TextBox ID="TextBoxTotalAlaCartePrice" runat="server" Visible="false"></asp:TextBox>
<asp:TextBox ID="TextBoxUserID" runat="server" Visible="false"></asp:TextBox>
<asp:TextBox ID="TextBoxInInsert" runat="server" Visible="false"></asp:TextBox>
<telerik:RadGrid ID="usersGrid" runat="server"
DataSourceID="ContentCentralUsers" GridLines="None" Skin="Office2007"
AutoGenerateColumns="False"
ondatabound="usersGrid_DataBound" onitemdatabound="usersGrid_ItemDataBound"
onitemdeleted="usersGrid_ItemDeleted" onitemevent="usersGrid_ItemDataBound"
oniteminserted="usersGrid_ItemInserted" onitemupdated="usersGrid_ItemUpdated"
onload="usersGrid_Load" ondatabinding="usersGrid_DataBinding" ShowStatusBar="True"
ondeletecommand="usersGrid_DeleteCommand" OnPreRender="usersGrid_PreRender"
oninsertcommand="usersGrid_InsertCommand1" onitemcreated="usersGrid_ItemCreated"
onpageindexchanged="usersGrid_PageIndexChanged" OnItemCommand="usersGrid_ItemCommand"
onupdatecommand="usersGrid_UpdateCommand" commanditemdisplay="TopAndBottom"
onselectedindexchanged="usersGrid_SelectedIndexChanged"
AllowAutomaticDeletes="True" AllowAutomaticInserts="True" AllowAutomaticUpdates="True" >
<MasterTableView DataSourceID="ContentCentralUsers"
AllowFilteringByColumn="True" AllowSorting="True"
InsertItemPageIndexAction="ShowItemOnFirstPage" CommandItemDisplay="Top"
NoMasterRecordsText="No users to display.">
<CommandItemSettings AddNewRecordText="Add new user" />
<RowIndicatorColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</RowIndicatorColumn>
<ExpandCollapseColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</ExpandCollapseColumn>
<Columns>
<telerik:GridEditCommandColumn ButtonType="ImageButton" UniqueName="EditCommandColumn1">
<HeaderStyle Width="20px" />
</telerik:GridEditCommandColumn>
<telerik:GridBoundColumn DataField="user_id" UniqueName="user_id" Visible="false" ReadOnly="true"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="login_name" HeaderText="Seat(User)"
SortExpression="login_name" UniqueName="login_name" >
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="password"
HeaderText="Password" SortExpression="password" AllowFiltering="false"
UniqueName="password" EditFormColumnIndex="1">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="email" HeaderText="Email Address"
SortExpression="email" UniqueName="email" >
</telerik:GridBoundColumn>
<telerik:GridTemplateColumn HeaderText="Package" UniqueName="RadComboPackage" EditFormColumnIndex="1" >
<HeaderStyle Width="100%" />
<ItemTemplate>
<asp:Label runat="server" ID="Label1" Text='<%#Eval("package_name") %>'></asp:Label>
<telerik:RadToolTip runat="server" ID="RadToolTip1" TargetControlID="Label1"
Skin="Office2007" ShowDelay="1000" AutoCloseDelay="200000" Font-Names="arial"
Font-Size="XX-Large" BorderStyle="Inset" HideDelay="100000" ManualClose="False"
Position="BottomRight" Sticky="True" Width="300">
<asp:Label runat="server" ID="Label3" Text='<%#Eval("descriptions") %>'></asp:Label><br/>
</telerik:RadToolTip>
</ItemTemplate>
<EditItemTemplate>
<telerik:RadComboBox ID="RadComboBox1" Width="220px" runat="server" OnSelectedIndexChanged="RadComboBoxSelectedIndexChanged"
MarkFirstMatch="True" AllowCustomText="True" DataSourceID="ContentCentralPackages" EmptyMessage="Select a package"
ExpandAnimation-Type="None" CollapseAnimation-Type="None" AutoPostBack="true" AppendDataBoundItems="false"
DataTextField="package_name" DataValueField="package_cd" DropDownWidth="650px"
Skin="Office2007" SelectedValue='<%# Bind("package_cd") %>'
EnableVirtualScrolling="True" HighlightTemplatedItems="True">
<ExpandAnimation Type="None" />
<CollapseAnimation Duration="200" Type="None" />
<HeaderTemplate>
<table style="width: 620px; text-align: left">
<tr>
<td style="width: 170px;">
Package Name
</td>
<td style="width: 450px;" >
Description
</td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table style="width: 620px; text-align: left">
<tr>
<td style="width: 170px;" valign="top">
<%# DataBinder.Eval(Container.DataItem, "package_name") %>
</td>
<td style="width: 450px;">
<%# DataBinder.Eval(Container.DataItem, "descriptions") %>
</td>
</tr>
</table>
</ItemTemplate>
</telerik:RadComboBox>
</EditItemTemplate>
</telerik:GridTemplateColumn>
<telerik:GridMaskedColumn DataField="packageprice" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Package Price" Display="true" UniqueName="packageprice"
readonly="true" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
<telerik:GridMaskedColumn DataField="packagealaprice" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Ala Carte Price" Display="true" UniqueName="packagepriceala"
readonly="true" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
<telerik:GridButtonColumn ButtonType="ImageButton" CommandName="Delete"
ConfirmDialogType="RadWindow" Text="Delete" UniqueName="column"></telerik:GridButtonColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" HeaderStyle-VerticalAlign="Top" Visible="false">
<EditItemTemplate>
<telerik:RadGrid ID="PackagePriceGrid" runat="server" AutoGenerateColumns="False"
DataSourceID="spAddNewsTradeProduct" GridLines="None" Skin="Office2007"
ondatabound="PackagePriceGrid_DataBound" onitemdatabound="PackagePriceGrid_ItemDataBound"
onload="PackagePriceGrid_Load" ondatabinding="PackagePriceGrid_DataBinding" ShowStatusBar="True"
OnPreRender="PackagePriceGrid_PreRender" >
<MasterTableView CommandItemDisplay="None" >
<Columns>
<telerik:GridBoundColumn HeaderText="Service ID" UniqueName="service_id" DataField="service_id"
Visible="false" Display="false"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="service_name" HeaderText="Default Package" Display="true"
UniqueName="service_name" ></telerik:GridBoundColumn>
<telerik:GridTemplateColumn HeaderText="Entitled" UniqueName="TemplateColumn">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Eval("Checked") %>' AutoPostBack="true"
EditFormColumnIndex="1" OnCheckedChanged="cbCheckedChangedDefaultPackage"/>
</ItemTemplate>
</telerik:GridTemplateColumn >
<telerik:GridMaskedColumn DataField="Price" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Price" Display="false" UniqueName="Price"
EditFormColumnIndex="2" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
</Columns>
<EditFormSettings ColumnNumber="5">
</EditFormSettings>
<PagerStyle AlwaysVisible="True" />
</MasterTableView>
</telerik:RadGrid>
</EditItemTemplate>
<HeaderStyle VerticalAlign="Top"></HeaderStyle>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" HeaderStyle-VerticalAlign="Top" Visible="false">
<EditItemTemplate>
<telerik:RadGrid ID="PackageReadOnlyGrid" runat="server" AutoGenerateColumns="False" Visible="false"
DataSourceID="spAddNewsTradeProduct" GridLines="None" Skin="Office2007"
ondatabound="PackagePriceGrid_DataBound" onitemdatabound="PackageReadOnlyGrid_ItemDataBound"
onload="PackagePriceGrid_Load" ondatabinding="PackagePriceGrid_DataBinding" ShowStatusBar="True"
OnPreRender="PackagePriceGrid_PreRender" >
<MasterTableView CommandItemDisplay="None" >
<Columns>
<telerik:GridBoundColumn HeaderText="Service ID" UniqueName="service_id" DataField="service_id"
Visible="false" Display="false"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="service_name" HeaderText="Default Package" Display="true"
UniqueName="service_name" ></telerik:GridBoundColumn>
<telerik:GridCheckBoxColumn HeaderText="Entitled" UniqueName="CHECKED" DataField="Checked" EditFormColumnIndex="1">
</telerik:GridCheckboxColumn >
<telerik:GridMaskedColumn DataField="Price" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Price" Display="false" UniqueName="Price"
EditFormColumnIndex="2" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
</Columns>
<EditFormSettings ColumnNumber="5">
</EditFormSettings>
<PagerStyle AlwaysVisible="True" />
</MasterTableView>
</telerik:RadGrid>
</EditItemTemplate>
<HeaderStyle VerticalAlign="Top"></HeaderStyle>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" HeaderStyle-VerticalAlign="Top" Visible="false" EditFormColumnIndex="1">
<EditItemTemplate>
<telerik:RadGrid ID="AlaCartePriceGrid" runat="server" AutoGenerateColumns="False"
DataSourceID="spAddNewsTradeProductAlaCarte" GridLines="None" Skin="Office2007"
ondatabound="AlaCartePriceGrid_DataBound" onitemdatabound="AlaCartePriceGrid_ItemDataBound"
onload="AlaCartePriceGrid_Load" ondatabinding="AlaCartePriceGrid_DataBinding" ShowStatusBar="True"
OnPreRender="AlaCartePriceGrid_PreRender" >
<MasterTableView CommandItemDisplay="None" >
<Columns>
<telerik:GridBoundColumn HeaderText="Service ID" UniqueName="service_id" DataField="service_id"
Visible="false" Display="false"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="service_name" HeaderText="Available a la carte Content" Display="true"
UniqueName="service_name" ReadOnly="true" ></telerik:GridBoundColumn>
<telerik:GridTemplateColumn HeaderText="Entitled" UniqueName="CHECKED">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Eval("Checked") %>' AutoPostBack="true" OnCheckedChanged="cbCheckChangedAlaCarteEntitled"
EditFormColumnIndex="1" />
</ItemTemplate>
</telerik:GridTemplateColumn >
<telerik:GridTemplateColumn HeaderText="Price" UniqueName="Price" >
<ItemTemplate>
<telerik:RadNumericTextBox ID="RadNumericTextBox1" runat="server" AutoPostBack="true" DbValue='<%#Eval("Price") %>'
Culture="English (United States)" Type="Currency" Value="0" Width="125px" OnTextChanged="RadNumericTextBox1_TextChanged"
EditFormColumnIndex="2" >
</telerik:RadNumericTextBox>
</ItemTemplate>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn HeaderText="Incl. Inv" UniqueName="InclInv">
<ItemTemplate>
<asp:CheckBox ID="InclInv" runat="server" Checked='<%# Eval("InvoiceChecked") %>' AutoPostBack="true" OnCheckedChanged="cbCheckedChangedAlaCarteInvoice"
EditFormColumnIndex="3" />
</ItemTemplate>
</telerik:GridTemplateColumn >
</Columns>
<EditFormSettings ColumnNumber="5">
</EditFormSettings>
<PagerStyle AlwaysVisible="True" />
</MasterTableView>
</telerik:RadGrid>
</EditItemTemplate>
<HeaderStyle VerticalAlign="Top"></HeaderStyle>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" HeaderStyle-VerticalAlign="Top" Visible="false" EditFormColumnIndex="1">
<EditItemTemplate>
<telerik:RadGrid ID="AlaCarteReadOnlyGrid" runat="server" AutoGenerateColumns="False" Visible="false"
DataSourceID="spAddNewsTradeProductAlaCarte" GridLines="None" Skin="Office2007"
ondatabound="AlaCartePriceGrid_DataBound" onitemdatabound="AlaCarteReadOnlyGrid_ItemDataBound"
onload="AlaCartePriceGrid_Load" ondatabinding="AlaCartePriceGrid_DataBinding" ShowStatusBar="True"
OnPreRender="AlaCartePriceGrid_PreRender" >
<MasterTableView CommandItemDisplay="None" >
<Columns>
<telerik:GridBoundColumn HeaderText="Service ID" UniqueName="service_id" DataField="service_id"
Visible="false" Display="false"></telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="service_name" HeaderText="Available a la carte Content" Display="true"
UniqueName="service_name" ReadOnly="true" ></telerik:GridBoundColumn>
<telerik:GridCheckBoxColumn HeaderText="Entitled" UniqueName="CHECKED" DataField="Checked" EditFormColumnIndex="1">
</telerik:GridCheckboxColumn >
<telerik:GridMaskedColumn DataField="Price" Mask="$ #####.##" DataFormatString="{0:C2}" HeaderText="Price" Display="true" UniqueName="Price"
EditFormColumnIndex="2" >
<ItemStyle HorizontalAlign="Right" />
</telerik:GridMaskedColumn>
</Columns>
<EditFormSettings ColumnNumber="5">
</EditFormSettings>
<PagerStyle AlwaysVisible="True" />
</MasterTableView>
</telerik:RadGrid>
</EditItemTemplate>
<HeaderStyle VerticalAlign="Top"></HeaderStyle>
</telerik:GridTemplateColumn>
<telerik:GridTemplateColumn EditFormHeaderTextFormat="" Visible="false" >
<EditItemTemplate>
<asp:Label runat="server" Text="* Prices are monthly"></asp:Label>
</EditItemTemplate>
</telerik:GridTemplateColumn>
</Columns>
<EditFormSettings ColumnNumber="5">
<EditColumn ButtonType="ImageButton" UniqueName="EditCommandColumn1" EditFormColumnIndex="5">
</EditColumn>
</EditFormSettings>
<CommandItemStyle BorderStyle="Solid" />
</MasterTableView>
<FilterMenu EnableTheming="True">
<CollapseAnimation Type="OutQuint" Duration="200"></CollapseAnimation>
</FilterMenu>
</telerik:RadGrid>
<asp:SqlDataSource ID="ContentCentralUsers" runat="server"
ConnectionString="<%$ ConnectionStrings:ContentCentralConnectionString %>"
SelectCommand="isp_sbLoadUserData"
InsertCommand="isp_sbInsertNewsTradePkgs"
UpdateCommand="isp_sbUpdateNewsTradePkgsSave"
SelectCommandType="StoredProcedure"
InsertCommandType="StoredProcedure"
UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="SAPID" QueryStringField="SAPID" Type="String" />
<asp:QueryStringParameter Name="sbODID" QueryStringField="sbODID" Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:ControlParameter ControlID="temptablename" Name="temptablename" PropertyName="Text" Type="String" />
<asp:QueryStringParameter Name="SAPID" QueryStringField="SAPID" Type="String" />
<asp:ControlParameter ControlID="package_cd" Name="package_cd" PropertyName="Text" Type="String" />
<asp:Parameter Name="login_name" Type="String" />
<asp:Parameter Name="password" Type="String" />
<asp:Parameter Name="email" Type="String" />
<asp:QueryStringParameter Name="sbOrderID" QueryStringField="sbOID" Type="Int32" />
<asp:QueryStringParameter Name="sbProductID" QueryStringField="sbproductid" Type="Int32" />
</InsertParameters>
<UpdateParameters>
<asp:ControlParameter ControlID="temptablename" Name="temptablename" PropertyName="Text" Type="String" />
<asp:QueryStringParameter QueryStringField="sbODID" Name="sbODID" Type="Int32" />
<asp:Parameter Name="user_id" Type="Int32" />
<asp:ControlParameter ControlID="package_cd" Name="package_cd" PropertyName="Text" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="ContentCentralPackages" runat="server"
ConnectionString="<%$ ConnectionStrings:ContentCentralConnectionString %>"
SelectCommand="SELECT package_name, descriptions, package_id, package_cd, mid_id FROM PACKAGES WHERE (pkg_type = 'NT') OR (pkg_type = 'NEV8')" >
</asp:SqlDataSource>
<asp:SqlDataSource ID="spAddNewsTradeProduct" runat="server"
ConnectionString="<%$ ConnectionStrings:ContentCentralConnectionString %>"
SelectCommand="isp_sbUpdateNewsTradeProduct"
SelectCommandType="StoredProcedure" >
<SelectParameters>
<asp:ControlParameter ControlID="temptablename" DefaultValue=""
Name="temptablename" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="package_cd" DefaultValue="NTB"
Name="package_cd" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="spAddNewsTradeProductAlaCarte" runat="server"
ConnectionString="<%$ ConnectionStrings:ContentCentralConnectionString %>"
SelectCommand="isp_sbUpdateNewsTradeProduct"
SelectCommandType="StoredProcedure" >
<SelectParameters>
<asp:ControlParameter ControlID="temptablename" Name="temptablename"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxAdditionalItemsPackageCdAla"
DefaultValue="NTBALA" Name="package_cd" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
Stored Procedure:
USE [ContentCentral]
GO
/****** Object: StoredProcedure [dbo].[isp_sbUpdateNewsTradePkgsSave] Script Date: 10/04/2008 19:41:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[isp_sbUpdateNewsTradePkgsSave]
@temptablename nvarchar(200),
@sbODID int,
@user_id int,
@package_cd nvarchar(20)
AS
set nocount on
declare
@sqlstring1 nvarchar(1000),
@sqlstring2 nvarchar(1000),
@database nvarchar(20),
@usercount int,
@quote nvarchar(1),
@ODIDOrig int,
@ODIDPkg int,
@ODIDPkgALA int,
@strODIDPkg nvarchar(12),
@strODIDPkgALA nvarchar(12),
@package_id int,
@package_name nvarchar(100),
@package_cdala nvarchar(20), --NTBALA etc
@package_price numeric(8,2),
@package_mid_id int,
@mid_type_id int,
@mid_type_name nvarchar(50),
@defaultenddate varchar(10),
@billing_freq char(1),
@billing_freqala char(1),
@sumpriceodid numeric(8,2),
@sumpricealaodid numeric(8,2),
@sumpriceuser numeric(8,2),
@sumpricealauser numeric(8,2),
@acvprice numeric(8,2),
@acvpriceala numeric(8,2)
select @defaultenddate = '01/01/1900'
select @database = 'Temporary.dbo.'
select @quote = char(39)
select @package_cdala = @package_cd + 'ALA'
select @temptablename = @database + @temptablename
/************************************************************************************************************/
select @ODIDPkg = @sbODID
select @ODIDPkgALA = isnull(convert(int,rtrim(CUSTOMFLD4)),0) from SPSData.dbo.sbOrderDetail where sbOrderDetailID = @ODIDPkg
select @strODIDPkg = convert(nvarchar,@ODIDPkg)
select @strODIDPkgALA = convert(nvarchar,@ODIDPkgALA)
select @database = 'Temporary.dbo.'
-- Step 2b for package_cd data..Get come local vars next .. may not need all of these but get them for now...
select @package_mid_id = mid_id from ContentCentral.dbo.MODULE_IDS where short_name = @package_cd
select @mid_type_id = mid_type_id from ContentCentral.dbo.MODULE_IDS where short_name = @package_cd
select @mid_type_name = mid_type_name from ContentCentral.dbo.MID_TYPES where mid_type_id = @mid_type_id
select @package_id = package_id from ContentCentral.dbo.PACKAGES where package_cd = @package_cd
select @package_name = package_name from ContentCentral.dbo.PACKAGES where package_cd = @package_cd
select @package_price = asking_price from ContentCentral.dbo.PRICES where mid_id = @package_mid_id
select @billing_freq = billing_freq from ContentCentral.dbo.PRICES where mid_id = @package_mid_id
-- First get the NT_NEV8_USER_MIDS package header information
update n
set n.mid_id = @package_mid_id,
n.mid_type_name = @mid_type_name,
n.start_date = getdate(),
n.end_date = @defaultenddate,
n.price = @package_price,
n.last_changed_by = 'tbd',
n.date_last_changed = getdate(),
n.DisplayonInvoice = 1
from ContentCentral.dbo.NT_NEV8_USER_MIDS n
where n.sbOrderDetailID = @ODIDPkg and n.[user_id] = @user_id
delete from ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent where sbOrderDetailID = @ODIDPkg and [user_id] = @user_id
select @sqlstring1 = 'insert into ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent(mid_id,package_mid_id,sbOrderDetailID,[user_id],price,date_created,end_date,date_last_changed,DisplayonInvoice) select mid_id,' + str(@package_mid_id) + ',' + @strODIDPkg + ',' + str(@user_id) + ',Price,getdate(),' + @quote + @defaultenddate + @quote + ',getdate(),InvoiceChecked from ' + @temptablename + ' where Checked > 0 and package_cd = ' + @quote + @package_cd + @quote
exec(@sqlstring1)
-- Step 2b for package_cd data..Get come local vars next .. may not need all of these but get them for now...
select @package_mid_id = mid_id from ContentCentral.dbo.MODULE_IDS where short_name = @package_cdala
select @mid_type_id = mid_type_id from ContentCentral.dbo.MODULE_IDS where short_name = @package_cdala
select @mid_type_name = mid_type_name from ContentCentral.dbo.MID_TYPES where mid_type_id = @mid_type_id
select @package_id = package_id from ContentCentral.dbo.PACKAGES where package_cd = @package_cdala
select @package_name = package_name from ContentCentral.dbo.PACKAGES where package_cd = @package_cdala
select @package_price = asking_price from ContentCentral.dbo.PRICES where mid_id = @package_mid_id
select @billing_freqala = billing_freq from ContentCentral.dbo.PRICES where mid_id = @package_mid_id
update n
set n.mid_id = @package_mid_id,
n.mid_type_name = @mid_type_name,
n.start_date = getdate(),
n.end_date = @defaultenddate,
n.price = @package_price,
n.last_changed_by = 'tbd',
n.date_last_changed = getdate(),
n.DisplayonInvoice = 1
from ContentCentral.dbo.NT_NEV8_USER_MIDS n
where n.sbOrderDetailID = @ODIDPkgALA and n.[user_id] = @user_id
delete from ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent where sbOrderDetailID = @ODIDPkgALA and [user_id] = @user_id
select @sqlstring1 = 'insert into ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent(mid_id,package_mid_id,sbOrderDetailID,[user_id],price,date_created,end_date,date_last_changed,DisplayonInvoice) select mid_id,' + str(@package_mid_id) + ',' + @strODIDPkgALA + ',' + str(@user_id) + ',Price,getdate(),' + @quote + @defaultenddate + @quote + ',getdate(),InvoiceChecked from ' + @temptablename + ' where Checked > 0 and package_cd = ' + @quote + @package_cdala + @quote
exec(@sqlstring1)
/***********************************************************************************************************/
-- Next update the sbOrderDetail and NT_NEV8_USER_MIDS table with summary price information
select @sumpriceuser = isnull(price,0) from ContentCentral.dbo.NT_NEV8_USER_MIDS where user_id = @user_id and sbOrderDetailID = @ODIDPkg
select @sumpricealauser = isnull(sum(price),0) from ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent where user_id = @user_id and sbOrderDetailID = @ODIDPkgALA
select @sumpriceodid = isnull(sum(price),0) from ContentCentral.dbo.NT_NEV8_USER_MIDS where sbOrderDetailID = @ODIDPkg
select @sumpricealaodid = isnull(sum(price),0) from ContentCentral.dbo.NT_NEV8_USER_MIDSPkgContent where sbOrderDetailID = @ODIDPkgALA
if @billing_freq = 'm' select @acvprice = (@sumpriceodid * 12)
if @billing_freqala = 'm' select @acvpriceala = (@sumpricealaodid * 12)
update ContentCentral.dbo.NT_NEV8_USER_MIDS set Price = @sumpriceuser where user_id = @user_id and sbOrderDetailID = @ODIDPkg
update ContentCentral.dbo.NT_NEV8_USER_MIDS set Price = @sumpricealauser where user_id = @user_id and sbOrderDetailID = @ODIDPkgALA
update SPSData.dbo.sbOrderDetail set ACV = @acvprice where sbOrderDetailID = @ODIDPkg
update SPSData.dbo.sbOrderDetail set ACV = @acvpriceala where sbOrderDetailID = @ODIDPkgALA
/************************************************************************************************************/
--END PROC
1 Answer, 1 is accepted
0
Hi Laura,
To see more information about Stored Procedures and SqlDataSource, please refer to the following forum post
http://www.telerik.com/community/forums/thread/b311D-kkghe.aspx
Best wishes,
Plamen
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.
To see more information about Stored Procedures and SqlDataSource, please refer to the following forum post
http://www.telerik.com/community/forums/thread/b311D-kkghe.aspx
Best wishes,
Plamen
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.