How do I make a connection to the database in ASP .NET?

Posted on 2007-07-24
Last Modified: 2013-11-26
In classic ASP I would connect to any database like this

      Set conn = Server.CreateObject("ADODB.Connection")
      conn.Open = "Provider=SQLOLEDB.1;Data Source=......."

      Set com = Server.CreateObject("ADODB.Command")
      com.CommandType = 1
      Set rs = Server.CreateObject("ADODB.Recordset")
      Set com.ActiveConnection = conn

Is this the same way I would do it in ASP .NET 2.0?

I want to have as much flexibility when I execute any SQL statement.  

Thanks in advance...
Question by:mmedi005
    LVL 8

    Expert Comment

    LVL 10

    Expert Comment

    Private MyStr as String = "Provider=SQLOLEDB.1;Data Source=......."
    Private Conn as New SQLConnection(MyStr)

    Sub OpenDB
    End Sub
    LVL 10

    Expert Comment

    Sorry, you must import

    On the top of the class,

    Imports System.Data.SqlClient

    Author Comment

    that easy? is it pretty much all the same code?
    LVL 10

    Expert Comment

    Yes. It's nothing complicated.

    Just take not if your are using sqlclient to connect, the connection string is something like

    If you are using SQL Server Connection, you can try something like:
    (and take note where is your SQL Server, save if using IP Address and assign a Port Number)

    "Data,PortNumber;Initial Catalog=DBName;UID=YourID;Pwd=YourPwd;"

    Author Comment

    then how do I execute sql statements, get record sets?

    What I'm trying to ask is, how do set up the command and recordset?

    Old way you had cmd.CommandText = "", cmd. Execute, or Set rs = cmd.Execute  
    LVL 10

    Expert Comment

    Well, sounds like another question though...

    Various ways available, you can use Datareader or DataAdapter with Dataset to read/or store your

    For executing SQL, you can ExecuteNonQuery for non-result returning SQL
    Use ExecuteScalar for only return one value

    For DataReader, once you finished reading, there's no way you can get back the resultset but
    reading is fast.

    Something like:

    Private Sub CheckPrice()
            Dim Cmd As New SqlCommand("SP_Check_Price", Conn)
            Cmd.CommandType = CommandType.StoredProcedure 'For Stored Procedure
            Cmd.CommandType = CommandType.CommandText  ''For SQL or don't need to declare for SQL
           'Below are parameters declared for Stored Procedures
            With Cmd.Parameters
                .Add(New SqlParameter("@Customer_Code", Trim(cbCust.Text)))  
                .Add(New SqlParameter("@Outlet_Code", Trim(cbOutlet.Text)))
                .Add(New SqlParameter("@Item_Code", Trim(cbItem.Text)))
                .Add(New SqlParameter("@UOM_Code", Trim(cbUOM.Text)))
            End With
           'Declare a DataReader here and read the resultset
            Dim RD As SqlDataReader = Cmd.ExecuteReader
                While RD.Read
                    MyStrings += RD(0).ToString
                End While
            Catch ex As Exception
                MsgBox("Error while executing (" & Cmd.CommandText & ")" & vbCrLf & "Details: " & _
                ex.Message, MsgBoxStyle.Exclamation, "MSIS Server")
            End Try
    End Sub

    'For dataset, you can take a look at:

    Author Comment

    It seems so much easier in classic ASP.

    I would iterate a SELECT statement with a while loop....

    While Not rs.EOF
            s = rs("something").value

    Now I have to declare numerous objects and stick information inside arrays....

    Is there a way to grab the information much easier or in the similiar fashion?
    LVL 10

    Accepted Solution

    You got it all wrong.

    I am just giving you an example lol.:D

    While RD.Read
          'This reading here reads all the rows that you have fetch from your SQL Statement
    End While

    It's a brand new concept and more OOP in .net compare to typical VB.
    If you are more familiar using the recordset, I advise you to use dataadapter. I show you an example here.

    You have three rows of data here in a table with these columns

    TableName = Customer
    Col1 = Customer_Code
    Col2 = Tel
    Col3 = Fax
    Row1 = Adrian, 2525256, 2525266
    Row2 = Tom, 3332222, 3332233
    Row3 = June, 4443322, 4443333

    Private Sub LoadCustomer
     Dim CmdStr as string = "Select * From Customer"
     Dim Cmd As New SqlCommand(CmdStr, MyConnection)
     Cmd.Parameters.Add(New SqlParameter("@Item_Code", Para))
     Dim da As New SqlDataAdapter
     Dim ds As New DataSet
     Dim dt As DataTable
     da.SelectCommand = Cmd
                da.Fill(ds, "Data")
                dt = ds.Tables("Data")
            Catch ex As Exception
                MsgBox("Error: " & ex.Message, MsgBoxStyle.Exclamation, "Eunix POS Server")
            End Try
    End Sub

    Private Sub Pop(Byval DT as DataTable)
    Dim x as Integer
    For x = 0 to DT.Rows.Count -1
    End Sub

    'The above example you will see the
    First three msgbox as "Adrian, 2525256, 2525266" respectively
    Second three msgbox as "Tom, 3332222, 3332233" respectively
    Third three msgbox as "June, 4443322, 4443333" respectively

    You may find a bit troublesome starting to use VB.Net but when you get familiarise with it, you will like it, especially when you slowly find more things you can play around with datasets and datatables

    Author Comment


    thanks for the help, I'm sure I will like VB .NET better, like anything, takes getting use to, but this will work.  I just want to create a function for my web page that can retrieve the info without writing all that.

    Thanks again......
    LVL 10

    Expert Comment

    If you just want to retrieve and display information easily, try explore data binding. It's just few sentences of code to bind a table to a grid.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    In my previous article ( we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
    Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now