UPDATE problems with AccessDataSource

A

aikighost

Newbie programmer
Local time
, 18:07
Joined
Sep 14, 2007
Messages
1
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>
<asp:Parameter Name="ImageName" Type="String" />
<asp:Parameter Name="ID" Type="Int32" />
<asp:Parameter Name="Title" Type="String" />
<asp:Parameter Name="ForeName" Type="String" />
<asp:Parameter Name="SurName" Type="String" />
<asp:Parameter Name="PhoneNo" Type="String" />
<asp:Parameter Name="RoomNo" Type="String" />
<asp:Parameter Name="EMail" Type="String" />
<asp:Parameter Name="Role" Type="String" />
<asp:Parameter Name="Course" Type="String" />
<asp:Parameter Name="Year" Type="String" />
<asp:Parameter Name="Staff" Type="Boolean" />
<asp:Parameter Name="BscInfo" Type="Boolean" />
<asp:Parameter Name="MscInfo" Type="Boolean" />
<asp:Parameter Name="Mats" Type="Boolean" />
<asp:Parameter Name="Cracks" Type="Boolean" />
<asp:Parameter Name="HdrBoard" Type="Boolean" />
<asp:Parameter Name="HdrForum" Type="Boolean" />
<asp:Parameter Name="InfoSec" Type="Boolean" />
<asp:Parameter Name="Lab" Type="Boolean" />
<asp:Parameter Name="Media" Type="Boolean" />
<asp:Parameter Name="PhdInfo" Type="Boolean" />
<asp:Parameter Name="PhdSuper" Type="Boolean" />
<asp:Parameter Name="Research" Type="Boolean" />
<asp:Parameter Name="Secs" Type="Boolean" />
<asp:Parameter Name="Security" Type="Boolean" />
<asp:Parameter Name="StartDate" Type="String" />
<asp:Parameter Name="ProbationDate" Type="String" />
<asp:Parameter Name="EndDate" Type="String" />
<asp:Parameter Name="RetirementDate" Type="String" />
<asp:Parameter Name="FundingBody" Type="String" />
<asp:Parameter Name="ResearchGroup" Type="String" />
<asp:Parameter Name="lineManager" Type="String" />
<asp:Parameter Name="PayCategory" Type="String" />
<asp:Parameter Name="Notes" Type="String" />
</UpdateParameters>

<InsertParameters>
<asp:Parameter Name="ID" Direction="Output" Type="Int32" DefaultValue="0" />
<asp:Parameter Name="ImageName" Type="String" DefaultValue="noimage.gif" />
<asp:Parameter Name="Title" Type="String" DefaultValue="" />
<asp:Parameter Name="ForeName" Type="String" DefaultValue="" />
<asp:Parameter Name="SurName" Type="String" DefaultValue="" />
<asp:Parameter Name="PhoneNo" Type="String" />
<asp:Parameter Name="RoomNo" Type="String" />
<asp:Parameter Name="EMail" Type="String" />
<asp:Parameter Name="Role" Type="String" />
<asp:Parameter Name="Course" Type="String" />
<asp:Parameter Name="Year" Type="String" />
<asp:Parameter Name="Staff" Type="Boolean" />
<asp:Parameter Name="BscInfo" Type="Boolean" />
<asp:Parameter Name="MscInfo" Type="Boolean" />
<asp:Parameter Name="Mats" Type="Boolean" />
<asp:Parameter Name="Cracks" Type="Boolean" />
<asp:Parameter Name="HdrBoard" Type="Boolean" />
<asp:Parameter Name="HdrForum" Type="Boolean" />
<asp:Parameter Name="InfoSec" Type="Boolean" />
<asp:Parameter Name="Lab" Type="Boolean" />
<asp:Parameter Name="Media" Type="Boolean" />
<asp:Parameter Name="PhdInfo" Type="Boolean" />
<asp:Parameter Name="PhdSuper" Type="Boolean" />
<asp:Parameter Name="Research" Type="Boolean" />
<asp:Parameter Name="Secs" Type="Boolean" />
<asp:Parameter Name="Security" Type="Boolean" />
<asp:Parameter Name="StartDate" Type="String" />
<asp:Parameter Name="ProbationDate" Type="String" />
<asp:Parameter Name="EndDate" Type="String" />
<asp:Parameter Name="RetirementDate" Type="String" />
<asp:Parameter Name="FundingBody" Type="String" />
<asp:Parameter Name="ResearchGroup" Type="String" />
<asp:Parameter Name="lineManager" Type="String" />
<asp:Parameter Name="PayCategory" Type="String" />
<asp:Parameter Name="Notes" Type="String" />
</InsertParameters>

<DeleteParameters>
<asp:Parameter Name="ImageName" Type="String" />
<asp:Parameter Name="ID" Type="Int32" />
<asp:Parameter Name="Title" Type="String" />
<asp:Parameter Name="ForeName" Type="String" />
<asp:Parameter Name="SurName" Type="String" />
<asp:Parameter Name="PhoneNo" Type="String" />
<asp:Parameter Name="RoomNo" Type="String" />
<asp:Parameter Name="EMail" Type="String" />
<asp:Parameter Name="Role" Type="String" />
<asp:Parameter Name="Course" Type="String" />
<asp:Parameter Name="Year" Type="String" />
<asp:Parameter Name="Staff" Type="Boolean" />
<asp:Parameter Name="BscInfo" Type="Boolean" />
<asp:Parameter Name="MscInfo" Type="Boolean" />
<asp:Parameter Name="Mats" Type="Boolean" />
<asp:Parameter Name="Cracks" Type="Boolean" />
<asp:Parameter Name="HdrBoard" Type="Boolean" />
<asp:Parameter Name="HdrForum" Type="Boolean" />
<asp:Parameter Name="InfoSec" Type="Boolean" />
<asp:Parameter Name="Lab" Type="Boolean" />
<asp:Parameter Name="Media" Type="Boolean" />
<asp:Parameter Name="PhdInfo" Type="Boolean" />
<asp:Parameter Name="PhdSuper" Type="Boolean" />
<asp:Parameter Name="Research" Type="Boolean" />
<asp:Parameter Name="Secs" Type="Boolean" />
<asp:Parameter Name="Security" Type="Boolean" />
<asp:Parameter Name="StartDate" Type="String" />
<asp:Parameter Name="ProbationDate" Type="String" />
<asp:Parameter Name="EndDate" Type="String" />
<asp:Parameter Name="RetirementDate" Type="String" />
<asp:Parameter Name="FundingBody" Type="String" />
<asp:Parameter Name="ResearchGroup" Type="String" />
<asp:Parameter Name="lineManager" Type="String" />
<asp:Parameter Name="PayCategory" Type="String" />
<asp:Parameter Name="Notes" Type="String" />
</DeleteParameters>

</asp:AccessDataSource>
 
You can't update/insert a primary key (unique) value. Remove those parameters from your query.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom