Hi there, Im using the following AccessDataSource (Connected to a DetailsView) to try and INSERT and UPDATE user details on a database I'm working on, but try as I might Im consistently getting " Exception Details: System.Data.OleDb.OleDbException: Syntax error in UPDATE statement." and similar INSERT errors.
Is there anything obvious I'm doing wrong with the AccessDataSource?
<asp:AccessDataSource id="DetailsSource" Runat="Server"
DataFile="C:\Program Files\Common Files\ODBC\Data Sources\user_info2.mdb"
SelectCommand="SELECT * FROM user_table WHERE ID = @ID"
InsertCommand="INSERT INTO user_table (ImageName, ID, Title, ForeName, SurName, PhoneNo, RoomNo, EMail,
Role, Course, Year, Staff, BscInfo, MscInfo, Mats, Cracks, HdrBoard, HdrForum, InfoSec, Lab,
Media, PhdInfo, PhdSuper, Research, Secs, Security, Teapot, UgBoard, UgVis, WWW,
StartDate, ProbabtionDate, EndDate, RetirementDate, FundingBody,
ResearchGroup, LineManager, PayCategory, Notes) VALUES (@ImageName, @ID, @Title, @ForeName, @SurName,
@PhoneNo, @RoomNo, @EMail, @Role, @Course, @Year, @Staff, @BscInfo, @MscInfo,
@Mats, @Cracks, @HdrBoard, @HdrForum, @InfoSec, @Lab, @Media,
@PhdInfo, @PhdSuper, @Research, @Secs, @Security, @Teapot, @UgBoard,
@UgVis, @WWW, @StartDate, @ProbabtionDate, @EndDate, @RetirementDate,
@FundingBody, @ResearchGroup, @LineManager, @PayCategory, @Notes); SELECT @ID = SCOPE_IDENTITY()"
UpdateCommand="UPDATE user_table SET ID=@ID, ImageName=@ImageName, Title=@Title, ForeName=@ForeName, SurName=@SurName,
PhoneNo=@PhoneNo, RoomNo=@RoomNo, Email=@Email, Role=@Role, Course=@Course, Year=@year, Staff=@Staff, BscInfo=@BscInfo,
MscInfo=@MscInfo, Mats=@Mats, Cracks=@Cracks, HdrBoard=@HdrBoard, HdrForum=@HdrForum, InfoSec=@InfoSec,
Lab=@Lab, Media=@Media, PhdInfo=@PhdInfo, PhdSuper=@PhdSuper, Research=@Research, Secs=@Secs,
Security=@Security, Teapot=@Teapot, UgBoard=@UgBoard, UgVis=@UgVis, WWW=@WWW,
StartDate=@StartDate, ProbabtionDate=@ProbabtionDate, EndDate=@EndDate, RetirementDate=@RetirementDate,
FundingBody=@FundingBody, ResearchGroup=@ResearchGroup, LineManager=@LineManager, PayCategory=@PayCategory, Notes=@Notes WHERE ID=@ID"
>
<SelectParameters>
<asp:ControlParameter ControlID="GridView" Name="ID" PropertyName="SelectedValue"/>
</SelectParameters>
<UpdateParameters>
<asparameter Name="ImageName" Type="String" />
<asparameter Name="ID" Type="Int32" />
<asparameter Name="Title" Type="String" />
<asparameter Name="ForeName" Type="String" />
<asparameter Name="SurName" Type="String" />
<asparameter Name="PhoneNo" Type="String" />
<asparameter Name="RoomNo" Type="String" />
<asparameter Name="EMail" Type="String" />
<asparameter Name="Role" Type="String" />
<asparameter Name="Course" Type="String" />
<asparameter Name="Year" Type="String" />
<asparameter Name="Staff" Type="Boolean" />
<asparameter Name="BscInfo" Type="Boolean" />
<asparameter Name="MscInfo" Type="Boolean" />
<asparameter Name="Mats" Type="Boolean" />
<asparameter Name="Cracks" Type="Boolean" />
<asparameter Name="HdrBoard" Type="Boolean" />
<asparameter Name="HdrForum" Type="Boolean" />
<asparameter Name="InfoSec" Type="Boolean" />
<asparameter Name="Lab" Type="Boolean" />
<asparameter Name="Media" Type="Boolean" />
<asparameter Name="PhdInfo" Type="Boolean" />
<asparameter Name="PhdSuper" Type="Boolean" />
<asparameter Name="Research" Type="Boolean" />
<asparameter Name="Secs" Type="Boolean" />
<asparameter Name="Security" Type="Boolean" />
<asparameter Name="StartDate" Type="String" />
<asparameter Name="ProbationDate" Type="String" />
<asparameter Name="EndDate" Type="String" />
<asparameter Name="RetirementDate" Type="String" />
<asparameter Name="FundingBody" Type="String" />
<asparameter Name="ResearchGroup" Type="String" />
<asparameter Name="lineManager" Type="String" />
<asparameter Name="PayCategory" Type="String" />
<asparameter Name="Notes" Type="String" />
</UpdateParameters>
<InsertParameters>
<asparameter Name="ID" Direction="Output" Type="Int32" DefaultValue="0" />
<asparameter Name="ImageName" Type="String" DefaultValue="noimage.gif" />
<asparameter Name="Title" Type="String" DefaultValue="" />
<asparameter Name="ForeName" Type="String" DefaultValue="" />
<asparameter Name="SurName" Type="String" DefaultValue="" />
<asparameter Name="PhoneNo" Type="String" />
<asparameter Name="RoomNo" Type="String" />
<asparameter Name="EMail" Type="String" />
<asparameter Name="Role" Type="String" />
<asparameter Name="Course" Type="String" />
<asparameter Name="Year" Type="String" />
<asparameter Name="Staff" Type="Boolean" />
<asparameter Name="BscInfo" Type="Boolean" />
<asparameter Name="MscInfo" Type="Boolean" />
<asparameter Name="Mats" Type="Boolean" />
<asparameter Name="Cracks" Type="Boolean" />
<asparameter Name="HdrBoard" Type="Boolean" />
<asparameter Name="HdrForum" Type="Boolean" />
<asparameter Name="InfoSec" Type="Boolean" />
<asparameter Name="Lab" Type="Boolean" />
<asparameter Name="Media" Type="Boolean" />
<asparameter Name="PhdInfo" Type="Boolean" />
<asparameter Name="PhdSuper" Type="Boolean" />
<asparameter Name="Research" Type="Boolean" />
<asparameter Name="Secs" Type="Boolean" />
<asparameter Name="Security" Type="Boolean" />
<asparameter Name="StartDate" Type="String" />
<asparameter Name="ProbationDate" Type="String" />
<asparameter Name="EndDate" Type="String" />
<asparameter Name="RetirementDate" Type="String" />
<asparameter Name="FundingBody" Type="String" />
<asparameter Name="ResearchGroup" Type="String" />
<asparameter Name="lineManager" Type="String" />
<asparameter Name="PayCategory" Type="String" />
<asparameter Name="Notes" Type="String" />
</InsertParameters>
<DeleteParameters>
<asparameter Name="ImageName" Type="String" />
<asparameter Name="ID" Type="Int32" />
<asparameter Name="Title" Type="String" />
<asparameter Name="ForeName" Type="String" />
<asparameter Name="SurName" Type="String" />
<asparameter Name="PhoneNo" Type="String" />
<asparameter Name="RoomNo" Type="String" />
<asparameter Name="EMail" Type="String" />
<asparameter Name="Role" Type="String" />
<asparameter Name="Course" Type="String" />
<asparameter Name="Year" Type="String" />
<asparameter Name="Staff" Type="Boolean" />
<asparameter Name="BscInfo" Type="Boolean" />
<asparameter Name="MscInfo" Type="Boolean" />
<asparameter Name="Mats" Type="Boolean" />
<asparameter Name="Cracks" Type="Boolean" />
<asparameter Name="HdrBoard" Type="Boolean" />
<asparameter Name="HdrForum" Type="Boolean" />
<asparameter Name="InfoSec" Type="Boolean" />
<asparameter Name="Lab" Type="Boolean" />
<asparameter Name="Media" Type="Boolean" />
<asparameter Name="PhdInfo" Type="Boolean" />
<asparameter Name="PhdSuper" Type="Boolean" />
<asparameter Name="Research" Type="Boolean" />
<asparameter Name="Secs" Type="Boolean" />
<asparameter Name="Security" Type="Boolean" />
<asparameter Name="StartDate" Type="String" />
<asparameter Name="ProbationDate" Type="String" />
<asparameter Name="EndDate" Type="String" />
<asparameter Name="RetirementDate" Type="String" />
<asparameter Name="FundingBody" Type="String" />
<asparameter Name="ResearchGroup" Type="String" />
<asparameter Name="lineManager" Type="String" />
<asparameter Name="PayCategory" Type="String" />
<asparameter Name="Notes" Type="String" />
</DeleteParameters>
</asp:AccessDataSource>
Is there anything obvious I'm doing wrong with the AccessDataSource?
<asp:AccessDataSource id="DetailsSource" Runat="Server"
DataFile="C:\Program Files\Common Files\ODBC\Data Sources\user_info2.mdb"
SelectCommand="SELECT * FROM user_table WHERE ID = @ID"
InsertCommand="INSERT INTO user_table (ImageName, ID, Title, ForeName, SurName, PhoneNo, RoomNo, EMail,
Role, Course, Year, Staff, BscInfo, MscInfo, Mats, Cracks, HdrBoard, HdrForum, InfoSec, Lab,
Media, PhdInfo, PhdSuper, Research, Secs, Security, Teapot, UgBoard, UgVis, WWW,
StartDate, ProbabtionDate, EndDate, RetirementDate, FundingBody,
ResearchGroup, LineManager, PayCategory, Notes) VALUES (@ImageName, @ID, @Title, @ForeName, @SurName,
@PhoneNo, @RoomNo, @EMail, @Role, @Course, @Year, @Staff, @BscInfo, @MscInfo,
@Mats, @Cracks, @HdrBoard, @HdrForum, @InfoSec, @Lab, @Media,
@PhdInfo, @PhdSuper, @Research, @Secs, @Security, @Teapot, @UgBoard,
@UgVis, @WWW, @StartDate, @ProbabtionDate, @EndDate, @RetirementDate,
@FundingBody, @ResearchGroup, @LineManager, @PayCategory, @Notes); SELECT @ID = SCOPE_IDENTITY()"
UpdateCommand="UPDATE user_table SET ID=@ID, ImageName=@ImageName, Title=@Title, ForeName=@ForeName, SurName=@SurName,
PhoneNo=@PhoneNo, RoomNo=@RoomNo, Email=@Email, Role=@Role, Course=@Course, Year=@year, Staff=@Staff, BscInfo=@BscInfo,
MscInfo=@MscInfo, Mats=@Mats, Cracks=@Cracks, HdrBoard=@HdrBoard, HdrForum=@HdrForum, InfoSec=@InfoSec,
Lab=@Lab, Media=@Media, PhdInfo=@PhdInfo, PhdSuper=@PhdSuper, Research=@Research, Secs=@Secs,
Security=@Security, Teapot=@Teapot, UgBoard=@UgBoard, UgVis=@UgVis, WWW=@WWW,
StartDate=@StartDate, ProbabtionDate=@ProbabtionDate, EndDate=@EndDate, RetirementDate=@RetirementDate,
FundingBody=@FundingBody, ResearchGroup=@ResearchGroup, LineManager=@LineManager, PayCategory=@PayCategory, Notes=@Notes WHERE ID=@ID"
>
<SelectParameters>
<asp:ControlParameter ControlID="GridView" Name="ID" PropertyName="SelectedValue"/>
</SelectParameters>
<UpdateParameters>
<asparameter Name="ImageName" Type="String" />
<asparameter Name="ID" Type="Int32" />
<asparameter Name="Title" Type="String" />
<asparameter Name="ForeName" Type="String" />
<asparameter Name="SurName" Type="String" />
<asparameter Name="PhoneNo" Type="String" />
<asparameter Name="RoomNo" Type="String" />
<asparameter Name="EMail" Type="String" />
<asparameter Name="Role" Type="String" />
<asparameter Name="Course" Type="String" />
<asparameter Name="Year" Type="String" />
<asparameter Name="Staff" Type="Boolean" />
<asparameter Name="BscInfo" Type="Boolean" />
<asparameter Name="MscInfo" Type="Boolean" />
<asparameter Name="Mats" Type="Boolean" />
<asparameter Name="Cracks" Type="Boolean" />
<asparameter Name="HdrBoard" Type="Boolean" />
<asparameter Name="HdrForum" Type="Boolean" />
<asparameter Name="InfoSec" Type="Boolean" />
<asparameter Name="Lab" Type="Boolean" />
<asparameter Name="Media" Type="Boolean" />
<asparameter Name="PhdInfo" Type="Boolean" />
<asparameter Name="PhdSuper" Type="Boolean" />
<asparameter Name="Research" Type="Boolean" />
<asparameter Name="Secs" Type="Boolean" />
<asparameter Name="Security" Type="Boolean" />
<asparameter Name="StartDate" Type="String" />
<asparameter Name="ProbationDate" Type="String" />
<asparameter Name="EndDate" Type="String" />
<asparameter Name="RetirementDate" Type="String" />
<asparameter Name="FundingBody" Type="String" />
<asparameter Name="ResearchGroup" Type="String" />
<asparameter Name="lineManager" Type="String" />
<asparameter Name="PayCategory" Type="String" />
<asparameter Name="Notes" Type="String" />
</UpdateParameters>
<InsertParameters>
<asparameter Name="ID" Direction="Output" Type="Int32" DefaultValue="0" />
<asparameter Name="ImageName" Type="String" DefaultValue="noimage.gif" />
<asparameter Name="Title" Type="String" DefaultValue="" />
<asparameter Name="ForeName" Type="String" DefaultValue="" />
<asparameter Name="SurName" Type="String" DefaultValue="" />
<asparameter Name="PhoneNo" Type="String" />
<asparameter Name="RoomNo" Type="String" />
<asparameter Name="EMail" Type="String" />
<asparameter Name="Role" Type="String" />
<asparameter Name="Course" Type="String" />
<asparameter Name="Year" Type="String" />
<asparameter Name="Staff" Type="Boolean" />
<asparameter Name="BscInfo" Type="Boolean" />
<asparameter Name="MscInfo" Type="Boolean" />
<asparameter Name="Mats" Type="Boolean" />
<asparameter Name="Cracks" Type="Boolean" />
<asparameter Name="HdrBoard" Type="Boolean" />
<asparameter Name="HdrForum" Type="Boolean" />
<asparameter Name="InfoSec" Type="Boolean" />
<asparameter Name="Lab" Type="Boolean" />
<asparameter Name="Media" Type="Boolean" />
<asparameter Name="PhdInfo" Type="Boolean" />
<asparameter Name="PhdSuper" Type="Boolean" />
<asparameter Name="Research" Type="Boolean" />
<asparameter Name="Secs" Type="Boolean" />
<asparameter Name="Security" Type="Boolean" />
<asparameter Name="StartDate" Type="String" />
<asparameter Name="ProbationDate" Type="String" />
<asparameter Name="EndDate" Type="String" />
<asparameter Name="RetirementDate" Type="String" />
<asparameter Name="FundingBody" Type="String" />
<asparameter Name="ResearchGroup" Type="String" />
<asparameter Name="lineManager" Type="String" />
<asparameter Name="PayCategory" Type="String" />
<asparameter Name="Notes" Type="String" />
</InsertParameters>
<DeleteParameters>
<asparameter Name="ImageName" Type="String" />
<asparameter Name="ID" Type="Int32" />
<asparameter Name="Title" Type="String" />
<asparameter Name="ForeName" Type="String" />
<asparameter Name="SurName" Type="String" />
<asparameter Name="PhoneNo" Type="String" />
<asparameter Name="RoomNo" Type="String" />
<asparameter Name="EMail" Type="String" />
<asparameter Name="Role" Type="String" />
<asparameter Name="Course" Type="String" />
<asparameter Name="Year" Type="String" />
<asparameter Name="Staff" Type="Boolean" />
<asparameter Name="BscInfo" Type="Boolean" />
<asparameter Name="MscInfo" Type="Boolean" />
<asparameter Name="Mats" Type="Boolean" />
<asparameter Name="Cracks" Type="Boolean" />
<asparameter Name="HdrBoard" Type="Boolean" />
<asparameter Name="HdrForum" Type="Boolean" />
<asparameter Name="InfoSec" Type="Boolean" />
<asparameter Name="Lab" Type="Boolean" />
<asparameter Name="Media" Type="Boolean" />
<asparameter Name="PhdInfo" Type="Boolean" />
<asparameter Name="PhdSuper" Type="Boolean" />
<asparameter Name="Research" Type="Boolean" />
<asparameter Name="Secs" Type="Boolean" />
<asparameter Name="Security" Type="Boolean" />
<asparameter Name="StartDate" Type="String" />
<asparameter Name="ProbationDate" Type="String" />
<asparameter Name="EndDate" Type="String" />
<asparameter Name="RetirementDate" Type="String" />
<asparameter Name="FundingBody" Type="String" />
<asparameter Name="ResearchGroup" Type="String" />
<asparameter Name="lineManager" Type="String" />
<asparameter Name="PayCategory" Type="String" />
<asparameter Name="Notes" Type="String" />
</DeleteParameters>
</asp:AccessDataSource>