Click here to Skip to main content
15,912,932 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
<pre>using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace Northwind_Sql_Data_Extraction
{
    public partial class Form1: Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'northwindDataSet.Customers' table. You can move, or remove it, as needed.
            this.customersTableAdapter.Fill(this.northwindDataSet.Customers);
            // TODO: This line of code loads data into the 'northwindDataSet.Order_Details' table. You can move, or remove it, as needed.
            this.order_DetailsTableAdapter.Fill(this.northwindDataSet.Order_Details);
            // TODO: This line of code loads data into the 'northwindDataSet.Orders' table. You can move, or remove it, as needed.
            this.ordersTableAdapter.Fill(this.northwindDataSet.Orders);        
        }

        private void FunctionThree()
        {
            using (SqlConnection con = new SqlConnection("Data Source=LAPTOP- CI6IN3IA\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"))
            {
               {
                    con.Open();
             string AddColumn = "ALTER TABLE[Order Details] ADD[Order Details Value] int";
                    SqlCommand sql = new SqlCommand(AddColumn, con);
                    sql.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("order detail value already created",ex.Message);
                }
                try
                {
                    string UpdateValue = "UPDATE [Order Details] SET [Order Details].[Order Details Value] = ([Order Details].Quantity * [Order Details].UnitPrice)";
                    SqlCommand insertCommand = new SqlCommand(UpdateValue, con);
                    insertCommand.ExecuteNonQuery();
                    con.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void Button1_Click(object sender, EventArgs e)
        {
            switch (comboBox1.SelectedItem.ToString())
            {
                case "Task 3":
                    FunctionThree();                   
                    break;
                default:
                    MessageBox.Show("Please select a value between 1 and 5");
                    break;
            }
        }
    }
}


What I have tried:

I'm trying to make my code for adding a new column to a table more streamlined. Although my method works, I receive an exception error indicating that the table produced must be unique. Obviously, this is due to the fact that Sql.ExecuteNonQuery causes the table to be recreated if I run the code several times, but I can't seem to find a viable solution.
Posted
Updated 16-Mar-22 1:00am
Comments
Maciej Los 15-Mar-22 16:04pm    
Which line causes the error?
[EDIT]
Do you really want to let user to change your table structure? That's insane!
Tafadzwa Tapera 15-Mar-22 18:21pm    
No, I'm not saying I want a user to be allowed to modify my table structure; I'm saying that when I run the code again after SQL.executenonquery, it generates an error in the sense that it recreates the table that would have already been constructed during the initial debugging session. How do I go about solving that part

I would strongly advise you move the SQL Command text which adds the column into a SProc in the database, then you can re-run without issues.

The SProc could look something like:

IF NOT EXISTS ( SELECT [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = 'Order Details' AND [COLUMN_NAME] = 'Order Details Value')
BEGIN
	-- ALTER TABLE command here to add column
END
GO


Then when you re-run it, the IF NOT EXISTS will return false, and the SProc will end without attempting to add the column.

How to improve your code:
Your code which sets the 'Order details value' field could be improved, by writing the UPDATE command a separate SProc to perform the UPDATE.

I'm assuming you posted this code as part of a study topic, so the answer above will resolve for you, but I will suggest however you study the Computed Column type in SQL

The problem with your approach is that if you need to update that order details value field from another function, you will need to remember to call it from every possible point of code in your application! This can get tedious and repetitive. Imagine if you change the logic which decides if it needs updating? It will result in more code.
So, have a look at Computed Column.

Adding computed column:
In SQL Server Management Studio, right click table > Design > Add Column manually 'OrderDetailsValue' > Properties: set 'Computed Column Spec' to:
SQL
Quantity*UnitPrice

What this will do is perform the calculation on every row in that table and automatically update the field for you.

Another possible, and more efficient solution is to call SQL Command to set the Order Details Value when you insert a new Order row. For this you will need to set SQLParameters on the SQLCommand object.

All the best, hope it helps.
 
Share this answer
 
There is a Catch without a Try, you are missing a Try statement after:
using (SqlConnection con = new SqlConnection("Data Source=LAPTOP- CI6IN3IA\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"))
{
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900