Webboy2008
asked on
classic asp to asp.net/vb.net
Trying to convert the attached codes from classic asp to asp.net/vb.net.
See any experts can help ....
Thanks
See any experts can help ....
Thanks
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=1xxx;DATABASE=xxx;User ID=sa;Password=xxx"
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
Header of ASp.NET code file you need to write
Imports System.Data.SqlClient
'VB.NET function returns string value.
Private Function SingleItem(ByVal Category As Integer, 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.ConnectionStr ing = "PROVIDER=SQLOLEDB;DATA SOURCE=1xxx;DATABASE=xxx;U ser ID=sa;Password=xxx"
myConnection.Open()
'opening the connection
Dim sSQLCmd As String = String.Empty
sSQLCmd = "SELECT * FROM (Select Description, PartNo,Row_Number() Over(Order By PartNo) as rownumber, Count(*) Over() as TotalRecordCount From Products " _
& "where Category = '" & Category & "' ) T1 WHERE rownumber = '" & RowNumber & "' Order By partno asc"
myCommand = New SqlCommand(sSQLCmd, myConnection)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()
While dr.Read()
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
End While
dr.Close()
myConnection.Close()
Catch e As Exception
End Try
Return strPartNo
End Function
Hope this will work, let me know if you face any issue.
Imports System.Data.SqlClient
'VB.NET function returns string value.
Private Function SingleItem(ByVal Category As Integer, 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.ConnectionStr
myConnection.Open()
'opening the connection
Dim sSQLCmd As String = String.Empty
sSQLCmd = "SELECT * FROM (Select Description, PartNo,Row_Number() Over(Order By PartNo) as rownumber, Count(*) Over() as TotalRecordCount From Products " _
& "where Category = '" & Category & "' ) T1 WHERE rownumber = '" & RowNumber & "' Order By partno asc"
myCommand = New SqlCommand(sSQLCmd, myConnection)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()
While dr.Read()
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
End While
dr.Close()
myConnection.Close()
Catch e As Exception
End Try
Return strPartNo
End Function
Hope this will work, let me know if you face any issue.
ASKER
thank. since, it only returns one item. can we avoid using while statement to reduce some loading time?
If it only returns only one value then While loop repeat for one time only; so you no need to worry about loading, actually datareader is light wait.
If you still want to avoid then go for .net data table.
If you still want to avoid then go for .net data table.
ASKER
I don't want data table. Can you just modify your codes? Thanks
>>thank. since, it only returns one item. can we avoid using while statement to reduce some loading time? <<
Then try and IF statement instead.
Then try and IF statement instead.
>>Can you just modify your codes?<<
How difficult can it be, instead of:
While dr.Read()
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
End While
Use:
If dr.Read()
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
Else
' Oops no data.
End If
How difficult can it be, instead of:
While dr.Read()
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
End While
Use:
If dr.Read()
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
Else
' Oops no data.
End If
Let's try that again:
If dr.Read() Then
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
Else
' Oops no data.
End If
If dr.Read() Then
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
Else
' Oops no data.
End If
Private Function SingleItem(ByVal Category As Integer, 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.ConnectionStr ing = "PROVIDER=SQLOLEDB;DATA SOURCE=1xxx;DATABASE=xxx;U ser ID=sa;Password=xxx"
myConnection.Open()
'opening the connection
Dim sSQLCmd As String = String.Empty
sSQLCmd = "SELECT * FROM (Select Description, PartNo,Row_Number() Over(Order By PartNo) as rownumber, Count(*) Over() as TotalRecordCount From Products " _
& "where Category = '" & Category & "' ) T1 WHERE rownumber = '" & RowNumber & "' Order By partno asc"
myCommand = New SqlCommand(sSQLCmd, myConnection)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()
If Not IsDBNull(dr("PartNo") Then
strPartNo = CStr(dr("PartNo"))
End If
'displaying the data from the table
dr.Close()
myConnection.Close()
Catch e As Exception
End Try
Return strPartNo
End Function
This will work.. do not forget to provide rating
Dim strPartNo As String = String.Empty
Dim myConnection As New SqlConnection
Dim myCommand As SqlCommand
Dim dr As SqlDataReader
Try
myConnection.ConnectionStr
myConnection.Open()
'opening the connection
Dim sSQLCmd As String = String.Empty
sSQLCmd = "SELECT * FROM (Select Description, PartNo,Row_Number() Over(Order By PartNo) as rownumber, Count(*) Over() as TotalRecordCount From Products " _
& "where Category = '" & Category & "' ) T1 WHERE rownumber = '" & RowNumber & "' Order By partno asc"
myCommand = New SqlCommand(sSQLCmd, myConnection)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()
If Not IsDBNull(dr("PartNo") Then
strPartNo = CStr(dr("PartNo"))
End If
'displaying the data from the table
dr.Close()
myConnection.Close()
Catch e As Exception
End Try
Return strPartNo
End Function
This will work.. do not forget to provide rating
You can also do:
If dr.HasRows Then
dr.Read()
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
Else
' Oops no data.
End If
If dr.HasRows Then
dr.Read()
strPartNo = CStr(dr("PartNo"))
'displaying the data from the table
Else
' Oops no data.
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ExecuteScalar will also work.
ExecuteScalar() in SqlCommand Object is used for get a single value from Database after its execution. It executes the query, and returns the first column of the first row in the result set returned by the query.
It returns the first field in the first row. This is very light-weight and is perfect when all your query asks for is one item.
This would be excellent for receiving a count of records in an sql statement, or for any query where only one specific field in one column is needed.
ExecuteNonQuery
ExecuteNonQuery() is used when you do not need to return any values back, it is most often used with INSERT UPDATE DELETE queries.
These changes can be of to create table using executeNonQuery or to insert, update, delete Records in Table. These type of operations are know as catalog operations.
ExecuteReader
ExecuteReader method is used for sequential Data Reading from Database. while datareader is in use, the connection object related to DataReader is busy. we cannot use the connection for other operations. We can only use that connection object until we close either the connection or datareader.
http://dng-ado.blogspot.com/2007/05/what-is-difference-between.html
I hope this will be useful.
ExecuteScalar() in SqlCommand Object is used for get a single value from Database after its execution. It executes the query, and returns the first column of the first row in the result set returned by the query.
It returns the first field in the first row. This is very light-weight and is perfect when all your query asks for is one item.
This would be excellent for receiving a count of records in an sql statement, or for any query where only one specific field in one column is needed.
ExecuteNonQuery
ExecuteNonQuery() is used when you do not need to return any values back, it is most often used with INSERT UPDATE DELETE queries.
These changes can be of to create table using executeNonQuery or to insert, update, delete Records in Table. These type of operations are know as catalog operations.
ExecuteReader
ExecuteReader method is used for sequential Data Reading from Database. while datareader is in use, the connection object related to DataReader is busy. we cannot use the connection for other operations. We can only use that connection object until we close either the connection or datareader.
http://dng-ado.blogspot.com/2007/05/what-is-difference-between.html
I hope this will be useful.
Sorry, I am a Classic ASP, and have very little experience with .net coding.
Good Luck
Carrzkiss