I am trying to have a from submit and date entered into the table Chargability, but it is coming up with the error ODBC Error Code = 21S01 (Insert value list does not match column list) but I dont see anything wrong with my code or database.

CHARGEABILITY DATABASE:
ID - AutoNumber
Employee - Number
Week - Number
ChargeHours - Text
InternalHours - Text
VacationHours - Text
NassignHours - Text
AvailableHours - Text
BudgetCharge - Number
FiscalYear - Number

FORM PAGE:
<form name=AllEditWeek action=chargeabilityreportsubmit3.cfm method=post>
<cfoutput group="UserName">
<input type="hidden" name="Employee" value="#Charge.UserID#">
<input type="hidden" name="Week" value="#Week#">
<input type="hidden" name="FiscalYear" value="2004">
<input type="hidden" name="BudgetCharge" value="25">
<tr>
<td width="120" bordercolor="blue"><font size="1"><b>#Charge.fullname#</b></font></td>
<td width="75" Align="Center"><input type="text" name="ChargeHours" size=3 ></font></td>
<td width="75" Align="Center"><input type="text" name="InternalHours" size=3></font></td>
<td width="75" Align="center"><input type="text" name="VacationHours" size=3></font></td>
<td width="75" Align="Center"><input type="text" name="NassignHours" size=3></font></td>
<td width="75" Align="Center"><input type="text" name="AvailableHours" size=3></font></td>
<td width="75" Align="center"> % </font></td>
<td width="50"><input type="submit" name="Submit" value="OK"> </td>
</tr>
</cfoutput>
</form>

SUBMIT PAGE:
<CFQUERY NAME="AddOrder" DATASOURCE="literature">
INSERT INTO Chargeability (Employee, Week, ChargeHours, InternalHours, VacationHours, NassignHours, AvailableHours, FiscalYear, BudgetCharge)
VALUES(#Form.Employee#, #Week#, #ChargeHours#, #InternalHours#, '#vacationHours#', '#NassignHours#', '#AvailableHours#', #FiscalYear#, #BudgetCharge#)
</CFQUERY>

Hi mindy,

The only difference I see is that in your database list FiscalYear and BudgetCharge are reversed. AFAIK, that shouldn't be a problem. I'm assuming that you're not having problems selecting those fields for download, so the only other possibility I can think of is that perhaps the spelling and/or case is off in the lists above -- doublecheck against the list you use for downloading.

Vinny

Hi Mindy

When looking for errors I always try to eliminate stuff. In this case lets get the SQL out of it. Try using CFINSERT and see if the fields match your DB columns

<cfinsert datasource="literature" tablename="Chargeability" dbtype="ODBC" dbname="?????" username="?????" password="?????" formfields="Employee, Week, ChargeHours, InternalHours, VacationHours, NassignHours, AvailableHours, FiscalYear, BudgetCharge">

If they don't match they really don't match.

Next remove the fields one by one till it works then you know the offending field :)

also.. is there a reason you are using FORM. for employee and not for the rest?

CHARGEABILITY DATABASE:
ID - AutoNumber
Employee - Number
Week - Number
ChargeHours - Text
InternalHours - Text
VacationHours - Text
NassignHours - Text
AvailableHours - Text
BudgetCharge - Number
FiscalYear - Number

INSERT INTO Chargeability (Employee, Week, ChargeHours, InternalHours, VacationHours, NassignHours, AvailableHours, FiscalYear, BudgetCharge)
VALUES(#Form.Employee#, #Week#, #ChargeHours#, #InternalHours#, '#vacationHours#', '#NassignHours#', '#AvailableHours#', #FiscalYear#, #BudgetCharge#)
If ChargeHours and InternalHours are Text, you should wrap the values in single quotes like your other text values.

INSERT INTO Chargeability
(Employee, Week, ChargeHours, InternalHours, VacationHours,
NassignHours, AvailableHours, FiscalYear, BudgetCharge)
VALUES (#FORM.Employee#, #FORM.Week#, '#FORM.ChargeHours#',
'#FORM.InternalHours#', '#FORM.VacationHours#', '#FORM.NassignHours#',
'#FORM.AvailableHours#', #FORM.FiscalYear#, #FORM.BudgetCharge#)

I noticed that the fields you have designated as Text strings in the database are not surrounded by single quotes in the SQL insert statement. You can omit quotes for numeric fields (that is, fields designated as numeric within the database), but for fields that the database interprets as text, you need to surround their values with quotes, even if the content of those fields will end up being numbers.