We help IT Professionals succeed at work.

Using a data reader to get a count

Medium Priority
1,094 Views
Last Modified: 2012-08-16
I have a data reader that I am using that runs a simle query that counts the number of records in a table that meet a certain criteria.  I cannot get it to show me the value the query returns.  I have tried:

datareader.read ()
intFlag=datareader(0)

I am constantly geting zero when I can run the query and I am getting counts of >0  any advice?
Comment
Watch Question

SwapnilSoftware Architect
CERTIFIED EXPERT
Top Expert 2005

Commented:
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.
CERTIFIED EXPERT

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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
>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.

>I am constantly geting zero when I can run the query and I am getting counts of >0

Could it be because of

>and ProductServiceName like  @ProductServiceName

What happens if you remove that part? Try

and ProductServiceName like  '%' + @ProductServiceName + '%'

Author

Commented:
Thats exactly what my problem was.  Thank you so much.
CERTIFIED EXPERT

Commented:
>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!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.