ERROR in prepared Statement (INSERT INTO stopping on non valid variable, I want to replace it with NULL)

Question

Was trying to use a prepStatement to insert into database with multiple columns. I want the statement to adapt when I try to insert INSERT INTO prepared Statement ignoring non valid number (replacing with Null) and inserting Valid ones

            string prepStatement = "INSERT INTO outright_data.sample (`date`,`last`, `lastqty`) VALUES(@date @last, @lastqty)";
            _prepStmt.CommandText = prepStatement;

            if (frun)
            {
                _prepStmt.Parameters.AddWithValue("@date", datetime_var);
                _prepStmt.Parameters.AddWithValue("@last", last_var);
                _prepStmt.Parameters.AddWithValue("@lastqty", lastQty_var);
                _prepStmt.Prepare();
                frun = false;
            }
            else
            {
                _prepStmt.Parameters["@date"].Value = datetime_var;
                _prepStmt.Parameters["@last"].Value = last_var;
                _prepStmt.Parameters["@lastqty"].Value = lastQty_var;
            }
            i++;
            _counter += _prepStmt.ExecuteNonQuery();

Imagine that lastQty_var variable is not accepted in the lastQty (but the datetime_var and last_var are accepted into their columns) column of mySQL table. When I execute, it raises an exception and stops the code. Is there a way for it to insert the other values and replace the invalid value (lastQty_var) by NULL in the database?

I tried validating the variable using is (problem is, the variable is a double on C#, but not accepted in my double column, so validating in C# types is not a big help);

I wanted to avoid big validating structures, since speed is very important in the whole proccess.

Any advice?

Thanks,

Tomas

so basically you want to validate if value is a double? if so insert happens? — Masoud Andalibi, Jan 27 '17 at 19:50
Thanks for the warning on the missing comma, but it was just a copy/paste error, in the code it is fine. Yes, I want to validate BUT I always want to insert, the non valid values would be replaced by NULL — Lar Bo, Jan 30 '17 at 11:00

score 0 · Answer 1 · edited May 23 '17 at 12:24

0

Try this solution https://stackoverflow.com/a/19870942/7457316

If execute fails then you are trying to fulfill what it needs to run coreectly

edited May 23 '17 at 12:24

Community

1
1

answered Jan 27 '17 at 19:48

Paweł Swajdo

391
1
13

Hi, thanks for the reply. This thread doesn't fit very well in what I'm trying to do, I want the query to always run, detect invalid input fields (field by field) and replace them by NULL. – Lar Bo Jan 30 '17 at 11:03
Hmm... one more thing, let's assume that you will achieve your goal and will be able to set bad values to 'null' then what if field doesn't allow null? – Paweł Swajdo Jan 30 '17 at 13:03
It probably would allow, if I did the validation within the C# program, but as speed is key, I would like to know if mysql can handle this type of exception the way I was thinking about it (and explained). Otherwise, I would need to validate 7*200 values per second, which can be wuite heavy on the program... – Lar Bo Jan 31 '17 at 19:02

ERROR in prepared Statement (INSERT INTO stopping on non valid variable, I want to replace it with NULL)

1 Answers1