Solved

C Sharp

Posted on 2014-04-03
7
263 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
  • 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction: Hints for the grid button.  Nested classes, templated collections.  Squash that darned bug! Continuing from the sixth article about sudoku.   Open the project in visual studio. First we will finish with the SUD_SETVALUE messa…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

18 Experts available now in Live!

Get 1:1 Help Now