0

I want to update two tables using SqlDataSource. The control I am using is Gridview. I have a stored procedure. But I am getting error :

Procedure or function has too many arguments specified

I know my question is repeated but I have tried many solution yet my problem is not solved. Here is my SqlDataSource code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IHDConnectionString %>" 
            SelectCommand="SELECT issue.issue_id, issue.pro_id, project.name, issue.type, issue.summary, issue.mem_id, issue.priority, issue.status, issue.impact, CONVERT (date, GETDATE()) AS Expr1, issue.Name AS Expr2, contact.cust_name, contact.ph_no, contact.time, time_frame.Estmtd_Hrs, time_frame.Hrs_Spent, time_frame.internal_status FROM issue INNER JOIN project ON issue.pro_id = project.pro_id INNER JOIN contact ON issue.issue_id = contact.issue_id INNER JOIN time_frame ON issue.issue_id = time_frame.issue_id" 
            UpdateCommand="UpdateTwoTables" UpdateCommandType="StoredProcedure">

Here is my update parameters

<UpdateParameters>
                <asp:Parameter Name="mem_id" Type="String" />
                <asp:Parameter Name="priority" Type="String" />
                <asp:Parameter Name="status" Type="String" />
                <asp:Parameter Name="hrs" Type="String" />
                <asp:Parameter Name="i_status" Type="String" />
                <asp:Parameter Name="issue_id" Type="Int32" />
            </UpdateParameters>

Here is my stored Procedure:

PROCEDURE [dbo].[UpdateTwoTables]
       (
       @mem_id varchar(20),
       @priority varchar(30),
       @status varchar(20),
       @hrs varchar(20),
       @i_status varchar(30),
       @issue_id int
       )
AS
       BEGIN

       UPDATE [issue] SET  [mem_id] = @mem_id, [priority] = @priority, [status] = @status WHERE [issue_id] = @issue_id
       UPDATE time_frame SET Estmtd_Hrs=@hrs, internal_status=@i_status where issue_id=@issue_id

       END
       RETURN

I have also tried a solution which is on SqlDataSource update event below is code:

 protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
    {
        DbParameterCollection CmdParams = e.Command.Parameters;
        ParameterCollection UpdParams = ((SqlDataSourceView)sender).UpdateParameters;

        Hashtable ht = new Hashtable();
        foreach (Parameter UpdParam in UpdParams)
            ht.Add(UpdParam.Name, true);

        for (int i = 0; i < CmdParams.Count; i++)
        {
            if (!ht.Contains(CmdParams[i].ParameterName.Substring(1)))
                CmdParams.Remove(CmdParams[i--]);
        }
    }

The above code removed my error but only update 1 table second table is not updating. Please anyone help me. Thanks

Rami Far
  • 406
  • 1
  • 10
  • 29
  • Run SQL Profiler. What is actually being executed? –  Jul 10 '17 at 06:35
  • @JohnFaz `exec UpdateTwoTables @mem_id=N'dfg',@priority=N'dfsd',@status=N'Submitted',@hrs=NULL,@i_status=NULL,@issue_id=12` this is result. – Rami Far Jul 10 '17 at 06:52
  • Does the second table 'time_frame' contain the issue Id you are updating? –  Jul 10 '17 at 06:56
  • yes it contains – Rami Far Jul 10 '17 at 07:01
  • Does running exec UpdateTwoTables @mem_id=N'dfg',@priority=N'dfsd',@status=N'Submitted',@hrs=N‌​ULL,@i_status=NULL,@‌​issue_id=12 directly in SQL give you an error? –  Jul 10 '17 at 07:03
  • No when I call stored procedure in SqlServer it works perfectly fine. Only it is not working with visual studio code – Rami Far Jul 10 '17 at 07:04
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/148754/discussion-between-rami-far-and-john-faz). – Rami Far Jul 10 '17 at 07:10
  • Since the GridView uses its DataKeys collection to populate the SqlDataSource parameters, it s vital that the GridView s DataKeyNames property be set to the column(s) that constitute the primary key and that the SqlDataSource s SelectCommand returns these columns. [SQLDataSource](https://learn.microsoft.com/en-us/aspnet/web-forms/overview/data-access/accessing-the-database-directly-from-an-aspnet-page/inserting-updating-and-deleting-data-with-the-sqldatasource-cs) – DataWrangler Jul 10 '17 at 08:17
  • @Joby can you please explain exactly how can I do this – Rami Far Jul 10 '17 at 08:20
  • The values that are passed for the `Stored Procedure` should have the same naming convention.. Please take a look at the [Parameters](https://stackoverflow.com/questions/11246747/how-to-set-parameters-for-sqldatasource-updatecommand) also your select should return all the data thats required for the SP to execute. – DataWrangler Jul 10 '17 at 08:31
  • @Joby these are same you can see above code – Rami Far Jul 10 '17 at 08:57
  • The Select Command should have the same naming... – DataWrangler Jul 10 '17 at 09:23
  • @Joby select command has column names in my tables how can I change them – Rami Far Jul 10 '17 at 09:30
  • @RamiFar you can give an alias name to the Columns in SQL `SELECT column_name AS alias_name FROM table_name;` something like this `SELECT issue.issue_id AS 'mem_id', issue.pro_id AS 'priority' FROM issue` also make sure that the data thats required for the `SP` to execute are returned from the SELECT – DataWrangler Jul 10 '17 at 12:49
  • Your select statement is missing the values for `hrs` and `i_status` – DataWrangler Jul 10 '17 at 13:00
  • @Joby I have tried but same result – Rami Far Jul 10 '17 at 16:40

0 Answers0