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.
Unfortunately, activation email could not send to your email. Please try again.

SQLDataSource not saving data to database, reads fine

Thread ID:

Created:

Updated:

Platform:

Replies:

125149 Jul 25,2016 03:37 PM Jul 28,2016 06:58 AM ASP.NET Web Forms 5
loading
Tags: Grid
Craig Greenway
Asked On July 25, 2016 03:37 PM

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 [Syncfusion]
Replied On July 26, 2016 09:40 AM

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 


Craig Greenway
Replied On July 26, 2016 02:38 PM

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 [Syncfusion]
Replied On July 27, 2016 07:33 AM

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. 


Craig Greenway
Replied On July 27, 2016 07:50 AM

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 [Syncfusion]
Replied On July 28, 2016 06:58 AM

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. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;