Database row counting -- HEEELLLPPPP!!!

  • Thread starter Thread starter Neo Geshel
  • Start date Start date
N

Neo Geshel

I am at my rope's end. Please, someone, help me or hang me!

I have (finally!!) been able to use ASP.NET to add records to an ACCESS
database. Now, all I need to do is count the number of rows that the
Access database has. I have tried about 8 different methods (of wildly
varying complexity... one method required four different files!), and
NONE of them work for me. Most examples are for SQL Server, which (for
some bizarre reason) will not install in my server (it hangs at the last
step). So I have to use an Access Database.

So far, this is the simplest example that I have been able to come up
with:

<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.OleDb.OleDbDataAdapter" %>

<script language="VB" runat="server" debug="true">
Sub Page_Load(sender As Object, e As EventArgs)
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim strSQLQuery As String

objConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\Inetpub\Web Sites\Metz\db\hitcount.mdb;")
strSQLQuery = "Select * From Browser"
objCommand = New OleDbCommand(strSQLQuery, objConnection)
objConnection.Open()
objDataReader = objCommand.ExecuteReader
(CommandBehavior.CloseConnection)

Dim DS as DataSet
DS = new DataSet()
objCommand.Fill(DS, "hits")

Dim RcdCount as Integer
RcdCount = DS.Tables("hits").Rows.Count.ToString()
ResultCount = RcdCount
RecordCount.Text = "There were " & RcdCount & " records found"

End Sub
</script>

<html>
<body>
<asp:label id="RecordCount" runat="server"></asp:label>
</body>
</html>




From this example, I get: Compiler Error Message: BC30456: 'Fill' is
not a member of 'System.Data.OleDb.OleDbCommand'.

However, EVERY database count example that I have come across makes use
of objCommand.Fill, and in almost the exact same way I have!!

HEEEELLLLPPPPPP!!!!!

How do I get the page to make use of objCommand.Fill without throwing an
error?

I like the power, but cannot understand the needless and illogical
complexity of asp.net. It seems like everything that took 10 lines to
code in normal ASP now takes 20 or more in asp.net. Couldn't MS have
simply added power and left out the complexity?

TIA to all those that take the time to help this poor sod.

....Geshel
--
************************************************************************
Reply-to e-mail forwards all received e-mails to SpamCop for UBE/UCE
(Unsolicited Bulk/Commercial E-mail) reporting. DO NOT USE THAT ADDRESS!
Please send e-mail to my first name at my last name dot org.
************************************************************************
 
Try strSQLQuery = "Select count(*) From Browser"

that returns you the row count without reading in the whole table in your
apps dataset
 
Try strSQLQuery = "Select count(*) From Browser"

that returns you the row count without reading in the whole table in your
apps dataset
Click to expand...

How would you handle that, then? Do you have an example? I tried this as
well, but all examples were for SQL Server, and I couldn't get my
adaptations to work either.

TIA
....Geshel
--
************************************************************************
Reply-to e-mail forwards all received e-mails to SpamCop for UBE/UCE
(Unsolicited Bulk/Commercial E-mail) reporting. DO NOT USE THAT ADDRESS!
Please send e-mail to my first name at my last name dot org.
************************************************************************
 
Neo said:
How would you handle that, then? Do you have an example? I tried this as
well, but all examples were for SQL Server, and I couldn't get my
adaptations to work either.

TIA
...Geshel
Click to expand...

The SQL will return a 1 row table with 1 column, which will contain the
number of records it counted in that table.

BTW, I think the first thing you mentioned was failing because the OLEDb
drivers don't support using the second parameter to Fill. Not all
databases types can fill just one table of a dataset.....
 
Hi Neo,

What you need to use is the OleDbDataAdapter, and I think you could also use
the free .Net SDK from microsoft for future reference. Here is a link to an
SDK article on the MSDN Library online about using the OleDbDataAdapter:

