Intersoft Solutions - Community - WebGrid Enterprise - Webgrid editing with SQLDataSource

Create New

New Discussion
New Question
New Product Feedback

Post Reply

Jason WrightMember

Posted: May 30, 2010 8:06 PM

I'm using Webgrid with an SQLdatasource that allows editing. For datasources that have a single column as the primary key this appears to be working fine. However if the Sqldatasource has multiple columns in the primary key, I get the message "The datarow you are trying to update no longer exists in the datasource....".

I've attached a file showing the table layout in SQL Server Management Studio, and the code showing the webgrid and sqldatasource. Using the old gridview tag in Visual Studio I used to be able to specify more then one field in the data key (and the editing would work fine), however I'm not sure how to do this in Webgrid.

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="DropdownItems.aspx.vb"
    Inherits="WebApplication1.DropdownItems" %>

<%@ Register Assembly="ISNet.WebUI.ISDataSource, Version=1.0.1500.1, Culture=neutral, PublicKeyToken=c4184ef0d326354b"
    Namespace="ISNet.WebUI.DataSource" TagPrefix="ISDataSource" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<%@ Register Assembly="ISNet.WebUI.WebGrid" Namespace="ISNet.WebUI.WebGrid" TagPrefix="ISWebGrid" %>
<%@ Register Assembly="ISNet.WebUI.WebDesktop" Namespace="ISNet.WebUI.WebDesktop"
    TagPrefix="ISWebDesktop" %>
<html>
<head id="Head1" runat="server">
    <title>MVMS Administration - Drop Down Items</title>
    <link rel="stylesheet" type="text/css" href="../GlobalMVMS.css" />
</head>
<body id="bdyDropDownItem" runat="server">
    <form id="frmDropDownItem" runat="server">
    <div class="" style="margin: 10px;">
        <asp:Label ID="Label1" runat="server" Text="Select Drop Down Item"></asp:Label>
        <asp:SqlDataSource ID="sqlDropDown" runat="server" ConnectionString="<%$ ConnectionStrings:MVMS %>"
            SelectCommand="SELECT * FROM [ValidValues] WHERE ([TableType] = @TableType)">
            <SelectParameters>
                <asp:Parameter DefaultValue="MASTER" Name="TableType" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:ToolkitScriptManager ID="ScriptManager1" runat="server">
        </asp:ToolkitScriptManager>
        <asp:DropDownList ID="lstValidValue" CssClass="DropDownList" AppendDataBoundItems="true"
            runat="server" AutoPostBack="True" DataSourceID="sqlDropDown" DataTextField="Description"
            DataValueField="TableValue">
        </asp:DropDownList>
        <br />
        <br />
        <asp:CheckBox ID="chFilter" AutoPostBack="true" CssClass="CheckBox" Text="Filter List"
            runat="server" />
        <ISWebGrid:WebGrid ID="grdValidValue" runat="server" Height="245px" UseDefaultStyle="True"
            Width="500px" DataSourceID="sqlGridView" DefaultStyleMode="Win7">
            <LayoutSettings AllowAddNew="No" AllowEdit="Yes" AllowExport="Yes" AllowFilter="Yes"
                AllowSelectColumns="Yes" AllowSorting="Yes" CellClickAction="CellSelect" EditOnClick="True"
                RowChangedAction="FullPagePostback">
            </LayoutSettings>
            <RootTable DataKeyField="TableType">
                <Columns>
                    <ISWebGrid:WebGridColumn Visible="true" AllowMultiLine="True" Caption="TableType"
                        DataMember="TableType" Name="TableType" Width="100px">
                        <ValueList DataSourceID="sqlGridView" DataTextField="TableType" DataValueField="TableType">
                        </ValueList>
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn AllowMultiLine="True" Caption="TableValue" DataMember="TableValue"
                        Name="TableValue" Width="100px">
                        <ValueList DataSourceID="sqlGridView" DataTextField="TableValue" DataValueField="TableValue">
                        </ValueList>
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Description" DataMember="Description" Name="Description"
                        Width="100px">
                        <ValueList DataSourceID="sqlGridView" DataTextField="Description" DataValueField="Description">
                        </ValueList>
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="ExpiryDate" DataMember="ExpiryDate" DataType="System.Date"
                        EditType="CalendarCombo" DataFormatString="dd/MM/yyyy" Name="ExpiryDate" Width="100px">
                    </ISWebGrid:WebGridColumn>
                </Columns>
            </RootTable>
        </ISWebGrid:WebGrid>
        <asp:SqlDataSource ID="sqlGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MVMS %>"
            UpdateCommand="UPDATE [ValidValues] set [Description] = @Description, [ExpiryDate] = convert(datetime,@ExpiryDate,103) where [TableType] = @TableType and [TableValue] = @TableValue"
            SelectCommand="SELECT * FROM [ValidValues] WHERE ([TableType] = @TableType)">
            <SelectParameters>
                <asp:ControlParameter ControlID="lstValidValue" Name="TableType" PropertyName="SelectedValue" />
            </SelectParameters>
            <UpdateParameters>
                <asp:Parameter Name="TableValue" />
                <asp:Parameter Name="Description" />
                <asp:Parameter Name="ExpiryDate" />
                <asp:Parameter Name="TableType" />
            </UpdateParameters>
        </asp:SqlDataSource>
        <br />
        <br />
        <asp:Table ID="Table1" CellPadding="5" runat="server">
            <asp:TableRow>
                <asp:TableCell>
                    <asp:Label ID="lblTableValue" CssClass="label" runat="server" Text="Value"></asp:Label>
                </asp:TableCell>
                <asp:TableCell>
                    <asp:TextBox ID="txtTableValue" CssClass="textbox" runat="server">
                    </asp:TextBox>
                </asp:TableCell>
                <asp:TableCell></asp:TableCell>
                <asp:TableCell>
                    <asp:Label ID="lblDescription" CssClass="label" runat="server" Text="Description">
                    </asp:Label>
                </asp:TableCell>
                <asp:TableCell>
                    <asp:TextBox ID="txtDescription" CssClass="textbox" runat="server">
                    </asp:TextBox>
                </asp:TableCell>
                <asp:TableCell>
                    <asp:Label ID="lblExpiryDate" CssClass="label" runat="server" Text="Expiry Date">
                    </asp:Label>
                </asp:TableCell>
                <asp:TableCell>
                    <asp:TextBox ID="txtExpiryDate" CssClass="textbox" runat="server">
                    </asp:TextBox>
                    <asp:CalendarExtender ID="CalendarExtender1" TargetControlID="txtExpiryDate" Format="dd/MM/yyyy"
                        PopupButtonID="imgDatePicker" runat="server" />
                    <asp:ImageButton ID="imgDatePicker" runat="server" ImageUrl="~/Images/date_picker.gif" />
                </asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>
                </asp:TableCell><asp:TableCell></asp:TableCell>
                <asp:TableCell>
                    <asp:Button ID="btnAddEntry" runat="server" Text="Add Entry" />
                </asp:TableCell>
            </asp:TableRow>
        </asp:Table>
    </div>
    </form>
