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. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Another CRUD example.

Thread ID:

Created:

Updated:

Platform:

Replies:

143777 Apr 4,2019 02:59 PM UTC Apr 15,2019 12:05 PM UTC ASP.NET Web Forms 15
loading
Tags: Grid
Alex Jermy
Asked On April 4, 2019 02:59 PM UTC

Hi there,

I have prepared an example following this tutorial:

https://asp.syncfusion.com/demos/web/grid/sqlbinding.aspx

As before I can display the correct data and perform CRUD operations in the browser, but after a page refresh the data is lost and the database has not been updated.

I have attached the ASP and C# files below.

Thanks,

Alex

Attachment: Desktop_99932408.zip

Guadalupe
Replied On April 4, 2019 08:44 PM UTC

Hello
When is the function of CRUD? I am not know.

Cordially, Guadalupe

Alex Jermy
Replied On April 5, 2019 07:50 AM UTC

Hi,

This is just an example where I couldn't get the syncfusion example to work. I have tried several methods of recording CRUD operations to the database but none are working outside of the client.

Thanks

Manivannan Padmanaban [Syncfusion]
Replied On April 5, 2019 12:21 PM UTC

  
Hi Guadalupe, 

Thanks for contacting syncfusion support. 

Query: As before I can display the correct data and perform CRUD operations in the browser, but after a page refresh the data is lost and the database has not been updated. 

We are unable to reproduce the reported issue at our end. Please refer the below link for sample, 


After referring the sample, still facing the issue please get back to us with the following details. 

  1. share the Essential studio version are you using.
  2. Share the video demonstration of the issue.
  3. If possible, reproduce the reported issue in the provided sample and revert back to us.

Regards, 
Manivannan Padmanaban. 


Guadalupe
Replied On April 5, 2019 07:25 PM UTC

Hello Alex
You can resolved problem?

Regards, Guadalupe

Alex Jermy
Replied On April 8, 2019 12:22 PM UTC

Hi,

I have recorded myself building the version that you have provided and as you can see I can perform CRUD 'add' and 'deletes' on the rows in the grid but after a refresh of the page the new row is gone and the deleted rows have returned.

Thanks,

Alex

Attachment: 20190408_130825_408ca273.zip

Alex Jermy
Replied On April 8, 2019 12:57 PM UTC

Hi,

Also I am using version 16.4.0.42 but am upgrading to version 17.1.0.38.

Thanks very much,

Alex

Vignesh Natarajan [Syncfusion]
Replied On April 9, 2019 08:18 AM UTC

Hi Alex,  
 
Thanks for the video demonstration.  
 
Query: “after a refresh of the page the new row is gone and the deleted rows have returned 
 
From your query, we understand that you are facing issue with CRUD operation in ejGrid. From your video, we suspect that you are binding the local data (GetBindData()) to Grid. So it might not get preserved while refreshing. Changes gets preserved only when they are saved in server. So kindly ensure that you are using remote data and saving the changes in server while performing CRUD actions.  
 
If you are using remote data and still facing the issue, kindly share the following details which will be helpful for us to validate the reported issue at our end. 
 
  1. Share the Grid rendering code (client side and server side)  
  2. Share more details about GetDataBind() method in server side. (ensure that you have bound the Grid with remote data)
  3. If possible share the issue reproducible sample.  
 
Regards, 
Vignesh Natarajan. 


Alex Jermy
Replied On April 9, 2019 08:57 AM UTC

Hi,

Here is a video of the actual application with the table I am trying to perform operations.

I will also post the ASP and CS files used.

Thanks,

Alex

Attachment: 20190409_095029_d86753f6.zip

Alex Jermy
Replied On April 9, 2019 08:58 AM UTC

Hi,

Here are the ASP and CS files used.

Thanks,

Alex

Attachment: Bands_9e366ec.zip

Vignesh Natarajan [Syncfusion]
Replied On April 10, 2019 08:37 AM UTC

Hi Alex, 
 
Sorry for the inconvenience caused. 
 
