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

16
  • Run SQL Profiler. What is actually being executed? Commented Jul 10, 2017 at 6: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. Commented Jul 10, 2017 at 6:52
  • Does the second table 'time_frame' contain the issue Id you are updating? Commented Jul 10, 2017 at 6:56
  • yes it contains Commented Jul 10, 2017 at 7: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? Commented Jul 10, 2017 at 7:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.