How to update database without using SqlDataSource

[AnagJohari]

If I want to access the method in which we define update query, how can I access that method on aspx page & update the data in a grid view without direct use of an query on aspx page under sql data source?

[Frinavale]

What you would have to do is implement a method that handles the GridView.RowUpd ating Event. In this method you will have to retrieve the data from the row that is updating and use this data to update your database.

I'm not sure what you're stuck on so if you provide more information I can give you more help on the topic.

-Frinny

[AnagJohari]

What you would have to do is implement a method that handles the GridView.RowUpd ating Event. In this method you will have to retrieve the data from the row that is updating and use this data to update your database.

I'm not sure what you're stuck on so if you provide more information I can give you more help on the topic.

-Frinny

sorry frinny for my titile i use......
Actually I made a data acces layer class, buiness logic layer class & presentation layer(.aspx page). Now I want to update the data in a grid view.
In most of the books generally direct update query is written on aspx page under sql data source using update commnad property & do updation in grid view.
Here is the code

<asp:SqlDataSource ID="datasource1" ProviderName="System.Data.SqlClient"  runat="server" ConnectionString="<%$ConnectionStrings:ConnectionString%>" SelectCommand="Select * From ManageClient" 
 UpdateCommand="Update ManageClient Set ClientName=@ClientName,Address=@Address,MobileNo=@MobileNo,Image=@Image Where ClientId=@ClientId"></asp:SqlDataSource>

Connect it to the grid view.

Now i do not want to use update command or connection string in sql data source.
This thing I have done in data acces layer class where I make a method of an update.
I want to use this method so I can do an update in grid view directly.

[Frinavale]

I've never actually used the SqlDataSource before. I like to be in more control of my database connections.

Anyways, it's not that hard to do. Delete your SqlDataSource.. .you don't need it. You don't need to use a SqlDataSource at all. GridViews can bind to DataTables, DataViews, Arrays of Objects ...and more.

So, you need to populate the object that you are going to use as your data source at some point in your page life cycle. You could do this in your Page Load event if you want to.

You could cache the object that you are going to use as a data source too. This way you don't have to repopulate the data source every page postback. This saves time but it depends on how long you can tolerate "old data" being displayed.

Anyways, once you've populated your data source (or retrieved it from cache) you set the GridView.DataSo urce property to the object you are using...then call the GridView.DataBi nd method to bind the GridView to the data source. I do this in the Page PreRender event for a bunch of reasons....for example if you bind in the Page Load event every page request you will have problems with getting the values that the user entered while editing (because these values will be over written with the data that you've bound to).

In your GridViews OnRowUpdating event you will call your data access layer's methods to do the updating....

That's pretty much it.

-Frinny

[AnagJohari]

I've never actually used the SqlDataSource before. I like to be in more control of my database connections.

Anyways, it's not that hard to do. Delete your SqlDataSource.. .you don't need it. You don't need to use a SqlDataSource at all. GridViews can bind to DataTables, DataViews, Arrays of Objects ...and more.

So, you need to populate the object that you are going to use as your data source at some point in your page life cycle. You could do this in your Page Load event if you want to.

You could cache the object that you are going to use as a data source too. This way you don't have to repopulate the data source every page postback. This saves time but it depends on how long you can tolerate "old data" being displayed.

Anyways, once you've populated your data source (or retrieved it from cache) you set the GridView.DataSo urce property to the object you are using...then call the GridView.DataBi nd method to bind the GridView to the data source. I do this in the Page PreRender event for a bunch of reasons....for example if you bind in the Page Load event every page request you will have problems with getting the values that the user entered while editing (because these values will be over written with the data that you've bound to).

In your GridViews OnRowUpdating event you will call your data access layer's methods to do the updating....

That's pretty much it.

-Frinny

You said that we have to bind object with grid view at page.prerender . I think if we do the same thing on page load event it work because value is set when row updating event is fired. When this event fired it saves the values at their view state & this view state is sent to the client. That is the concept of page life cycle. Am I right?

[Frinavale]

Not quite right.
What happens is that the ViewState for the GridView is loaded just before the Page Load event. This means that all of the data that the user provided for the row that they are updating is loaded into the ASP.NET controls used to access the data in your C#/VB.NET code.

If you do a DataBind in the Page Load event then all of this data is lost!
Edit: if you do a DataBind in the Page Load event every page request all of your data will be lost

It's the most common problem that people have with GridViews that I've noticed.

If you don't need to get data from the user, than yes you can do the DataBind in the Page Load event. But if you need to get data from the user for updating purposes then, no, you should do it in the Page PreRender event instead.

