Counting rows in access db...

Posted on 2011-04-28
Last Modified: 2012-06-27
I currently have this code
  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)
            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 numrep = 0  "
            Res.Result2 = cmd.ExecuteScalar()

            cmd.CommandText = "Select TOP 1  title + '|' + LastPoster +'|' + Format(lasttime, 'dd/mm/yyyy hh:mm:ss') from forum where Forum =@forumname AND numrep = 0 order by lasttime DESC"
            Res.Result3 = cmd.ExecuteScalar()
            Return (Res)
        Catch ex As Exception
            Throw ex
        End Try
    End Function

Open in new window

i have 3 options i use to get no of threads from different forums
    Dim Res As Results = Class1.noposts("general")
        threads.Text = Res.Result1
 Dim Res As Results = Class1.noposts("races")
        threads.Text = Res.Result1
 Dim Res As Results = Class1.noposts("info")
        threads.Text = Res.Result1

now could i add all 3 together without changing the code above or would i have to re-write the code?
Question by:runnerjp2005

    Author Comment

    this is the way i have done it but seems long winded

      Dim Res As Results = Class1.noposts("Injurys&Health")
            Dim Res2 As Results = Class1.noposts("general")
            Dim Res3 As Results = Class1.noposts("News")
            Dim Res4 As Results = Class1.noposts("General")
            Dim Res5 As Results = Class1.noposts("Training&Races")
            Dim totalthreads As Integer = Res.Result1 + Res2.Result1 + Res3.Result1 + Res4.Result1 + Res5.Result1
            threads.Text = totalthreads

    Open in new window

    LVL 83

    Accepted Solution

    Try this

            Dim totalthreads As Integer = Class1.noposts("Injurys&Health").Result1 + Class1.noposts("general").Result1 + Class1.noposts("News").Result1 + Class1.noposts("General").Result1 + Class1.noposts("Training&Races").Result1
            threads.Text = totalthreads

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now