How to find the number of rows in a table

I try to find the number of rows in a table with this commands:

CountRec =

New SqlParameter
CountRec.ParameterName ="@.countrec"
CountRec.SqlDbType = SqlDbType.Int
CountRec.Value = 0

MyCommand =New Data.SqlClient.SqlCommand()
MyCommand.CommandText ="select count(*) as @.countrec from Customer;"
MyCommand.CommandType = Data.CommandType.Text
MyCommand.Connection = MyConnection
MyCommand.Parameters.Add(CountRec)
MyCommand.Connection.Open()
MyReader = MyCommand.ExecuteReader

iRecordCount = CountRec.Value

This is the result:

Incorrect syntax near '@.countrec'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near '@.countrec'.

Source Error:

Line 39: Line 40: MyCommand.Connection.Open()Line 41: MyReader = MyCommand.ExecuteReaderLine 42: iRecordCount = CountRec.ValueLine 43:

Source File:E:\Develop\Web\ASPweb\AccessTimberSales\UserEntry.aspx.vb Line:41

What to do? I need a complete example to see how it works.

Thanks...

The cause of your error is that your SQL statement should look like this:
select @.countrec = count(*) from Customer;

But you will next run into the problem that your parameter was not declared as an output parameter so its value will always contain the 0 that you assigned it, and then the further problem that output parameter values are not accessible until a data reader is closed.

There are a few different ways to achieve what you are trying to accompish. Since you only need one value, you should not be using an ExecuteReader. Try an ExecuteNonQuery instead:

CountRec =New SqlParameter

CountRec.ParameterName ="@.countrec"
CountRec.Direction = ParameterDirection.Output
CountRec.SqlDbType = SqlDbType.Int
CountRec.Value = 0

MyCommand =New Data.SqlClient.SqlCommand()
MyCommand.CommandText ="select @.countrec = count(*) from Customer;"
MyCommand.CommandType = Data.CommandType.Text
MyCommand.Connection = MyConnection
MyCommand.Parameters.Add(CountRec)
MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()

iRecordCount = CountRec.Value

Alternately, you could forget about the parameter and do an ExecuteScalar:

MyCommand =New Data.SqlClient.SqlCommand()
MyCommand.CommandText ="select count(*) from Customer;"
MyCommand.CommandType = Data.CommandType.Text
MyCommand.Connection = MyConnection
MyCommand.Connection.Open()
iRecordCount =MyCommand.ExecuteScalar()

|||

Change your query to

SELECT COUNT(*) FROM Customer

Then use an execute Scalar from your command, don't bother with the parameter.

The way you originally wrote your query doesn't return anything to the data reader...

If you've got your heart set on using the parameter make sure you set the direction to Out , then alter your query to something like:

SET @.countrec = SELECT COUNT(*) FROM Customer

|||Thanks a lot, I can use this several places.