C# issue when trying to insert into database file

Question

I have been having issues getting my insert to work. I have been considering it to be done based on the selectedindex of two comboboxes(1. cboCustomer 2. cboProduct) and one textbox (RegistrationDate). The error I get the most would be related to the CustomerID being Null; which I thought was supposed to be automatically generated. Of course what confuses me the most are the form specifications. The cboCustomer combobox has to display the Name of the customer; which isn't part of the Registration table. And the cboProduct combobox has to display the product name; which also isn't related to the Registration table. The registration table is where I need to make the insert, but the combobox are loaded using two methods that fetch the name from the Customer and Product tables, which cannot be used for the insert. Which, if I try to Insert just the registrationDate I get an error saying ProductCode cannot be Null. I am also a bit confused if the AddRegistration method will run the insert despite returning a boolean value. Thank you for any assistance and for your time.

Most of the code below has been changed a few times, because I have tried several solutions with no success.

This is the code that I have to fetch the values in to be passed to the AddRegistration method.

public void PutRegistrationData(Registration registration)
    {
        registration.RegistrationDate = registrationDateTextBox.Text;
        registration.ProductCode = productComboBox.SelectedValue.ToString();
    }

This is the code for the Button on the form that triggers the insert in the AddRegistration.

    private void btnRegister_Click(object sender, EventArgs e)
    {
        if (this.IsPresent(registrationDateTextBox.Text))
        {
            registration = new Registration();
            this.PutRegistrationData(registration);
            try
            {
                RegistrationDB.AddRegistration(registration);
                MessageBox.Show("The product was entered successfully", "Registration Success");

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

This is the code I have the for my insert within AddRegistration of the RegistrationDB Class.

public static bool AddRegistration(Registration registration)
    {
        try
        {
            SqlConnection connection = TechSupportDB.GetConnection();
            string insertStatement = "INSERT INTO Registrations (ProductCode, RegistrationDate" +
                                     "VALUES (@ProductCode,@RegistrationDate)";
            SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
            //insertCommand.Parameters.AddWithValue("@CustomerID", registration.CustomerID);
            insertCommand.Parameters.AddWithValue("@ProductCode", registration.ProductCode);
            insertCommand.Parameters.AddWithValue("@RegistrationDate", registration.RegistrationDate);
            insertCommand.Connection = connection;

            connection.Open();
            insertCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }
}

}

The insert command is missing the close parenthesys after the field list. Is it a typo? — Steve, Sep 24 '16 at 08:53
Also remove the empty catch inside the AddRegistration because in that way you loose the real exception that tells you what is wrong with your query — Steve, Sep 24 '16 at 08:56
Thanks for the reply. And it may be a typo,I have spent several hours on this code and changed the insert a few times in the process. — JaceLandrum, Sep 24 '16 at 08:59
Probably because with that text you will get a 'syntax error'. However, fix it and remove the catch block in the static AddRegistration and tell us what error do you get with exactly this code. — Steve, Sep 24 '16 at 09:01
With the corrections. I am getting the SqlException that CustomerID cannot have NULL inserted. This is probably the one issue that has been elusive. — JaceLandrum, Sep 24 '16 at 09:03
This means that you need the same code used for the Product also for the CustomerID and then change the INSERT to add the ProductID — Steve, Sep 24 '16 at 09:11
I agree Steve. But it's better to change your table for set auto increment column on your CustomerID (if CustomerID is your key of course) — Esperento57, Sep 24 '16 at 09:14
I don't think that CustomerID is the primary key on the Registrations table. Otherwise a Customer could register only one product? — Steve, Sep 24 '16 at 09:16

Steve · Accepted Answer · 2016-09-25T08:00:05.603

0

According to your comments above you need to read also the CustomerID value from the customers' combo, add it to your Registration instance and change the query to insert also that value

public void PutRegistrationData(Registration registration)
{
    registration.RegistrationDate = registrationDateTextBox.Text;
    registration.ProductCode = productComboBox.SelectedValue.ToString();
    registration.CustomerID = customerComboBox.SelectedValue.ToString();
}


public static bool AddRegistration(Registration registration)
{
     string insertStatement = @"INSERT INTO Registrations 
            (ProductCode, RegistrationDate, CustomerID) 
            VALUES (@ProductCode,@RegistrationDate, @CustomerID)";
    using(SqlConnection connection = TechSupportDB.GetConnection())
    using(SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
    {    
        insertCommand.Parameters.AddWithValue("@CustomerID", registration.CustomerID);
        insertCommand.Parameters.AddWithValue("@ProductCode", registration.ProductCode);
        insertCommand.Parameters.AddWithValue("@RegistrationDate", registration.RegistrationDate);

        connection.Open();
        int added = insertCommand.ExecuteNonQuery();
        return added != 0;
   }
}

Notice that I have put your SqlConnection and SqlCommand inside a using block to ensure proper closing and disposing of these two object instances. When a class implements the IDisposable interface is better to always dispose the object as soon as possible and the using statement allows exactly this. Also, to return a boolean true if the record has been added I have changed the return statement of the AddRegistration to be true only if the insertion succeed checking if the return of ExecuteNonQUery is not zero.

In all other cases, let the exception reach the upper level of your application where you can handle it for your user.

I wish also underline the fact that AddWithValue is a dangerous method and should be used with extreme care (or better not at all). It makes a lot of assumptions about your parameters. For example, the date is passed as a string here. You can only hope that the database engine can translate back the string to a date if the column expects a date.

edited Sep 25 '16 at 08:00

answered Sep 24 '16 at 09:15

Steve

213,761
22
232
286

I applied the changes you suggested, but I am getting another exception stating; The INSERT statement conflicted with the FOREIGN KEY constraint. Table Customers, column CustomerID. The statement has been terminated. – JaceLandrum Sep 24 '16 at 09:42
This question explains your issue: http://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint How do you manage to insert in the Customer combo box a customer not present in its table? – Steve Sep 24 '16 at 09:49
The information was useful, thank you for the link. I am however left to ponder how I can resolve this issue. I have considered a second INSERT that will apply an insert of the customerID into the Customers table, but it doesn't seem to be a working solution atm. I'll keep trying to fix this problem.I also noted that the post mentioned 'inserting bad data", which I can say is possible. I got a look at the running values during an application execution, and the value stated was 0 for customerID, – JaceLandrum Sep 24 '16 at 10:36
But how do you fill the customer combo? This combo should be not editable and let the user choose only between existing customers. If you need to add a customer on the fly then provide a button (New Customer) open an appropriate page/form to insert the new customer and reload the combo with the new value. – Steve Sep 24 '16 at 10:46
I have a coded method that fills the customer combobox. That's why I didn't consider needing a customerID was necessary as the values for the listed customers shouldn't have changed. But the insert seems to have an issue with the CustomerID being null, which I am stumped on. I have inidications that there is no value being assigned to customerID. It may be that I need to use an update rather than an insert, but the specifications mentioned the AddRegistration method which implies an insert. – JaceLandrum Sep 24 '16 at 10:52
I can't help more but let me say this. If you have a CustomerID field (probably an integer, not a text like ProductCode) and you need to provide a value for this field then you need to retrieve the ID of all the Customers you put in the customer combo. You set the DisplayMember property to see the customer name, and you set the ValueMember property to set the respective CustomerID value. Now SelectedValue will return a CustomerID and not the customer name or anything else you show in the combobox display area – Steve Sep 24 '16 at 10:58
I have managed to fix my code so that the Insert are definitely working, anytime a duplicate is entered there is an error indicating a PRIMARY KEY constraint. The only issue left would be the need for the AddRegistration method to return a boolean value so that a messagebox can be displayed if the insert is successful. What have at present will almost always display the Messagebox contents. Any suggestions would be appreciated. – JaceLandrum Sep 24 '16 at 23:41
Make it _public static bool AddRegistration_ and _return added != 0_ – Steve Sep 25 '16 at 07:57

C# issue when trying to insert into database file

1 Answers1