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