SQLDataSource not saving data to database, reads fine

[Craig Greenway]

Hi I am having trouble getting the ASP.Net grid to save to the database using the SQLDataSource, the SELECT is OK but the INSERT, DELETE and UPDATE don't update the database (the grid looks like you have changed the data but the database does not reflect changes).  Can you spot where I have gone wrong please?

<ej:Grid ID="Grid1" runat="server" DataSourceCachingMode="None" DataSourceID="SqlDataSource1" MinWidth="0" AllowFiltering="True" AllowSorting="True" >
    <EditSettings AllowEditing="True" AllowAdding="True" AllowDeleting="True"></EditSettings>
    <ToolbarSettings ShowToolbar="True" ToolbarItems="add,edit,delete,update,cancel"></ToolbarSettings>
    <Columns>
        <ej:Column AllowEditing="False" DataType="number" Field="ProductID" IsIdentity="True" Visible="False">
        </ej:Column>
        <ej:Column DataType="string" Field="ProductName">
        </ej:Column>
        <ej:Column DataType="string" Field="ProductDescription">
        </ej:Column>
        <ej:Column DataType="string" Field="ProductImageName">
        </ej:Column>
        <ej:Column DataType="string" Field="ProductCategory">
        </ej:Column>
        <ej:Column DataType="date" Field="ProductDateAdded">
        </ej:Column>
        <ej:Column DataType="number" Field="ProductPrice">
        </ej:Column>
    </Columns>

    <PageSettings Template=""></PageSettings>

    <RowDropSettings DropTargetID="" DropMapper=""></RowDropSettings>

    <ScrollSettings EnableTouchScroll="False"></ScrollSettings>
</ej:Grid>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SQLConnectionString %>"
    SelectCommand="SELECT ProductID, ProductName, ProductDescription, ProductImageName, ProductCategory, ProductDateAdded, ProductPrice FROM tblProducts"
    UpdateCommand="UPDATE tblProducts SET ProductName = @ProductName, ProductDescription = @ProductDescription, ProductImageName = @ProductImageName, ProductCategory = @ProductCategory, ProductDateAdded = @ProductDateAdded, ProductPrice = @ProductPrice WHERE (ProductID = @ProductID)"
    DeleteCommand="DELETE FROM tblProducts WHERE (ProductID = @ProductID)"
    InsertCommand="INSERT INTO tblProducts SELECT @ProductName AS Expr1, @ProductDescription AS Expr2, @ProductImageName AS Expr3, @ProductCategory AS Expr4, @ProductDateAdded AS Expr5, @ProductPrice AS Expr6">
    <DeleteParameters>
        <asp:Parameter Name="ProductID" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="ProductName" />
        <asp:Parameter Name="ProductDescription" />
        <asp:Parameter Name="ProductImageName" />
        <asp:Parameter Name="ProductCategory" />
        <asp:Parameter Name="ProductDateAdded" />
        <asp:Parameter Name="ProductPrice" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="ProductName" />
        <asp:Parameter Name="ProductDescription" />
        <asp:Parameter Name="ProductImageName" />
        <asp:Parameter Name="ProductCategory" />
        <asp:Parameter Name="ProductDateAdded" />
        <asp:Parameter Name="ProductPrice" />
        <asp:Parameter Name="ProductID" />
    </UpdateParameters>
</asp:SqlDataSource>

[Mani Sankar Durai]

Hi Craig, 

We have analyzed you query and we are not able to reproduce the issue. We found that the InsertCommand syntax mentioned in the SqlDataSource is wrong. So when inserting the data it doesn’t take place and updated the inserted data in the database. 

For your convenience please refer the below code example for syntax of InsertCommand, 

<asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 

            SelectCommand="SELECT * FROM [Orders]" OnInit="Page_Load" OnDeleting="SqlData_Deleting" 

        

        InsertCommand="INSERT INTO [Orders] ([Freight], [EmployeeID], [ShipCity]) 

         VALUES (@Freight, @EmployeeID, @ShipCity)" 

        DeleteCommand=  

        "DELETE FROM [Orders] WHERE [OrderID] = @OrderID" 

         

 

The data will not update in the database only when the mentioned fields in the SqlDataSource commands and in the grid columns are not same. So please ensure once whether the fields given are same in the SqlDataSource. 

We have also prepared a sample that can be downloaded from the below link, 

Link: http://www.syncfusion.com/downloads/support/forum/125149/ze/Sample-2074789955 

If you still face the issue please get back to us with the following details, 

1.       Full video of the issue you have faced. 

2.       Send the full dataSource of tblProducts. 

3.       Reproduce the issue in the above given sample, if possible 

 

 

The provided information will help us to analyze the issue and provide you the response as early as possible 

 

 

Regards, 

Manisankar Durai 

[Craig Greenway]

Hi, the events for the data source are not firing so I suspect that is the route of the issue.

For info I had an existing website project and added the grid control to an existing page, which did not install any supporting files such as theme css or js files.  I copied these from a sample project to get it to display the grid, so this could also be the cause if it did not install correctly.

The delete, update and insert statements, all do not fire it seems.

[Seeni Sakthi Kumar Seeni Raj]

Hi Mega, 

We suspect that you are not referring the ej.webform.min.js in the application. We have already discussed about resolving the server-side and its non-triggering issues in the following KB.  

https://www.syncfusion.com/kb/2523/how-to-resolve-the-non-triggering-issue-of-server-side-events 

Please refer the KB and update your application as follows. 

%@ Master Language="C#" AutoEventWireup="true" CodeBehind="Site.master.cs" Inherits="Sample.SiteMaster" %> 

 

<!DOCTYPE html> 

<html lang="en"> 

<head runat="server"> 

    .. . . . 

    <link rel='nofollow' href="/Content/ej/default-theme/ej.web.all.min.css" rel="stylesheet" /> 

    <script src="/Scripts/jquery-1.10.2.min.js"></script> 

    <script src="/Scripts/jsrender.min.js"></script> 

    <script src="/Scripts/jquery.easing-1.3.min.js"></script>  

    <script src="Scripts/ej.web.all.js"></script> 

    <script src="Scripts/ej.webform.min.js"></script> 

        . . . 

</head> 

<body> 

    <form runat="server">   

        <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager> 

            <asp:ContentPlaceHolder runat="server" ID="MainContent" /> 

         

    </form> 

</body> 

</html> 

Note: ej.webform.min.js and ScriptManager are essential components to trigger the server events.  

If the problem still persists, please share the following information to analyze the issue. 

1)      If possible, share your project  

2)      If possible, modify the attached sample and replicate the issue.  

 

Sample: http://www.syncfusion.com/downloads/support/forum/125149/ze/Sample567314074.zip 

 

3)      Tell us whether the page reloaded after updating the Grid records using the Grid Tools? 

4)      Share us the table definition of the “tblProducts” 

  

Regards, 

Seeni Sakthi Kumar S. 

[Craig Greenway]

Hi I managed to figure it out, the grid was not within an update panel.  I struggled to find any documentation on how to add the control to an existing web application, perhaps this would be a useful guide is there one already that I have overlooked?

[Mani Sankar Durai]

Hi Craig, 

Query:  I struggled to find any documentation on how to add the control to an existing web application 

 

We can integrate the Syncfusion ASP.NET controls manually into the newly created/existing ASP.NET Web Forms application. 

For your convenience please refer the below documentation link, 

Link: http://help.syncfusion.com/aspnet/getting-started#manual-integration-of-syncfusion-aspnet-controls-into-the-newexisting-application 

Please let us know if you need further queries. 

Regards, 

Manisankar Durai.