SqlDataSource run stored procedure twice

Question

I have the following table

[Department]  [Location]    [Project]      [Job Title]   [Qty]
-------------------------------------------------------------  
HR            Jeddah        Andalusia       JT2            5  
IT            Jeddah        Head Office     JT1            3

and I'm trying to run a sorted procedure using SqlDataSource as the following

CREATE PROCEDURE [dbo].[MManPowerUpdate]
    @Dept     nvarchar(350),
    @Loca     nvarchar(350),
    @Proj     nvarchar(350),
    @JobT     nvarchar(350)
AS

   IF (SELECT QTY FROM [MANPOWER] WHERE ([Department] = @Dept) AND ([Location] = @Loca) AND ([Project] = @Proj) AND ([JobTitle] = @JobT)) > 0
       Begin
         SELECT  'Operation Completed Successfully.' AS [Result]
         UPDATE [MANPOWER] SET QTY = QTY - 1 WHERE ([Department] = @Dept) AND ([Location] = @Loca) AND ([Project] = @Proj) AND ([JobTitle] = @JobT)
       End
   Else
       Begin
         SELECT 'Can''t Execute Operation.' AS [Result]
       End
Return 0

ASP.net Code :

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:_JOOBERConnectionString %>" SelectCommandType="StoredProcedure" SelectCommand="MManPowerUpdate">
        <SelectParameters>
            <asp:ControlParameter ControlID="txtDept" Name="Dept" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="txtLoca" Name="Loca" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="txtProj" Name="Proj" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="txtJobT" Name="JobT" PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AutoGenerateColumns="False">
        <Columns>
            <asp:BoundField DataField="Result" HeaderText="Result" ReadOnly="True" SortExpression="Result" />
        </Columns>
    </asp:GridView>

vb.net code behind :

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    SqlDataSource1.Select(New DataSourceSelectArguments)
End Sub

When I execute the code with this values ex.

@Dept  = HR
@Loca  = Jeddah
@proj  = Andalus
@JobT  = JT2

I expect the Qty to be 4 after update and retrieve 'Operation Completed Successfully.' but I used to get 3 after update, and that's mean the update statement was run twice when i called the procedure. so I hope to find the solution with you.

Martin Parenteau · Accepted Answer · 2016-04-12 14:19:23Z

The problem is that your Select query also updates your data. Since the Select query is automatically called on PreRender during life cycle, the decrement operation is done twice when clicking on the button: once in your button click event handler and once in PreRender (and it probably decrements QTY each time you open your form, even if you don't press Button1).

One solution would be to define MManPowerUpdate as the Update query:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:_JOOBERConnectionString %>" SelectCommandType="StoredProcedure" UpdateCommand="MManPowerUpdate">
    <UpdateParameters>
        <asp:ControlParameter ControlID="txtDept" Name="Dept" PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="txtLoca" Name="Loca" PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="txtProj" Name="Proj" PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="txtJobT" Name="JobT" PropertyName="Text" Type="String" />
    </UpdateParameters>
</asp:SqlDataSource>

And call it when you click the button:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    SqlDataSource1.Update()
    GridView1.DataBind()
End Sub

You could define another Select query, which would get the data and show it the GridView, without modifying it.

Collectives™ on Stack Overflow

SqlDataSource run stored procedure twice

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related