0

I have list of BuilderString which I want to contain data 

 public  List<int> IDS = new List<int>();

 public List<StringBuilder> Items = new List<StringBuilder>();

What's wrong with this code?

SqlConnection con2 = new SqlConnection("Data Source=aya-PC\\SQLEXPRESS;Initial Catalog=ItemSet;Integrated Security=True");
SqlDataReader rdr2;
SqlCommand cmd2;

con2.Open();

for (int i = 0; i < IDS.Count; i++)
{
    cmd2 = new SqlCommand("select item From TransactiontData where idT=@IDS[i]", con2);
    cmd2.CommandType = CommandType.Text;
    rdr2 = cmd2.ExecuteReader();
    SqlParameter param = new SqlParameter();
    param.ParameterName = "@IDS[i]"

    while (rdr2.Read())
    {
        Items[i].Append((StringBuilder)rdr2["item"]);
    }
}
Improve this question
0

2 Answers 2

Reset to default
1

You need to rearrange your code a bit:

using (SqlConnection con2 = new SqlConnection("Data Source=aya-PC\\SQLEXPRESS;Initial Catalog=ItemSet;Integrated Security=True"))
using (SqlCommand cmd2 = new SqlCommand("select item From TransactiontData where idT = @IDS", con2))
{
   // add the paramter to the command
   cmd2.Parameter.Add("@IDS", SqlDbType.Int);

   con2.Open();

   for (int i = 0; i < IDS.Count; i++)
   {
       // set the parameter value
       cmd2.Parameter["@IDS"].Value = IDS[i];

       // only *THEN* call ExecuteReader()
       using (SqlDataReader rdr2 = cmd2.ExecuteReader())
       {
              while (rdr2.Read())
              {
                  // **NOT SURE** what you're trying to do here.....
                  // First of all, you need to just call Items.Add()
                  // to add new items to the list - and I'm TOTALLY
                  // UNCLEAR what you're trying to do casting the reader
                  // value to a StringBuilder.......
                  // 
                  // Items[i].Append((StringBuilder)rdr2["item"]);
                  //
                  // replaced with what *might* make more sense.....
                  Items.Add(rdr2["item"].ToString());
              }

              rdr.Close();
       }
    }        

    con2.Close(); 
}

Points to note:

  • I would recommend to always put your SqlConnection, SqlCommand and SqlDataReader into using() {...} blocks to ensure proper disposal

  • you need to add your parameter and set its value BEFORE you call .ExecuteReader()!

  • Since the query itself never changes - there's no point in creating a new SqlCommand on every iteration. Create the command once - and then just set the parameter value (which is the only thing changing) once per iteration

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

there is error with range of list " Index was out of range. Must be non-negative and less than the size of the collection. "
A combination/merge of this answer and the other from @DanGuzman-SQLServerMVP would be best / work. For this answer: 1) setting cmd2.Parameter["@IDS"].Value needs to be IDS[i], 2) most likely don't need a while around the rdr2.Read(), 3) the Items collection doesn't have any elements yet so you need to use Items.Add() rather than reference via Items[i], 4) I don't think you can (or should) cast directly to a StringBuilder as the incoming value is actually just a string, and 5) should probably have con2.Close() after the for loop.
@srutzky: (1): no, check my answer - the parameter name in my answer IS just @IDS - no point in having an indexing on that parameter name.... and (5) - not necessary, since the using() {...} blocks is ensuring proper disposal (and thus closing) of the connectoin
Marc, you misunderstood what I meant regarding #1. I was not referring to the param name. I am referring to the value being passed in. Your param name is correct. But you are passing in the index value of just i, but the C# IDS variable is a collection of ints, so you need to use the index variable i to get the appropriate item out of the IDS collection (else you need a foreach loop). And regarding #5, I thought you might say that ;-), but I noticed that you had closed the SqlDataReader so I figured it would be consistent to also close the connection :). But yes, a minor point.
0

You need to assign the parameter value in the application code rather than within the query. I'm not sure exactly what you are trying to accomplish by casting the column value as a StringBuilder. Assuming that each StringBuilder item is to contain a single string retrieved from a varchar/nvarchar column, the example below will do that.

 for (int i = 0; i < IDS.Count; i++)
 {
        var cmd2 = new SqlCommand("select item From TransactiontData where idT=@IDS", con2);

        SqlParameter param = new SqlParameter("@IDS", SqlDbType.Int) { Value = IDS[i] };

        var rdr2 = cmd2.ExecuteReader();

        while (rdr2.Read())
        {
            Items.Add(new StringBuilder((string)rdr2["item"]));
        }
 }
Improve this answer

3 Comments

there is problem with index of list " Index was out of range. Must be non-negative and less than the size of the collection."
That error implies your code has not initialized the List<StringBuilder>. I changed the code to add the item to the collection.
A combination/merge of this answer and the other from @marc_s would be best / work. For this answer: 1) you should move the var cmd2 line above the loop as it doesn't change, 2) you should move SqlParameter param = new SqlParameter("@IDS", SqlDbType.Int) outside of the loop, 3) set param inside of the loop, 4) most likely don't need a while around the rdr2.Read(), and 5) you do need to close the reader since you can't issue another ExecuteReader until the current one is closed (right?).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.