C Sharp

Hi All ,

I  added listbox to show history in my application. I wrote this application with C# windows form. please see screenshot of my application.

what I would like to do is when user click on approve button the listbox will pick and show what written in the "current status textbox" and + show time in one line in the textbox.

I hope it is possible to do.

thanks
LVL 1
Moti Mashiah.NET DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
You'd be better off parameterising the whole query, which is a better practice than using inline code. So, for example, you would be looking at something like:
string Query = "update RMSCRMTest.dbo.sales set lastname=@LastName, status=@Status, status1=@Status1, history=@History, history1=@History1 where lastname=@LastName";
            
SqlConnection Myconn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand("", conn);

cmd.Parameters.AddWithValue("@LastName", this.txtlname.Text);
cmd.Parameters.AddWithValue("@Status", this.txtstatus.Text);
cmd.Parameters.AddWithValue("@Status1", this.txtStatus1.Text);

List<string> history = new List<string>(5);
for (int i = 0; i < listBox1.Items.Count; ++i)
{
    history.Add(listBox1.Items[i].ToString());
}

for (int i = 0; i < history.Count; ++i)
{
    string paramName = "@History";
    if (i > 0) paramName += i.ToString();

    cmd.Parameters.AddWithValue(paramName, history[i]);
}

// open connection, execute query, etc

Open in new window

0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Sorry I forgot to upload the screenshot. also I forgot to mention would like to keep history in the database as well.
Capture.JPG
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Please , let me know if there is any other way to do it.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Carl TawnSystems and Integration DeveloperCommented:
You should just need something like:
yourListBox.Items.Add(YourTextBox.Text + "(" + DateTime.Now.ToString() + ")");

Open in new window

Switching out the DateTime.Now.ToString() part for your label if you need that time specifically.

