Using a data reader to get a count

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?
mwhodgesAsked:
Who is Participating?
 
CodeCruiserCommented:
>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 + '%'
0
 
SwapnilSoftware ArchitectCommented:
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.
0
 
LIONKINGCommented:
Can you show us the rest of your code?
How are you filling the data reader? Stored procedure?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mwhodgesAuthor 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
0
 
LIONKINGCommented:
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.
0
 
mwhodgesAuthor Commented:
Thats exactly what my problem was.  Thank you so much.
0
 
LIONKINGCommented:
>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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.