UPDATE problems with AccessDataSource (1 Viewer)

A

aikighost

Newbie programmer
Local time
Yesterday, 21:42
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>
 
Kodo

Kodo

"The Shoe"
Local time
Today, 00:42
Joined
Jan 20, 2004
Messages
707
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)
Top Bottom