locked
SqlDataSource telling me i have no parameters, but they are there. RRS feed

  • Question

  • User119957594 posted

    I have an SqlDataSource that i'm using for a GridView. I have used the Delete functionality of the GridView the same as iv used it for the Update function on other ones, but for some reason this is telling me that my parameters have not been declared. Youll see in code that I put them there, I just copied them from the Update Parameters. I am stumped as to why sql is telling me I hav enot declared the variable when they are clearly there.

    Thanks

    <asp:SqlDataSource ID="SqlDataSourceVisitors" runat="server" ConnectionString="connstring" DeleteCommand="DELETE FROM [RegisteredVisitors] WHERE [PK] = @PK; UPDATE [dbo].[Users] SET [NumberOfVisitors] = (SELECT [NumberOfVisitors] FROM [dbo].[NumberOfVisitors] WHERE [ResidentName] = @ResidentName) -1 WHERE [ResidentName] = @ResidentName" InsertCommand="INSERT INTO [RegisteredVisitors] ([ResidentName], [ResidentAddress], [VisitorCode], [VisitorName]) VALUES (@ResidentName, @ResidentAddress, @VisitorCode, @VisitorName)" SelectCommand="SELECT * FROM [RegisteredVisitors] WHERE ([ResidentName] = @ResidentName)" UpdateCommand="UPDATE [RegisteredVisitors] SET [ResidentName] = @ResidentName, [ResidentAddress] = @ResidentAddress, [VisitorCode] = @VisitorCode, [VisitorName] = @VisitorName WHERE [PK] = @PK" ProviderName="System.Data.SqlClient">
        <DeleteParameters>
            <asp:Parameter Name="PK" Type="Int32" />
            <asp:Parameter Name="ResidentName" Type="String" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="ResidentName" Type="String" />
            <asp:Parameter Name="ResidentAddress" Type="String" />
            <asp:Parameter Name="VisitorCode" Type="Int32" />
            <asp:Parameter Name="VisitorName" Type="String" />
        </InsertParameters>
        <SelectParameters>
            <asp:ControlParameter ControlID="txtboxSelectedResident" Name="ResidentName" PropertyName="Text" Type="String" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="ResidentName" Type="String" />
            <asp:Parameter Name="ResidentAddress" Type="String" />
            <asp:Parameter Name="VisitorCode" Type="Int32" />
            <asp:Parameter Name="VisitorName" Type="String" />
            <asp:Parameter Name="PK" Type="Int32" />
        </UpdateParameters>
    </asp:SqlDataSource>
    Wednesday, November 16, 2016 10:43 PM

All replies

  • User-1716253493 posted

    Have you set gdridview datakeynames="pk"?

    Thursday, November 17, 2016 12:32 AM
  • User119957594 posted

    <asp:GridView ID="DataGridViewVisitors" runat="server" CellPadding="4" DataSourceID="SqlDataSourceVisitors" ForeColor="#333333" GridLines="None" DataKeyNames="PK">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
    <asp:CommandField ShowDeleteButton="True" />
    </Columns>
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#E9E7E2" />
    <SortedAscendingHeaderStyle BackColor="#506C8C" />
    <SortedDescendingCellStyle BackColor="#FFFDF8" />
    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
    </asp:GridView>

    I changed the delete statement in the data source to just do a default delete statement, I don't get a parameter error, but the whole table dissapears and never changes the row in sql

    Thursday, November 17, 2016 1:28 AM
  • User-1716253493 posted

    If you want to delete / update data based unique pk values

    Use datakeynames="pk", each row will have deferent datakey value (the data from your select query)

    then add "pk" in delete / update parameter, it's bound to the datakeynames. The value come from datakey value where delete button clicked.

    Then used in delete / update command as criteria.

    Usualy we use ID (Autogenerate number) as the key.

    Thursday, November 17, 2016 1:49 AM