C# Textbox.Text into SQL SmallMoney

Question

I have the following code

String query = "INSERT INTO Product (ProductCode, Range, Type, Size, Description, Cost, Barcode) VALUE ('" + txbx_ProductCode.Text + "', '" + txbx_Range.Text + "','" +
                    txbx_Type.Text + "' , '" + txbx_Size.Text + "', '" + txbx_Description.Text + "' , '" + decimal.Parse(txbx_Cost.Text) + "' , '" + txbx_Barcode.Text + "')";

I get the following FormatException on this line: FormatException Image

I suspect there is an issue with me parsing the .text. I've tried parsing to float, double, Decimal and decimal. It's the same on all cases.

The values entered are between 20000.00 and 100.00 always having 2 decimals so they never exceed the smallmoney value.

Answer 1

Firstly thank you to E.J. Brennan and Ian Kemp for putting me on the right path. I don't know how to add a commendation or whatever it is called on StackOverflow but thank you.

I ended up adding everything into prepared statements and got a few errors but they were different to the one I got earlier and easier to squash.

The new method looks like this:

using (SqlConnection sqlCon = new SqlConnection(connectionString))
        {
            //open database (closed below)
            sqlCon.Open();

            //sql query that inserts data into table using user inputs
            String query = "INSERT INTO Product(ProductCode, Range, Type, Size, Description, Cost, Barcode) VALUES(@ProductCode, @Range, @Type, @Size, " +
                "@Description, @Cost, @Barcode) ";

            //initialise sqlcommand using the query and connection
            using var command = new SqlCommand(query, sqlCon);

            //assign values and prepare them
            command.Parameters.Add("@ProductCode", SqlDbType.VarChar, 25).Value = txbx_ProductCode.Text;
            command.Parameters.Add("@Range", SqlDbType.VarChar, 20).Value = txbx_Range.Text;
            command.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = txbx_Type.Text;
            command.Parameters.Add("@Size", SqlDbType.VarChar, 15).Value = txbx_Size.Text;
            command.Parameters.Add("@Description", SqlDbType.VarChar, 100).Value = txbx_Description.Text;
            command.Parameters.Add("@Cost", SqlDbType.SmallMoney).Value = Decimal.Parse(txbx_Cost.Text);
            command.Parameters.Add("@Barcode", SqlDbType.VarChar, 13).Value = txbx_Barcode.Text;

            //prepare the added statements
            command.Prepare();
            //execute using nonquery (cause of no expected return data)
            command.ExecuteNonQuery();

            //close database connection (opened above)
            sqlCon.Close();
           
            //display succesfull insertion
            MessageBox.Show("Product succesfully added.");
        }

PS. Sorry for all the commenting, I just like knowing at a glance whats what.

And yes, "for the love of all that's holy, use prepared statements".