How do I insert data into a relational database

Question

I have 3 Microsoft SQL Server tables which consist of the following:

All 3 tables are related.

I need to insert data into the Training table which I can do.

The problem is inserting the CustomerID column of the customers table into the training table by only knowing the customer name.

I created a separate class as I will be using this quite a lot in my applications.

 public void UpdateDatabase(string sql_query)
 {
        using (SqlConnection conn = new SqlConnection(connection_string))
        {
            using (SqlCommand comm = new SqlCommand(sql_query, conn))
            {
                conn.Open();
                comm.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

This is my main class which is used to update my DB.

In my button click even i have the following code.

 private void btnSave_Click(object sender, EventArgs e)
 {
        DB NewJob = new DB();

        if (TabControl1.SelectedTab == TabControl1.TabPages["TPTraining"])//If TPTraiing Tab is selected
            {
                try
                {
                    string Customer = TPTcboCustomer.Text;
                    string Product = TPTcboProduct.Text;
                    DateTime DemoDate = TPTdtpDate.Value.Date;
                    string EndResult = TPTtxtEndResult.Text;

                    NewJob.UpdateDatabase("INSERT INTO TrainingDemos(CustomerID, ProductID, DemoDate, EndResult)VALUES('" + Customer + "','" + Product + "','" + DemoDate + "','" + EndResult + "')");
                    MessageBox.Show("Training/Demo Saved Sussesfully");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

The Customer variable holds the customer name that i get from the Customer Table. Instead of inserting the Customer Name into the Training Table i must insert the CustomerID.

string Customer = TPTcboCustomer.Text;

When the form is loaded the TPTcboCustomer combobox control retrieves all customer names from the customer table.

Long story short, how do I insert the CustomerID into the training table by only knowing the customer name.

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Apr 12, 2016 at 5:17
INSERT INTO TrainingDemos (...) VALUES ((SELECT CustomerId FROM Customer WHERE name = @CustomerName), @ProductId,...) — adrianm
– adrianm, Commented Apr 12, 2016 at 6:02

itsme86 · Accepted Answer · 2016-04-12 05:21:38Z

1

You'll need to do another query to obtain the CustomerID. Once you have that, you can do your insert.

I mean, you need to verify that it's a real customer anyway, right?

I usually prefer to fill a drop down (or similar field) with customers to choose from anyway when there's a finite number of items to choose from. Personally, I'd do an initial query pulling in all of the customers and populate the drop down with those customer objects. Then you just use the selected item's object information to do your insert.

Make Customer a strong type:

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Other properties as desired

    public override string ToString()
    {
        return Name ?? "<No Name>";
    }
}

Once you have that, read in all of your customers and populate the combo box with your list of Customer objects. The ToString() will make it display the customer's name in the drop down. Then you just have to do:

Customer customer = (Customer)TPTcboCustomer.SelectedItem;

Then BLAM-O, you have all of the customer information you need to do your insert.

edited Apr 12, 2016 at 5:21

answered Apr 12, 2016 at 5:09

itsme86

19.6k4 gold badges44 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gerry Humphreys Over a year ago

There is another form that adds the customers to the customer table. The drop down combo box retrieved the customer names from the customer table and does not allowing editing. Thank you, I am going to try another query and that should do the job.

Collectives™ on Stack Overflow

How do I insert data into a relational database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related