Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using a data reader to get a count

Posted on 2012-08-16
7
Medium Priority
?
1,079 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?
0
Comment
Question by:mwhodges
7 Comments
 
LVL 16

Expert Comment

by:Swapnil Piparia
ID: 38300712
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
 
LVL 13

Expert Comment

by:LIONKING
ID: 38300919
Can you show us the rest of your code?
How are you filling the data reader? Stored procedure?
0
 

Author Comment

by:mwhodges
ID: 38300954
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:LIONKING
ID: 38300979
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 38301007
>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
 

Author Closing Comment

by:mwhodges
ID: 38301170
Thats exactly what my problem was.  Thank you so much.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38301206
>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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question