[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

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

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
efz
Asked:
efz
1 Solution
 
nmarunCommented:
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
 
DhaestCommented:
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
 
Vikram Singh SainiSoftware Engineer cum AD DeveloperCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
efzAuthor Commented:
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
 
Vikram Singh SainiSoftware Engineer cum AD DeveloperCommented:
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
 
efzAuthor Commented:
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
 
efzAuthor Commented:
Visual Basic programmers will need to look at the VB adjustments in the later comment by efz.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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