asp.net vb.net

convert from classic asp to asp.net / vb.net as follow.
For some reasons, asp.net does not return any values without any error...no idea why?

Function SingleItem(Category,RowNumber)

SQL=" SELECT * FROM ( "
SQL=SQL & " Select Description, PartNo, "
SQL=SQL & " Row_Number() Over(Order By PartNo) as rownumber, "
SQL=SQL & " Count(*) Over() as TotalRecordCount "
SQL=SQL & " From Products "
SQL=SQL & " where Category = '" & Category & "' ) T1 "
SQL=SQL & " WHERE rownumber = '" & RowNumber & "' "
SQL=SQL & " Order By partno asc "

Set dbsecurecon  = Server.CreateObject("ADODB.Connection")
dbsecurecon = "PROVIDER=SQLOLEDB;DATA SOURCE=1533;DATABASE=aviation;User ID=sa;Password=1"
Set RS = Server.CreateObject("ADODB.recordset")
RS.open SQL, dbsecurecon,3,3
If Not RS.EOF Then
PartNo = RS("PartNo")
End If
 SingleItem=PartNo
End Function


Private Function SingleItem(ByVal Category As String, ByVal RowNumber As Integer) As String
        Dim strPartNo As String = String.Empty
        Dim myConnection As New SqlConnection
        Dim myCommand As SqlCommand
        Dim dr As SqlDataReader
        Try
            myConnection.ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=1533;DATABASE=aviation;User ID=sa;Password=1"
            myConnection.Open()
            'opening the connection
            'Dim sSQLCmd As String = String.Empty
            Dim SQL As New StringBuilder()
            SQL.Append(" SELECT * FROM ( ")
            SQL.Append(" Select Description, PartNo, ")
            SQL.Append(" Row_Number() Over(Order By PartNo) as rownumber, ")
            SQL.Append(" Count(*) Over() as TotalRecordCount ")
            SQL.Append(" From Products ")
            SQL.Append(" where Category = '" & Category & "' ) T1 ")
            SQL.Append(" WHERE rownumber = '" & RowNumber & "' ")
            SQL.Append(" Order By partno asc ")
            myCommand = New SqlCommand(SQL.ToString(), myConnection)
            'executing the command and assigning it to connection
            dr = myCommand.ExecuteReader()

            If Not IsDBNull(dr("PartNo")) Then
                strPartNo = dr("PartNo")

            Else
                strPartNo = "No data"
            End If

            'displaying the data from the table
            dr.Close()

            myConnection.Close()
        Catch e As Exception
        End Try
        Return strPartNo
    End Function
Webboy2008Asked:
Who is Participating?
 
PaulHewsCommented:
You need to call Read on the datareader for one thing...

           dr = myCommand.ExecuteReader()
            If dr.Read() AndAlso Not IsDBNull(dr.GetOrdinal("PartNo")) Then
                strPartNo = dr.GetString(dr.GetOrdinal("PartNo"))
            Else
                strPartNo = "No data"
            End If

Open in new window

0
 
PaulHewsCommented:
Correction:
        If dr.Read() AndAlso Not dr.IsDBNull(dr.GetOrdinal("PartNo")) Then
            strPartNo = dr.GetString(dr.GetOrdinal("PartNo"))
        Else
            strPartNo = "No data"
        End If

Open in new window

0
 
Webboy2008Author Commented:
PaulHews:still no data
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.