We help IT Professionals succeed at work.

Function To Connect To Database ASP.NET VB

vdiaz761
vdiaz761 asked
on
Medium Priority
275 Views
Last Modified: 2008-02-01
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
Comment
Watch Question

Elvio Lujan.Net Senior Developer
CERTIFIED EXPERT

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

Author

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

Author

Commented:
Lem,

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

Vic
   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.
.Net Senior Developer
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
what do I need to import for DataSet (i.e Imports System.Data.OleDb)
Elvio Lujan.Net Senior Developer
CERTIFIED EXPERT

Commented:
System.Data

Author

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
Elvio Lujan.Net Senior Developer
CERTIFIED EXPERT

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

Author

Commented:
Perfect! You da man! Thanks...

Author

Commented:
Lem...

Are you ready?
Isaiah Chapter 13
Elvio Lujan.Net Senior Developer
CERTIFIED EXPERT

Commented:
sure man... but still i have to live in this planet :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.