Problem updating simple Query using DetailView Control 1

[Steve101]

I have a DetailView control with Edit enabled. the SelectQuery for the SqlDataSource is built dynamically and looks something like this:

[tt]
sq = "SELECT I.NameOfInsured, IP.PolicyNumber, IP.PolicyCurrency, " +
" IPT.TurnoverFrom, IPT.TurnoverTo, IPT.InsuredPolicyTurnoverId " +
"FROM dbo.tblInsured I " +
"INNER JOIN dbo.tblInsuredPolicy IP " +
"ON I.InsuredId = IP.InsuredId " +
"INNER JOIN dbo.tblInsuredPolicyTurnover IPT " +
"ON IP.InsuredPolicyId = IPT.InsuredPolicyId " +
"WHERE InsuredPolicyTurnoverId = " + txtInsuredPolicyTurnoverId;
SqlDataSource4.SelectCommand = sq;
[/tt]

The details of the above are probably not important; suffice to say, its a multi table construct because I want to include a number of read only lookup fields in the generated form.

Associated with this, is an UpdateQuery which looks something like this, with the parameters appropriately defined:

[tt]
UPDATE tblInsuredPolicyTurnover
SET TurnoverFrom = @TurnoverFrom,
TurnoverTo = @TurnoverTo
WHERE (InsuredPolicyTurnoverId = @InsuredPolicyTurnoverId)
[/tt]

Heres the problem. When I attempt to update the DetailView form by changing one of the displayed fields, as soon as I click on the DetailView's Update link, the changes to the DetailView revert to the original record values; ie. the update does not occur.

However, if I hardcode the UpdateQuery's WHERE clause parameter to say the value of 1, then the update works correctly ie.

[tt]
UPDATE tblInsuredPolicyTurnover
SET TurnoverFrom = @TurnoverFrom,
TurnoverTo = @TurnoverTo
WHERE (InsuredPolicyTurnoverId = 1)
[/tt]

correctly updating the first record;

Could anyone please advise me on how to correctly define / populate the @InsuredPolicyTurnoverId parameter correctly so that the above works.

Please note that I am keen to retain using the multi table SELECT clause as this makes it simple to display the lookup fields. I am also keen on resolving this situation without having to define SQL Views, Stored procedures etc.

Happy to supply additional information on request, but hopefully there's enough here to solve.

TIA,

Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[ca8msm]

Can you paste the HTML view of the DetailsView and SqlDataSource please?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Steve101]

Here they are as requested. Sorry, not very pretty, but hopefully contains the info you need to troubleshoot:

[tt]
<aspetailsView ID="dvPolicyTurnoverHeader" runat="server" AutoGenerateRows="False" CellPadding="4"
DataSourceID="SqlDataSource4" ForeColor="#333333" GridLines="None" Height="50px" Width="281px" DefaultMode="Edit" AutoGenerateEditButton="True" >
<CommandRowStyle BackColor="#D1DDF1" Font-Bold="True" />
<AlternatingRowStyle BackColor="White" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<FieldHeaderStyle BackColor="#DEE8F5" Font-Bold="True" />
<EditRowStyle BackColor="#2461BF" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<Fields>
<asp:BoundField DataField="InsuredPolicyTurnoverId" HeaderText="InsuredPolicyTurnoverId" InsertVisible="False" ReadOnly="True" SortExpression="InsuredPolicyTurnoverId" />
<asp:BoundField DataField="NameOfInsured" HeaderText="NameOfInsured" SortExpression="NameOfInsured" />
<asp:BoundField DataField="PolicyNumber" HeaderText="PolicyNumber" SortExpression="PolicyNumber" />
<asp:BoundField DataField="PolicyCurrency" HeaderText="PolicyCurrency" SortExpression="PolicyCurrency" />
<asp:BoundField DataField="TurnoverFrom" HeaderText="TurnoverFrom" SortExpression="TurnoverFrom" />
<asp:BoundField DataField="TurnoverTo" HeaderText="TurnoverTo" SortExpression="TurnoverTo" />
</Fields>
</aspetailsView>

<asp:SqlDataSource ID="SqlDataSource4" runat="server"
ConnectionString="<%$ ConnectionStrings:ChiefTradeCreditInsuranceConnectionString1 %>"
SelectCommand="SELECT I.NameOfInsured, IP.PolicyNumber, IP.PolicyCurrency, IPT.TurnoverFrom, IPT.TurnoverTo, IPT.InsuredPolicyTurnoverId
FROM dbo.tblInsured I
INNER JOIN dbo.tblInsuredPolicy IP
ON I.InsuredId = IP.InsuredId
INNER JOIN dbo.tblInsuredPolicyTurnover IPT
ON IP.InsuredPolicyId = IPT.InsuredPolicyId "
UpdateCommand="UPDATE tblInsuredPolicyTurnover
SET TurnoverFrom = @TurnoverFrom,
TurnoverTo = @TurnoverTo
WHERE (InsuredPolicyTurnoverId = @InsuredPolicyTurnoverId)">
<UpdateParameters>
<asp:FormParameter FormField="TurnoverFrom" Name="TurnoverFrom" Type="DateTime" />
<asp:FormParameter FormField="TurnoverTo " Name="TurnoverTo" Type="DateTime" />
<asp:FormParameter FormField="InsuredPolicyTurnoverId" Name="InsuredPolicyTurnoverId"
Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>

[/tt]

Many thanks,

Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[ca8msm]

OK, I haven't looked into this in too much detail, but it looks as though you are missing the DataKeyNames attribute for the DetailsView. Set this attribute to the primary key ID for the relevant table and see if that makes a difference.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Steve101]

ca,
Something else I ommitted to say which may be relavent. I do populate the SelectSQL programmatically to set the SelectSQL. This is done via the page OnLoad event to return an empty datasource, and also via a button.

Not pretty, and I dont think its related to the problem, but it might be. Here's the buttons code:

[tt]
protected void btnSelect3_Click(object sender, EventArgs e)
{
string txtInsuredPolicyTurnoverId = this.lstTurnoverRange.Text;
if (txtInsuredPolicyTurnoverId == "")
txtInsuredPolicyTurnoverId = "0";
string sq;
sq = "SELECT I.NameOfInsured, IP.PolicyNumber, IP.PolicyCurrency, " +
" IPT.TurnoverFrom, IPT.TurnoverTo, IPT.InsuredPolicyTurnoverId " +
"FROM dbo.tblInsured I " +
"INNER JOIN dbo.tblInsuredPolicy IP " +
"ON I.InsuredId = IP.InsuredId " +
"INNER JOIN dbo.tblInsuredPolicyTurnover IPT " +
"ON IP.InsuredPolicyId = IPT.InsuredPolicyId " +
"WHERE InsuredPolicyTurnoverId = " + txtInsuredPolicyTurnoverId;
SqlDataSource4.SelectCommand = sq;

dvPolicyTurnoverHeader.DataBind();
[/tt]

Cheers,

Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

ca,

You're a legend! That seems to have done the trick. At least the correct record is updating now, though after the update, the selected record is returning to the first record in the set. Thats my next minor challenge, possibly caused by some of the OnLoad code.

Many thanks for your great help; I wouldnt have figured that one out in a hurry,




Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)