Farpoint Web Spread 6

Posted by: jagan1982 on 8 September 2017, 2:56 pm EST

  • Posted 8 September 2017, 2:56 pm EST

    Hi,

       I am using Farpoint Web Spread 6. I am facing issuue on Edit/Update the data to database. I am using SQLdatasource of store procedures for select & update with parmaeters specified.In total there are 22 columns, and only 3 columns is editable.

       The error, I am getting is " Procedure or function xxx has too many arguments specified." When I am executing the sp alsone for update, it is sucessfully updating. where as I am getting the error only in application.

       Please advise me, to solve the issue.It's very urgent, as I am breaking my heads for a while.

      Thanks,

    Jagan

  • Replied 8 September 2017, 2:56 pm EST

    Paul,

       Where I need to use the error comand. Do I need paste the below code in my application? Please advise.

    <span>private</span> <span>void</span> FpSpread1ErrorCommand(<span>object</span> sender, FarPoint.Web.Spread.SpreadCommandEventArgs e) 
    {
    <span>if</span> (e.CommandName == <span>"InsertFailed"</span>)
    {
    TextBox1.Text = <span>"Insert of row failed."</span>;
    }
    }
     
    Also, What I understand from the error message is  - the application shows 20 fields, but I am editing 3 fields and also the store procedure accept only 3 parameters. How can I limit, the remaing 17 fields. Is there any properties available?
    Thanks,
    Jagan
  • Replied 8 September 2017, 2:56 pm EST

    This is the Spread for Winforms forum but I just noticed your title says FarPoint Web Spread 6. If this is indeed Spread for ASP.Net there is an ErrorCommand Event you can catch that would give us more information about what is failing in your Update. Please try this and let us know about your Event Data.

    <span id="pagetitle">ErrorCommand Event </span>

    <span>Occurs when the use of a command button causes an error.
    </span>

    <h1 class="heading"><span class="expandcollapse"><img id="syntaxToggle" class="toggle" src="http://www.clubfarpoint.com/Forums/forums/dotnetimages/collapse.gif">Syntax</span></h1><div id="syntaxSection" class="section">
    <div style="display:block;" id="Syntax_VB" class="LanguageSpecific">
    <table class="syntaxtable" cellSpacing="0" cellPadding="0">

    <tr>
    <th>Visual Basic (Declaration)</th>
    <th> </th></tr>
    <tr>
    <td colSpan="2">
    Public Event ErrorCommand As SpreadCommandEventHandler</pre></td></tr></table></div>
    <div style="display:block;" id="Syntax_VBUsage" class="LanguageSpecific">
    <table class="syntaxtable" cellSpacing="0" cellPadding="0">

    <tr>
    <th>Visual Basic (Usage)</th>
    <th><span class="copyCode"><img class="copyCodeImage" align="absMiddle" src="http://www.clubfarpoint.com/Forums/forums/dotnetimages/copycode.gif">Copy Code</span></th></tr>
    <tr>
    <td colSpan="2">
    Dim instance As FpSpread
    Dim handler As SpreadCommandEventHandler

    AddHandler instance.ErrorCommand, handler
    </td></tr></table></div>
    <div style="display:block;" id="Syntax_CS" class="LanguageSpecific">
    <table class="syntaxtable" cellSpacing="0" cellPadding="0">

    <tr>
    <th>C#</th>
    <th> </th></tr>
    <tr>
    <td colSpan="2">
    public event SpreadCommandEventHandler ErrorCommand
    </td></tr></table></div></div><h1 class="heading"><span class="expandcollapse"><img id="exceptionsToggle" class="toggle" src="http://www.clubfarpoint.com/Forums/forums/dotnetimages/collapse.gif">Event
    Data</span></h1><div id="exceptionsSection" class="section">
    <p>The event handler receives an argument of type SpreadCommandEventArgs
    containing data related to this event. The following
    <b>SpreadCommandEventArgs</b> properties provide information specific to this
    event.


    <table class="FilteredItemListTable">

    <tr>
    <th>Property</th>
    <th>Description</th></tr>
    <tr>
    <td class="LinkCell">CommandArgument (Inherited from System.Web.UI.WebControls.CommandEventArgs)</td>
    <td class="DescriptionCell">Gets the argument for the command.</td></tr>
    <tr>
    <td class="LinkCell">CommandName (Inherited from System.Web.UI.WebControls.CommandEventArgs)</td>
    <td class="DescriptionCell">Gets the name of the command.</td></tr>
    <tr>
    <td class="LinkCell">EditValues
    </td>
    <td class="DescriptionCell">Gets the postback data in an array list of
    values.</td></tr>
    <tr>
    <td class="LinkCell">Handled
    </td>

    <td class="DescriptionCell">Gets or sets whether the event has been
    handled.</td></tr>
    <tr>
    <td class="LinkCell">SheetView
    </td>
    <td class="DescriptionCell">Gets the sheet being updated.</td></tr>
    <tr>
    <td class="LinkCell">SortFrozenRows
    </td>
    <td class="DescriptionCell">Gets or sets whether to the frozen rows should be
    sorted when sorting command happens.</td></tr></table></div><h1 class="heading"><span class="expandcollapse"><img id="remarksToggle" class="toggle" src="http://www.clubfarpoint.com/Forums/forums/dotnetimages/collapse.gif">Remarks</span></h1><div id="remarksSection" class="section">

    The ErrorCommand command name can be one of the following values:


    <table class="general">

    <tr>
    <th>Error</th>
    <th>Description</th></tr>
    <tr>
    <td>InvalidInput</td>
    <td>Input values are not valid</td></tr>
    <tr>
    <td>UpdateFailed</td>
    <td>Spread failed to update the data model</td></tr>
    <tr>
    <td>InsertFailed</td>
    <td>FarPoint could not insert the new row to the data model</td></tr>
    <tr>
    <td>DeleteFailed</td>
    <td>FarPoint could not delete the specified row.</td></tr></table>

    The ErrorCommand command argument is the index of the row being
    acted on.


    The SpreadCommandEventHandler
    receives an argument of type SpreadCommandEventArgs
    containing data related to this event.

    </div>


    <h1 class="heading"><span class="expandcollapse"><img id="exampleToggle" class="toggle" src="http://www.clubfarpoint.com/Forums/forums/dotnetimages/collapse.gif">Example</span></h1><div id="exampleSection" class="section">This example
    illustrates the use of the event.
    <div style="display:block;" id="Example_CS" class="LanguageSpecific">
    <table class="syntaxtable" cellSpacing="0" cellPadding="0">

    <tr>
    <th>C#</th>
    <th><span class="copyCode"><img class="copyCodeImage" align="absMiddle" src="http://www.clubfarpoint.com/Forums/forums/dotnetimages/copycode.gif">Copy Code</span></th></tr>
    <tr>
    <td colSpan="2">
    <span>private</span> <span>void</span> FpSpread1ErrorCommand(<span>object</span> sender, FarPoint.Web.Spread.SpreadCommandEventArgs e) 
    {
    <span>if</span> (e.CommandName == <span>"InsertFailed"</span>)
    {
    TextBox1.Text = <span>"Insert of row failed."</span>;
    }
    }
    </pre></td></tr></table></div>
    <div style="display:block;" id="Example_VB" class="LanguageSpecific">
    <table class="syntaxtable" cellSpacing="0" cellPadding="0">

    <tr>
    <th>Visual Basic</th>
    <th><span class="copyCode"><img class="copyCodeImage" align="absMiddle" src="http://www.clubfarpoint.com/Forums/forums/dotnetimages/copycode.gif">Copy Code</span></th></tr>
    <tr>
    <td colSpan="2">
    <span>Private</span> <span>Sub</span> FpSpread1ErrorCommand(<span>ByVal</span> sender <span>As</span> System.Object, <span>ByVal</span> e <span>As</span> FarPoint.Web.Spread.SpreadCommandEventArgs) <span>Handles</span>
    FpSpread1.ErrorCommand
    <span>If</span> e.CommandName = <span>"InsertFailed"</span> <span>Then</span>
    TextBox1.Text = <span>"Insert of row failed."</span>
    <span>End</span> <span>If</span>
    <span>End Sub</span>
    </td></tr></table></div></div><h1 class="heading"><span class="expandcollapse"><img id="requirementsToggle" class="toggle" src="http://www.clubfarpoint.com/Forums/forums/dotnetimages/collapse.gif">Requirements</span></h1><div id="requirementsSection" class="section">
    <p><b>Target Platforms:</b> Windows 98, Windows NT 4.0, Windows Millennium
    Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows
    Server 2003 family, Windows Vista, Windows Server 2008 family

    </div>
  • Replied 8 September 2017, 2:56 pm EST

    Paul,

        To make clear, I am displaying data through select store procedure and for update, I am using update store procedure. Out of 22 columns, only 3 columns are editable. I also specified the key columns for the property DataKeynames. Is anything else, do I need to mention in my code?

    Thanks,

    Jagan 

  • Replied 8 September 2017, 2:56 pm EST

    Paul,

       It's the store procedure, I am calling for the update.XXX is my procdure name.

  • Replied 8 September 2017, 2:56 pm EST

    Could you clarify what Procedure or function "xxx" is? What its definition says the arguments should be and what you are sending it in your code?
  • Replied 8 September 2017, 2:56 pm EST

    The best way I've found to add events so that they are wired up correctly is to go into the Design screen of the aspx page where Spread is and select the Spread and right click it then select properties. On the properties tab select events then scroll down to ErrorCommand and double click next to it. this will create an event stub on your aspx.cs or aspx.vb page and wire the event up for you. What you put inside the stub isn't that important, you can put a breakpoint in the first "{" inside it and do a quickwatch on the event args to get the information we are interested in.
  • Replied 8 September 2017, 2:56 pm EST

    You may need to catch the UpdateCommand Event. It fires once for each row you have edited. The Event Data includes CommandArgument which should be the row it is being fired for, the CommandName, and the EditValues which is an arraylist of the postback data. You could probably find the 3 fields you need from that and call your Stored Procedure giving it those 3 arguments. The UpdateCommand is another event of the FpSpread Class and you can find its information in the documentation
  • Replied 8 September 2017, 2:56 pm EST

    One of our developers has suggested you could try this code. You would need to add the Update Event and put this code into it and add the dataSourceCallback method

    <font size="3" face="Times New Roman">

    </font>

    <font size="3"><span>protected</span><span> <span style="color:blue;">void</span> FpSpread1_UpdateCommand1(<span style="color:blue;">object</span> sender, FarPoint.Web.Spread.<span>SpreadCommandEventArgs</span>
    e)</span></font>

    <span><font size="3">       
    {</font></span>

    <span><font size="3">           
    <span style="color:blue;">int</span> row = (<span style="color:blue;">int</span>)
    e.CommandArgument;</font></span>

    <span></span> 

    <span><font size="3">           
    <span>DataSourceView</span> dsv = ((<span>IDataSource</span>) <span style="color:blue;">this</span>.SqlDataSource1).GetView(<span style="color:blue;">string</span>.Empty);</font></span>

    <span></span> 

    <span><font size="3">           
    <span>IDictionary</span> tmp = <span style="color:blue;">new</span>
    <span>Hashtable</span>();</font></span>

    <span><font size="3">  
             <span>IDictionary</span>
    vals = <span style="color:blue;">new</span> <span>Hashtable</span>();</font></span>

    <span><font size="3">           
    <span>IDictionary</span> olds = <span style="color:blue;">null</span>;</font></span>

    <span><font size="3">           
    <span>IDictionary</span> keys = <span style="color:blue;">null</span>;</font></span>

    <span><font size="3"></font></span> 

    <span><font size="3">           
    <span>Hashtable</span> dataKey =
    e.SheetView.GetDataKey(row) <span style="color:blue;">as</span> <span>Hashtable</span>;</font></span>

    <span><font size="3"></font></span> 

    <span><font size="3">           
    keys = dataKey[<span>"key"</span>] <span style="color:blue;">as</span> <span>IDictionary</span>;</font></span>

    <span><font size="3">          
     olds = dataKey[<span>"value"</span>] <span style="color:blue;">as</span> <span>IDictionary</span>;</font></span>

    <span><font size="3"></font></span> 

    <span><font size="3">           
    <span style="color:blue;">for</span> (<span style="color:blue;">int</span> c = 0;
    c < e.SheetView.ColumnCount; c++)</font></span>

    <span><font size="3">           
    {</font></span>

    <span><font size="3">               
    <span style="color:blue;">if</span> (!e.SheetView.Columns[ c].Locked)</font></span>

    <span><font size="3">               
    {</font></span>

    <span><font size="3">                   
    <span style="color:blue;">string</span> cname = e.SheetView.GetColumnLabel(0,
    c); <span style="color:green;">// use data column name.</span></font></span>

    <span><font size="3">                   
    <span style="color:blue;">if</span> (cname != <span style="color:blue;">null</span>)</font></span>

    <span><font size="3">                   
    {</font></span>

    <span><font size="3">                       
    <span style="color:blue;">if</span> (olds != <span style="color:blue;">null</span>
    && olds.Contains(cname))</font></span>

    <span><font size="3">                       
    {</font></span>

    <span><font size="3">                           
    tmp.Add(cname, olds[cname]);</font></span>

    <span><font size="3">                           
    vals.Add(cname, olds[cname]);</font></span>

    <span><font size="3">                       
    }</font></span>

    <span><font size="3">                       
    <span style="color:blue;">object</span> val = e.EditValues[ c]; <span style="color:green;">// convert value if needed.</span></font></span>

    <span><font size="3">                       
    <span style="color:blue;">if</span> (val!=<span>FpSpread</span>.Unchanged)
    vals[cname] = val;</font></span>

    <span><font size="3">                   
    }</font></span>

    <span><font size="3">               
    }</font></span>

    <span><font size="3">           
    }</font></span>

    <span><font size="3">           
    <span style="color:green;">// the vals and tmp should only contains the values
    for the columns in the UpdateParameters.</span></font></span>

    <span><font size="3">           
    dsv.Update(keys, vals, tmp, <span style="color:blue;">new</span> <span>DataSourceViewOperationCallback</span>(dataSourceCallback));</font></span>

    <span><font size="3"></font></span> 

    <span><font size="3">           
    e.Handled = <span style="color:blue;">true</span>;</font></span>

    <span><font size="3">       
    }</font></span>

    <span><font size="3"></font></span> 

    <span><font size="3">       
    <span style="color:blue;">private</span> <span style="color:blue;">bool</span>
    dataSourceCallback(<span style="color:blue;">int</span> rows, <span>Exception</span> e)</font></span>

    <span><font size="3">       
    {</font></span>

    <span><font size="3">           
    <span style="color:green;">// if (e!=null) failed;</span></font></span>

    <span><font size="3">           
    <span style="color:blue;">return</span> <span style="color:blue;">true</span>;</font></span>

    <span><font size="3">       
    }<o:p></o:p></font></span>

    <font size="3" face="Times New Roman">

    </font>

  • Replied 8 September 2017, 2:56 pm EST

    You said earlier that your Stored Procedure only took 3 arguments but the markup you just posted has 7 Update parameters listed

    <UpdateParameters>

    <asp:Parameter Name="CurrentMonthSpecificProvision" Type="Decimal"/>

    <asp:Parameter Name="CurrentMonthSuspendedInterest" Type="Decimal"/>

    <asp:Parameter Name="ReasonForMovement"Type="String"/>

    <asp:Parameter Name="AccountNo" Type="string"/>

    <asp:Parameter Name="AsAtDate" Type="string" />

    <asp:ControlParameter Name="UpdatedBy" Type="String" ControlID="lblUpdateby" PropertyName="Text" />

    <asp:ControlParameter Name="WorkStation" Type="string" ControlID="lblworkstation" PropertyName="Text" />

    </UpdateParameters>

    Did you create this or was it created for you from some settings you made in Design view through the SqlDataSource component? Have you tried commenting out the parameters you don't need here?

  • Replied 8 September 2017, 2:56 pm EST

    Paul,

       Sorry, It's 7 parameters only.Is anything else, do i need to specify in order to update.

    I mean any code, do i need to write in code-behind file. I tried the same store procedure with Visual studio grid, and the data is updated in DB. I am facing the issue only web spread.

    Please advise.

    Thanks,

    Jagan

  • Replied 8 September 2017, 2:56 pm EST

    Paul,

       I am already passing the parameters via SQL data source, below is the code i am using in .aspx page and I am passing the field "AccountNo" as Datakeynames property. In total there are 7 parameters for the update store procedure.

      Through SELECT store procedure, there are 22 fields.

      

    <asp:SqlDataSource runat="server" ID="AFDataSource"EnableCaching="True" CacheDuration="20"

    ConnectionString="Data Source=z3msdw05;Initial Catalog=ODS;User ID=usr_WriteBack;Password=usr_WriteBack;Connect Timeout=100;Load Balance Timeout=100"

    SelectCommand ="sp_GetAssetFinanceProvisionReport" SelectCommandType="StoredProcedure" updateCommand="sp_UpdateAFProvision" UpdateCommandType="StoredProcedure"

    ProviderName="System.Data.SqlClient" onselecting="AFDataSource_Selecting" >

    <SelectParameters>

    <asp:ControlParameter ControlID="txtReportDate" Name="asatdate"DefaultValue="02/2012" PropertyName="Text" Type="String" />

    <asp:ControlParameter ControlID="dropbus" Name="business"  PropertyName="SelectedValue" Type="String" />

    <asp:ControlParameter ControlID="droparrears" Name="MIA" PropertyName="SelectedValue" Type="String" />

    </SelectParameters>

    <UpdateParameters>

    <asp:Parameter Name="CurrentMonthSpecificProvision" Type="Decimal"/>

    <asp:Parameter Name="CurrentMonthSuspendedInterest" Type="Decimal"/>

    <asp:Parameter Name="ReasonForMovement"Type="String"/>

    <asp:Parameter Name="AccountNo" Type="string"/>

    <asp:Parameter Name="AsAtDate" Type="string" />

    <asp:ControlParameter Name="UpdatedBy" Type="String" ControlID="lblUpdateby" PropertyName="Text" />

    <asp:ControlParameter Name="WorkStation" Type="string" ControlID="lblworkstation" PropertyName="Text" />

    </UpdateParameters>

    </asp:SqlDataSource>

    Thanks,
    Jagan

  • Replied 8 September 2017, 2:56 pm EST

    Paul,

       Thanks for your code. Do I need to modify the code or I can simply copy/paste the code.

       However, I can change SQLdata source name. Other than, in the code any where do I need to change anything? Please advise.

     Thanks,

    Jagan

  • Replied 8 September 2017, 2:56 pm EST

    Hello Jagan,

    Please elaborate a bit more on your requirement. I don't think your formula is working. Your code is applying the formula to the whole column. Please let me know what formula you want to apply to a cell/column.

     

    Thanks,

  • Replied 8 September 2017, 2:56 pm EST

    Paul,

        Thanks for your reply, I atlast found the solution. The farpoint web spread expects the parameter sequence in the same order as in store procedure.Normally, other grids won't make this difference.

        Now, I am facing another issue. The issue is on getting update on total in client side. i.e. when I change the value in cell, it should reflect the total sum in the footer.

        I made the below change, but the total sum is not changing in client side. Can you please adive, what's wrong on below code<font color="#ff0000" size="2" face="Consolas"><font color="#ff0000" size="2" face="Consolas"><font color="#ff0000" size="2" face="Consolas"></font></font></font>

    <font color="#ff0000" size="2" face="Consolas"><font color="#ff0000" size="2" face="Consolas"><font color="#ff0000" size="2" face="Consolas">

    ---- in aspx file 

    ClientAutoCalculation= "true"

    ----- in Code behind file

    <font color="#0000ff" size="2" face="Consolas"><font color="#0000ff" size="2" face="Consolas"><font color="#0000ff" size="2" face="Consolas"> protected void Page_Load(object sender, EventArgs e)
            {
               //FpSpread1.Sheets[0].ColumnFooter.Columns[5].Formula = "SUM(F)";              
        
            }</font></font></font>

    </font>

    thanks,

    Jagan

    </font></font>

    <font color="#0000ff" size="2" face="Consolas"><font color="#0000ff" size="2" face="Consolas"><font color="#0000ff" size="2" face="Consolas"></font></font></font> 

  • Replied 8 September 2017, 2:56 pm EST

    I don't really know anything about your DataSource it would be hard for me to determine if any modifications need to be made without having access to it. Simply copy and pasting the code into yur application would not work because the event would not be wired up. I would suggest you add the event through the FpSpread Properties pane Events Tab as described above then paste the code inside the event. You can debug from there to determine if it is working or not.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels