Function To Connect To Database ASP.NET VB

Hello Experts,

I have the following code:
Function connectToDB(ByVal strQuery As String) As OleDbDataReader
    Dim connDb As OleDbConnection
    Dim cmdParamQuery As OleDbCommand
    Dim dtrDataReader As OleDbDataReader
    Dim datasource As String = (Server.MapPath(System.Configuration.ConfigurationManager.AppSettings("conString"))).ToLower()
    connDb = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + datasource)
    connDb.Open()
    cmdParamQuery = New OleDbCommand(strQuery, connDb) 'strQuery PASSED IN FUNCTION CALL
    dtrDataReader = cmdParamQuery.ExecuteReader()
    Return dtrDataReader
  End Function

  Sub displayData()
    Dim dtrNewsArticles As OleDbDataReader
    Dim strQuery As String = "SELECT * FROM tableName"
    dtrNewsArticles = connectToDB(strQuery)
    While dtrNewsArticles.Read()
      'Do Code
    End While
    'Close Database
  End Sub

My question is, how would I close the database after ther WHile Loop?

Thanks

Vic
vdiaz761Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Elvio Lujan.Net Senior DeveloperCommented:
vdiaz761...
first i suggest you use an dataset in thi case...

and for your problem you'll need to pass the connection to the funciton too:

Function connectToDB(ByVal strQuery As String, ByVal connDb As OleDbConnection) As OleDbDataReader
    Dim cmdParamQuery As OleDbCommand
    Dim dtrDataReader As OleDbDataReader
    Dim datasource As String = (Server.MapPath(System.Configuration.ConfigurationManager.AppSettings("conString"))).ToLower()
    connDb = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + datasource)
    connDb.Open()
    cmdParamQuery = New OleDbCommand(strQuery, connDb) 'strQuery PASSED IN FUNCTION CALL
    dtrDataReader = cmdParamQuery.ExecuteReader()
    Return dtrDataReader
  End Function

  Sub displayData()
    Dim connDb As OleDbConnection
    Dim dtrNewsArticles As OleDbDataReader
    Dim strQuery As String = "SELECT * FROM tableName"
    dtrNewsArticles = connectToDB(strQuery, connDb)
    While dtrNewsArticles.Read()
      'Do Code
    End While
    connDb.Close()
  End Sub
0
vdiaz761Author Commented:
Lem,

Thanks for the quick reply. Two things however:
I was trying to avoid having to Dim the OleDbConnection and I get the following error with your suggestion:
Object reference not set to an instance of an object.

This error is in reference to connDb.Close()

Thanks

Vic
0
vdiaz761Author Commented:
Lem,

Also, your suggestion to use a dataset... can you explain?

Vic
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

pbocanegraCommented:
   Public Function DevolverDataset(ByVal myExecuteQuery As String) As DataSet
        Dim MyConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("conn_peic.ConnectionString")
        Dim myConnection As New System.Data.OleDb.OleDbConnection(MyConnectionString)
        myConnection.Open()
        Dim dataadapter_PBQ As New OleDb.OleDbDataAdapter(myExecuteQuery, myConnection)
        Dim dataset_PBQ As New DataSet
        dataadapter_PBQ.Fill(dataset_PBQ)
        myConnection.Close()
        Return dataset_PBQ
    End Function


This Function returns Dummy Dataset, that bounds with any VB.NET controls.
0
Elvio Lujan.Net Senior DeveloperCommented:
Function connectToDB(ByVal strQuery As String) As DataSet
    Dim cmdParamQuery As OleDbCommand
    Dim ds As New DataSet
    Dim datasource As String = (Server.MapPath(System.Configuration.ConfigurationManager.AppSettings("conString"))).ToLower()

    connDb = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + datasource)

    connDb.Open()
    cmdParamQuery = New OleDbCommand(strQuery, connDb) 'strQuery PASSED IN FUNCTION CALL

    Dim da As New DataAdapter(cmdParamQuery)

    da.Fill(ds)

    Return ds
  End Function

  Sub displayData()
    Dim ds As DataSet
    Dim strQuery As String = "SELECT * FROM tableName"
    ds = connectToDB(strQuery, connDb)

    for each dr As DataRow in ds.Tables(0).Rows
        'Do Code
    next
  End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vdiaz761Author Commented:
what do I need to import for DataSet (i.e Imports System.Data.OleDb)
0
Elvio Lujan.Net Senior DeveloperCommented:
System.Data
0
vdiaz761Author Commented:
Great... Almost there. How do I now access the columns return from the query in my displayData subRoutine as I would have I used a datareader:
ie dataReader("columnName")

Thanks
0
Elvio Lujan.Net Senior DeveloperCommented:
   for each dr As DataRow in ds.Tables(0).Rows
        dr("columnName") 'with this you'll get the column value in the actual row
    next
0
vdiaz761Author Commented:
Perfect! You da man! Thanks...
0
vdiaz761Author Commented:
Lem...

Are you ready?
Isaiah Chapter 13
0
Elvio Lujan.Net Senior DeveloperCommented:
sure man... but still i have to live in this planet :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.