c# and Access mdb files

  • Thread starter Thread starter Eric Kiernan
  • Start date Start date
E

Eric Kiernan

I'm having problems from c# with an access ( 2003 ) mdb file. It blows
up with a "Data Type mismatch in criteria" when i execute the command
to add a record to a table. It is when MonthBegin, which is a byte is
part of the insert command. It works if I omit MonthBegin. It reads
from the Access file as a byte ( i once cast it to an object to confirm
it was a byte), though, and it is defined in Access as a byte.
Ironically, it does fill the MonthBegin field in the mdb table
accurately. Below is the code. I'm using the OLEDB provider

stringCommand = "INSERT INTO BudgetSpending ( BudgetYear,
Account,MonthToBegin) "
stringCommand += "VALUES (@BudgetYear,@Account,@MonthToBegin)";


objCommand.Parameters.AddWithValue("@BudgetYear", "2009"); // string
objCommand.Parameters.AddWithValue("@Account", tempRow.Account); //string
objCommand.Parameters.AddWithValue("@MonthToBegin", tempRow.MonthToBegin
) // byte.

objCommand.CommandText = stringCommand;
objCommand.CommandType = CommandType.Text;
objConnection.Open();
objCommand.ExecuteNonQuery(); // BLOWS WITH THE DATA TYPE MISMATCH ERROR

the insert command looks like this in the debugger
"INSERT INTO BudgetSpending ( BudgetYear, Account,MonthToBegin) VALUES
(@BudgetYear,@Account,@MonthToBegin)"

the same thing happens if i don't use parameters and use the object
fields directly.
 
I'm having problems from c# with an access ( 2003 ) mdb file.  It blows
up with a "Data Type mismatch in criteria"  when i execute the command
to add a record to a table.  It is when MonthBegin, which is a byte is
part of the insert command.  It works if I omit MonthBegin.  It reads
from the Access file as a byte ( i once cast it to an object to confirm
it was a byte), though, and it is defined in Access as a byte.
Ironically, it does fill the MonthBegin field in the mdb table
accurately.  Below is the code.  I'm using the OLEDB provider

stringCommand = "INSERT INTO BudgetSpending ( BudgetYear,
Account,MonthToBegin) "
stringCommand += "VALUES (@BudgetYear,@Account,@MonthToBegin)";

objCommand.Parameters.AddWithValue("@BudgetYear", "2009");  // string
objCommand.Parameters.AddWithValue("@Account", tempRow.Account); //string
objCommand.Parameters.AddWithValue("@MonthToBegin", tempRow.MonthToBegin
) // byte.

  objCommand.CommandText = stringCommand;
  objCommand.CommandType = CommandType.Text;
  objConnection.Open();
  objCommand.ExecuteNonQuery();  // BLOWS WITH THE DATA TYPE MISMATCHERROR

the insert command looks like this in the debugger
"INSERT INTO BudgetSpending ( BudgetYear, Account,MonthToBegin) VALUES
(@BudgetYear,@Account,@MonthToBegin)"

the same thing happens if i don't use parameters and use the object
fields directly.
Click to expand...

I know this may sound silly, but have you tried sending in a number
(i.e. 1 or 0, or 1 or -1) instead? I know we have had trouble with
Access and their true/false flags and how that correlates to SQL
Server data types and VBA data types. I think I remember something
about the Access True/False value being handled with a -1 for False.
Not sure though. From your sample code, we can't really tell what
tempRow.MonthToBegin actually is.
 
Maybe the parameter is assigning the incorrect data type? You could try
using .Parameters.Add to specify the datatype, and then assign the parameter
value.
 
I know this may sound silly, but have you tried sending in a number
(i.e. 1 or 0, or 1 or -1) instead? I know we have had trouble with
Access and their true/false flags and how that correlates to SQL
Server data types and VBA data types. I think I remember something
about the Access True/False value being handled with a -1 for False.
Not sure though. From your sample code, we can't really tell what
tempRow.MonthToBegin actually is.
Click to expand...
I'm sending a byte with a number in it. i'm using 0/1 for Yes/No
because it fits well well a combo boxes yes/no choices.
 
I'm sending a byte with a number in it.  i'm using 0/1 for Yes/No
because it fits well well a combo boxes yes/no choices.
Click to expand...

Okay then, have you tried sending a true or false value instead or an
actual number?
 
Back
Top