Start Free Trial

asked on

SQL and C#

Hi Guys ,

I have implemented windows form application with C# and used sql2012 server.

I connect my application to one database and use one table. Now I would like to change the way I connect to the table and separate it to two tables and build between them relationship then connect my application to this tables for example:

table name = sales - columns - ID, customer, account, deposit, time
table name = history - columns - ID, history1, history2, history3, history4

1.I would like to know how can I build these tables and make between them relationship in order to work with the application.
2. I would like to know how to connect the application to the new structure.

basically I need two things to create the tables in the sql database server and point my app to the new structure.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you for the quick replay.

I wanna be clear of the second part:

REATE TABLE History
(
      [ID]            INTEGER            NOT NULL      IDENTITY(1,1)      PRIMARY KEY,
      [SalesID]      INTEGER            NOT NULL,
      [History]      VARCHAR(100)
)

If I wanna put more history I need to add more columns right?
something like this:

REATE TABLE History
(
      [ID]            INTEGER            NOT NULL      IDENTITY(1,1)      PRIMARY KEY,
      [SalesID]      INTEGER            NOT NULL,
      [History]      VARCHAR(100)
[History1]      VARCHAR(100)
[History2]      VARCHAR(100)

)

No, you use more rows rather than more columns - this gives you much more flexibility and simplifies how you deal with the data.

Say you wanted 5 pieces of history data for order number 1, you would have 5 rows in the history table all relating to that one sales record.

So, for example, in the following screenshot there are three pieces of history data all related to the Sales record with an ID of 1:

User generated image

ASKER

I got it that's make sense.

Now after I created the tables I would like to point the application to the new tables I was trying to do some join but it wasn't work.