</body>
</html>

Thanks,

Jason.

1 attachment

Valid Values.docx

Quick Reply

Quote

All Replies

Yudi Member

Posted: May 30, 2010 11:48 PM in response to Jason Wright

WebGrid has supported table with multiple column primary key. When bind to data source that has multiple column primary key, please leave the ‘DataKeyField’ property of WebGrid blank. Just leave it blank and it will automatically set the primary keys.

I made a simple test by binding WebGrid to ‘OrderDetails’ table of Northwind.mdf file by using SQLDataSource. The test was made by edit values on multiple rows and found no error occurs.

Please let us know your response.

Quick Reply

Quote

Jason WrightMember

Posted: May 30, 2010 11:55 PM in response to Yudi

Thanks for your quick response.

I had already tried with a blank value in DataKeyField but still got the error. Perhaps the multiple keys is not the problem? I only thought that was the case as that is the only difference between the two forms that I have (the web form with the datasource with a single column parent key works).

Is there anything else I'm missing?

<ISWebGrid:WebGrid ID="grdValidValue" runat="server" Height="245px" UseDefaultStyle="True" Width="500px" DataSourceID="sqlGridView" DefaultStyleMode="Win7"> <LayoutSettings AllowAddNew="No" AllowEdit="Yes" AllowExport="Yes" AllowFilter="Yes" AllowSelectColumns="Yes" AllowSorting="Yes" CellClickAction="CellSelect" EditOnClick="True" RowChangedAction="FullPagePostback"> </LayoutSettings> <RootTable > <Columns> <ISWebGrid:WebGridColumn Visible="true" AllowMultiLine="True" Caption="TableType" DataMember="TableType" Name="TableType" Width="100px"> <ValueList DataSourceID="sqlGridView" DataTextField="TableType" DataValueField="TableType"> </ValueList> </ISWebGrid:WebGridColumn> <ISWebGrid:WebGridColumn AllowMultiLine="True" Caption="TableValue" DataMember="TableValue" Name="TableValue" Width="100px"> <ValueList DataSourceID="sqlGridView" DataTextField="TableValue" DataValueField="TableValue"> </ValueList> </ISWebGrid:WebGridColumn> <ISWebGrid:WebGridColumn Caption="Description" DataMember="Description" Name="Description" Width="100px"> <ValueList DataSourceID="sqlGridView" DataTextField="Description" DataValueField="Description"> </ValueList> </ISWebGrid:WebGridColumn> <ISWebGrid:WebGridColumn Caption="ExpiryDate" DataMember="ExpiryDate" DataType="System.Date" EditType="CalendarCombo" DataFormatString="dd/MM/yyyy" Name="ExpiryDate" Width="100px"> </ISWebGrid:WebGridColumn> </Columns> </RootTable> </ISWebGrid:WebGrid>

