Using a data reader to get a count

Use scalar instead it is more efficient than datareader to get count.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

Thanks,
Netswap.

Can you show us the rest of your code?
How are you filling the data reader? Stored procedure?

Here is my code:

Dim cmd As New SqlCommand("spTaregtVerification", dbconn)
        cmd.CommandType = CommandType.StoredProcedure
        With cmd.Parameters
            .Add("@fkPLanID", SqlDbType.Int).Value = introwkey
            .Add("@ProductServiceName", SqlDbType.NVarChar).Value = strProduct
        End With

        dbconn.Open()
        intPlan = Convert.ToInt32(cmd.ExecuteScalar())

I am also including the sql query maybe thats my issue.

select count(*)
      from Table1
      where fk_planid=@fkPLanID and ProductServiceName like  @ProductServiceName

With the command right now, you're only "executing" the stored procedure, but you're not capturing whatever it returns.

If you use a dataReader it would be something like:

Dim cmd As New SqlCommand("spTaregtVerification", dbconn)
        cmd.CommandType = CommandType.StoredProcedure
        With cmd.Parameters
            .Add("@fkPLanID", SqlDbType.Int).Value = introwkey
            .Add("@ProductServiceName", SqlDbType.NVarChar).Value = strProduct
        End With

        dbconn.Open()

Dim dr as SqlDataReader

dr=cmd.ExecuteReader()
dr.read()

        intPlan = Integer.Parse(dr(0).ToString())


You could check if the dataReader has rows with dr.HasRows, and process this accordingly.

Thats exactly what my problem was.  Thank you so much.

>With the command right now, you're only "executing" the stored procedure, but you're not capturing whatever it returns.

>intPlan = Convert.ToInt32(cmd.ExecuteScalar())

He is actually.

You're right... I forgot that gets the first col of the first row... I stand corrected...
Thanks!