Bug with between sql command

yueru

I've used sql command here is my code

string CommandText = "select * from par where Time between '" + textBox7.Text + "%' and '" + textBox13.Text + "%' and Date like '" + textBox6.Text + "%' and Linein like'" + textBox8.Text + "%' and Lineout like '" + textBox9.Text + "%'and Duration >= " + textBox10.Text + " ;";
DB = new SQLiteDataAdapter(CommandText, sql_con);//นำข้อมูลออกมา
DS.Reset();
DB.Fill(DS);//นาม ข้อมูลปายส่ายนาย ดาต้าเซด
DT = DS.Tables[0];
dataGridView1.DataSource = DT;
int rowCount = ((DataTable)this.dataGridView1.DataSource).Rows.Count;
textBox12.Text = rowCount.ToString();
sql_con.Close();

It has problem that if I haven't insert value to Time it will not show anything but if I take the value to it I can run the search and It'll take the value to mydatagrid :confused:

Rob Philpott

What do you want it to do? ie. when no time or date is entered. You don't have any validation here at all do you? That's a concern. They could type anything into those textboxes. Wrong input could throw an exception and malicious input could wreck your database. I'd consider parameterising your query rather than string building it.

Regards, Rob Philpott.

yueru

I want to search it but if I didn't insert value in textBox7 or textBox13 it will not show anything( but I 've inserted data in another textbox)

string CommandText = "select * from par where Time between '" + textBox7.Text + "%' and '" + textBox13.Text + "%' and Date like '" + textBox6.Text + "%' and Linein like'" + textBox8.Text + "%' and Lineout like '" + textBox9.Text + "%'and Duration <= " + textBox10.Text + " ;";

but if i insert data into it it will show like this command It's mean that every function is subset of between right ?????

PIEBALDconsult

That's what it's supposed to do. I suggest using DateTimePickers rather than TextBoxes, and parameterize the query.

yueru

Actully I've get the code but it 's too longgg

private void button4_Click(object sender, EventArgs e)
{

        sql\_con = new SQLiteConnection("Data Source= " + info + ".db;Version=3;New=False;Compress=True;");
        sql\_con.Open();
        sql\_cmd = sql\_con.CreateCommand();
        if ((radioButton1.Checked == true))
        {
            if (textBox10.Text != "")
            {
                if (textBox7.Text != "")
                {
                    string CommandText = "select \* from  par where  Time between '" + textBox7.Text + "%' and '" + textBox13.Text + "%' and Date like '" + textBox6.Text + "%'  and Linein like'" + textBox8.Text + "%' and Lineout like '" + textBox9.Text + "%'and Duration >= " + textBox10.Text + " ;"; 
                    DB = new SQLiteDataAdapter(CommandText, sql\_con);//นำข้อมูลออกมา
                    DS.Reset();
                    DB.Fill(DS);//นาม ข้อมูลปายส่ายนาย ดาต้าเซด
                    DT = DS.Tables\[0\];
                    dataGridView1.DataSource = DT;
                    int rowCount = ((DataTable)this.dataGridView1.DataSource).Rows.Count;
                    textBox12.Text = rowCount.ToString();
                    sql\_con.Close();
                    MessageBox.Show("OK");
                }
                else
                {
                    string CommandText = "select \* from  par where Date like '" + textBox6.Text + "%'  and Linein like'" + textBox8.Text + "%' and Lineout like '" + textBox9.Text + "%'and Duration >= " + textBox10.Text + " ;";
                    DB = new SQLiteDataAdapter(CommandText, sql\_con);//นำข้อมูลออกมา
                    DS.Reset();
                    DB.Fill(DS);//นาม ข้อมูลปายส่ายนาย ดาต้าเซด
                    DT = DS.Tables\[0\];
                    dataGridView1.DataSource = DT;
                    int rowCount = ((DataTable)this.dataGridView1.DataSource).Rows.Count;
                    textBox12.Text = rowCount.ToString();
                    sql\_con.Close();
                    MessageBox.Show("OK");
                }
            }
            else
            {
                if (textBox7.Text != "")
                {
                    string CommandText = "select \* from  par where  Time between '" + textBox7.Text + "%' and '" + textBox13.Text + "%' and Date like '" + textBox6.Text + "%'  and Line