[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

C Sharp

Posted on 2014-04-03
7
Medium Priority
?
275 Views
Last Modified: 2014-04-04
Hi Guys ,

I have application that save history to the database and present the database in the history listbox. I wrote code that saved history to two columns:

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=@History, history1=@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


The database saved in these two columns but in the listbox present just column 1
columns name history and history1 see my code:

  //comboid to display in the textboxes
        private void comboid_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 id= '" + comboid.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;
                    listBox1.Items.Clear();
                    //listBox1.Items.Add(slbox);

                    if (slbox != null)
                    {
                        listBox1.Items.Clear();
                        listBox1.Items.Add(slbox);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }
        }

Open in new window


also I uploaded screenshot for example see attachment
Capture.JPG
0
Comment
Question by:Moti Mashiah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39975373
How many "history" columns are in your query? You'll need to add each potential column to your listbox. You may find it easier to load them into a List<string> first and then add them as a range to the Listbox.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39975497
For now I have just two Columns for history :
1. history
2.history1

I can save the history to both columns now after you provided the last code and what I'm looking for now is to preset them in the listbox after I choose some customer in the combobox. It shows just the history column

here is my combobox code:

/combobox lastname 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;
                    listBox1.Items.Clear();
                    //listBox1.Items.Add(slbox);

                    if (slbox != null)
                    {
                        listBox1.Items.Clear();
                        listBox1.Items.Add(slbox);
                    }

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

Open in new window

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39975593
Try:
       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);

                    List<string> history = new List<string>();
                    if (!Reader.IsDBNull(Reader.GetOrdinal("history")))
                        history.Add(Reader.GetString(Reader.GetOrdinal("history")));

                    if (!Reader.IsDBNull(Reader.GetOrdinal("history1")))
                        history.Add(Reader.GetString(Reader.GetOrdinal("history1")));

                    txtid.Text = ID;
                    txtacnum.Text = AccountNuber;
                    txttime.Text = Time;
                    txtdeposit.Text = Deposit;
                    txtlname.Text = slastname;
                    txtstatus.Text = sstatus;
                    txtStatus1.Text = sstatus1;
                    
                    listBox1.Items.Clear();
                    
                    if (history.Count > 0)
                    {
                        listBox1.Items.AddRange(history.ToArray());
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }
        }

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39975677
You are the Champion thank you very much.

Actually I didn't understand really the concept of this code but it is working :)


thanks again
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 39977543
It basically creates an empty List<string> and then checks each of your history fields in turn. If the value in the database is non-null, it gets added to the list.

Then, if the list is not empty, it is used to populate the listbox. Doing it this way allows for any gaps in your database. So, for example, if the "history1" field had a value but "history" didn't then it would still show in the list, and you wouldn't have a blank row in the listbox for the missing value.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39977924
Thank you very much for wonderful help
0
 
LVL 1

Author Closing Comment

by:Moti Mashiah
ID: 39977927
Carl is the best support I had ever
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: Dialogs (1) modal - maintaining the database. Continuing from the ninth article about sudoku.   You might have heard of modal and modeless dialogs.  Here with this Sudoku application will we use one of each type: a modal dialog …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Suggested Courses

656 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