I have a data source which I created a custom statement for which I need to delete from. I created the folowing selecct statement
SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @.UserName)
I wrote the following DELETE statement
DELETE FROM SrcComputer WHERE (CompId = @.CompId)
I have tested it in SQL server management and it does what I want it to do, but I don't know how to use it in my code. I put it inside of the DELETE tab inside of my custome statement and added a delete button inside of the gridview which utializes my data source, but it's not working. I don't know how to set the CompId variable? I want the statement to delete the row that the user clicks on. Can anyone give me some advice?
You can set the delete parameter like in this sample:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SNo" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="SNo" HeaderText="SNo" ReadOnly="True" SortExpression="SNo" />
<asp:BoundField DataField="CQNo" HeaderText="CQNo" SortExpression="CQNo" />
<asp:BoundField DataField="WorkDate" HeaderText="WorkDate" SortExpression="WorkDate" />
<asp:BoundField DataField="Analysis" HeaderText="Analysis" SortExpression="Analysis" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MSDN_forumConnectionString %>"
DeleteCommand="DELETE FROM [effort] WHERE [SNo] = @.SNo"
SelectCommand="SELECT [SNo], [CQNo], [WorkDate], [Analysis] FROM [effort]"
UpdateCommand="UPDATE [effort] SET [CQNo] = @.CQNo, [WorkDate] = @.WorkDate, [Analysis] = @.Analysis WHERE [SNo] = @.SNo">
<DeleteParameters>
<asp:Parameter Name="SNo" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CQNo" Type="String" />
<asp:Parameter Name="WorkDate" Type="DateTime" />
<asp:Parameter Name="Analysis" Type="Decimal" />
<asp:Parameter Name="SNo" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
ok I believe that I have done what you said. Most of it was already there the Parameter CompId's type wasn't there but the rest was there here is what I have.
<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:srcConnectionString1 %>"SelectCommand="SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @.UserName)"OnSelecting="SqlDataSource2_Selecting"DeleteCommand="DELETE FROM SrcComputer WHERE [CompId] = @.CompId"><SelectParameters><asp:SessionParameterName="UserName"SessionField="UserName"Type="String"/></SelectParameters><DeleteParameters><asp:ParameterName="CompId"Type="int64"/></DeleteParameters></asp:SqlDataSource>My question is where do assign a value for CompId. When I click the delete link it does nothing. Just seems to refresh the page and nothing changes.
|||You need to set DataKeyNames="CompId" from your gridview .|||limno,
Thank you. That is exactly what I needed. I have one other question for you. If I am already using the DataKeyNames for a different variable is it possible to have 2? It just so happened in this case I could use the same variable for both selecting and deleting.
Thanks, Matt
|||Matt,
You can have more than one as your DataKeyNames, just separate them with comma like DataKeyNames="id1, id2".
