none
Issues with sqldatareader RRS feed

  • Question

  • I am writing a simple code where trying to connect from .NET to SQL through ADO.NET.

    My Issue is my query select_count = "select count(*) as CNT from ... "; as 8 but when i connect and execute reader in 2 ways

    1)

     

     

     

    conn = new SqlConnection(ConnectionString);

     conn.Open();

     

    SqlCommand cmd = new SqlCommand(select_count, conn);

     

    int count = (int) cmd.ExecuteScalar();

    2)

     

     

    reader_count = sqlclass.connector(ConnectionString, select_count);

     

     

    while (reader_count.Read())  "CNT"].ToString();

    {

    cnt = reader_count[

     

    }

    reader_count.Close();

     

     

    int cnts = Convert.ToInt32(cnt);

    where sqlclass is an instance of a class SQLConnector which opens and executes the reader.

    In both the cases i get wrong results as my count =4 , means readers fetches wrong result.

    Please help in solving this.

    Thanks

     

     

     

     

     

     

     

     

     

     

     

    SqlDataReader

     

     

    SqlConnection

    Wednesday, June 2, 2010 9:17 PM

Answers

  • You are trying to fix everything at the same time.

    I don't think dr["typeID"] is returning null, because you would have got an exception when you call the .ToString() method.

     

    I suggets to check the values in the array to isolate where the problem is coming from. If you have a problem with the reader, then I would simplify the query to one table. If results  are good for one table then your problem is with the query.

    • Marked as answer by SDE_Sal Wednesday, June 9, 2010 6:15 PM
    Thursday, June 3, 2010 4:40 PM

All replies

  • Does your SQL statement have a WHERE clause? It might help if you posted the complete statement.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, June 2, 2010 10:30 PM
  • Hey thanks for replying..

    I think query returns proper Cnt when i check in debug mode , but data reader when executed does not generate proper values.

    I simply dont understand why..I used this same code many times..but was working..

    Here is the query

    string select_count1

     

     

     

    = "Select count(*) as Cnt from" +

    "(SELECT DISTINCT t.dtype_sort,t.type_id,t.type FROM emd_schools s WITH (nolock) INNER JOIN emd_degrees d WITH (nolock) ON (d.school_id = s.school_id)" +

    "INNER JOIN emd_degreextype dxt WITH (nolock) ON (dxt.degree_id = d.degree_id) INNER JOIN emd_degree_type t WITH (nolock) ON (t.type_id = dxt.type_id)" +

    "WHERE (s.school_id IN (176,40,6,43,20,19,34,38) AND s.active=1 AND d.drop_active=1 AND d.active=1 AND s.ActiveIntl = 1 AND t.dtype_sort <= " + maxsort + ")" +

    "OR (s.school_id IN (176,40) AND s.active=1 AND d.drop_active=1 AND s.ActiveIntl = 1 AND t.dtype_sort <= " + maxsort + "))as query";

    Wednesday, June 2, 2010 11:15 PM
  • Query - in more cleaner format

    SELECT

     

    DISTINCT t.dtype_sort,t.type_id as TypeID,t.type FROM emd_schools s WITH (nolock) INNER JOIN emd_degrees d WITH (nolock) ON (d.school_id = s.school_id)INNER JOIN emd_degreextype dxt WITH (nolock) ON (dxt.degree_id = d.degree_id) INNER JOIN emd_degree_type t WITH (nolock) ON (t.type_id = dxt.type_id) WHERE (s.school_id IN (176,40,6,43,20,19,34,38) AND s.active=1 AND d.drop_active=1 AND d.active=1 AND s.ActiveIntl = 1 AND t.dtype_sort <= 9)OR (s.school_id IN (176,40) AND s.active=1 AND d.drop_active=1 AND s.ActiveIntl = 1 AND t.dtype_sort <= 9)

    I dont understand why does the sqlreader not able to execute this query properly

     

    Wednesday, June 2, 2010 11:39 PM
  • I would use the management studio to execute the query first and see what it returns.
    Thursday, June 3, 2010 12:19 AM
  • I already executed the query returned by my select_sql query in debug mode, it was perfectly fine but somehow when the reader is executed it returns few records not all. It is goofy..please suggest some ideas..

    Thursday, June 3, 2010 2:27 AM
  • Perhaps the difference could be that you're not returning a Count on the Distinct items? If you use Count(*) it will return null values and duplicates.

    http://msdn.microsoft.com/en-us/library/ms175997.aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, June 3, 2010 2:12 PM
  • I think problem is something else..Eventhough when i remove Count from my query and just use Distinct, i dont get proper results that means reader is not looping while for all the records, just for few it does . Actully i am creating an string array from all the

    array[i] = dr["typeid"]

    i++;

    Only array[0], array[1], array[2] has values while rest have null if lenght of array is 8 and should have 8 values.

    Please suggest whats going wrong.

    thanks

    Thursday, June 3, 2010 3:10 PM
  • OK, this is getting confusing. Can you post the code you currently have using the DataReader and populating the array?


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, June 3, 2010 3:57 PM
  • select_type =

     

    "SELECT DISTINCT t.type_id as TypeID FROM emd_schools s WITH (nolock) INNER JOIN emd_degrees d WITH (nolock) ON (d.school_id = s.school_id)" +

     

     

    "INNER JOIN emd_degreextype dxt WITH (nolock) ON (dxt.degree_id = d.degree_id) INNER JOIN emd_degree_type t WITH (nolock) ON (t.type_id = dxt.type_id)" +

     

     

    "WHERE (s.school_id IN (176,40,6,43,20,19,34,38) AND s.active=1 AND d.drop_active=1 AND d.active=1 AND s.ActiveIntl = 1 AND t.dtype_sort <= " + maxsort + ")" +

     

     

    "OR (s.school_id IN (176,40) AND s.active=1 AND d.drop_active=1 AND s.ActiveIntl = 1 AND t.dtype_sort <= " + maxsort + ")";

     

     

    string[] array = new string[count]; //count comes from the result of the count query

     

     

    string a;

     

     

    List<string> al = new List<string>();

     

     

    SqlConnection connec = new SqlConnection(emd_ConnectionString);

     

     

    SqlCommand cmd = new SqlCommand(select_type, connec);

    connec.Open();

     

     

    int i =0;

     

     

    using (SqlDataReader dr = cmd.ExecuteReader())

    {

     

     

    while (dr.Read())

    {

    a = dr[

     

    "TypeID"].ToString();

    array[i] = a

    i++;

    }

    }

     

     

    Random randstring = new Random();

     

     

    string type = array[randstring.Next(0,i-1)];

    I sometimes get null in type variable as the array has null values in it too.

    Thursday, June 3, 2010 4:31 PM
  • You are trying to fix everything at the same time.

    I don't think dr["typeID"] is returning null, because you would have got an exception when you call the .ToString() method.

     

    I suggets to check the values in the array to isolate where the problem is coming from. If you have a problem with the reader, then I would simplify the query to one table. If results  are good for one table then your problem is with the query.

    • Marked as answer by SDE_Sal Wednesday, June 9, 2010 6:15 PM
    Thursday, June 3, 2010 4:40 PM