asp-classic to .NET conversion... i could use some suggestions

Question

i'm converting a classic asp website to .NET and could use some suggestions to this problem.

original code:

Dim oCountCMD
oCountCMD = Server.CreateObject("ADODB.Command")
With oCountCMD
    .ActiveConnection = Application("ConnString")
    .CommandText = "sp_GetSearchHistoryCount"
    .CommandType = .CommandType.StoredProcedure
    .oCountCMD(1) = strUser
    .oCountCMD(2) = DateAdd("n", -1, Now())
    .Execute()

    If oCountCMD(0) > 60 Then
        CheckSearchCounts = True
    Else
        CheckSearchCounts = False
    End If
End With
oCountCMD = Nothing

my unfinished converted code:

Dim objConnection As New SqlConnection(Application("ConnString"))
Dim oCountCMD As New SqlCommand("sp_GetSearchHistoryCount", objConnection)

With oCountCMD
    .CommandType = CommandType.StoredProcedure

    .Parameters.Add(New SqlParameter("@UserName", SqlDbType.VarChar, 20))
    .Parameters("@UserName").Value = strUser

    .Parameters.Add(New SqlParameter("@SearchDt", SqlDbType.DateTime))
    .Parameters("@SearchDt").Value = DateAdd("n", -1, Now())

    // need to execute here, and find number of rows/records returned
End With

how can i capture the number of rows returned when i execute this stored procedure?

Answer 1

Try

Dim theCount Integer 
theCount = .ExecuteScalar()

Answer 2

Thanks for the comment update.

If the response of the Stored proc is a single value then you need to execute the SqlCommand which you have setup. If you ExecuteScaler() on this command then it will return an single Sql type, most likely and int and then you can convert it into a .net value.

Hope it helps :-)

Answer 3

Dim countEffected As Integer=0

With oCountCMD
    .CommandType = CommandType.StoredProcedure    
    .Parameters.Add(New SqlParameter("@UserName", SqlDbType.VarChar, 20, strUser))       
    .Parameters.Add(New SqlParameter("@SearchDt", SqlDbType.DateTime,,DateAdd("n", -1, Now())))

    countEffected = .ExecuteScalar()    
End With

'return true/false as previous VB6 did.
CheckSearchCounts = countEffected > 60