Using SQL Between Query and Showing all results in ASP.NET C#

Question

this is my sample code to check the data on Table1 using 2 filters, column1 and between data in column2. The code I have is working but is only getting 1 result. So for example. I enter "1" in textbox1, "3" in textbox2 and "6" in textbox3. Select * from TABLE1 where COLUMN1 = '1' AND COLUMN2 BETWEEN '3' AND '6' -- when run in sql result is 3,4,5,6 but in C# I am only getting "6". Can you help me with this to get "3,4,5,6" as a result. Thank you.

public partial class WebForm1 : System.Web.UI.Page
    {

    SqlConnection SC;
    SqlCommand CMD;
    SqlDataReader DR;

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        SC = new SqlConnection(ConfigurationManager.ConnectionStrings["BABBLER"].ConnectionString);
        SC.Open();
        CMD = new SqlCommand("Select * from TABLE1 WHERE COLUMN1= '" + TextBox1.Text + "' and  COLUMN2 Between '" + TextBox2.Text + "'" + " and " + "'" + TextBox3.Text + "'", SC);

        DR = CMD.ExecuteReader();

        if (DR.HasRows)
        {
            while (DR.Read())
            {
                label1.Text = DR["COLUMN2"].ToString();
            }

        }
    }
}

}

Please use SqlParameter instead of composing your sql statement like that or you might get a visit from little bobby tables. — Corak
– Corak, Commented May 21, 2013 at 6:04

Arun · Accepted Answer · 2013-05-21 06:00:28Z

1

Your loop is not appending the values, rather overwriting Label1. Change your while loop to

while (DR.Read())
{
   label1.Text += DR["COLUMN2"].ToString() + ",";
}

if (label1.Text.EndsWith(",")) label1.Text = label1.Text.SubString(0, label1.Text.Length-1) //Remove the last comma

answered May 21, 2013 at 6:00

Arun

3,0673 gold badges37 silver badges59 bronze badges

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

4 Comments

Rose Over a year ago

Thank very much Arun. It now shows 3 4 5 6 but no comma. How can I add the commas? Thank you.

Arun Over a year ago

I think you need brackets around the expression. label1.Text += (DR["COLUMN2"].ToString() + ",");

Rose Over a year ago

I tried but still showing 3 4 5 6 as an output. Commas are nowhere to find. Thank you for the answering. Such a relief.

Arun Over a year ago

Am surprised. You may need to debug by putting a break point inside the loop and watch the label1.Text in your immediate window. Can you post your code?

TechDo · Accepted Answer · 2013-05-21 06:36:02Z

1

Change

label1.Text = DR["COLUMN2"].ToString();

as

label1.Text = label1.Text +", " + DR["COLUMN2"].ToString();
if (Label1.Text.Length > 2)
   Label1.Text = Label1.Text.Substring(2);

edited May 21, 2013 at 6:36

answered May 21, 2013 at 5:57

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

2 Comments

Rose Over a year ago

Thank you but this code will show data without commas ",". Result is 3 4 5 6 should be 3,4,5,6

Richard Over a year ago

Concatenating strings in a loop should be avoided - it is slow.

Aijaz Chauhan · Accepted Answer · 2013-05-21 06:45:42Z

0

try this code

SC = new SqlConnection(ConfigurationManager.ConnectionStrings["BABBLER"].ConnectionString);
SC.Open();
CMD = new SqlCommand("Select * from TABLE1 WHERE COLUMN1= '" + TextBox1.Text + "' and  COLUMN2 Between '" + TextBox2.Text + "'" + " and " + "'" + TextBox3.Text + "'", SC);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(CMD);
da.Fill(ds);

string data="";
for (int i = 0; i < ds.Tables[0].Rows.Count; i++ )
{

   if(data=="")
   {
      label1.Text = ds.Tables[0].Rows[i]["COLUMN2"].ToString();
   }
   else
   {
      label1.Text +=","+ ds.Tables[0].Rows[i]["COLUMN2"].ToString();
   }

}

edited May 21, 2013 at 6:45

answered May 21, 2013 at 6:36

Aijaz Chauhan

1,6693 gold badges28 silver badges55 bronze badges

Comments

Richard · Accepted Answer · 2013-05-21 06:58:47Z

There are a number of methods to combine results into a comma-separated list. However, using string concatenation should not be one - concatenating strings is slow, especially if you might have a large number of results. Try one of the following instead:

Using a StringBuilder

StringBuilder sb = new StringBuilder();
boolean doneFirstRow = false;
while (DR.READ())
{
    if (doneFirstRow) 
    {
        sb.Append(", ");
    }
    else 
    {
        doneFirstRow = true;
    }
    sb.Append(dr["COLUMN2"].ToString());
}
Label1.Text = sb.ToString();

Using a List with String.Join:

List<string> values = new List<string>();
while (DR.READ())
{
    values.Add(dr["COLUMN2"].ToString());
}
Label1.Text = String.Join(", ", values);

NB: If not using NET4.5 you'll need String.Join(", ", values.ToArray())

Collectives™ on Stack Overflow

Using SQL Between Query and Showing all results in ASP.NET C#

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related