-Frinny

[AnagJohari]

Not quite right.
What happens is that the ViewState for the GridView is loaded just before the Page Load event. This means that all of the data that the user provided for the row that they are updating is loaded into the ASP.NET controls used to access the data in your C#/VB.NET code.

If you do a DataBind in the Page Load event then all of this data is lost!

It's the most common problem that people have with GridViews that I've noticed.

If you don't need to get data from the user, than yes you can do the DataBind in the Page Load event. But if you need to get data from the user for updating purposes then, no, you should do it in the Page PreRender event instead.

-Frinny

The event is fired between load view state & save view state when the event is fired it set the values in textbox or whatever server control we used. After that it saves the values in view state. Then there is no matter of data loss if you load the page again but I have to bind data in a grid view through view state on page load no chance of data loss.

[Frinavale]

You are mistaken.

Events are created when the ViewState is loaded. Methods that handle events occur after the Page Load event.

This is the basic outline of what happens in the ASP.NET Life Cycle:

• Page Init (before ViewState is loaded)
• Page Load (after ViewState is loaded)
• Event handling code is executed
• Page PreRender (occurs Just before the page is rendered as HTML)

Please review the ASP.NET Page Life Cycle for a more detailed explination.

If you perform a GridView.DataBi nd in the Page Load event (without checking if it's postback) then you will not be able to retrieve user input in the GridView.RowEdi ting event.

-Frinny

[AnagJohari]

You are mistaken.

Events are created when the ViewState is loaded. Methods that handle events occur after the Page Load event.

This is the basic outline of what happens in the ASP.NET Life Cycle:

• Page Init (before ViewState is loaded)
• Page Load (after ViewState is loaded)
• Event handling code is executed
• Page PreRender (occurs Just before the page is rendered as HTML)

Please review the ASP.NET Page Life Cycle for a more detailed explination.

If you perform a GridView.DataBi nd in the Page Load event (without checking if it's postback) then you will not be able to retrieve user input in the GridView.RowEdi ting event.

-Frinny

Can you tell me when the data is saved in a view state & that data is loss from the view state next time when it load the view state?

[Frinavale]

The ViewState is stored some time in between the Page PreRender event and the SaveStateComple te event (heading towards the Render stage).

All of this is covered in the article I sent you a link to :)

I'm not sure what you mean by:

AnagJohari: that data is loss from the view state

Maybe you're talking about data loss that happens if you don't call the DataBind method??

[AnagJohari]

The ViewState is stored some time in between the Page PreRender event and the [http://msdn.microsoft.com/en-us/libr...eStateComplete event[/url] (heading towards the Render stage).

All of this is covered in the article I sent you a link to :)

I'm not sure what you mean by:

Maybe you're talking about data loss that happens if you don't call the DataBind method??

Now i implemented the editing in gridview i handle events & after that i call display method in which i bind the grid view on page load event its work correctly...... .
i want to say one more thing i call the display method at the end of every event which i handled ....
like onrowupdating event after performing all operation
i call display method at the end.
you also check this link please , by thw way ur link is not working.
http://msdn.microsoft.com/en-us/library/ms972976.aspx

[Frinavale]

I've fixed the link.

I'm sorry AnagJohari, but you've completely lost me.

I'm am fully aware of when ViewState is loaded....it's loaded right after the Page Init Event and before Page Load event.

So, the ViewState with all of the data for your GridView is loaded just after the Init event. This makes the data in your GridView is available in the Page Load event...includi ng data entered by the user.

If you do a DataBind in your Page Load event every page request (without checking if isPostback is False) then you will be overwritting anything that the user provided.


BUT this information that I'm telling you does not matter to you because you don't care about anything the user has entered into the GridView for editing. (Well it should matter to you because this is a very important behaviour that you need to understand)

All you want to do is determine which row the user was editing (which is easily done in the OnRowEditing event)...and retrieve any information that you need in order to display the information in more detail in some other "page" or "control".

You were not very clear about this.
This is why you are able to do a DataBind in your Page Load without losing data entered by the user.

Anyways.
I don't know what you're talking about with regards to your "display" method.
I'm not even sure you have a question at this point... :)

-Frinny

[AnagJohari]

I've fixed the link.

I'm sorry AnagJohari, but you've completely lost me.

I'm am fully aware of when ViewState is loaded....it's loaded right after the Page Init Event and before Page Load event.

So, the ViewState with all of the data for your GridView is loaded just after the Init event. This makes the data in your GridView is available in the Page Load event...includi ng data entered by the user.

If you do a DataBind in your Page Load event then you will be overwritting anything that the user provided.


BUT this information that I'm telling you does not matter to you because you don't care about anything the user has entered into the GridView for editing. (Well it should matter to you because this is a very important behaviour that you need to understand)

All you want to do is determine which row the user was editing (which is easily done in the OnRowEditing event)...and retrieve any information that you need in order to display the information in more detail in some other "page" or "control".

You were not very clear about this.
This is why you are able to do a DataBind in your Page Load without losing data entered by the user.

Anyways.
I don't know what you're talking about with regards to your "display" method.
I'm not even sure you have a question at this point... :)

-Frinny

Frinny I send You a code ,
By the way You are so senior & i respect each & every word which you are writing for me....
I am just new ,so i m eager to know what is the problem actually .In mycode,
why i donot lose my data in grid view.
I just need your help Thats it

That is .cs code file

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using BllDummy;

public partial class ManageClient : System.Web.UI.Page
{
    DummyBLL dummy = new DummyBLL();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            ddlbind();
            display();
        }
    }
    protected void click_Add(object sender, EventArgs e)
    {
        dummy.ClientName = txtClientName.Text;
        dummy.Address = txtAddress.Text;
        dummy.MobileNo = Convert.ToInt64(txtMobileNo.Text);
        dummy.Image = txtImage.Text;
        dummy.add();
        txtAddress.Text = string.Empty;
        txtClientName.Text = string.Empty;
        txtImage.Text = string.Empty;
        txtMobileNo.Text = string.Empty;
        txtClientName.Text = "the addtion is done";
        display();
  
    }
    private void display()
    {
        gv_DisplayClient.DataSource = DummyBLL.display();
       // gv_DisplayClient.DataSourceID = "datasource1";
        gv_DisplayClient.DataBind();
    }
    private void ddlbind()
    {
        List<DummyBLL> list = DummyBLL.Clientlist();
        ddlClient.Items.Add(new ListItem("--SelectClient--", "0"));
        foreach (DummyBLL dummylist in list)
        {
            ddlClient.Items.Add(new ListItem(dummylist.ClientName, dummylist.ClientId.ToString()));
        }
    }



 /*protected void ClickStatus(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "StatusClick")
        {
            message.Text = "You Currently Click The ClientId=" + e.CommandArgument.ToString();
        }
    }*/
   
    protected void View_Click(object sender, EventArgs e)
    {
     
    }
    protected void GvEditing(object sender, GridViewEditEventArgs e)
    {
        gv_DisplayClient.EditIndex = e.NewEditIndex;
        display();
      }
    protected void gv_EditCancel(object sender, GridViewCancelEditEventArgs e)
    {
        gv_DisplayClient.EditIndex = -1;
        display();
    }

    protected void gv_Updating(object sender, GridViewUpdateEventArgs e)
    {

        Label lb = (Label)gv_DisplayClient.Rows[e.RowIndex].FindControl("Label6"); 
        int ClientId = Convert.ToInt32(lb.Text);
        TextBox tx1 = (TextBox)gv_DisplayClient.Rows[e.RowIndex].FindControl("gvClientName");
        TextBox tx2 = (TextBox)gv_DisplayClient.Rows[e.RowIndex].FindControl("gvAddress");
        TextBox tx3 = (TextBox)gv_DisplayClient.Rows[e.RowIndex].FindControl("gvMobileNo");
        TextBox tx4 = (TextBox)gv_DisplayClient.Rows[e.RowIndex].FindControl("gvImage");
        dummy.ClientName = tx1.Text;
        dummy.Address = tx2.Text;
        dummy.MobileNo = Convert.ToInt32(tx3.Text);
        dummy.Image = tx4.Text;
        dummy.ClientId = ClientId;
        dummy.Update();
        gv_DisplayClient.EditIndex = -1;
        display();

    }
 protected void gv_Deleting(object sender, GridViewDeleteEventArgs e)
    {
        Label lbl = (Label)gv_DisplayClient.Rows[e.RowIndex].FindControl("Label6");
        dummy.ClientId = Convert.ToInt32(lbl.Text);
        dummy.Delete();
        display();
    }
