locked
How do I return the Count from my scalar query vb.net RRS feed

  • Question

  • User-1168492827 posted

    When I type in cntrows.  I get all my table variables except the one I want which is TotalComments.   

     

    How do I return the Count from my scalar query?

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

    I have a Table Adapter named :   tblUserInfo

    A scalar query named Using the table adapter wizard : GetCommentCount

    <o:p> </o:p>

    The SQL for it is:

    <o:p> </o:p>SELECT     COUNT(*) AS TotalComments<o:p></o:p>

    FROM         tblUserInfo

    <o:p> </o:p>

    Which when I execute it returns TotalComments

    <o:p> </o:p>

    Here is my VB Code

    <o:p> </o:p>Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load<o:p></o:p>    Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load<o:p></o:p>        Dim cntta As New UserInfoTableAdapters.tblUserInfoTableAdapter<o:p></o:p><o:p> </o:p>        cntta.GetCommentCount()<o:p></o:p><o:p> </o:p>        Dim cntrows As UserInfo.tblUserInfoRow<o:p></o:p><o:p> </o:p>        For Each cntrows In cntta.GetCommentCount<o:p></o:p>            Me.lblTotalComments.Text = cntrows.<o:p></o:p>        Next<o:p></o:p>

        End Sub

     

    Thanks

    Andre'

     

    Wednesday, April 1, 2009 9:43 AM

Answers

  • User-319574463 posted

     With a data layer module like:

        #Region " Count   "
        ''' <summary>
        ''' Count records on Person table
        ''' </summary>
        ''' <remarks>
        ''' This code was autogenerated on 03Apr2009
        ''' </remarks>
        ''' <returns>Count of records</returns>
        Public Function Count() AS Integer
          Dim count As Integer ' set to 0 by runtime
          Dim connect As String = CommonData.ConnectionString
          Dim xSqlConnection As SqlConnection = new SqlConnection(connect)
          Dim xSqlCommand As SqlCommand = new SqlCommand("usp_PersonCount", xSqlConnection)
          Try
            xSqlCommand.CommandType = CommandType.StoredProcedure
            xSqlCommand.Parameters.Add("@Count", SqlDbType.Int)
            xSqlCommand.Parameters("@Count").Direction = ParameterDirection.Output
            xSqlCommand.Connection.Open()
            xSqlCommand.ExecuteNonQuery()
            count = CommonData.NullToInteger(xSqlCommand.Parameters["@Count"].Value)
          Catch ex As Exception
            const Message As String = "Failure calling usp_PersonCount"
            CommonData.WriteEventError(ex, Message)
            throw new DalGeneralException(Message, ex)
          Finally
            xSqlCommand.Dispose()
            xSqlConnection.Dispose()
          End Try
          Return count
        End Function
        #End Region

    The stored procedure:

    ALTER  PROCEDURE dbo.usp_PersonCount
    (
      @Count         INT OUTPUT
    ) AS
    --  Purpose:
    --     Count records on Person table
    --  Parameters:
    --    Count          - Count of records
    --  History:  
    --  03Apr2009 Administrator Original coding
    SET NOCOUNT ON
    SELECT @COUNT= COUNT(*) FROM Person
    RETURN
    -------------- this is the end ----------------
    GO

    The library function are from the CommonData solution at http://www.CodePlex.Com/CommonData

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 3, 2009 11:30 AM