C# invalid cast error and and possibly table issues.

Question

0.00/5 (No votes)

See more:

Hello all,
I am creating a new Customer and in my C# SQL query I am getting the error “Invalid cast operation error.” I have a User and I am grabbing info from that user and inserting it into Customer. I was getting a foreign key error but I removed the foreign key so I could insert data now im getting invalid cast operations,

C#

        private void BtnProfileEdit_Click(object sender, RoutedEventArgs e)
        {            
            EnableControls();

            if (boolBtnPush == true)
            {
                if (BtnProfileEdit.Content.Equals("Save"))
                {
                    if (currentCustomer.IsCustomer == true)
                    {
                        if (TextboxFirstName.Text.Equals(""))
                        {   //Display messagebox so user MUST enter firstname.
                            MessageBox.Show("You 'MUST' enter a First name,", "WARNING", MessageBoxButton.OK);
                        }
                        else
                        {
                            try
                            {
                                customerLoaded = new Customer(TextboxFirstName.Text,
                                    TextboxLastName.Text, TextboxAddress.Text, TextboxCity.Text,
                                    ComboboxState.SelectedValue.ToString(), TextboxZip.Text, TextEmailAddress.Text);
                                UsersDB.UpdateCustomer(customerLoaded);

                                Window SrcCustomerScreen = new CustomerScreen(currentCustomer);
                                SrcCustomerScreen.Show();
                                Close();
                            }
                            catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); }
                        }
                    }
                    else
                    {
                        MessageBox.Show("You must press 'Yes' selecting combobox customer", "Unacceptable", MessageBoxButton.OK);
                    }                  
                }
            }
            //ON THE FIRST BUTTON CLICK DO THIS SO ON SECOND ABOVE CODE IS EXECUTED.
            boolBtnPush = true;
            BtnProfileEdit.Content = "Save";
        }

        private void Btntest_Click(object sender, RoutedEventArgs e)
        {
            MessageBox.Show(currentCustomer.ToString());
        }

        private void CboCustomer_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            string cboValue = "";
            if (CboCustomer.SelectedIndex > 0)
                cboValue = ((ComboBoxItem)CboCustomer.SelectedItem).Content.ToString();
            if(cboValue.Equals("Yes"))
            {
                boolIsCustomer = true;
                User addCustomer = null;
                Customer newCustomer = null;

                MessageBoxResult result = MessageBox.Show("Updating database to customer status.",
                    "Customer Confirmation", MessageBoxButton.YesNo, MessageBoxImage.Exclamation);
                if (result == MessageBoxResult.Yes)
                {
                    try
                    {
                        addCustomer = new User(currentCustomer.UserID, currentCustomer.Username,
                            currentCustomer.Password, currentCustomer.IsAdmin, currentCustomer.UserCreatedDate, boolIsCustomer);
                        UsersDB.UpdateCurrentUser(addCustomer);
                        //Create New Customer
                        newCustomer = new Customer(currentCustomer.UserID, currentCustomer.Username, null, null,
                                                    null, null, null, null);
                        UsersDB.CreateCustomer(newCustomer);

                        //Load the newly created customer object so it can be read.
                        customerLoaded = UsersDB.ReadCustomerById(currentCustomer.UserID);
                        PopulateControls(customerLoaded);
                    }
                    catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); }
                }
            }
        }
public static int CreateCustomer(Customer customer)
        {
            string SQLcreateQuery = "INSERT INTO Customers (CustomerId, FirstName, LastName, Address, " +
                "City, State, ZipCode, EmailAddress) VALUES(@id, @fn, @ln, @ad, @ci, @st, @zc, @ea)";
            SqlCommand cmdCreate = new SqlCommand(SQLcreateQuery, connection);
            cmdCreate.Parameters.AddWithValue("@id", customer.UserID);
            cmdCreate.Parameters.AddWithValue("@fn", customer.FirstName);
            cmdCreate.Parameters.AddWithValue("@ln", customer.LastName ?? Convert.DBNull);
            cmdCreate.Parameters.AddWithValue("@ad", customer.Address ?? Convert.DBNull);
            cmdCreate.Parameters.AddWithValue("@ci", customer.City ?? Convert.DBNull);
            cmdCreate.Parameters.AddWithValue("@st", customer.State ?? Convert.DBNull);
            cmdCreate.Parameters.AddWithValue("@zc", customer.ZipCode ?? Convert.DBNull);
            cmdCreate.Parameters.AddWithValue("@ea", customer.EmailAddress ?? Convert.DBNull);
            try
            {
                connection.Open();
                cmdCreate.ExecuteNonQuery();
                string SQLselect = "SELECT @@IDENTITY FROM Customers";
                SqlCommand cmdSELECT = new SqlCommand(SQLselect, connection);
                int CustomerId = (int)cmdSELECT.ExecuteScalar();
                return CustomerId;
            }
            catch (Exception ex) { throw ex; }
            finally { connection.Close(); }
        }

