locked
how to retrieve data into textbox using ms sql 2019 stored procedure in asp.net webpage? RRS feed

  • Question

  • User-1640542475 posted

    Hi

    I need to display value/data from stored procdure into text box in asp.net

    Pls advice me

    Thank you 

    Maideen

    It is my code
    
    Stored Procedure
    
        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
    
        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


    </div> <div></div> <div></div>

    Wednesday, January 27, 2021 1:34 AM

Answers

  • User1535942433 posted

    Hi maideen5,

    Which lines cause the problems? Do you have get the data of SqlDataReader? Do you have write wrongly of 'subtotal'?

    I suggest you could debug and breakpoint to check what the problems.

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 2, 2021 8:19 AM

All replies

  • User1535942433 posted

    Hi maideen5,

    Stored Procedure
    
        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
    
        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

    What's your problems?Accroding to your codes,I found your code have errors. You need to for loop of data rows.You could do just like this:

    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()
    
                    If sdr.HasRows Then
    
                        While sdr.read()
                            Me.lblWeeklyRevenue.Text += sdr("subtotal").ToString() & ","
                        End While
                    End If
                End Using
    
                con.Close()
            End Using
        End Using
    End Sub

    Best regards,

    Yijing Sun

    Wednesday, January 27, 2021 9:48 AM
  • User-1640542475 posted

    Hi yij sun

    as per your advice, i have changed code but there is error:

    System.IndexOutOfRangeException: subtotal

    Pls advice me

    Thank you

    Maideen

    Saturday, January 30, 2021 1:51 AM
  • User1535942433 posted

    Hi maideen5,

    Which lines cause the problems? Do you have get the data of SqlDataReader? Do you have write wrongly of 'subtotal'?

    I suggest you could debug and breakpoint to check what the problems.

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 2, 2021 8:19 AM
  • User-1640542475 posted

    Yij Sun

    Thank you. for your advice. I have debugged and  found the subtotal is null. that why prompt the error

    Maideen

    Wednesday, February 3, 2021 1:30 AM
  • User1535942433 posted

    Hi maideen5,

    I suggest you could break point at these lines and check which are null step by step.

    Using sdr As SqlDataReader = cmd.ExecuteReader()
    
                    If sdr.HasRows Then
    
                        While sdr.read()
                            Me.lblWeeklyRevenue.Text += sdr("subtotal").ToString() & ","
                        End While
                    End If
                End Using

    Or you could use try catch to get the error.

    Best regards,

    Yijing Sun

    Wednesday, February 3, 2021 5:16 AM