Writing to the database would need something along the lines of (assuming it's SQL Server):
SqlConnection cn = new SqlConnection("your connection string");
SqlCommand cmd = new SqlCommand("INSERT INTO YourTable ([ApprovalText], [ApprovalTime]) VALUES ( @ApprovalText, @ApprovalDate )", cn);
cmd.Parameters.AddWithValue("@ApprovalText", YourTextBox.Text);
cmd.Parameters.AddWithValue("@ApprovalDate", DateTime.Now);

cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

Open in new window

Obviously it will need modifying a bit to suit your database/table/columns, but hopefully you get the general idea.
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
You are great it is working that simple.

now I need to know how to keep 5 time history in my database....any suggestion.

this is my button code.

        //update button updating status and status1 + send email
        private void button1_Click(object sender, EventArgs e)
        {
            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRMTest;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "update RMSCRMTest.dbo.sales set lastname='" + this.txtlname.Text + "' ,status='" + this.txtstatus.Text + "' ,status1='" + this.txtStatus1.Text + "' where lastname='" + this.txtlname.Text + "' ;";
            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmdDataBase = new SqlCommand(Query, Myconn);
            SqlDataReader Reader;
            try
            {
                Myconn.Open();
                Reader = cmdDataBase.ExecuteReader();
                MessageBox.Show("Updated Client Status see email Approved");
                while (Reader.Read())
                {


                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }
0
 
Carl TawnSystems and Integration DeveloperCommented:
Assuming you have a secondary table to record the history, then I personally would add a trigger to the table itself to remove the oldest record each time you insert a new one. Specifically you'd need an INSERT trigger - reference:

   http://msdn.microsoft.com/en-us/library/ms189799.aspx

Failing that you would need to count how many rows are in your listbox and run a separate DELETE query against the database to remove the oldest before INSERTing the new row.
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Thank you soo much I will try it soon and let you know how it goes.
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
I'm really beginner and I have hard time to understand the trigger concept.

Actually, I have one table and I created more columns for the history as I don't now how to navigate for two tables in one button.

here is some screenshot how my table looks like. I know the idea looks stupid :) but as I said I'm really beginner.


Thanks,
Capture.JPG
0
 
Carl TawnSystems and Integration DeveloperCommented:
Well, if you're going for that approach you'll need to take a count of how many items are in your listbox and use that to generate the column name to update:
// code to add item to listbox

...

string fieldName = "History";

// figure out which field we need to update
// you also probably need to do something here to prevent more than 5 entries
if (YourListBox.Items.Count > 1)
    fieldName += (YourListBox.Items.Count-1).ToString();

SqlConnection cn = new SqlConnection("connection string");
SqlCommand cmd = new SqlCommand("UPDATE YourTable SET " + fieldName + " = @HistoryValue WHERE SomeIDColumn = @YourRowID", cn);

cmd.Parameters.AddWithValue("@HistoryValue", yourHistoryValue);
cmd.Parameters.AddWithValue("@YourRowID", identifierForTheRow);

cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

Open in new window

0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Thank you very much it was really helpful.

now I can update the listbox to the database history column but I have another one more issue that I can't figure out.

when I drop down the combobox "by Name"(see screenshot) I have two textboxes that change between each name I choose, but its not happening with the listbox its just remain the same and add more and more items when I click the approve button.

Here is my code:

//combobox last name display in the textboxs
        private void comboLname_SelectedIndexChanged(object sender, EventArgs e)
        {
            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRMTest;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "select * from RMSCRMTest.dbo.sales where LastName= '" + comboLname.Text + "' ;";
            
           
            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmdDataBase = new SqlCommand(Query, Myconn);
            SqlDataReader Reader;
            try
            {
                Myconn.Open();
                Reader = cmdDataBase.ExecuteReader();
                while (Reader.Read())
                {
                    string ID = Reader.GetInt32(Reader.GetOrdinal("ID")).ToString();
                    string AccountNuber = Reader.GetString(Reader.GetOrdinal("AccountNumber")).ToString();
                    string Time = Reader.GetDateTime(Reader.GetOrdinal("Time")).ToString();
                    string Deposit = Reader.GetDecimal(Reader.GetOrdinal("Deposit")).ToString();
                    string slastname = Reader.GetString(Reader.GetOrdinal("lastname"));
                    int statusIndex = Reader.GetOrdinal("status");
                    string sstatus = Reader.IsDBNull(statusIndex) ? null : Reader.GetString(statusIndex);
                    int status1Index = Reader.GetOrdinal("status1");
                    string sstatus1 = Reader.IsDBNull(status1Index) ? null : Reader.GetString(status1Index);
                    int historyIndex = Reader.GetOrdinal("history");
                    string slbox = Reader.IsDBNull(historyIndex) ? null : Reader.GetString(historyIndex);
                    txtid.Text = ID;
                    txtacnum.Text = AccountNuber;
                    txttime.Text = Time;
                    txtdeposit.Text = Deposit;
                    txtlname.Text = slastname;
                    txtstatus.Text = sstatus;
                    txtStatus1.Text = sstatus1;
                    listBox1.Text = slbox;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }
        }

Open in new window

Capture.JPG
0
 
Carl TawnSystems and Integration DeveloperCommented:
Instead of just:
listBox1.Text = slbox;

Open in new window

You should be doing something like:
// clear out any current items
listBox1.Items.Clear();

// add new item
listBox1.Items.Add(slbox);

Open in new window

0
 
Moti Mashiah.NET DeveloperAuthor Commented:
now it works thank you again you are great.

Now I have another 2 issue when I move around between  Customers that doesn't have value in the history I'm getting "value cannot be null. parameter name:item" how could I avoid this message.

 The second issue is when I have values in the listbox I see it as... (please see screenshot) this is how he save the data to the database.

Thank You sooooo much for help.
Capture.JPG
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
that's how I'm updating listbox and textboxs:

 //update button updating status and status1 + send email
        private void button1_Click(object sender, EventArgs e)
        {
            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRMTest;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "update RMSCRMTest.dbo.sales set lastname='" + this.txtlname.Text + "' ,status='" + this.txtstatus.Text + "' ,status1='" + this.txtStatus1.Text + "' ,history='" + this.listBox1 + "' where lastname='" + this.txtlname.Text + "' ;";
            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmdDataBase = new SqlCommand(Query, Myconn);
            SqlDataReader Reader;
            try
            {
                Myconn.Open();
                Reader = cmdDataBase.ExecuteReader();
                MessageBox.Show("Updated Client Status see email Approval");
                while (Reader.Read())
                {


                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }

Open in new window

0
 
Carl TawnSystems and Integration DeveloperCommented:
For the first issue it looks like you have just pulled to listboxes "Text" property, rather than dealing with the items in the listbox as a collection.

So, in your query, instead of using:
this.listBox1

Open in new window

You need to use:
this.listBox1.Items[0].Text

Open in new window

In order to get the value of the item.
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Thank you again it solved the problem.

Do you have any solution for my first issue:

"value cannot be null. parameter name:item"

it happens when I choose some customer from the combobox that he doesn't have history
0
 
Carl TawnSystems and Integration DeveloperCommented:
Where in your code is that error coming from? Specifically, which line of code?
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
For example: when I choose some Customer with out history I'm getting this error.

Please see screenshot:
Capture.JPG
0
 
Carl TawnSystems and Integration DeveloperCommented:
You just need to check that you have a value before trying to add it to the listbox:
if (slbox != null)
{
    listBox1.Items.Clear();
    listBox1.Items.Add(slbox);
}

Open in new window

0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Working perfect now and Thank you for that one.

Now I think it is the last thing I have to solved to really make it works.

In my database I created another column "History" and whenever I updated in my listbox it should save to this column.  the issue is that it is saving just one line.

Please see screenshot

Thanks ,
Capture.JPG
0
 
Carl TawnSystems and Integration DeveloperCommented:
As I mentioned in an earlier post, you need to build up a SQL query that will map to the items in the listbox. Perhaps something like:
SqlCommand cmd = new SqlCommand("UPDATE YourTable SET History1=@History1, History2=@History2, History3=@History3, History4=@History4, History5 = @History5", cn);

// build up a list of history items (or blank if not available)
List<string> history = new List<string>(5);
for (int i = 0; i < listBox1.Items.Count; ++i)
{
     history[i] = listBox1.Items[i].Text;
}

// create a parameter for each history field
for (int i = 0; i < history.Length; ++i)
{
    cmd.Parameters.AddWithValue("@History" + i.ToString(), history[i]);
}

cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

Open in new window

0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Sorry , for missed understanding as I said I'm really beginner:

Can you help me to understand where to locate this code you provided above.

I would like to put this code in to this button see my code:

string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRMTest;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "update RMSCRMTest.dbo.sales set lastname='" + this.txtlname.Text + "' ,status='" + this.txtstatus.Text + "' ,status1='" + this.txtStatus1.Text + "' ,history='" + this.listBox1.Items[0] + "' where lastname='" + this.txtlname.Text + "' ;";
            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmdDataBase = new SqlCommand(Query, Myconn);
            SqlDataReader Reader;
            try
            {
                Myconn.Open();
                Reader = cmdDataBase.ExecuteReader();
                MessageBox.Show("Updated Client Status see email Approval");
                while (Reader.Read())
                {


                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }

Open in new window

0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Please look at the screenshot I put the code in this location and it gave me error.


Thanks,
Capture.JPG
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Please , can I get some help guys. I'm really stack.
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
Thank you very much for y your great help I have changed my code to this one you gave me and it looks much easier to understand it.

I wrote the code that way see below and it is save just one history to the history column the history1 wasn't filled when I put two value in the listbox.

string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRMTest;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "update RMSCRMTest.dbo.sales set lastname=@LastName, Status=@Status, Status1=@Status1, history=@History1 where lastname=@Lastname";

            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmd = new SqlCommand(Query, Myconn);
            SqlDataReader reader;

            cmd.Parameters.AddWithValue("@Lastname", this.txtlname.Text);
            cmd.Parameters.AddWithValue("@Status", this.txtstatus.Text);
            cmd.Parameters.AddWithValue("@Status1", this.txtStatus1.Text);

            List<string> history = new List<string>(5);
            for (int i = 0; i < listBox1.Items.Count; ++i)
            {
                history.Add(listBox1.Items[i].ToString());
            }
            for (int i = 0; i <history.Count; ++i)
            {
                string parameName = "@History";
                if (i > 0) parameName += i.ToString();

                cmd.Parameters.AddWithValue(parameName, history[i]);
            }
            try
            {
                Myconn.Open();
                reader = cmd.ExecuteReader();
                MessageBox.Show("Updated Client status see email approval");
                while (reader.Read())
                {

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }

Open in new window

0
 
Moti Mashiah.NET DeveloperAuthor Commented:
K I figured my mistake I had to added another history string now everything work perfect.


Thank you sooo much for the wonderful help really one of the best I got in this site.


it is possible to learn from you by private session like online learning.  I'm looking for some teacher who can help me to progress more faster.

Let me know if is it possible and how much it is for an hour.
my email is - moti2424@gmail.com.
0
 
Moti Mashiah.NET DeveloperAuthor Commented:
one of the best help I ever had thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.