Join 306,824 C# Programmers for FREE! Get instant access to thousands of C# experts, tutorials, code snippets, and more! There are 1,751 people online right now. Registration is fast and FREE... Join Now!
I have a database, which has atable called Company. Inside are columns with the basic informations (company name, street, post, city, tel.numbers, ...). I would like to know to populate listBox with company names which have a "stationary number" in the database when checkBox is checked! But I mean if the column "StationaryNumber" is NULL, then this comany shouldn`t be included in the listBox, if the company has a number (column is NOT NULL) then the name of the is company MUST be incuded in the listBox!
..let` say if I write in the textBox a telephone number which is in the StationaryNumber column, it will populate the listBox with the companies which have this number(it should be just one company).
But, if I want to put a checkBox like:
CODE
cmd.Parameters.Add(new SqlParameter("@stationary", System.Data.SqlDbType.VarChar, 40, "Stationary")); cmd.Parameters["@stationary"].Value = ??; //OR this whole row is wrong - it has to be bind to the "Stationary" Column somehow.
... How to code these 2 last lines that the readed will read from a database (column "StationaryNumber") ... 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 ,...
If (checkbox1.checked == true) { // If the check box is ticked do some code here } else If (checkbox1.checked == false) { // If the check box is unticked do something here }
In the checked part you would want to populate your listbox when it's unchecked you could do a listbox.clear(); method.
I don't know sql so you might have to get someone else to help you populate from that unless you know.
I am trying to understand what these checkboxes will be. Tell me if I have this right....a checkbox may have "090" as it's text. When the user clicks that checkbox, you want to populate the ListBox with companies that have a StationaryNumber that contains(or starts with?) "090". Is that what you are trying to do?
Yes. Or better, that listBox will be populated with WHOLE column "StationaryNumber". The checkBox==checked has to call the database, column "StationaryNumber" and populate the listBox.
now, to add the two columns(CompanyName and StationaryNumber) to the ListBox...
CODE
listBox1.MultiColumn = true;
// do a loop for your sql statement...assuming you are using a SqlDataReader SqlDataReader dr = cmd.ExecuteReader(); while(dr.Read()) { string data = string.Format("{0}\t\t{1}", dr.GetString(0), dr.GetString(1)); listBox1.Items.AddRange(new object[] {data}); }
I haven't tested it, but that should work.
This post has been edited by eclipsed4utoo: 4 Jun, 2009 - 05:10 AM
On 1st step: SELECT CompanyName, StationaryNumber FROM Company WHERE StationaryNumber LIKE @stationary + '%'
I actaully dont need StationaryNumber, only the CompanyName - I think. But I would need to fill some variable with the company names, that I can call it later! How?
if you want to grab everything, all you have to do is change "SELECT CompanyName" to "SELECT *". That will get you every field from the database for which the StationaryNumber matches what you want.
1. "SELECT CompanyName" to "SELECT *". isnt sometihng I need. I need ONLY companyNames which have stationary number (all companies which have stationary number)
2.Is it really possible to save more then 1 string into checkBox.Text? As far as I know there can be only one string.
Let me give the info what I have now (but its in my language):
CODE
private void ButtonIskanje_Click(object sender, EventArgs e) { string companySearch = "SELECT ImePodjetja FROM PodjetjeWHERE " + "((@pošta IS NULL) OR (Podjetje.Pošta = @pošta)) AND " + "((@kraj IS NULL) OR (Podjetje.Kraj = @kraj)) AND " + "((@stacionarni IS NULL) OR (Podjetje.Stacionarni = @stacionarni))"; //POST NUMBER (POŠTA) if (textBoxPošta.Text != string.Empty) { cmd.Parameters.Add(new SqlParameter("@pošta", System.Data.SqlDbType.Int)); cmd.Parameters["@pošta"].Value = textBoxPošta.Text; } else { cmd.Parameters.Add(new SqlParameter("@pošta", System.Data.SqlDbType.Int)); cmd.Parameters["@pošta"].Value = String.IsNullOrEmpty(textBoxPošta.Text) ? DBNull.Value : (object)textBoxPošta.Text; }
private void checkBoxStacionarni_CheckedChanged(object sender, EventArgs e) { listBoxListaPodjetij.Items.Clear(); string stacionarneŠtevilke = "SELECT ImePodjetja FROM Podjetje WHERE Podjetje.Stacionarni LIKE '%'"; SqlCommand cmd = new SqlCommand(stacionarneŠtevilke, povezava); povezava.Open(); SqlDataReader bralec = cmd.ExecuteReader(); while (bralec.Read()) { //WHAT COMES HERE!! In my opinion I need all the companynames which have stationary number! but how to save it? //and then to call them in event button_Iskanje (Search) where checkBox is checked! } bralec.Close(); povezava.Close(); }
if you want to grab everything, all you have to do is change "SELECT CompanyName" to "SELECT *". That will get you every field from the database for which the StationaryNumber matches what you want.
he doesn't want all fields. He only wants the CompanyName.
QUOTE(frostyraver @ 4 Jun, 2009 - 10:40 AM)
Ahh, I think you maybe need to change
CODE
while (reader1.Read())
to
CODE
while (reader1.ReadLine())
I imagine the "Read" is dropping out at the first word, if you "ReadLine" it will read all of the link in varible 0.
Things I would be checking, if the varible is in index 0 and 1? maybe try changing
Or something, maybe change the read to readtoend thing, as I said I have not much of an idea on MySQL
No. The SqlDataReader object does not support the "ReadLine" method. The "Read" method is what you use.
now, to fix the problem, set a breakpoint on the if statement where the "Read" method is called. Then step through your code. It will only take you two passes through to figure out the problem.
also, why are you trying to assign the value from the database to the text of the checkbox? I thought you wanted the name of the company(the data coming from the database) to be listed in the ListBox.
I want all the company names which have a stationary number! I tried with: DataSet dataSet = new DataSet(); SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT Podjetje.ImePodjetja FROM Podjetje WHERE Podjetje.Stacionarni LIKE '%'", povezava); dataAdapter.Fill(dataSet);
But I dont know how to tell the: while (reader1.Read()) { listBoxCompanyList.Items.Add(reader1["CompanyName"]); }
I want all the company names which have a stationary number! I tried with: DataSet dataSet = new DataSet(); SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT Podjetje.ImePodjetja FROM Podjetje WHERE Podjetje.Stacionarni LIKE '%'", povezava); dataAdapter.Fill(dataSet);
But I dont know how to tell the: while (reader1.Read()) { listBoxCompanyList.Items.Add(reader1["CompanyName"]); }
to read it!!
I gave you the exact code that you needed. You changed it and now it doesn't work.
Go back to the code you were using with the SqlConnection and SqlCommand objects. The following code will read the data from the database, and put in the CompanyName into the ListBox(assuming that the CompanyName is the first field in the query).
c#
SqlDataReader dr = cmd.ExecuteReader(); while(dr.Read()) { listBox1.Items.Add(dr.GetString(0)); }
We were talking about wildcards before. is this correct: "SELECT CompanyName FROM Company WHERE ((@stationary IS NULL) OR (Company.Stationary LIKE @stationary + '01%'))";
What I would like to do, it to get tel. numbers which starts with 01
This post has been edited by Mitja Bonca: 4 Jun, 2009 - 11:02 AM
We were talking about wildcards before. is this correct: "SELECT CompanyName FROM Company WHERE ((@stationary IS NULL) OR (Company.Stationary LIKE @stationary + '01%'))";
What I would like to do, it to get tel. numbers which starts with 01
CODE
SELECT CompanyName FROM Company WHERE ((Company.Stationary IS NOT NULL) AND (Company.Stationary LIKE '01%'))
that is not using the parameter. If you still want to use the parameter...
CODE
SELECT CompanyName FROM Company WHERE Company.Stationary LIKE @stationary + '%'
and make the parameter's value = "01". This will return all Company Names where the Stationary starts with "01". You don't need to check for the null.
This post has been edited by eclipsed4utoo: 4 Jun, 2009 - 11:58 AM
I do need, cause I have other items in the query. I have a coulpe of textBoxes and some comboBoxes, and all together is meant for searching company names. So if the user selects the query just for example city all the other quires are null, otherwise it does not work. So i need to set this one too. But how? This is my whole query, but its my langueage (u will see the point): string podrobnoIskanje = "SELECT Podjetje.ImePodjetja FROM Podjetje, Regija, Dejavnost WHERE " + "((@imeRegije IS NULL) OR (Regija.ImeRegije = @imeRegije)) AND " + "((@imeDejavnosti IS NULL) OR (Dejavnost.ImeDejavnosti = @imeDejavnosti)) AND " + "((@nazivPodjetja IS NULL) OR (Podjetje.NazivPodjetja = @nazivPodjetja)) AND " + "((@imePodjetja IS NULL) OR (Podjetje.ImePodjetja = @imePodjetja)) AND " + "((@pošta IS NULL) OR (Podjetje.Pošta = @pošta)) AND " + "((@kraj IS NULL) OR (Podjetje.Kraj = @kraj)) AND " + "((@stacionarni IS NULL) OR (Podjetje.Stacionarni LIKE @stacionarni + '01%')) AND " + "Dejavnost.IDDejavnosti = Podjetje.IDDejavnostiFK AND Regija.IDRegije = Podjetje.IDRegijeFK " + "ORDER BY Podjetje.ImePodjetja DESC";
This post has been edited by Mitja Bonca: 4 Jun, 2009 - 01:06 PM