• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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

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...
0
mmedi005
Asked:
mmedi005
  • 6
  • 4
1 Solution
 
adriankohwsCommented:
Private MyStr as String = "Provider=SQLOLEDB.1;Data Source=......."
Private Conn as New SQLConnection(MyStr)

Sub OpenDB
     Conn.Open()
End Sub
0
 
adriankohwsCommented:
Sorry, you must import

On the top of the class,

Imports System.Data.SqlClient
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
mmedi005Author Commented:
that easy? is it pretty much all the same code?
0
 
adriankohwsCommented:
Yes. It's nothing complicated.

Just take not if your are using sqlclient to connect, the connection string is something like
"Provider=SQLOLEDB.1............................"

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 Source=xxx.xxx.xxx.xxx,PortNumber;Initial Catalog=DBName;UID=YourID;Pwd=YourPwd;"
0
 
mmedi005Author Commented:
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  
0
 
adriankohwsCommented:
Well, sounds like another question though...

Various ways available, you can use Datareader or DataAdapter with Dataset to read/or store your
resultset(s).

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
        Try
            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")
        Finally
            RD.Close()
            Cmd.Dispose()
        End Try
End Sub

'For dataset, you can take a look at:
http://www.codeproject.com/vb/net/Working_with_Dataset_Grid.asp
0
 
mmedi005Author Commented:
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
Wend

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?
0
 
adriankohwsCommented:
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
 Try
            da.Fill(ds, "Data")
            dt = ds.Tables("Data")
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Exclamation, "Eunix POS Server")
        Finally
            Cmd.Dispose()
        End Try
        Pop(dt)
End Sub

Private Sub Pop(Byval DT as DataTable)
Dim x as Integer
For x = 0 to DT.Rows.Count -1
     Msgbox(DT.Rows(x)("Customer").ToString)
     Msgbox(DT.Rows(x)("Tel").ToString)
     Msgbox(DT.Rows(x)("Fax").ToString)
Next
DT=Nothing
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
0
 
mmedi005Author Commented:
adriankohws,

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......
0
 
adriankohwsCommented:
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now