Please , can you review my code again:) thank you soo much man.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace Alt8
{
    public partial class frmworkorder : Form
    {
        


        public frmworkorder(string UserName)
        {
            InitializeComponent();
            Fillcombo();
            Fillcombo1();
            combStatus.Items.Add("Waiting For Items/PO");
            combStatus.Items.Add("Waiting For Customer Pick Up");
            combStatus.Items.Add("Waiting For Delivery");
            combStatus.Items.Add("Customer Not Ready for Goods");
            combStatus.Items.Add("Partial Payment");
            timer1.Start();
            label6.Text = UserName;

           
        }

       
        //combobox last name to fill all the name from the database
        void Fillcombo()
        {
            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  ;";
            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmdDataBase = new SqlCommand(Query, Myconn);
            SqlDataReader Reader;
            try
            {
                Myconn.Open();
                Reader = cmdDataBase.ExecuteReader();
                while (Reader.Read())
                {
                    string lname = Reader.GetString(Reader.GetOrdinal("lastname"));
                    comboLname.Items.Add(lname);

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


        }
        //combobox id to fill all the id from the database
        void Fillcombo1()
        {
            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  ;";
            SqlConnection Myconn = new SqlConnection(conn);
            SqlCommand cmdDataBase = new SqlCommand(Query, Myconn);
            SqlDataReader Reader;
            try
            {
                Myconn.Open();
                Reader = cmdDataBase.ExecuteReader();
                while (Reader.Read())
                {
                    string idc = Reader.GetInt32(Reader.GetOrdinal("id")).ToString();
                    comboid.Items.Add(idc);

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

        }

        private object GetInt32(string p)
        {
            throw new NotImplementedException();
        }

        private void frmworkorder_Load(object sender, EventArgs e )
        {
            
        }
       

        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        public object Cust { get; set; }

        //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);

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

                    if (slbox != null)
                    {
                        listBox1.Items.AddRange(history.ToArray());
                        // listBox1.Items.Clear();
                        //listBox1.Items.Add(slbox);
                    }

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }
        }
        
        //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=@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();
            }



                


            
               





            /*System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();
            message.To.Add("helpdesk@althompson.com");
            message.Subject = "Approve status Email";
            message.From = new System.Net.Mail.MailAddress("moti.mashiah@althompson.com");
            message.Body = txtStatus1.Text;
            System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient("srv-mx-01");
            smtp.Send(message);*/
           


        }

        public string status { get; set; }

        private void groupBox1_Enter(object sender, EventArgs e)
        {
        
        }

        public int statusIndex { get; set; }
        
        //exit the application from the x 
        private void frmworkorder_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult dialog = MessageBox.Show("Do you really want to close the program?", "Exit", MessageBoxButtons.YesNo);
            if (dialog == DialogResult.Yes)
            {
                Application.Exit();
            }
            else if (dialog == DialogResult.No)
            {
                e.Cancel = true;
            }
        }

        //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);
                    

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

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

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
           
        }

        //approve button pick up 
        private void button2_Click(object sender, EventArgs e)
        {
           

            if (combStatus.SelectedIndex == 0)
            {
                txtStatus1.Text = "Waiting For Items/PO";
            }
            if (combStatus.SelectedIndex == 1)
            {
                txtStatus1.Text = "Waiting For Customer Pick Up";
            }
            if (combStatus.SelectedIndex == 2)
            {
                txtStatus1.Text = "Waiting For Delivery";
            }
            if (combStatus.SelectedIndex == 3)
            {
                txtStatus1.Text = "Customer Not Ready for Goods";
            }
            if (combStatus.SelectedIndex == 4)
            {
                txtStatus1.Text = "Partial Payment";
            }

           
            listBox1.Items.Add(txtStatus1.Text + "(" + DateTime.Now.ToString() + ":" + label6.Text + ")");



            


           /* System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();
            message.To.Add("helpdesk@althompson.com");
            message.Subject = "Approve status Email";
            message.From = new System.Net.Mail.MailAddress("moti.mashiah@althompson.com");
            message.Body = txtStatus1.Text;
            System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient("srv-mx-01");
            smtp.Send(message);*/
        }

        public int statusIndex1 { get; set; }

       

        private void timer1_Tick(object sender, EventArgs e)
        {
            DateTime datetime = DateTime.Now;
            this.labtime.Text = datetime.ToString();
           
        }

        private void labtime_Click(object sender, EventArgs e)
        {

        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            
        }

        //Exit button
        private void btnexit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
        
        //Logoff button
        private void button2_Click_1(object sender, EventArgs e)
        {
            this.Hide();
            frmLogin f1 = new frmLogin();
            f1.ShowDialog();
        }
        //reset button
        private void button3_Click(object sender, EventArgs e)
        {
            txtstatus.Clear();
            txtStatus1.Clear();
            listBox1.Items.Clear();

        }

        

   
           

       
    }
}

Open in new window

ASKER

Sorry it was enough to show you one line connection from the code

Since you are using the LastName to get the header details, you'll need to make a second database call to get the associated history (i've also assumed you're only getting a single sales row back, so have modified your loop):

        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();
                
                // retrieve header information
                if (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);

                    txtid.Text = ID;
                    txtacnum.Text = AccountNuber;
                    txttime.Text = Time;
                    txtdeposit.Text = Deposit;
                    txtlname.Text = slastname;
                    txtstatus.Text = sstatus;
                    txtStatus1.Text = sstatus1;

                    // close the reader ready for our next query
                    Reader.Close();

                    cmdDataBase.CommandText = "SELECT * FROM History WHERE SalesID=@SalesID";
                    
                    // add parameter to SalesID
                    cmdDataBase.Parameters.Clear();
                    cmdDataBase.Parameters.AddWithValue("@SalesID", ID);

                    Reader = cmdDataBase.ExecuteReader();
                    
                    // clear and repopulate listbox
                    listBox1.Items.Clear();
                    while (Reader.Read())
                    {
                        listBox1.Items.Add(Reader.GetString(Reader.GetOrdinal("History")));
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Myconn.Close();
            }
        }

Open in new window

Note how we no longer need to populate an intermediate List<string>? Because we are now storing one history row for each entry we no longer need to check for specific column names, or cater for potential missing values.

ASKER

K I got this one. Now I would like to know how to bring up the new database I have created with two table to my code. I would like to get off from this database "RMSCRMTest".

