Database Programming from ASP Classic to ASP.NET

[ajclifford]

Hi,

I've migrated from ASP Classic to ASP.NET but I'm having real trouble learning the new ADO.NET way of database programming. I used to use the following two functions for creating ASP Classic database connections:

<%
Function CreateConn()

 dim Connect
 dim cnDB
 Connect = "Provider=SQLOLEDB.1;Data Source=DBASESVR;Initial Catalog=Contractors;uid=user;pwd=pass"
 Set cnDB = Server.CreateObject("ADODB.Connection")
 cnDB.Open(Connect)
 Set CreateConn = cnDB

End Function

Function SQLQuery(cnDB, queryString)

 dim rsADO
 Set rsADO = Server.CreateObject("ADODB.Recordset")
 rsADO.ActiveConnection = cnDB
 rsADO.Open(queryString)
 Set SQLQuery = rsADO

End Function
%>

And then display data to my ASP Classic pages with code like this:

<%
dim cnDB
Set cnDB = CreateConnection

SqlCommandView = "SELECT ID, Name FROM Contacts WHERE ID=1"
Set RsView = SQLQuery(cnDB, SqlCommandView)

Dim DataVariable
DataVariable = RsView("Name")
%>

How do I convert this process to ADO.NET? Help would be muchly appreicated, thanks!

Alex

 

[ca8msm]

You should have a look at the System.Data.SQLClient class as this will give you the options you need to connect to a SQL Server database.

There is a MSDN article at:

http://msdn.microsoft.com/library/d...ref/html/frlrfSystemDataDataSetClassTopic.asp

that you can use and adapt.

Try having a go using their example and let us know if you have any problems.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ajclifford]

How do I use that class? And I don't think I want a DataGrid? This method seems very complicated for just selecting a singluar column from a table. Do you know of a tutorial on the technique that explains it better?

Alex

 

[ca8msm]

That example may be a bit longer than you wanted but within the code it gives you examples of not only how to connect to the database, but also how to work with the data.

If you just want a really simple example I've just created the basics here:

Imports System.Data.SqlClient
Public Class WebForm1
    Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object


    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlCommand
        Dim MyReader As SqlDataReader

        MyConnection = New SqlConnection("server=MyServer;uid=MyUser;pwd=MyPassword;database=MyDb")
        MyConnection.Open()
        MyCommand = New SqlCommand("select id from MyTable", MyConnection)
        MyReader = MyCommand.ExecuteReader

        While MyReader.Read
            Response.Write(MyReader.GetValue(0))
        End While

    End Sub

End Class

Hope this helps.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ajclifford]

Hi ca8msm, thanks very much for the help. I got it working from the last class code you showed me. Much appreciated.

Alex