I have made somekind of a search application with some textBoxes and now I`m adding a checkBox, which will reduse the results to what the checkBox is about. In my case it will embrance the stationary telephone numbers from a database.
Here is an example for textBox:
if (textBoxCity.Text != string.Empty)
{
cmd.Parameters.Add(new SqlParameter("@city", System.Data.SqlDbType.VarChar, 30, "City"));
cmd.Parameters["@city"].Value = textBoxCity.Text;
}
else
{
cmd.Parameters.Add(new SqlParameter("@city", System.Data.SqlDbType.VarChar, 30, "City"));
cmd.Parameters["@city"].Value = String.IsNullOrEmpty(textBoxCity.Text) ? DBNull.Value : (object)textBoxCity.Text;
}
I would like to knowhow do I do the same code for checkBox.
It starts with:
if(checkBoxStationaryTel.Checked)
{
//code here?
}
else
{
//code here?
}
I am a newbie at C#, so please don`t be mad if I`ll ask some stupid questions :) - Moved byOmegaManMVPWednesday, June 03, 2009 3:24 PM (From:Visual C# General)
-
| | Mitja Bonca Wednesday, June 03, 2009 3:18 PM | Then you could create another class to manage the list of them, like this: 1) Right-click on your project and select Add | Class. 2) Name the class something obvious, like "Companies". In the class, the code could look like this:
public class Companies: List<Company>
{
public static Companies Retrieve()
{
//NOTE 1: Personnally, I would put this code into a data access component
// and call that data access component from this Retrieve method.
// See the sample application on www.insteptech.com.
//NOTE 2: This could use a DataReader instead for improved performance
DataTable dt = new DataTable();
// Open the connection
using (SqlConnection cnn = new SqlConnection("Your Connection String here"))
{
cnn.Open();
// Define the command
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
//TODO: Modify this to include the other necessary fields
cmd.CommandText = "Select IDCompany, IDRegionFK, IDActivityFK, CompanyName, CompanyTitle, Owner From Company";
// Define the data adapter and fill the dataset
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
}
}
// Build the list of companies from the data rows
//NOTE: Alternatively, you could define a constructor on the Company
// object and handle the properties in the constructor
Company company;
Companies companies = new Companies();
if (dt != null && dt.Rows.Count > 0)
{
//TODO: Add the other properties/fields here.
foreach (DataRow dr in dt.Rows)
{
company = new Company()
{
CompanyName = dr["CompanyName"].ToString(),
CompanyTitle = dr["CompanyTitle"].ToString(),
Owner = dr["Owner"].ToString(),
IDCompany = (int)dr["IDCompany"],
IDActivityFK = (int)dr["IDActivityFK"],
IDRegionFK = (int)dr["IDRegionFK"]
};
companies.Add(company);
}
}
return companies;
}
}
NOTE: I could not run any of this code because I obviously don't have this database. But it should be close to what you need. Hope this helps.
www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS! - Marked As Answer byMitja Bonca Thursday, June 04, 2009 7:23 AM
-
| | DeborahK Wednesday, June 03, 2009 6:41 PM | Ok, sorry!
back to my issue. I just did:
if (checkBoxStacionarni.Checked)
{
cmd.Parameters.Add(new SqlParameter("@stacionarni", System.Data.SqlDbType.VarChar, 40, "Stacionarni"));
cmd.Parameters["@stacionarni"].Value = checkBoxStacionarni.Checked = true;
}
else
{
cmd.Parameters.Add(new SqlParameter("@stacionarni", System.Data.SqlDbType.VarChar, 40, "Stacionarni"));
cmd.Parameters["@stacionarni"].Value = checkBoxStacionarni.Checked = false;
}
IS this OK?
And what about the sql statement it self?
For the String I did:
string myString = "SELECT companyName FROM Company WHERE ((@city IS NULL) OR (Company.City = @city))";
How do I do this for a checkBox, where is the bool (Boolean) type. I mean "IS NULL" can not be for a bool! I tried IS FALSE but its wrong. Any ideas?
I am a newbie at C#, so please don`t be mad if I`ll ask some stupid questions :) | | Mitja Bonca Wednesday, June 03, 2009 3:36 PM | Maybe I approached from a wrong side to this issue.
The point is, when the checkBox1 is checked, the reader has to read the number from a database (from a column name Stationary telephone numbers). How to do it?
I am a newbie at C#, so please don`t be mad if I`ll ask some stupid questions :) | | Mitja Bonca Wednesday, June 03, 2009 5:02 PM | Do you mean you want to change the select statement like so?
SqlCommand cmd = new SqlCommand();
if (checkBoxStacionarni.Checked)
cmd.CommandText = "SELECT companyName, Stacionarni FROM Company WHERE ((@city IS NULL) OR (Company.City = @city))";
else
cmd.CommandText = "SELECT companyName FROM Company WHERE ((@city IS NULL) OR (Company.City = @city))";
| | Tergiver Wednesday, June 03, 2009 5:28 PM | Hi Mitja! Your if statement can be simplified from the current 10 lines to only these two:
cmd.Parameters.Add(new SqlParameter("@stacionarni", System.Data.SqlDbType.VarChar, 40, "Stacionarni"));
cmd.Parameters["@stacionarni"].Value = checkBoxStacionarni.Checked;
If the checkBox is checked, it will set the parameter value to true; otherwise it will set it to false. Hope this helps!
www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS! | | DeborahK Wednesday, June 03, 2009 5:37 PM | thx for yur help, but I think we didnt understand each other. What I want is to show (in the listBox) the company names which have stationary numbers - when checkBox1 IS checked!
How to do it?
I am a newbie at C#, so please don`t be mad if I`ll ask some stupid questions :) - Edited byMitja Bonca Wednesday, June 03, 2009 5:55 PM
-
| | Mitja Bonca Wednesday, June 03, 2009 5:53 PM | Do you have any sense for how much data that your queries will return?
It seems like a constant struggle with these darn SQL parameters.
I would not do it this way at all.
If the data was a reasonable size, I would load it all into business objects. For example, a List<Company> witha Company class with address and station number properties.
Then you could MUCH more easily using LINQ over objects to find these or those based on user-defined parameters from TextBoxes, ComboBoxes, CheckBoxes and so on.
It might also perform better because it won't need to go to the database again and again and again as the user modifies their entries in the UI.
Thoughts?
www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS! | | DeborahK Wednesday, June 03, 2009 6:04 PM | You can find an example of this here: http://www.insteptech.com/techLibrary/samplecode.htmClick on the C# Code Example link to download the code. This demonstrates loading all of my customer data into a list of customer objects. The code could then use LINQ to access objects based on any property or combination of properties in the customer. Hope this helps.
www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS! | | DeborahK Wednesday, June 03, 2009 6:09 PM | Great idea, but I dont know how to handle with.. yet. Iam a beginner. Do you have any example of how it should look?
In my database i have table Company, with column names (IDCompany, IDRegionFK, IDActivityFK, CompanyName, CompanyTitle, Owner, Street, HouseNo, Post, City, State, www, eMail, eMail2, fax, StationaryTel, MobileTel, Telephone1, Telephone2).
I hope this helps a bit - that you will know with what I work. And the application. on which I work now, ismeant for searching of Company names throughthose listBoxes, comboBoxes and checkBoxes.
So far I did all, only checkBoxes.
I am a newbie at C#, so please don`t be mad if I`ll ask some stupid questions :) | | Mitja Bonca Wednesday, June 03, 2009 6:14 PM | For your specific case, you could try this: 1) Right-click on your project and select Add | Class. 2) Name the class something obvious, like "Company". In the class, add properties for each of the relevant column names. It will look something like this:
public class Company<br/> {
public int IdCompany { get; set; }
public int IDRegionFK { get; set; }
public int IDActivityFK { get; set; }
public string CompanyName { get; set; }
public string CompanyTitle { get; set; }
public string Owner { get; set; }
//TODO: Add the other properties similar to the above.
}
Hope this helps.
www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS! | | DeborahK Wednesday, June 03, 2009 6:22 PM | Then you could create another class to manage the list of them, like this: 1) Right-click on your project and select Add | Class. 2) Name the class something obvious, like "Companies". In the class, the code could look like this:
public class Companies: List<Company>
{
public static Companies Retrieve()
{
//NOTE 1: Personnally, I would put this code into a data access component
// and call that data access component from this Retrieve method.
// See the sample application on www.insteptech.com.
//NOTE 2: This could use a DataReader instead for improved performance
DataTable dt = new DataTable();
// Open the connection
using (SqlConnection cnn = new SqlConnection("Your Connection String here"))
{
cnn.Open();
// Define the command
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
//TODO: Modify this to include the other necessary fields
cmd.CommandText = "Select IDCompany, IDRegionFK, IDActivityFK, CompanyName, CompanyTitle, Owner From Company";
// Define the data adapter and fill the dataset
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
}
}
// Build the list of companies from the data rows
//NOTE: Alternatively, you could define a constructor on the Company
// object and handle the properties in the constructor
Company company;
Companies companies = new Companies();
if (dt != null && dt.Rows.Count > 0)
{
//TODO: Add the other properties/fields here.
foreach (DataRow dr in dt.Rows)
{
company = new Company()
{
CompanyName = dr["CompanyName"].ToString(),
CompanyTitle = dr["CompanyTitle"].ToString(),
Owner = dr["Owner"].ToString(),
IDCompany = (int)dr["IDCompany"],
IDActivityFK = (int)dr["IDActivityFK"],
IDRegionFK = (int)dr["IDRegionFK"]
};
companies.Add(company);
}
}
return companies;
}
}
NOTE: I could not run any of this code because I obviously don't have this database. But it should be close to what you need. Hope this helps.
www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS! - Marked As Answer byMitja Bonca Thursday, June 04, 2009 7:23 AM
-
| | DeborahK Wednesday, June 03, 2009 6:41 PM | Finally, your UI code could then have lots of LINQ statements to get the required set of companies based on any entered information. Here is one example:
Companies companies = Companies.Retrieve();
var query = companies.Where(c => c.CompanyName == textBox1.Text && c.CompanyTitle == listBox1.SelectedValue);
I hope this gets you started ...
www.insteptech.com
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS! | | DeborahK Wednesday, June 03, 2009 6:44 PM | Thank you very much Deborah! I`ll try to do it tomorrow! Will let you know got it go! :)
bye
I am a newbie at C#, so please don`t be mad if I`ll ask some stupid questions :) | | Mitja Bonca Wednesday, June 03, 2009 8:06 PM | But I`d still like to know how to populate listBox with company names which have a stationary number in the database when checkBox is checked!
this example:
cmd.Parameters.Add(new SqlParameter("@stationary", System.Data.SqlDbType.VarChar, 40, "Stationary"));
cmd.Parameters["@stationary"].Value = textBox.Text;
..if I write in the textBox a telephone number which is in the Stationary column,
it will populate the listBox with the companies which have this number.
But, if I want to put a checkBox like:
cmd.Parameters.Add(new SqlParameter("@stationary", System.Data.SqlDbType.VarChar, 40, "Stationary"));
cmd.Parameters["@stationary"].Value = ??; //OR this whole row - it has to be bind to the "Stationary" Column somehow.
... what has to be instead of ??? to get into my listBox all the company names,
which have the telephone number in Stationary column
For a better idea what I am doing, take a look at THIS picture. In the buttom right you will notice checkBoxes. This is what I wanna do.
There is example of choosing just number which starts with 080, or 090 or stationary number ,...
I am a newbie at C#, so please don`t be mad if I`ll ask some stupid questions :) | | Mitja Bonca Wednesday, June 03, 2009 9:24 PM |
|
index > Windows Forms General > query with checkBox
