Click here to Skip to main content
14,828,863 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi
I need to display value/data from stored procdure into text box in asp.net

Pls advice me

Thank you

Maideen

What I have tried:

It is my code

Stored Procedure
SQL
IF @Action = 'WEEKLY-REVENUE'
BEGIN
    SELECT sum(subtotal) FROM [dbo].[tbl_Finance]
    where YEARNO = '2021' AND Weekno = '5'
    GROUP BY YEARNO,WeekNo

END

in asp.net web page
VB.NET
Private Sub WeeklyRevenue()
    Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectString").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("usp_W_Finance")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Action", "WEEKLY-REVENUE")
            cmd.Connection = con
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                sdr.Read()
                Me.lblWeeklyRevenue.Text = sdr("subtotal").ToString()

            End Using
            con.Close()
        End Using
    End Using
End Sub
Posted
Updated 26-Jan-21 21:47pm
v2

1 solution

The resultset returned by your stored procedure does not specify a column name. When you try to read the subtotal column, you will get an error, because there is no such column in the resultset.

Add a column alias in your stored procedure:
SQL
SELECT sum(subtotal) As subtotal FROM ...
Or access the column by index instead of name:
VB.NET
Me.lblWeeklyRevenue.Text = Convert.ToString(sdr(0))
   
Comments
Maideen Abdul Kader 27-Jan-21 19:05pm
   
Thanks Mr.Richard
I used your advice, but result is '1' even though there are data in table.
Pls advice me.
Thank you
Maideen
Richard Deeming 28-Jan-21 3:25am
   
That means that the first column of the first row returned by your stored procedure is 1.

Since we can't see your entire procedure, and have no access to your data, we can't tell you why. You'll need to debug the code yourself.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900