[B][B]
this event i write after our discussion so its seprate thing, by the way thisevent is not working
at this time
 protected void Gv_Insert(object sender, GridViewRowEventArgs e)
    {
        TextBox tx1 = (TextBox)gv_DisplayClient.FindControl("gvClientName");
        TextBox tx2 = (TextBox)gv_DisplayClient.FindControl("gvAddress");
        TextBox tx3 = (TextBox)gv_DisplayClient.FindControl("gvMobileNo");
        TextBox tx4 = (TextBox)gv_DisplayClient.FindControl("gvImage");
       
        dummy.ClientName = tx1.Text;
        dummy.Address = tx2.Text;
        dummy.MobileNo = Convert.ToInt32(tx3.Text);
        dummy.Image = tx4.Text;
        dummy.add();
        display();
    }
}[/B]

 There is problem in this event that is gv_insert i make it in bold letter.
i took on rowcreating event but not able to fetch the value from grid view row
i want that when i click on add the dynamic row is created in editable form
after i enter the value in that row the data is add in database[/B]

.aspx code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ManageClient.aspx.cs" Inherits="ManageClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table border="3" cellspacing="3" width="300px">
    <tr>
    <td>
    <b>Client_Name</b>
    </td>
    <td>
    <asp:TextBox ID="txtClientName" runat="server" ></asp:TextBox>
  </td>
 
    </tr>
    <tr>
    <td>
    <b>Address</b>
 
  
  
  
    </td>
    <td>
    <asp:TextBox ID="txtAddress" runat="server" ></asp:TextBox>
    </td>
   
    </tr>
    <tr>
    <td>
    <b>Mobile_No</b>
    </td>
    <td>
    <asp:TextBox ID="txtMobileNo" runat="server" ></asp:TextBox>
    </td>
 </tr>
 <tr>
    <td>
    <b>Image</b>
    </td>
    <td>
    <asp:TextBox ID="txtImage" runat="server" ></asp:TextBox>
    </td>
    </tr>
    <tr><td><b>ClientList</b>
    </td>
    <td><asp:DropDownList ID="ddlClient" runat="server"></asp:DropDownList>
    </td></tr>
    
    <tr><td align="right" colspan="2">
    <asp:Button ID="txtAdd" runat="server" Font-Bold="true" OnClick="click_Add" Font-Size="Larger" Text="Add" Height="30px" />
  
   </td>
   </tr>
  
 </table>
 <asp:Button ID="SaveChanges" runat="server" />
 <asp:Button ID="View" runat="server" onclick="View_Click" />
  <asp:GridView ID="gv_DisplayClient" runat="server" DataKeyNames="ClientId" 
            ForeColor="#333333" GridLines="Horizontal"  
            CellPadding="4" AutoGenerateColumns="false" 
              OnRowEditing="GvEditing" OnRowCancelingEdit="gv_EditCancel" 
                EnableViewState="true" onrowupdating="gv_Updating" 
                onrowdeleting="gv_Deleting" onrowcreated="Gv_Insert" 
                >
            <%-- (placed in grid view when using sql data source) OnRowCommand="ClickStatus" DataSourceID="datasource1"--%>
          
 <RowStyle BackColor="#F7F6F3" ForeColor="#000000"/>
     <HeaderStyle BackColor="BurlyWood" ForeColor="AliceBlue" Font-Bold="true" />
 
  <Columns>
  <%-- <asp:BoundField DataField="ClientId" ReadOnly="true" HeaderText="ClientId" />
  <asp:BoundField DataField="ClientName" HeaderText="Client Name" />
  <asp:BoundField DataField="Address" HeaderText="Address" />
  <asp:BoundField DataField="MobileNo" HeaderText="MobileNo" />
  <asp:ImageField DataImageUrlField="Image" HeaderText="Image"/>
   <asp:CommandField ShowEditButton="true"  SelectText="Edit"/>
  <asp:TemplateField HeaderText="click">
  <ItemTemplate>
  <asp:Button ID="click" runat="server" CommandName="StatusClick" CommandArgument='<%#Eval("ClientId")%>' Text="Click" />
  
  </ItemTemplate>
  
  </asp:TemplateField>--%>
  <asp:TemplateField HeaderText="ClientId">
  <ItemTemplate >
  <asp:Label ID="Label6" runat="server" Text='<%#Bind("ClientId")%>'></asp:Label>
  </ItemTemplate>
  
  </asp:TemplateField>
  <asp:TemplateField HeaderText="ClientName">
  <ItemTemplate >
  <asp:Label ID="lblClientName" runat="server" Text='<%#Eval("ClientName")%>'></asp:Label>
  </ItemTemplate>
  <EditItemTemplate>
  <asp:TextBox ID="gvClientName" runat="server" Text='<%#Bind("ClientName")%>'></asp:TextBox>
  </EditItemTemplate>
  
  </asp:TemplateField>
   <asp:TemplateField HeaderText="Address">
  <ItemTemplate >
  <asp:Label ID="lblAddress" runat="server" Text='<%#Eval("Address")%>'></asp:Label>
  </ItemTemplate>
  <EditItemTemplate>
  <asp:TextBox ID="gvAddress" runat="server" Text='<%#Bind("Address")%>'></asp:TextBox>
  </EditItemTemplate>
  
  </asp:TemplateField>
  <asp:TemplateField HeaderText="MobileNo">
  <ItemTemplate >
  <asp:Label ID="lblMobileNo" runat="server" Text='<%#Eval("MobileNo")%>'></asp:Label>
  </ItemTemplate>
  <EditItemTemplate>
  <asp:TextBox ID="gvMobileNo" runat="server" Text='<%#Bind("MobileNo")%>'></asp:TextBox>
  </EditItemTemplate>
  
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Image">
  <ItemTemplate >
  <asp:Label ID="lblImage" runat="server" Text='<%#Eval("Image")%>'></asp:Label>
  </ItemTemplate>
  <EditItemTemplate>
  <asp:TextBox ID="gvImage" runat="server" Text='<%#Bind("Image")%>'></asp:TextBox>
  </EditItemTemplate>
  
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Operations">
  <ItemTemplate>
  <asp:LinkButton ID="edit" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton>
  <asp:LinkButton ID="Delete" runat="server" CommandName="Delete" Text="Delete"></asp:LinkButton>
  </ItemTemplate>
  <EditItemTemplate>
                   <asp:LinkButton ID="btnupdate" runat="server" 
			CommandName="Update" Text="Update" ></asp:LinkButton>
                   <asp:LinkButton ID="btncancel" runat="server" 
			CommandName="Cancel" Text="Cancel"></asp:LinkButton>
			<asp:LinkButton ID="btnAdd" runat="server" CommandName="Insert" Text="Insert"></asp:LinkButton>
               </EditItemTemplate>
 </asp:TemplateField>
 </Columns>
 
     <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
     <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
     <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
     <EditRowStyle BackColor="#999999" />
     <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
 </asp:GridView>
  
  
 <%-- <asp:SqlDataSource ID="datasource1" ProviderName="System.Data.SqlClient"  runat="server" ConnectionString="<%$ConnectionStrings:ConnectionString%>" SelectCommand="Select * From ManageClient" 
 UpdateCommand="Update ManageClient Set ClientName=@ClientName,Address=@Address,MobileNo=@MobileNo,Image=@Image Where ClientId=@ClientId"></asp:SqlDataSource> --%>
    </div>
    <asp:Label ID="message" runat="server"></asp:Label>
    </form>
