I needed to access a SQL 2008 database from my codebehind without a control like a gridview.  After scouring the internet I put together the pieces to do so.

If you were familiar with the (oldschool) “non .NET”  scripting you remember doing something like the following:

Conn = Server.CreateObject("ADODB.Connection") 
Conn.Open("ODBC-DSN", "sa", "**********") 
rs = Server.CreateObject("ADODB.Recordset") 
rs.Open(StrSQL, Conn) 
Do While Not rs.eof 
Urvariable=rs("whs-num").value 
rs.movenext() 
Loop

You can still do this in an ASPX page if you use aspcompat=”true”, but as many women have said to me throughout the years “It’s time we move on Vin” (or “no, it really IS you” but that doesn’t apply here)   So the replacement .NET code to do the same thing is…

Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data.SqlTypes
Dim URConnection As System.Data.SqlClient.SqlConnection
Dim URCommand As System.Data.SqlClient.SqlCommand
Dim URDataReader As System.Data.SqlClient.SqlDataReader
Dim StrSQL, URVariable as String
'Your sql string
strSQL = "Select * From Sales"
'Set your Connections and your command
URConnection = New System.Data.SqlClient.SqlConnection("server=URSERVER; initial catalog=URDB;uid=sa;pwd=********")
URConnection.Open()
URCommand = New System.Data.SqlClient.SqlCommand
URCommand.Connection = URConnection
URCommand.CommandText = StrSQL
URDataReader = URCommand.ExecuteReader()

While (URDataReader.Read)
URVariable = URDataReader.Item("whs-num")
End While

URDataReader.Close()
URDataReader = Nothing
URCommand = Nothing
URConnection.Close()
URConnection = Nothing

If you want an explanation of the newer commands, you can Google  it or look on MS’s site.  For my purposes of creating a data driven web page every once in a while, the nitty gritty is not what I’m concerned about.  I just need it to work.  If you have an aspx page that uses old asp code now, it actually is not too difficult to convert it over using a few copy and pastes and Find and Replaces.

Advertisements