Solved

SQL and C#

Posted on 2014-04-04
44
243 Views
Last Modified: 2014-04-07
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.
0
Comment
Question by:Moti Mashiah
  • 26
  • 18
44 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39978185
Logically it would make more sense to have a History table with multiple rows per Sales record, rather than having a single row with multiple columns.

So more like:

Sales      -  ID, Customer, Account, Deposit, Time
History   -  ID, SalesID, History

Where SalesID will be used to create the relationship between the two tables.

In SQL terms that would be something like (you'll need to change the data type to suit):
CREATE TABLE Sales
(
	[ID]		INTEGER		NOT NULL  IDENTITY(1,1)		PRIMARY KEY,
	[Customer]	VARCHAR(50)	NOT NULL,
	[Account]	VARCHAR(50) NOT NULL,
	[Deposit]	MONEY		NOT NULL,
	[Time]		DATETIME	NOT NULL
)

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

ALTER TABLE History
	ADD CONSTRAINT [FK_SalesID] FOREIGN KEY ( [SalesID] ) REFERENCES Sales ( [ID] )

Open in new window

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39978205
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)

)
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39978245
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:

Sample data
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39978266
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

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39978269
Sorry it was enough to show you one line connection from the code
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39978298
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.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39978340
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.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39978353
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

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39978374
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?
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39978415
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?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39978422
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.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39978468
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

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39978542
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.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39978595
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

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39978752
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.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39978946
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

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39979032
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.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39979119
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

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39979133
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?
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39979134
Thank you for really good explanation... I'm going to try if it works.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39979142
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

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39979147
I meant it is just contain the surname
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39979169
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.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39979201
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.
0
 
LVL 1

Author Comment

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

Expert Comment

by:Carl Tawn
ID: 39979247
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?
0
 
LVL 1

Author Comment

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

Expert Comment

by:Carl Tawn
ID: 39979299
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.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39979417
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

0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39979973
I'd put the ID in a variable then if you need to change where it is coming from you only need to change it once:
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);

            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", salesId);
                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", salesId);
                    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

0
 
LVL 1

Author Comment

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

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39983148
There's an extra comma in the SQL query. It should say:
string Query = "update RMSCRM.dbo.sales set ID=@id, Status=@Status, Status1=@Status1, history=@History where ID=@id";

Open in new window

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39983180
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.
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39983201
Ah yes, in that case you need to remove the "History=@History" part from the UPDATE query as well. So it should read:
string Query = "update RMSCRM.dbo.sales set ID=@id, Status=@Status, Status1=@Status1 where ID=@id";

Open in new window

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39983213
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

0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39983249
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

Capture.JPG
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39983291
You need to remove the "SalesID" field from the UPDATE statement. The "SalesID" is the key field for the table, so it can't be modified directly.
string Query = "update RMSCRM.dbo.sales set Status=@Status, Status1=@Status1 where ID=@id";

Open in new window

You need to use it to tell the database which row to update, but you can't modify the key itself.
0
 
LVL 1

Author Comment

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

Author Comment

by:Moti Mashiah
ID: 39983383
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

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39983416
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.
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39983518
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

Capture.JPG
0
 
LVL 1

Author Comment

by:Moti Mashiah
ID: 39983628
when I debug I get this line what makes the problem:

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

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39983776
This:
cmd.Parameters.AddWithValue("@SalesID", ID);

Open in new window

Should say:
cmd.Parameters.AddWithValue("@SalesID", salesID);

Open in new window

0
 
LVL 1

Author Closing Comment

by:Moti Mashiah
ID: 39983872
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

705 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