PDA

View Full Version : SqlDataSource UpdateCommand not working…



danferd
Sep 04, 2010, 4:07 AM
I have a GridPanel which allows user to inline edit the Candidate Code only and databind to a sqldatasource. In the SelectCommand, query will inner join a Table1. What i was trying to say is i wish to update all the records with Candidate Code, '5374889' to the edited Candidate Code. How can i achieve it in the sqldatasource UpdateCommand?

Table1 Structure


[ID(Primary Key)] [Candidate Code] [Desc1] [Desc2] [Sessions] [Dun]

1 870630055018 Name(ENG) John 12 080
2 870630055018 Name(CHN) 约翰 12 080
3 870630055018 Education University 1 12 080
4 870630055018 Education University 2 12 080
5 5374889 Name(ENG) Lincoln 11 080
6 5374889 Name(CHN) 林肯 11 080
7 5374889 Education College 1 11 080
8 5374889 Education College 2 11 080

GridPanel

[Candidate Code] [Name(ENG)] [Name(CHN)]
5374889 Lincoln 林肯

Code

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SPR_SQLServer %>"
DeleteCommand="DELETE FROM Table1 WHERE Candidate_Code = @Candidate_Code"
SelectCommand="SELECT DISTINCT pr1.Candidate_Code, pr1.Desc2 AS Name_EN, pr2.Desc2 AS Name_CN FROM Table1 pr1
INNER JOIN Table1 pr2
ON pr1.Candidate_Code = pr2.Candidate_Code
AND pr1.Stat1=pr2.Stat1 AND pr2.Desc1 = 'NAME (CHINESE)'
WHERE pr1.SESSIONS = @Sessions and pr1.DUN = @Dun AND pr1.Stat1 = 'E' AND pr1.Desc1 = 'NAME (ENG)'"
UpdateCommand="UPDATE Table1 SET Candidate_Code = @Candidate_Code WHERE Candidate_Code = @Candidate_Code2">
<SelectParameters>
<asp:ControlParameter ControlID="ddlSession" Name="Sessions"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="ddlParliament" Name="Dun"
PropertyName="SelectedValue" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="Candidate_Code" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Candidate_Code" />
<asp:Parameter Name="Candidate_Code2" />
</UpdateParameters>
</asp:SqlDataSource>