In previously, shared sample we have stored the data in the session variable so its not updated in the database. By using, InsertCommandUpdateCommand and DeleteCommand properties of the asp SqlDataSource we can achieve the CRUD action. Refer the below code example, 
 
 
 
    <ej:Grid ID="EmployeesGrid2" runat="server"  DataSourceID="SqlData" EnableLoadOnDemand="false"  AllowTextWrap="True" AllowPaging="True"> 
    <EditSettings AllowEditing="true" AllowAdding="true" AllowDeleting="true" /> 
    <ToolbarSettings ToolbarItems="add,edit,delete,update,cancel" ShowToolbar="true"></ToolbarSettings> 
    <Columns> 
        <ej:Column Field="OrderID" IsPrimaryKey="true"  IsIdentity="True"/> 
        <ej:Column Field="EmployeeID" />             
        <ej:Column Field="Freight" Format="{0:c2}" /> 
        <ej:Column Field="ShipCity" /> 
    </Columns> 
</ej:Grid>  
 
    <asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 
            SelectCommand="SELECT * FROM [Orders]"  
         
        InsertCommand="INSERT INTO [Orders] ([Freight], [EmployeeID], [ShipCity]) 
         VALUES (@Freight, @EmployeeID, @ShipCity)" 
        DeleteCommand= 
        "DELETE FROM [Orders] WHERE [OrderID] = @OrderID" 
       UpdateCommand="UPDATE [Orders] SET  
         [Freight] = @Freight, [EmployeeID] = @EmployeeID, [ShipCity] = @ShipCity 
        WHERE [OrderID] = @OrderID">  
        <UpdateParameters> 
             <asp:Parameter Name="Freight" Type="Double" /> 
            <asp:Parameter Name="EmployeeID" Type="Int32" /> 
            <asp:Parameter Name="ShipCity" Type="String" /> 
        </UpdateParameters> 
        <InsertParameters> 
            <asp:Parameter Name="Freight" Type="Double" /> 
            <asp:Parameter Name="EmployeeID" Type="Int32" /> 
            <asp:Parameter Name="ShipCity" Type="String" /> 
        </InsertParameters> 
        <DeleteParameters> 
            <asp:Parameter Name="OrderID" Type="Int32" /> 
        </DeleteParameters> 
 
        </asp:SqlDataSource> 
                 
                
 
Note: If the IsIdentity property for a column is set in the database, it is not necessary to define the column within the InsertCommand property as the column value will be automatically generated and thus will conflict with the value we pass.  
Also PrimaryKey column value must be unique, because in the shared video demo you have tried to insert a record with same primarykey value. Kindly ensure to have unique primarykeu value.   
 
Or if you want to achieve the same (crud action) with server side events, please refer the below code example, 
 
<ej:Grid ID="OrdersGrid" runat="server" AllowPaging="True" AllowSorting="True" OnServerEditRow="EditEvents_ServerEditRow" 
                    OnServerAddRow="EditEvents_ServerAddRow" OnServerDeleteRow="EditEvents_ServerDeleteRow"> 
                    <Columns> 
                        <ej:Column Field="OrderID" IsPrimaryKey="true" /> 
                        <ej:Column Field="ShipCity" /> 
                    </Columns> 
                    <EditSettings AllowEditing="True" AllowAdding="True" AllowDeleting="True"></EditSettings> 
                    <ToolbarSettings ShowToolbar="True" ToolbarItems="add,edit,delete,update,cancel"></ToolbarSettings> 
                </ej:Grid> 
 
 
