jeudi 29 janvier 2015

How to Select * Using Unbound Combobox Lists

Background


I have created an app that brings back records from an SQL database. The query on the database uses two variables in its where clause. These two variables are taken from two unbound combo-boxes which have been populated using two data from the database.


Question


How do I allow the user to select all? I've added "All" to the tops of the unbound lists that the combo-boxes use and then used an if statement to select what SQL statement to use. But this doesn't seem to work.


How to select all records without inputting all list entries into the query?


Example


User selects "All" and "All" in both combo boxes.


App


enter image description here


Code



private void button2_Click(object sender, EventArgs e)
{

String ConnStr = "Data Source=server1; Initial Catalog=db1; User ID=mobile; Password=Password";

SQLSelection();
SqlConnection con = new SqlConnection(ConnStr);
SqlCommand command = new SqlCommand(SQL, con);
con.Open();

command.Parameters.Add("@username", SqlDbType.VarChar).Value = comboBox1.Text;
command.Parameters.Add("@status", SqlDbType.VarChar).Value = comboBox2.Text;

SqlDataAdapter adb = new SqlDataAdapter(command);

using (DataTable dt = new DataTable())
{
adb.Fill(dt);
dataGridView1.DataSource = dt;
con.Close();
}

SQLSelection();
}

private void SQLSelection()
{
if (comboBox1.Text == "All" & comboBox2.Text == "All")
{
String SQL = "SELECT stationID, LocationName, plandate, username, status FROM dbo.joblist WHERE status in ('New','Hold')";
}
else if (comboBox1.Text == "All" & comboBox2.Text == "@status")
{
String SQL = "SELECT stationID, LocationName, plandate, username, status FROM dbo.joblist WHERE status = @status";
}
else if (comboBox1.Text == "@username" & comboBox2.Text == "All")
{
String SQL = "SELECT stationID, LocationName, plandate, username, status FROM dbo.joblist WHERE status = @username";
}
else
{
String SQL = "SELECT stationID, LocationName, plandate, username, status FROM dbo.joblist WHERE username = @username and status = @status";
}

}

Aucun commentaire:

Enregistrer un commentaire