http://msdn.microsoft.com/library/d...systemdataoledboledbdataadapterclasstopic.asp

Here isw the link to the free download of the (amazingly excellent) .Net
SDK:

http://www.microsoft.com/downloads/...A6-3647-4070-9F41-A333C6B9181D&displaylang=en

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Big Things are made up of
Lots of Little Things.
 
Fill method is not a member of OleDbCommand class,Infact
it is a member of OleDbDataAdapter.

you can try the following code

OleDbDataAdapter dbAdapter = new OleDbDataAdaper
(sqlQuery,connObject)
dbAdapter.Fill(dsObject,"Name")

recordCount = dsObject.Tables("Name").Rows.Count
Click to expand...

Great. I still get an error. Now it is:

BC30684: 'OleDbDataAdapter' is a type and cannot be used as an
expression.

Below is the revised code:



<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.OleDb.OleDbDataAdapter" %>

<script language="VB" runat="server" debug="true">
Sub Page_Load(sender As Object, e As EventArgs)
Dim objConnection As OleDbConnection
Dim objDataReader As OleDbDataReader
Dim strSQLQuery As String

objConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\Inetpub\Web Sites\Metz\db\hitcount.mdb;")
strSQLQuery = "Select * From Browser"
OleDbDataAdapter dbAdapter = new OleDbDataAdapter (strSQLQuery,
objConnection)

Dim dsObject as DataSet
dsObject = new DataSet()
dbAdapter.Fill(dsObject,"hits")

recordCount = dsObject.Tables("hits").Rows.Count

End Sub
</script>

<html>
<body>
<asp:label id="RecordCount" runat="server"></asp:label>
</body>
</html>



Suggestions?

....Geshel
--
************************************************************************
Reply-to e-mail forwards all received e-mails to SpamCop for UBE/UCE
(Unsolicited Bulk/Commercial E-mail) reporting. DO NOT USE THAT ADDRESS!
Please send e-mail to my first name at my last name dot org.
************************************************************************
 
Hi Neo,

What you need to use is the OleDbDataAdapter, and I think you could also use
the free .Net SDK from microsoft for future reference. Here is a link to an
SDK article on the MSDN Library online about using the OleDbDataAdapter:

http://msdn.microsoft.com/library/d...systemdataoledboledbdataadapterclasstopic.asp

Here isw the link to the free download of the (amazingly excellent) .Net
SDK:

http://www.microsoft.com/downloads/...A6-3647-4070-9F41-A333C6B9181D&displaylang=en
Click to expand...

Thanks for your help. The link managed to help me do it correctly,
although the example on the web page needed a little tweaking. to work
correctly. Because I was working with a sub, the "return dataset" didn't
work, and I had to implement it another way:


<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.OleDb.OleDbDataAdapter" %>

<script language="VB" runat="server" debug="true">
Sub Page_Load(sender As Object, e As EventArgs)

Dim conn as New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\Inetpub\Web Sites\Metz\db\hitcount.mdb;")
Dim strSQLQuery As String
strSQLQuery = "Select * From Browser"
Dim adapter as New OleDbDataAdapter()
adapter.SelectCommand = new OleDbCommand(strSQLQuery, conn)

Dim dsObject as DataSet
dsObject = new DataSet()
adapter.Fill(dsObject,"hits")

Dim RcdCount as Integer
RcdCount = dsObject.Tables("hits").Rows.Count.ToString()
Dim ResultCount as String
ResultCount = RcdCount
RecordCount.Text = "There were " & RcdCount & " records found"

End Sub
</script>

<html>
<body>
<asp:label id="RecordCount" runat="server"></asp:label>
</body>
</html>


But hey, now it works!!!

