We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

SQLDataSource not saving data to database, reads fine

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>

5 Replies

MS Mani Sankar Durai Syncfusion Team July 26, 2016 01:40 PM UTC

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, 

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 



CG Craig Greenway July 26, 2016 06:38 PM UTC

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.


SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team July 27, 2016 11:33 AM UTC

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.  


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.  
 
 
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. 



CG Craig Greenway July 27, 2016 11:50 AM UTC

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?


MS Mani Sankar Durai Syncfusion Team July 28, 2016 10:58 AM UTC

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, 

Please let us know if you need further queries. 

Regards, 
Manisankar Durai. 


Loader.
Up arrow icon