number of row returned

[JIM.H.]

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

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:DE**********************************@microsof t.com...

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.

[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

"Jared" <VB***********@email.com> wrote in message
news:10*************@corp.supernews.com...

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

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:DE**********************************@microsof t.com...

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.

[JIM.H.]

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" wrote:

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

"Jared" <VB***********@email.com> wrote in message
news:10*************@corp.supernews.com...

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

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:DE**********************************@microsof t.com...

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.

[Dale]

Here's a couple links to get you started:

http://msdn.microsoft.com/sql/sqlrel...onetprimer.asp

http://msdn.microsoft.com/library/de...et09102002.asp

Dale Preston
MCAD, MCSE, MCDBA
"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:F2**********************************@microsof t.com...

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" wrote:

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

"Jared" <VB***********@email.com> wrote in message
news:10*************@corp.supernews.com...

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

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:DE**********************************@microsof t.com...
> 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.
>

[Dale]

A couple more links.

http://msdn.microsoft.com/sql/sqlrel...l/gazoutas.asp

And the mother of all ADO.NET links:

http://msdn.microsoft.com/sql/sqlrel...t/default.aspx

Dale Preston
MCAD, MCSE, MCDBA

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:F2**********************************@microsof t.com...

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" wrote:

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

"Jared" <VB***********@email.com> wrote in message
news:10*************@corp.supernews.com...

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

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:DE**********************************@microsof t.com...
> 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.
>