This is a migrated thread and some comments may be shown as answers.

Procedure or function has too many arguments specified error

1 Answer 92 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Laura
Top achievements
Rank 1
Laura asked on 05 Oct 2008, 12:15 AM
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


1 Answer, 1 is accepted

Sort by
0
Missing User
answered on 07 Oct 2008, 07:34 AM
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.
Tags
Grid
Asked by
Laura
Top achievements
Rank 1
Answers by
Missing User
Share this question
or