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

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
0
vdiaz761
Asked:
vdiaz761
  • 6
  • 5
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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