Data type mismatch in update query - C# Discussion Boards

23-Jul-19 0:07

Hi I have two queries on a button click. One query inserts a new record in a access database table called Engineering, the other updates an already existing record. They both use the same set of variables which in turn get their values from textboxes on the form. The insert query runs fine. However when I run the update query I get a data type mismatch error. I have checked and rechecked the values to be placed in the database are correct. So I believe that the error is in the syntax of the where clause.

This is the Update query
The where clause gets it's string value 'n' from a textbox on the form called Name_textBox, Which in turn gets it's value from the Name field in the table.

string my_querry = "UPDATE Engineering SET Address = a, Phone = p, Email = em, DateBroughtIn = d, Donation = don, Repair = r, Warranty = war, Type = IT, Make = m, Model = MO, Serial = s, ReportedFault = RF, RepairedBy = i, FaultType = FT, Repaired = Rep, BER = BER, FaultFound = FF, DateTested = DT, DueDate = DueD, TestedBy = TestedBy, ConductorResistance = CPass, CReading = CR, InsulatorResistance = InRes, IReading = IR, EarthLeakage = EarthL, EReading = ER WHERE [Name] ='" + n + "'";

Eddy Vluggen23-Jul-19 0:50

23-Jul-19 0:50

Since we're in the C# forum, care to show us how you assign the values to the parameters, and explain why you're not using a normal parameterized query?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 1326108423-Jul-19 1:54

23-Jul-19 1:54

Hi
I use this code to add the values to the parameters.

cmd.Parameters.AddWithValue("@n", this.Name_textBox.Text);
                    cmd.Parameters.AddWithValue("@a", this.Address_textBox.Text);
                    cmd.Parameters.AddWithValue("@p", this.Phone_textBox.Text);
                    cmd.Parameters.AddWithValue("@em", this.Email_textBox.Text);
                    cmd.Parameters.AddWithValue("@d", this.Date_textBox.Text);
                    cmd.Parameters.Add(new OleDbParameter("@don", OleDbType.Boolean));
                    cmd.Parameters.Add(new OleDbParameter("@r", OleDbType.Boolean));
                    cmd.Parameters.Add(new OleDbParameter("@war", OleDbType.Boolean));
                    cmd.Parameters.AddWithValue("@IT", this.Type_comboBox.Text);
                    cmd.Parameters.AddWithValue("@m", this.Make_textBox.Text);
                    cmd.Parameters.AddWithValue("@MO", this.Model_textBox.Text);
                    cmd.Parameters.AddWithValue("@s", this.Serial_textBox.Text);
                    cmd.Parameters.AddWithValue("@RF", this.ReportedFault_textBox.Text);
                    cmd.Parameters.AddWithValue("@i", this.RepairedBy_textBox.Text);
                    cmd.Parameters.AddWithValue("@FT", this.FaultType_comboBox.Text);
                    cmd.Parameters.Add(new OleDbParameter("@Rep", OleDbType.Boolean));
                    cmd.Parameters.Add(new OleDbParameter("@BER", OleDbType.Boolean));
                    cmd.Parameters.AddWithValue("@FF", this.FaultFound_textBox.Text);
                    if (DateTested_textBox.Text != "")
                    {
                        cmd.Parameters.AddWithValue("@DT", this.DateTested_textBox.Text);
                        cmd.Parameters.AddWithValue("@DueD", this.DueDate_textBox.Text);
                        cmd.Parameters.AddWithValue("@TestedBy", this.TestedBy_textBox.Text);
                        cmd.Parameters.Add(new OleDbParameter("@CPass", OleDbType.Boolean));
                        cmd.Parameters.AddWithValue("@CR", this.Conductor_textBox.Text);
                        cmd.Parameters.Add(new OleDbParameter("@InRes", OleDbType.Boolean));
                        cmd.Parameters.AddWithValue("@IR", this.Insulator_textBox.Text);
                        cmd.Parameters.Add(new OleDbParameter("@EarthL", OleDbType.Boolean));
                        cmd.Parameters.AddWithValue("@ER", this.Earth_textBox.Text);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@DT", "");
                        cmd.Parameters.AddWithValue("@DueD", "");
                        cmd.Parameters.AddWithValue("@TestedBy", "");
                        cmd.Parameters.AddWithValue("@CPass", false);
                        cmd.Parameters.AddWithValue("@CR", "");
                        cmd.Parameters.AddWithValue("@InRes", false);
                        cmd.Parameters.AddWithValue("@IR", "");
                        cmd.Parameters.AddWithValue("@EarthL", false);
                        cmd.Parameters.AddWithValue("@ER", "");
                    }