</body>
</html>

please see my code & give your important opinion.
Thank you

[Frinavale]

This is a lot easier to understand!

Ok the reason why you aren't losing data is because you are only doing your DataBind in your Page Load event the first time the page is loaded (if !IsPostback). This way your data is not getting over written when the user-provided-edit-data is posted back to the server.

But do you notice how you are calling the display() method in several different places? Wouldn't it be easier if you just had to call it once? That is why I recommended that you do this in your Page PreRender event.

Ok so everything is looking pretty good :)
But instead of using the Insert event to try and populate your TextBoxes, try using the GridView.RowDat aBound event :)

Edit: Do not use the DataRowBound event....you should be using the GridView.RowCom mand event. I should have been more observant earlier to see what you were trying to do! I'm sorry.




-Frinny

[AnagJohari]

This is a lot easier to understand!

Ok the reason why you aren't losing data is because you are only doing your DataBind in your Page Load event the first time the page is loaded (if !IsPostback). This way your data is not getting over written when the user-provided-edit-data is posted back to the server.

But do you notice how you are calling the display() method in several different places? Wouldn't it be easier if you just had to call it once? That is why I recommended that you do this in your Page PreRender event.

Ok so everything is looking pretty good :)
But instead of using the Insert event to try and populate your TextBoxes, try using the GridView.RowDat aBound event :)

I'm pretty sure this will fix your problem :)

The other thing that you could do is bind each TextBox.Text to a specific property of your object that you're using as the data source....

Let's do it your way first (use the RowDataBound event instead of the Insert) and then if you want to look into the other way of doing it we can


-Frinny

Now I get the answer your mean is that when we bind the data at page load event. the view state is already loaded with previous value & that value again saved in a view state so the same data is displayed again & again whether you edit the data in a grid view or not.
but if we bind the data at page.prerender event then this value is loaded in a view state & after page load event this view state is saved & the data is displayed with updated value..
Am i right frinny? please reply me that what i said is right or not.
other thing i want to know what about the load postback event that is also fire between pageprerender & page load . i read about this event in msdn but not able to distinquish beacuse loadpostback event is also use to remember the value & pageprerender also for remebering the values.
thank you