DataTable dt = new DataTable("Order"); 
        protected void Page_Load(object sender, EventArgs e) 
        { 
            if (!IsPostBack) 
            { 
                SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ToString()); dt = new DataTable("Order"); 
                SqlCommand cmd = new SqlCommand(); 
                cmd.Connection = myConnection; 
                cmd.CommandText = "select * from Orders"; 
                cmd.CommandType = CommandType.Text; 
                SqlDataAdapter da = new SqlDataAdapter(); 
                da.SelectCommand = cmd; 
                if (myConnection.State == ConnectionState.Closed) 
                { 
                    myConnection.Open(); 
                } 
                da.Fill(dt); 
                OrdersGrid.DataSource = dt; 
                OrdersGrid.DataBind(); 
            } 
 
        } 
         
        protected void EditEvents_ServerEditRow(object sender, GridEventArgs e) 
        { 
            EditAction(e.EventType, e.Arguments["data"]); 
 
        } 
 
        protected void EditEvents_ServerAddRow(object sender, GridEventArgs e) 
        { 
            EditAction(e.EventType, e.Arguments["data"]); 
        } 
 
        protected void EditEvents_ServerDeleteRow(object sender, GridEventArgs e) 
        { 
            EditAction(e.EventType, e.Arguments["data"]); 
        } 
 
        protected void EditAction(string eventType, object record) 
        { 
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ToString()); 
            Dictionary<string, object> KeyVal = record as Dictionary<string, object>; 
            if (eventType == "endEdit") 
            { 
                var Order = KeyVal.Values.ToArray(); 
                foreach (DataRow dr in dt.Rows) 
                { 
                    if (Convert.ToInt32(dr["OrderID"]) == Convert.ToInt32(Order[0])) 
                    { 
                        dr["ShipCity"] = Order[3]; 
                        dr.AcceptChanges(); 
                    } 
 
                } 
 
                myConnection.Open(); 
                SqlCommand cmd = new SqlCommand(); 
                cmd.Connection = myConnection; 
                cmd.CommandText = "UPDATE Orders SET ShipCity = @ShipCity WHERE OrderID = @OrderID"; 
                cmd.Parameters.AddWithValue("@OrderID", Order[0]); 
                cmd.Parameters.AddWithValue("@ShipCity", Order[1]); 
                cmd.ExecuteNonQuery(); 
                myConnection.Close(); 
            } 
 
            else if (eventType == "endAdd") 
            { 
 
 
                var Order = KeyVal.Values.ToArray(); 
                DataRow dr = dt.NewRow(); 
                dr["OrderID"] = Order[0]; 
                dr["ShipCity"] = Order[1]; 
                dt.Rows.Add(dr); 
 
                myConnection.Open(); 
                SqlCommand cmd = new SqlCommand(); 
                cmd.Connection = myConnection; 
                cmd.CommandText = "SET IDENTITY_INSERT Orders ON"; 
                cmd.ExecuteNonQuery(); 
                cmd.CommandText = "INSERT INTO Orders(OrderID,ShipCity)   VALUES(@OrderID,@ShipCity)"; 
                cmd.Parameters.AddWithValue("@ShipCity", Order[1]); 
                cmd.ExecuteNonQuery(); 
                myConnection.Close(); 
 
            } 
            else if (eventType == "endDelete") 
            { 
                var Order = KeyVal.Values.ToArray(); 
                myConnection.Open(); 
                SqlCommand cmd = new SqlCommand(); 
                cmd.Connection = myConnection; 
                cmd.CommandText = "SET IDENTITY_INSERT Orders ON"; 
                cmd.ExecuteNonQuery(); 
                cmd.CommandText = "DELETE FROM Orders WHERE OrderID = @OrderID"; 
                cmd.Parameters.AddWithValue("@OrderID", Order[0]); 
                cmd.ExecuteNonQuery(); 
                myConnection.Close(); 
 
                if (Session["SqlDataSource"] != null) 
                { 
                    DataRow[] rows = dt.Select("OrderID = " + Order[0]); 
 
                    foreach (DataRow row in rows) 
                        dt.Rows.Remove(row); 
 
                } 
 
            } 
            OrdersGrid.DataSource = dt; 
            OrdersGrid.DataBind(); 
        } 
 
 
Please get back to us if you have further queries.  
 
Regards, 
Vignesh Natarajan. 
 


Alex Jermy
Replied On April 10, 2019 03:28 PM UTC

Hi ,