I have changed the checkbox lines from

cmd.Parameter.AddWithValue("@r, this.Repair_checkbox.checked);

cmd.Parameters.Add(new OleDbParameter("@r", OleDbType.Boolean));

The Bool variable r gets it's value with the line

bool r = Repair_checkBox.Checked;

However changing the line gives me an error saying "Failed Due to Parameter r has no default value"
The only checkbox line before the r variable is don and the corresponding checkbox is ticked, which is why I think it doesn't error.

I am only learning C# so the database will never be used. Therefore I am concentrating on getting it working rather than security, Which to me would make it even harder to understand.

Eddy Vluggen23-Jul-19 2:40

23-Jul-19 2:40

Member 13261084 wrote:
cmd.Parameters.Add(new OleDbParameter("@r", OleDbType.Boolean));

That adds and creates a parameter, but doesn't assign a value for it yet. That's why it is complaining about the default value; it has no value assigned.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 1326108423-Jul-19 2:55

23-Jul-19 2:55

How do I go about assigning a value to it? I tried doing "r= Repair_checkBox.Checked;" before and after the line and still get the same error.

Eddy Vluggen23-Jul-19 3:00

23-Jul-19 3:00

Member 13261084 wrote:
How do I go about assigning a value to it?

Something like below should work;

OleDbParameter op = new OleDbParameter("@r", OleDbType.Boolean);
op.Value = Repair_checkBox.Checked;
cmd.Parameters.Add(op);

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 1326108423-Jul-19 3:31

23-Jul-19 3:31

Thanks for that but I get the same error.
However if I change the line

cmd.Parameters.Add(op);

cmd.Parameters.Add(op.Value);

I get the error message "Failed due to The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not Boolean objects"

stepping through the code op.Value equals false yet it seems to be treating it as if it is blank.

Eddy Vluggen23-Jul-19 6:05

23-Jul-19 6:05

Member 13261084 wrote:
Thanks for that but I get the same error.

Post the full exception. Is it pointing to the same line?

Member 13261084 wrote:
I get the error message "Failed due to The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not Boolean objects"

Exactly as it says; you add a OleDbParameter object to cmd.Parameters. The .Value should be pointing to the boolean.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 1326108423-Jul-19 7:09

23-Jul-19 7:09

Hi I changed the code back to what you suggested.

I'm not sure how to post the full exception but the error happens when it tries to execute the query

cmd.ExecuteNonQuery();

as soon as it tries to execute this line it jumps to the catch statement which runs a messagebox that gives the exception message. I can do the exception.ToString and screen print it, but I cant find a way to upload the image on this site and it is quite large. However the exception code that I get is 0x80040E10 the rest of the text is, the line after the exception message talks about error handling, next line is talking about the Parameters and not being able to get a result, this is followed by a line talking about behavior followed by a line that talks about ExecuteReaderInternal at ExecuteNonQuery
The exception message that I get is "Parameter r has no default value"
however in the locals window it shows "r false bool"

Member 1326108423-Jul-19 7:48

23-Jul-19 7:48

Hi I have sorted it. The mistake was that deleted a field from the query yesterday when building it because I was having trouble with the where part of the statement. I didn't realise that I had to have that field in the query since I was calling the fields by name. Any way with that field put back in and your code, it is all working

Eddy Vluggen24-Jul-19 0:10

24-Jul-19 0:10

Good to hear Thumbs Up | :thumbsup:

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.