Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Sql Query count using .net

I have the following code I would l ike to use as a sanity check.

I am open to suggestions if there is a better way.

This code currently does not work because ExecuteNonQuery only works on insert,update and delete statements.

Can anyone point out what I should be using?
Thank You

Dim CheckRecordStatus As String = "select * from table where RecordStatus is NULL"

        'Instantiate the SQL Query
        Dim CMDCheckRecord As SqlCommand = Nothing
        Try
            CMDCheckRecord = New SqlCommand(CheckRecordStatus, _conn)
            CMDCheckRecord.CommandTimeout = 30
            CheckRecordCount = CMDCheckRecord.ExecuteNonQuery()
            If CheckRecordCount > 0 Then Throw New Exception("A RecordStatus is Null. ")
        Catch ex As Exception
            CMDCheckRecord.Dispose()
            Throw New Exception("STOP There is at least one empty RecordStatus. " & ex.Message)
        End Try
0
jeremy4help
Asked:
jeremy4help
  • 3
  • 3
2 Solutions
 
Paul JacksonCommented:
ExecuteNonQuery will not return the number of rows affected for a select staement only for update, insert or delete.

try changing :
Dim CheckRecordStatus As String = "select * from table where RecordStatus is NULL"
to :
Dim CheckRecordStatus As String = "select * from table where RecordStatus is NULL;Select @@RowCount;"
0
 
Mohamed AbowardaSoftware EngineerCommented:
If you are trying to count the rows you can use SQL COUNT:
http://www.techonthenet.com/sql/count.php
0
 
jeremy4helpAuthor Commented:
Dim CheckRecordStatus As String = "select * from table where RecordStatus is NULL;Select @@RowCount;"

DIdnt work I had played with @@RowCount it seemes like it will work but something is wrong with the syntax

The count function works but I don't know how to pass the number back to my program
0
Industry Leaders: 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!

 
Mohamed AbowardaSoftware EngineerCommented:
0
 
jeremy4helpAuthor Commented:
ExecuteScalar() is still not filling the var with count or @@recordstatus.
0
 
jeremy4helpAuthor Commented:
i meant @@rowcount
0
 
Mohamed AbowardaSoftware EngineerCommented:
If you used DataSet, you will be able to count the rows:
http://vb.net-informations.com/dataset/dataset-count-rows-sqlserver.htm

ds.Tables(0).Rows.Count

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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