I now have 6 columns in my table 'Yacht_Event_ID" (primary key and also the 'Identity Column' (set at SQL level)), "Boat_ID", "Name", "Notes" "Estimated_Total_Hours",  and"Project_ID".

The 'IsPrimary' value is not set in the column in the Grid.

I still cannot perform CRUD operations.

The version of SQL I'm using is 2026...could this be an issue?

Thanks,

Alex

Alex Jermy
Replied On April 11, 2019 07:21 AM UTC

Hi,

I solved this by using the method I used in the example above, but I removed the server side events from the 'ej:grid' definition.

I think using the buttons was first calling the methods in the ej:grid definition before it was getting to my SQL code.

Thanks,

Alex

Vignesh Natarajan [Syncfusion]
Replied On April 11, 2019 09:17 AM UTC

Hi Alex,  
 
Thanks for the update.  
 
We are glad to know that your query has been resolved.  
 
Please get back to us if you have further queries. 
 
Regards, 
Vignesh Natarajan. 
 


Alex Jermy
Replied On April 12, 2019 12:47 PM UTC

Hi,

Is it possible to pass a scalar value whilst using a SqlDatasource for syncfusion CRUD operations?

So I want to get only records with a specific 'Boat_ID' where I have columns called Yacht_Event_ID, Boat_ID, Notes, Estimated_Hours, and Project_ID - and my Select statement looks like:

                SelectCommand="       
SELECT * FROM [EXT_Yacht_Event_New]"

How do I ensure that I can Select only those boats with a specific ID? The ID is recorded in the session but I am not sure how to access the session data from inside the SqlDatasource?

Vignesh Natarajan [Syncfusion]
Replied On April 15, 2019 12:05 PM UTC

Hi Alex, 
 
Query: How do I ensure that I can Select only those boats with a specific ID? The ID is recorded in the session but I am not sure how to access the session data from inside the SqlDatasource? 
 
As per your request, we have achieved your requirement using WHERE command and sessionparameter.  Please refer the below code example, 
 

 <ej:Grid ID="EmployeesGrid2" runat="server" DataSourceID="SqlData" EnableLoadOnDemand="false" AllowTextWrap="True" AllowPaging="True">
 
        <EditSettings AllowEditing="true" AllowAdding="true" AllowDeleting="true" /> 
        <ToolbarSettings ToolbarItems="add,edit,delete,update,cancel" ShowToolbar="true"></ToolbarSettings> 
        <Columns> 
            <ej:Column Field="OrderID" IsPrimaryKey="true" IsIdentity="True" /> 
            <ej:Column Field="EmployeeID" /> 
            <ej:Column Field="Freight" Format="{0:c2}" /> 
            <ej:Column Field="ShipCity" /> 
        </Columns> 
    </ej:Grid> 
 
    <asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 
        SelectCommand="SELECT * FROM [Orders] WHERE ([OrderID] = @OrderID)" 
        InsertCommand="INSERT INTO [Orders] ([Freight], [EmployeeID], [ShipCity])  
         VALUES (@Freight, @EmployeeID, @ShipCity)" 
        DeleteCommand="DELETE FROM [Orders] WHERE [OrderID] = @OrderID" 
        UpdateCommand="UPDATE [Orders] SET   
         [Freight] = @Freight, [EmployeeID] = @EmployeeID, [ShipCity] = @ShipCity  
        WHERE [OrderID] = @OrderID"> 
………………………………………………………….. 
        <SelectParameters> 
            <asp:SessionParameter Name="OrderID" SessionField="OrderID" Type="Int32" /> 
        </SelectParameters> 
    </asp:SqlDataSource>

 // code behind

 protected void Page_Load(object sender, EventArgs e)
 
        { 
            this.Session["OrderID"] = "10250"; 
        }


 
 
For your convenience we have prepared a sample which can be downloaded from below  
 
 
Please get back to us if you have further qeuries. 
 
Regards, 
Vignesh Natarajan. 
 
 


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.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon 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.Close Icon

Live Chat Icon For mobile
Live Chat Icon