This is a slightly altered function (very similar to the previous post on DataRowCollection) but returns only one item of data – sometimes very useful if doing something like:

Dim SQLString As String = “SELECT FirstNames, LastName FROM StaffList WHERE StaffID=1

Dim dr As DataRow = new dbrecordhelper().GetDataRow(SQLString)

‘Check if a valid row is returned

if NOT dr is Nothing then

Me.lblFirstNames.Text = dr(“FirstNames”).ToString

Me.lblLastName.Text = dr(“LastName”).ToString

end if

dr = Nothing

SQLString = Nothing

—————– in dbrecordhelper.vb file in App_Code —————

Public Function GetDataRow(ByVal SQLString As String) As DataRow

‘Declare variables
Dim sqlConn As SqlConnection
Dim sqlComm As SqlCommand
Dim dataAdapt As SqlDataAdapter

Dim ds As DataSet
‘Declare the dataset
ds = New DataSet

‘Connect to SQL database
sqlConn = New SqlConnection(m_connString)

‘Build up SqlCommand
sqlComm = New SqlCommand(SQLString, sqlConn)
sqlComm.CommandType = CommandType.Text

‘Create the DataAdapter
dataAdapt = New SqlDataAdapter(sqlComm)

Dim dt As DataTable = ds.Tables(0)
If dt.Rows.Count > 0 Then

GetDataRow = dt.Rows(0)


GetDataRow = Nothing

End If

‘Close connection

ds = Nothing
dt = Nothing
sqlConn = Nothing
sqlComm = Nothing
dataAdapt = Nothing

End Function

This method means that we can do simple database interactions with minimal code on each aspx page. Any feedback appreciated – particularly if there is a better way of doing this!

Leave a Reply