Quick Reply

Quote

Handy SuryaSupport

Posted: May 31, 2010 10:26 PM in response to Jason Wright

Hi Jason,

I also could not replicate your issue. Attached files are my sample with dummy database which used Multiple Primary Keys. When handling Insert,Update and delete, in SqlDataSource control you will need to check all the options. Sometimes, without those, the process delete, insert and update could be wrong because you are using Multiple Primary Keys.

Regards,
Handy

2 attachments

MultipleKeys.zip

6-1-2010 9-07-22 AM.png

Quick Reply

Quote

Jason WrightMember

Posted: June 3, 2010 12:00 AM in response to Handy Surya

Thanks again for your responses. I've narrowed down what the problem is. I've managed to get a simplistic version of the multi key update working - however I believe now this is not the original issue as I originally thought it was. The problem seems to be when I'm using a control (such as a drop down list) as a control parameter in the select parameters of the data source.

So my table is really simple, made up of TableType, TableValue, Description and ExpiryDate (with TableType and TableValue being the primary key fields).

I have a drop down list showing the different table types on the table, and when the user selects the TableType (like State, Location, Workflow type etc), the ISWebGrid shows values on the table based on the entry of the drop down list control.

The sql datasource is configured to use the dropdown list control as a select parameter. When I don't have the select parameter in the datasource (and I hard code the value of the Table Type), everything works fine - updating/editing is perfect.

When I put the control parameter in it all falls over. Below is my datasource.

<asp:SqlDataSource ID="sqlGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MVMS %>" SelectCommand="SELECT * FROM [ValidValues] WHERE ([TableType] = @TableType)" UpdateCommand="UPDATE [ValidValues] SET [Description] = @Description, [ExpiryDate] = @ExpiryDate WHERE [TableType] = @original_TableType AND [TableValue] = @original_TableValue AND [Description] = @original_Description AND [ExpiryDate] = @original_ExpiryDate" ConflictDetection="CompareAllValues" DeleteCommand="DELETE FROM [ValidValues] WHERE [TableType] = @original_TableType AND [TableValue] = @original_TableValue AND [Description] = @original_Description AND [ExpiryDate] = @original_ExpiryDate" InsertCommand="INSERT INTO [ValidValues] ([TableType], [TableValue], [Description], [ExpiryDate]) VALUES (@TableType, @TableValue, @Description, @ExpiryDate)" OldValuesParameterFormatString="original_{0}"> <SelectParameters> <asp:ControlParameter ControlID="lstValidValue" Name="TableType" PropertyName="SelectedValue" Type="String" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_TableType" Type="String" /> <asp:Parameter Name="original_TableValue" Type="String" /> <asp:Parameter Name="original_Description" Type="String" /> <asp:Parameter DbType="Date" Name="original_ExpiryDate" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Description" /> <asp:Parameter Name="ExpiryDate" /> <asp:Parameter Name="TableType" /> <asp:Parameter Name="TableValue" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="TableType" Type="String" /> <asp:Parameter Name="TableValue" Type="String" /> <asp:Parameter Name="Description" Type="String" /> <asp:Parameter DbType="Date" Name="ExpiryDate" /> </InsertParameters> </asp:SqlDataSource>

Love to know if you can help me out.

Thanks,

Jason.

Quick Reply

Quote

Handy SuryaSupport

Posted: June 3, 2010 3:23 AM in response to Jason Wright

Hi Jason,

I think that I know what caused the issue. Please look into my previous sample and try to configure by following the steps below.

1. Create DropdownList as a parameter control and use this as select parameter just like you did.

2. Now, after it works well in selecting, run the page and try to change dropdownlist value into "2".

WebGird would surely select the correct one.

3. Try to delete a record.

It will delete the wrong field and back to select record 1.
If you noticed, It will delete the record which has ID = 1, Not 2.

When I debug at Page_Load event, dropdownlist value is suddenly changed into 1 which will cause the issue.

To resolve the issue, you will need to use PostInputControls as true.

Incorrect passing parameter in server is the one cause this issue.

Regards,
Handy

1 attachment

MultipleKeys1.zip

Quick Reply

Quote

Jason WrightMember

Posted: June 3, 2010 3:30 AM in response to Handy Surya

Ah ha!!! That did the job. Didn't know about

That fixed it beautifully for me! Thanks for your help!!

Quick Reply

Quote