CODEWITHSUNDEEP
c#asp.netsql
Rose
Rose

Reputation: 9

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

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();
            }

        }
    }
}

}

Upvotes: 0

Views: 2658

Answers (4)

Richard
Richard

Reputation: 30628

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())

Upvotes: 0

Aijaz Chauhan
Aijaz Chauhan

Reputation: 1659

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();
   }

}

Upvotes: 0

TechDo
TechDo

Reputation: 18659

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);

Upvotes: 1

Arun
Arun

Reputation: 3077

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

Upvotes: 1

Related Questions