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

Joining two access connections together.

Hey guys,

The below code works but i wanted to know if there was a better way of joining them so i only connect to the db once?

Public Class Class1

    Shared Function noposts(ByVal forumname As String) As String
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim dbcmd As String = "Select Count(*) From forum where Forum =@forumname"
        Dim cmd As New OleDbCommand(dbcmd, con)
        cmd.Parameters.AddWithValue("@forumname", forumname)
        Try
            con.Open()
            Dim Count As Integer = cmd.ExecuteScalar()
            Return Count
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try
    End Function
    Shared Function noposts2(ByVal forumname As String) As String
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim norep As String = "Select Count(*) From forum where Forum =@forumname and LastPoster <> '' "
        Dim cmd As New OleDbCommand(norep, con)
        cmd.Parameters.AddWithValue("@forumname", forumname)
        cmd.Parameters.AddWithValue("@numrep", "0")


        Try
            con.Open()
            Dim Counter As Integer = cmd.ExecuteScalar()
            Return Counter
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try
    End Function
End Class

Open in new window

0
runnerjp2005
Asked:
runnerjp2005
  • 5
  • 4
1 Solution
 
Imran Javed ZiaCommented:
Hi,
you can use it as following
 
Public Class Class1

    Shared Function GetConnection() As OleDbConnection
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        con.Open()
        Return con
    End Function




    Shared Function noposts(ByVal forumname As String) As String
        Dim con As OleDbConnection = GetConnection()

        Dim dbcmd As String = "Select Count(*) From forum where Forum =@forumname"
        Dim cmd As New OleDbCommand(dbcmd, con)
        cmd.Parameters.AddWithValue("@forumname", forumname)
        Try
            Dim Count As Integer = cmd.ExecuteScalar()
            Return Count
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try
    End Function
    Shared Function noposts2(ByVal forumname As String) As String

        Dim con As OleDbConnection = GetConnection()

        Dim norep As String = "Select Count(*) From forum where Forum =@forumname and LastPoster <> '' "
        Dim cmd As New OleDbCommand(norep, con)
        cmd.Parameters.AddWithValue("@forumname", forumname)
        cmd.Parameters.AddWithValue("@numrep", "0")


        Try

            Dim Counter As Integer = cmd.ExecuteScalar()
            Return Counter
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try
    End Function
End Class

Open in new window

Thanks
0
 
CodeCruiserCommented:
Another approach


Public Class Class1
Public Structure Results
    Dim Result1 As Integer
    Dim Result2 As Integer
End Structure

    Shared Function noposts(ByVal forumname As String) As Results
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@forumname", forumname)
        Try
            con.Open()
            cmd.CommandText = "Select Count(*) From forum where Forum =@forumname"
            Dim Res As Results
            Res.Result1 = cmd.ExecuteScalar()
            cmd.CommandText =  "Select Count(*) From forum where Forum =@forumname and LastPoster <> '' "
            Res.Result2 = cmd.ExecuteScalar()
            
            Return Res
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try
    End Function

Open in new window

0
 
CodeCruiserCommented:
Do add the "End Class" at the end
0
Independent Software Vendors: 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!

 
runnerjp2005Author Commented:
would this be any different to my origianl way??
will it make a difference??
0
 
runnerjp2005Author Commented:
Also before i used

     Postnumber.Text = Class1.noposts2("General") & "/" & Class1.noposts("General")

to display the results ... how would i display the 2 results with your code codecruiser?
0
 
CodeCruiserCommented:
Move Results structure to a module

Dim Res As Results = Class1.noposts("General")
Postnumber.Text = Res.Result2 & "/" & Res.Result1
0
 
runnerjp2005Author Commented:
Thanks again codecruiser... u saved me again :)

can i asked what the advantages of doing it your way are?
#
is it faster?
0
 
CodeCruiserCommented:
Its using a single function (less code) and its opening database connection only once. It should be faster theoratically.
0
 
runnerjp2005Author Commented:
Thanks again :)
0
 
CodeCruiserCommented:
Glad to help :-)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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