What I have tried:

removing foreign key
and other things too

Posted 8-Nov-18 23:35pm

TheBigBearNow

Updated 29-Jan-19 2:24am

Add a Solution

Comments

CHill60 9-Nov-18 6:07am

Are you getting the error when you execute the query or before? What is your table schema and does it match what you are trying to insert?
By the way, blindly removing foreign keys is NOT a good way to debug your code - you are just grasping at straws

Richard Deeming 9-Nov-18 7:51am

SELECT @@IDENTITY FROM Customers

That's not how @@IDENTITY[^] works.

ZurdoDev 9-Nov-18 13:32pm

You posted all the code which tells me you have not debugged this to figure out what is going on. It is a SQL error, which means you need to see what values you are passing to SQL and fix it.

akshay_zz 15-Nov-18 12:15pm

Hope you have debugged your code.So can you tell us at which line you are getting the error.

-cmdCreate.ExecuteNonQuery();
-int CustomerId = (int)cmdSELECT.ExecuteScalar();

or any other. It would be great if if you can see what values you are passing that does that values matches to the tables column data type.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2019-01-29T02:25:00

Your ExecuteScalar command is returning Null (see the comment from @Richard-Deeming)

You should check for the null before attempting to cast the return value.

A neat way of doing that is as follows:

C#

object result = cmdSELECT.ExecuteScalar();
result = (result == DBNull.Value) ? null : result;
int CustomerId = Convert.ToInt32(result);

Which works because Convert.ToInt32(null) returns 0

Solution adapted from Nikhil Vartak's response here[^]

Now to address the fundamental problem in your code.

C#

string SQLselect = "SELECT @@IDENTITY FROM Customers";
                SqlCommand cmdSELECT = new SqlCommand(SQLselect, connection);

is not going to return you the Id of the row you just inserted. Even if it did then what if another user had inserted a row in the split second between your two sql statements?

Change your SQL insert to return the id generated e.g. (assuming your identity column is called ID)

string SQLcreateQuery = "INSERT INTO Customers (CustomerId, FirstName, LastName, Address, City, State, ZipCode, EmailAddress) OUTPUT INSERTED.ID VALUES(@id, @fn, @ln, @ad, @ci, @st, @zc, @ea)";

You will then need to use ExecuteScalar to run the SQL not ExecuteNonQuery

C#

object result = cmdSQLcreateQuery.ExecuteScalar();
result = (result == DBNull.Value) ? null : result;
int CustomerId = Convert.ToInt32(result);

Ravi Gaur 11 · Answer 2 · 2019-01-29T01:43:00

Solution 1

connection.Open();
      cmdCreate.ExecuteNonQuery();
      string SQLselect = "SELECT @@IDENTITY FROM Customers";
      SqlCommand cmdSELECT = new SqlCommand(SQLselect, connection);
      int CustomerId = (int)cmdSELECT.ExecuteScalar();
      return CustomerId;

error is in this section,
check what comes out and then convert/cast it

cmdSELECT.ExecuteScalar()

Posted 29-Jan-19 1:43am

Ravi Gaur 11

v2

Comments

CHill60 29-Jan-19 8:03am

It's more likely that the ExecuteScalar command is returning null. So check for null first.

Ravi Gaur 11 29-Jan-19 8:10am

also think so, beacuse "SELECT @@IDENTITY FROM Customers" will return null