Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

classic asp to asp.net/vb.net

Posted on 2011-09-11
12
Medium Priority
?
443 Views
Last Modified: 2012-05-12
Trying to convert the attached codes from classic asp to asp.net/vb.net.
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

Open in new window

0
Comment
Question by:Webboy2008
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 36519790
OK, I understand why you referred to .net in the other post.

Sorry, I am a Classic ASP, and have very little experience with .net coding.

Good Luck
Carrzkiss
0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36521719
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.ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=1xxx;DATABASE=xxx;User 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.
0
 

Author Comment

by:Webboy2008
ID: 36522959
thank. since, it only returns one item. can we avoid using while statement to reduce some loading time?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36524201
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.



0
 

Author Comment

by:Webboy2008
ID: 36524269
I don't want data table.  Can you just modify your codes? Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36524349
>>thank. since, it only returns one item. can we avoid using while statement to reduce some loading time? <<
Then try and IF statement instead.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36524386
>>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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36524395
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
 
0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36524624
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.ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=1xxx;DATABASE=xxx;User 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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36525062
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
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 36526344
How about using ExecuteScalar() function if its only one string to return



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
            Try
                myConnection.ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=1xxx;DATABASE=xxx;User ID=sa;Password=xxx"
                myConnection.Open()
                'opening the connection
                Dim sSQLCmd As String = String.Empty
                sSQLCmd = "SELECT IsNull(PartNo, '') 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 
                strPartNo = myCommand.ExecuteScalar()

                myConnection.Close()
            Catch e As Exception
            End Try
            Return strPartNo
        End Function

Open in new window

0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36527365
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question