none
SqlDataReader problem RRS feed

  • Question

  • Hello,

    I am having a problem with this piece of code:

    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString);
        conn.Open();
        string CommandText = "SELECT DISTINCT SUBSTRING(Type,0,CHARINDEX('\',Type)) as 'Type' FROM PartType WHERE Expired=0";
        SqlCommand comm = new SqlCommand(CommandText, conn);
        SqlDataReader reader = comm.ExecuteReader();
    
        while (reader.Read())
        {
          ListItem Item = new ListItem();
          Item.Text = reader["Type"].ToString();
          CheckBoxList1.Items.Add(Item);
        }
    
        reader.Close();
        conn.Close();

    Strange thing is that when I enter query to MSSQLMS it is ok, I get 14 rows. But when it is in code I get only empty one. When I change queryto other it works again ok. Whan can I do with that to work?
    • Moved by SamAgain Friday, October 1, 2010 2:30 AM better fit (From:.NET Base Class Library)
    Thursday, September 30, 2010 8:50 AM

Answers

  • It appears that you are not escaping your string properly, as C# will interpret '\' as ''

    You should use:

    string CommandText = "SELECT DISTINCT SUBSTRING(Type,0,CHARINDEX('\\',Type)) as 'Type' FROM PartType WHERE Expired=0";
    
    (\\ is interpretted by C# as \) or
    string CommandText = @"SELECT DISTINCT SUBSTRING(Type,0,CHARINDEX('\',Type)) as 'Type' FROM PartType WHERE Expired=0";
    
    (The @ in front of the string tells C# to ignore any escape sequences)
    • Proposed as answer by liurong luo Monday, October 4, 2010 6:58 AM
    • Marked as answer by liurong luo Wednesday, October 6, 2010 10:57 AM
    Friday, October 1, 2010 5:10 PM
    Moderator

All replies

  • Do you have any exception handler around your code? Do you receive any exceptions in this case?
    Val Mazur (MVP) http://www.xporttools.net
    Friday, October 1, 2010 10:13 AM
    Moderator
  • I had put it in try catch and got no exceptions at all.
    Friday, October 1, 2010 11:06 AM
  • Hi,

    once try by putting ListItem Item=new ListItem() outSide the WHILE LOOPas follows:-

    ListItem Item=new ListItem()

    Whiel(reader.Read())

    {

      Item.Text=reader["Tpe"].ToString();

       (or)

     Item.Add(reader["Tpe"].ToString());

     CheckBoxList1.Items.Add(Item);

    }

     


    PS.Shakeer Hussain
    Friday, October 1, 2010 1:20 PM
  • It appears that you are not escaping your string properly, as C# will interpret '\' as ''

    You should use:

    string CommandText = "SELECT DISTINCT SUBSTRING(Type,0,CHARINDEX('\\',Type)) as 'Type' FROM PartType WHERE Expired=0";
    
    (\\ is interpretted by C# as \) or
    string CommandText = @"SELECT DISTINCT SUBSTRING(Type,0,CHARINDEX('\',Type)) as 'Type' FROM PartType WHERE Expired=0";
    
    (The @ in front of the string tells C# to ignore any escape sequences)
    • Proposed as answer by liurong luo Monday, October 4, 2010 6:58 AM
    • Marked as answer by liurong luo Wednesday, October 6, 2010 10:57 AM
    Friday, October 1, 2010 5:10 PM
    Moderator