number of row returned

[Guest]

Hello,
What is the easiest way to run a select SQL string and find out the number of row returned. If you give a code example it would be helpful?
Jim.

 

[Jared]

This will return just the number of rows, no actual data.

Dim conn As New SqlConnection("Sever=MyServer;DataBase=MyDatabase;Trusted
Connection=True;")
conn.Open()
Dim cmd As New SqlCommand("Select Count(MyField) FROM MyTable", conn)
Dim result As String = cmd.ExecuteScalar().ToString
conn.Close()

If you use a dataadapter to fill a dataset you can access the datasets'
count property to get the number of records returned. This will return the
data and give you a row count.

Dim conn As New SqlConnection("Sever=MyServer;DataBase=MyDatabase;Trusted
Connection=True;")
conn.Open()
Dim da As New SqlDataAdapter("Select * from MyTable", conn)
Dim ds As New DataSet
da.Fill(ds)
Dim result As String = ds.Tables(0).Rows.Count.ToString
conn.Close

 

[Dale]

Or, better yet, run a stored procedure and in your stored procedure, declare
a couple integer variables, something like:

DECLARE @RC INT,
@ ERR INT

Then, in the next line after your select statement, use:

SELECT @RC = @@ROWCOUNT, @ERR = @@ERROR

Then, end your stored procedure with:

RETURN @RC

or, even better, use an output parameter to return the rowcount.

Note: I included @@ERROR along with @@ROWCOUNT above for a reason. You
should be checking for errors with every action in your stored proc, and
your stored procedure code should be heavily loaded with that statement
anyway.

Hope that helps,

Dale Preston
MCAD, MCSE, MCDBA

 

[Guest]

Thanks for the reply, it is really helpful. how should I call this store procedure and get row count in my visual basic code. So, store procedure should accept one input parameter and return row count and I need to use this row count in my code.

 

[Dale]

Here's a couple links to get you started:

http://msdn.microsoft.com/sql/sqlre...brary/en-us/dnsql2k/html/sql_adonetprimer.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp

Dale Preston
MCAD, MCSE, MCDBA

Thanks for the reply, it is really helpful. how should I call this store

procedure and get row count in my visual basic code. So, store procedure
should accept one input parameter and return row count and I need to use
this row count in my code.

 

[Dale]

A couple more links.

http://msdn.microsoft.com/sql/sqlre...ull=/library/en-us/dnadonet/html/gazoutas.asp

And the mother of all ADO.NET links:

http://msdn.microsoft.com/sql/sqlreldata/ado.net/default.aspx

Dale Preston
MCAD, MCSE, MCDBA

Thanks for the reply, it is really helpful. how should I call this store

procedure and get row count in my visual basic code. So, store procedure
should accept one input parameter and return row count and I need to use
this row count in my code.