Solved

How to get the database record count in MS SQL Server 2005?

Posted on 2010-08-18
7
720 Views
Last Modified: 2013-11-27
I have an aspx web page that I use privately to return records from a Microsoft SQL Server 2005 database file. The page works great. It allows me to view and pretty-print the records, which is just what I want. There is one annoying thing however. If the database is empty, the page loads with a NullReferenceException. I was wondering if there was Visual Basic code I could use to test the database for a zero record count to avoid this error. My old Delphi Borland Database Engine had a MyTable.RecordCount property that could be checked. I don’t see that the ASP.Net SQLDataSource component has a similar property that can be read. How or can this be done?
0
Comment
Question by:efz
7 Comments
 
LVL 27

Expert Comment

by:nmarun
ID: 33468030
Even if the record count is zero, it should not throw a NullReferenceException. There's something fishy about the code. Please post it.Arun
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 33468403
How are  you loading the data ?

If you are loading it into a datatable, you can check it through datatable.Rows.Count


To get the Record Count or Row Count returned by SQLDataSource control, we have to use DataSourceSelectArgument class.

Create an object to the DataSourceSelectArgument class, set its RetrieveTotalRowCount property to true and by using a DataView we can retrieve the Row Count of the SQLDataSource object. The sample block of code is given below. Try it.

DataSourceSelectArguments dssa = new DataSourceSelectArguments();
dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
dssa.RetrieveTotalRowCount = true;
DataView dv = (DataView)SqlDataSource1.Select(dssa);
Response.Write("No of Records : " + dv.Table.Rows.Count);
0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33469214
Hi,

Still question is not clear. Are you trying to check if the tables in the database are not null. And if this is the case you would need to use Microsoft.SqlServer.Management classes.

You can check the use of above namespace  classes at link:
http://niitdeveloper.blogspot.com/2010/07/vssc-vikram-sql-server-controller.html

Do write back with details.

Regards,
V.S.Saini
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:efz
ID: 33476033
Many thanks for your responses.

nmarun: It would probably help to explain that I am using an aspx page with an SQLDataSource component and a bunch of databound components (textboxes and checkboxes). The VB line throwing the exception is:

If cbxWA.Checked = True Then
     .... End If

My guess is that the cbxWA reference is throwing the error because the checkbox component expects to receive a true or false value. Consequently the page works fine when there’s a record in the database, but if the database is empty ….

dhaest: I really think the answer is in your code, but I can’t seem to implement it. I have some questions.

1. Why the semicolons? While I’m used to semicolons in Pascal where they’re required, Visual Basic does not accept semicolons as line terminations, correct?

2. These two lines do not compile (even without the semicolons):

DataSourceSelectArguments dssa = new DataSourceSelectArguments();
and
DataView dv = (DataView)SqlDataSource1.Select(dssa);

I do not recognize this as Visual Basic syntax.

3. In an effort to get your code to compile in VB, I converted it to:

'Test for empty database
Dim dssa As DataSourceSelectArguments
dssa = New DataSourceSelectArguments()
dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount)
dssa.RetrieveTotalRowCount = True
Dim dv As DataView
dv = SqlDataSource1.Select(dssa)
Response.Write("No of Records : " + dv.Table.Rows.Count)

Is this the VB equivalent of your code? It compiles until it comes to the line:

Dim dv As DataView

The compiler tells me “Type Dataview not defined”. I thought DataView was defined in the System module which is automatically incorporated into all VB, so I’m a bit stumped.

vs00saini: I can’t thank you enough for your kind response. I think dhaest has got the answer, so I’m going to defer investigation of your link in hopes that I can get a clarification on the above lines of code. If they work, they would appear to be a much simpler solution than that describe in your link, no?

0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33476170
Hi efz,

It's nice to know that you get solution to your problem. It's total your choice to look over link if problem still needs solution. Might be Dhaest solution is correct and solves your problem (I am too testing it).

But it would be ok if you let me know whether you are checking tables in the database are null or are you checking if database is having table or not.

Regards,
V.S.Saini
0
 

Author Comment

by:efz
ID: 33476290
Again, I must sincerely thank these three very knowledgeable individuals for their time and patience. The adaptation below works just right for me. The DataView problem was resolved by putting “Import System.Data” in the import section of the module. This was required even though there was a “Import System.Data.SQLClient” statement there already.

        'Test for no records
        Dim dssa As DataSourceSelectArguments
        dssa = New DataSourceSelectArguments()
        dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount)
        dssa.RetrieveTotalRowCount = True
        Dim dv As DataView
        dv = SqlDataSource1.Select(dssa)
        If dv.Table.Rows.Count = 0 Then
            Response.Write("Table is empty.")
            Exit Sub
        End If

It works great and I thank you all.
0
 

Author Closing Comment

by:efz
ID: 33476330
Visual Basic programmers will need to look at the VB adjustments in the later comment by efz.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

20 Experts available now in Live!

Get 1:1 Help Now