Do you know of another way to do this more efficiently? Is there a way I
can compact the code some more? Also, the database is quite extensive. I
do a full browserdetect, as well as use cookies to determine if a user
is unique or not. Do you have any suggestions for implementing multiple
database counts? Normally I would re-do this entire script multiple
times to count the database on specific criteria (isunique, isrobot,
isIE, isMozilla, etc.) but I was wanting to create one database access,
and then run multiple SQL Queries & counts. How would you suggest I do
this?

I'm downloading the SDK as we speak. Hopefully it'll be of some help.

TIA.
....Geshel
--
************************************************************************
Reply-to e-mail forwards all received e-mails to SpamCop for UBE/UCE
(Unsolicited Bulk/Commercial E-mail) reporting. DO NOT USE THAT ADDRESS!
Please send e-mail to my first name at my last name dot org.
************************************************************************
 
It seems that I have more problems. I am trying to do multiple counts of
unique database variables. I am trying to make the code more efficient.
Below is my code in trying to extract counts of the total number of
database entries, as well as the total number of DB entries that conform
to a particular criteria:


<%@ Page Language="VB" Debug="true"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.OleDb.OleDbDataAdapter" %>

<script language="VB" runat="server" debug="true">
Dim ObjConnection as OleDbConnection
Dim StrSQLQuery As String
Dim ObjAdapter as New OleDbDataAdapter()
Dim ObjDataSet as DataSet
Dim TotalHitCount as String
Dim UniqueHitCount as String

Sub Page_Load(sender As Object, e As EventArgs)
ObjConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\Inetpub\Web Sites\Metz\db\hitcount.mdb;")
TotalHits
' UniqueHits
End Sub

Sub TotalHits()
StrSQLQuery = "SELECT * FROM Browser"
ObjAdapter.SelectCommand = new OleDbCommand(StrSQLQuery,
ObjConnection)
ObjDataSet = new DataSet()
ObjAdapter.Fill(ObjDataSet,"hits")
Dim RcdCount as Integer
RcdCount = ObjDataSet.Tables("hits").Rows.Count.ToString()
TotalHitcount = RcdCount
TotalHitcount.Text = "<p>Your web site has been accessed " & RcdCount
& " times.</p>"
End Sub

Sub UniqueHits()
StrSQLQuery = "SELECT * FROM Browser WHERE IsUnique = True"
ObjAdapter.SelectCommand = new OleDbCommand(StrSQLQuery,
ObjConnection)
ObjDataSet = new DataSet()
ObjAdapter.Fill(ObjDataSet,"hits")
Dim RcdCount as Integer
RcdCount = ObjDataSet.Tables("hits").Rows.Count.ToString()
UniqueHitcount = RcdCount
UniqueHitcount.Text = "<p>There have been " & RcdCount & " unique
visitors to your site.</p>"
End Sub
</script>

<html>
<body>
<asp:label id="TotalHitcount" runat="server"></asp:label>
<asp:label id="UniqueHitcount" runat="server"></asp:label>
</body>
</html>


However, I get the following error message:

Compiler Error Message: BC30260: 'TotalHitcount' is already declared as
'Protected Dim TotalHitcount As System.Web.UI.WebControls.Label' in this
class.


WHAT THE **** DOES THIS MEAN??? When I place the DIMs inside their
respective SUBs, I get the following error:


Compiler Error Message: BC30456: 'Text' is not a member of 'String'.

Referring to the *Hitcount.Text line.

Problem is, it worked without a problem before. Nothing has changed,
except that I am now splitting the code up into multiple SUBs!!!

These problems almost make me want to remain with traditional ASP. At
least I wouldn't have to deal with a schitzophrenic language that says
something one time and contradicts itself at another time. It worked the
first time (with the single SUB). Why won't it work now????

TIA to all that help this poor sod.
....Geshel
--
************************************************************************
Reply-to e-mail forwards all received e-mails to SpamCop for UBE/UCE
(Unsolicited Bulk/Commercial E-mail) reporting. DO NOT USE THAT ADDRESS!
Please send e-mail to my first name at my last name dot org.
************************************************************************
 
Back
Top