c# and Access mdb files

[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.

 

[henry.lee.jr]

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.

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.

 

[Paul Shapiro]

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.

 

[Eric Kiernan]

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.

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.

 

[henry.lee.jr]

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.

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