Solved

C Sharp

Posted on 2014-04-01
26
187 Views
Last Modified: 2014-04-03
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
0
Comment
Question by:Moti Mashiah
  • 17
  • 9
26 Comments
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39970734
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39970735
Please , let me know if there is any other way to do it.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39970755
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39970798
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39970812
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39970824
Thank you soo much I will try it soon and let you know how it goes.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39970851
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39971561
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39972268
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39972284
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39972434
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39972445
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39972477
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39972558
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39972577
Where in your code is that error coming from? Specifically, which line of code?
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39972613
For example: when I choose some Customer with out history I'm getting this error.

Please see screenshot:
Capture.JPG
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39972751
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39972839
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39972876
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39973065
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39973248
Please look at the screenshot I put the code in this location and it gave me error.


Thanks,
Capture.JPG
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39973656
Please , can I get some help guys. I'm really stack.
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39973835
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39975078
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
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39975133
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
 
LVL 1

Author Closing Comment

by:Moti Mashiah
ID: 39975139
one of the best help I ever had thank you.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now