I have built this database:

RMSCRM and created two tables - sales and history.

ASKER

should I do something like that:

tring conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "select * from RMSCRM.sales join history where LastName= '" +   comboLname.Text + "' ;";

Open in new window

I'm not quite sure what you are attempting to do. What are you expecting the JOIN to do, and why do you think you need it?

ASKER

my missed understanding sorry.

In this case I need to add just one table to the code like sales?

let's say :

string Query = "select * from RMSCRM.sales  where LastName= '" +   comboLname.Text + "' ;";

Open in new window

I meant because they have relationship I don't need to connect both table to the code?

that's right?

No you don't need to JOIN the table in code. In code you generally deal with the two tables as separate entities. You use the ID retrieved from one table to then go and lookup the related data in the other - but they are two distinct operations.

ASKER

K I got what you say now I just change to the new database and table I created:

Query = "select * from RMSCRM.sales  where LastName= '" +   comboLname.Text + "' ;";

Open in new window

Yes. Although you shouldn't need the RMSCRM. prefix as you already specify the Initial Catalog parameter in your connection string, so the context is already set.

ASKER

Now my combobox look like that:

//combobox lastname display in the textboxs
        private void comboLname_SelectedIndexChanged(object sender, EventArgs e)
        {
            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "select * from RMSCRM.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();
                if (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;
                    
                    //close reader ready for our next query
                    Reader.Close();
                    cmdDataBase.CommandText = "SELECT * FROM History WHERE SalesID=@SalseID";

                    //add parameter to salesID
                    cmdDataBase.Parameters.Clear();
                    cmdDataBase.Parameters.AddWithValue("@Sales", ID);

                    Reader = cmdDataBase.ExecuteReader();

                    //cleare and repopulate listbox
                    listBox1.Items.Clear();

                    while (Reader.Read())
                    {
                        listBox1.Items.Add(Reader.GetString(Reader.GetOrdinal("History")));
                    }

                    //listBox1.Text = slbox;
                    //listBox1.Items.Clear();
                    //listBox1.Items.Add(slbox);

                    /*if (slbox != null)
                    {
                        listBox1.Items.AddRange(history.ToArray());
                        // listBox1.Items.Clear();
                        //listBox1.Items.Add(slbox);
                    }*/

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

Open in new window

My only issue for now is how do I change my update Button in depend the new structure database. I was trying to understand it myself and it didn't go well:).

here is my update button code:

private void button1_Click(object sender, EventArgs e)
        {

            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "update RMSCRM.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

For starters you need to modify your app so that you are using the SalesID column to reference the record, rather than the last name. Because the Sales and History tables are related based on that ID you need to keep track of it in your application.

ASKER

K , I did something like this:

//update button updating status, status1, history + send email
        private void button1_Click(object sender, EventArgs e)
        {

            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "update RMSCRM.dbo.sales set SalseID=@ID, Status=@Status, Status1=@Status1, history=@History, where SalesID=@ID"; 
            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>(10);
            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

OK, that's a good start. Now, because the history is stored in a separate table you need to run a separated SQL statement for each History item. But, since you can add and remove them, it will be easier to clear out the old items and re-add them, rather than trying to try and figure out if you need to UPDATE and existing entry or INSERT a new one.

With that in mind you want to something like:

//update button updating status, status1, history + send email
private void button1_Click(object sender, EventArgs e)
{
    string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
    string Query = "update RMSCRM.dbo.sales set SalseID=@ID, Status=@Status, Status1=@Status1, history=@History, where SalesID=@ID"; 
            
    SqlConnection Myconn = new SqlConnection(conn);
    SqlCommand cmd = new SqlCommand(Query, Myconn);
            
    cmd.Parameters.AddWithValue("@Lastname", this.txtlname.Text);
    cmd.Parameters.AddWithValue("@Status", this.txtstatus.Text);
    cmd.Parameters.AddWithValue("@Status1", this.txtStatus1.Text);

    try
    {        
        MyConn.Open();

        // execute the query to update the header
        cmd.ExecuteNonQuery();

        // build and execute query to delete current history
        cmd.CommandText = "DELETE FROM History WHERE SalesID=@SalesID"; 
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@SalesID", ID);
        cmd.ExecuteNonQuery();

        // build and execute queries to re-add history
        for (int i = 0; i < listBox1.Items.Count; ++i)
        {
            cmd.CommandText = "INSERT INTO [History] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SalesID", ID);
            cmd.Parameters.AddWithValue("@History", listBox1.Items[i].ToString());
            cmd.ExecuteNonQuery();
        }

        MessageBox.Show("Updated Client status see email approval");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        Myconn.Close();
    }
}

Open in new window

Although you don't appear to be adding a parameter for the SalesID at the moment. But once you set that up you should be good to go.

ASKER

K , I have made the change and now it look like:

//update button updating status, status1, history + send email
        private void button1_Click(object sender, EventArgs e)
        {

            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "update RMSCRM.dbo.sales set SalseID=@ID, Status=@Status, Status1=@Status1, history=@History, where SalesID=@ID"; 

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

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

            try
            {
                Myconn.Open();
                //execute the query to update the header
                cmd.ExecuteNonQuery();

                //build and execute query to delete current history
                cmd.CommandText = "DELETE FROM History WHERE SalesID=@SalesID";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@SalesID", ID);
                cmd.ExecuteNonQuery();

                //build and execute queries to re-add history
                for (int i = 0; i < listBox1.Items.Count; ++i)
                {
                    cmd.CommandText = "INSERT INTO [history] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@SalesID", ID);
                    cmd.Parameters.AddWithValue("@History", listBox1.Items[i].ToString());
                    cmd.ExecuteNonQuery();
                }

                MessageBox.Show("Updated Client status see email approval");


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

Open in new window

In this line:

cmd.Parameters.AddWithValue("@ID", this.txtlname.Text);

Open in new window

Does txtlname.Text actually contain the ID of the Sales record, or does it contain the surname text?

ASKER

Thank you for really good explanation... I'm going to try if it works.

ASKER

actually I figured that I don't need to use it anymore as it is just text record.

//update button updating status, status1, history + send email
        private void button1_Click(object sender, EventArgs e)
        {

            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "update RMSCRM.dbo.sales set SalseID=@ID, Status=@Status, Status1=@Status1, history=@History, where SalesID=@ID"; 

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

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

            try
            {
                Myconn.Open();
                //execute the query to update the header
                cmd.ExecuteNonQuery();

                //build and execute query to delete current history
                cmd.CommandText = "DELETE FROM History WHERE SalesID=@SalesID";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@SalesID", ID);
                cmd.ExecuteNonQuery();

                //build and execute queries to re-add history
                for (int i = 0; i < listBox1.Items.Count; ++i)
                {
                    cmd.CommandText = "INSERT INTO [history] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@SalesID", ID);
                    cmd.Parameters.AddWithValue("@History", listBox1.Items[i].ToString());
                    cmd.ExecuteNonQuery();
                }

Open in new window

ASKER

I meant it is just contain the surname

ASKER

The only things I have to update is status,status1 column what I added to the sales table and History the column from the History table.

I assumed that the SalseID=@ID is now what contain the ID.

It is. But the @ID part is just a placeholder, you still need to provide an actual value for it. So, when you first read the Sales record from the database you need to store the associated SalesID somewhere so that you can use it to update the record.

The form has a Tag property that is used for storing an arbitary piece of data, so you can use that if you need to.

ASKER

K before I changed to two tables I used to have the lastname as associated ID to update the table like:

string Query = "update RMSCRM.sales set lastname=@lastname, Status=@Status, Status1=@Status1, history=@History, where lastname=@lastname";

Open in new window

so now I thought it will be better to do:

string Query = "update RMSCRM.dbo.sales set SalseID=@ID, Status=@Status, Status1=@Status1, history=@History, where SalesID=@ID";

Open in new window

as the sales id is the associated to the update.

I have screenshot my databases structure and I marked the only columns I have to update the two status and status1 column which point to textboxes and the history column which point to the listbox.
Capture.JPG

That's fine. But in order to update the table based on the SalesID you have to be storing it somewhere within your form in order to be able to use it in the query. That's the part that you don't look to be currently doing.

At what point do you read the values from the Sales table, and what do you do with the SalesID value at the moment?

ASKER

I'm not sure what are you asking sorry...in my understanding I have to based on some value in the column lets say I don't wanna based on salesID and I wanna base on column "lastname" for updating

Please see screenshot.
Capture.JPG

That's fine for updating the Sales table. But because the History table is related to the Sales table through the SalesID column you need to have the SalesID value available in order to be able to do the inserts to the History table.

On a side note, where possible you want to do database UPDATES based on the primary key column (the SalesID in this case). This is because the database physically stores records in primary key order, so it is more efficient for retrieving an updating data.

The other issue with using the lastname column of course, is that as soon as you have more than one person with the same surname then your app will no longer work correctly. The SalesID is what uniquely identifies each row in the table so, if you can, you want to be using that.

ASKER

K , now I understand that the relationship between history and sales is:

sales table = ID connected to history table = salesID.

I also screenshot to just be sure that I understand it right.

If it is true I would like to understand where do I have to clarify the SalesID in the code.

I have done this:

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

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


            cmd.Parameters.AddWithValue("@id", this.txtid.Text);
            cmd.Parameters.AddWithValue("@Status", this.txtstatus.Text);
            cmd.Parameters.AddWithValue("@Status1", this.txtStatus1.Text);
            cmd.Parameters.AddWithValue("@History", this.listBox1.Text);
            

            try
            {
                Myconn.Open();
                //execute the query to update the header
                cmd.ExecuteNonQuery();

                //build and execute query to delete current history
                cmd.CommandText = "DELETE FROM History WHERE SalesID=@SalesID";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@SalesID", ID);
                cmd.ExecuteNonQuery();

                //build and execute queries to re-add history
                for (int i = 0; i < listBox1.Items.Count; ++i)
                {
                    cmd.CommandText = "INSERT INTO [history] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@SalesID", ID);
                    cmd.Parameters.AddWithValue("@History", listBox1.Items[i].ToString());
                    cmd.ExecuteNonQuery();
                }

                MessageBox.Show("Updated Client status see email approval");


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

Open in new window

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi ,

After I changed the code update button for the one you provide above I click the button update and get this message look at the screenshot.

thanks ,
Capture.JPG

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you so much for this one it seems to be fine now but now he doesn't find the history column "Invalid column name History" the history column is in the history table.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

OK now it is look like that:

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

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

            int salesID = int.Parse(this.txtid.Text);

            cmd.Parameters.AddWithValue("@id", salesID);
            cmd.Parameters.AddWithValue("@Status", this.txtstatus.Text);
            cmd.Parameters.AddWithValue("@Status1", this.txtStatus1.Text);
            //cmd.Parameters.AddWithValue("@History", this.listBox1.Text);
          
            

            try
            {
                Myconn.Open();
                //execute the query to update the header
                cmd.ExecuteNonQuery();

                //build and execute query to delete current history
                cmd.CommandText = "DELETE FROM History WHERE SalesID=@SalesID";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@SalesID", ID);
                cmd.ExecuteNonQuery();

                //build and execute queries to re-add history
                for (int i = 0; i < listBox1.Items.Count; ++i)
                {
                    cmd.CommandText = "INSERT INTO [history] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@SalesID", ID);
                    cmd.Parameters.AddWithValue("@History", listBox1.Items[i].ToString());
                    cmd.ExecuteNonQuery();
                }

                MessageBox.Show("Updated Client status see email approval");


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

Open in new window

ASKER

it seems to be OK now but now I get this message that he can't update the identity column ID I think this issue comes from the combo box because when I choose some item from the combobox I get this issue please look at the screenshoot and 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=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "select * from RMSCRM.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();
                if (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;


                    
                    //close reader ready for our next query
                    Reader.Close();
                    cmdDataBase.CommandText = "SELECT * FROM History WHERE SalesID=@SalseID";

                    //add parameter to salesID
                    cmdDataBase.Parameters.Clear();
                    cmdDataBase.Parameters.AddWithValue("@Sales", ID);

                    Reader = cmdDataBase.ExecuteReader();

                    //cleare and repopulate listbox
                    listBox1.Items.Clear();

                    while (Reader.Read())
                    {
                        listBox1.Items.Add(Reader.GetString(Reader.GetOrdinal("History")));
                    }

                    //listBox1.Text = slbox;
                    //listBox1.Items.Clear();
                    //listBox1.Items.Add(slbox);

                    /*if (slbox != null)
                    {
                        listBox1.Items.AddRange(history.ToArray());
                        // listBox1.Items.Clear();
                        //listBox1.Items.Add(slbox);
                    }*/

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

Open in new window

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Now I'm trying to understand what is this following message..

I think this issue related to the combobox

when I launch the application I first click in the combobox"by name" to get the item\user from the database and then get this issue see screenshoot "combobox"

After this step I click OK then add history and status and Click on update and get this issue.
see screenshoot updatebutton
Capture.JPG
Capture.JPG

ASKER

in my understanding I have first to declare the salesID in the combobox.

//combobox lastname display in the textboxs
        private void comboLname_SelectedIndexChanged(object sender, EventArgs e)
        {
            string conn = "Data Source=srv-db-02;Initial Catalog=RMSCRM;Persist Security Info=True;User ID=test;Password=masterB4";
            string Query = "select * from RMSCRM.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();
                if (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;

                    
                    
                    //close reader ready for our next query
                    Reader.Close();
                    cmdDataBase.CommandText = "SELECT * FROM History WHERE SalesID=@SalseID";


                    //add parameter to salesID
                    cmdDataBase.Parameters.Clear();
                    cmdDataBase.Parameters.AddWithValue("@SalesID", ID);

                    Reader = cmdDataBase.ExecuteReader();

                    //cleare and repopulate listbox
                    listBox1.Items.Clear();

                    while (Reader.Read())
                    {
                        listBox1.Items.Add(Reader.GetString(Reader.GetOrdinal("History")));
                    }

      

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

Open in new window

It looks like it's just a typo and a name mismatch. In your query you have the parameter down as @SalseID rather then @SalesID.

And the error message from the Update would suggest a similar issue. Check through your code carefully and make sure that the parameter names in the SQL query match the parameter names being added to the SqlCommands Parameters collection.

ASKER

OH Yes it was missed typo I think that I'm really blind :).

I really like to thank you sooo much for all your wonderful help. I'm beginner and I'm sure it is very challenging to deal with me.

Now I hope that I left with one last issue.

When I click the update I got this message please see attachment

here is my button update code.

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

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

            int salesID = int.Parse(this.txtid.Text);

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

            try
            {
                Myconn.Open();
                //execute the query to update the header
                cmd.ExecuteNonQuery();

                //build and execute query to delete current history
                cmd.CommandText = "DELETE FROM History WHERE SalesID=@SalesID";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@SalesID", ID);
                cmd.ExecuteNonQuery();

                //build and execute queries to re-add history
                for (int i = 0; i < listBox1.Items.Count; ++i)
                {
                    cmd.CommandText = "INSERT INTO [history] ( [SalesID], [History] ) VALUES ( @SalesID, @History )";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@SalesID", ID);
                    cmd.Parameters.AddWithValue("@History", listBox1.Items[i].ToString());
                    cmd.ExecuteNonQuery();
                }

                MessageBox.Show("Updated Client status see email approval");


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

Open in new window

ASKER

when I debug I get this line what makes the problem:

cmd.CommandText = "DELETE FROM History WHERE SalesID=@SalesID";

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Carl You are the champion.

Thank you very much for all your patience and wonderful help it seems that everything is working well now.

I have another SQL question and I'm going to open another thread for that one.