locked
Not getting Updated RRS feed

  • Question

  • User1407015516 posted

     

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("usp_update", con);
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CustomerID", SqlDbType.Int);
            cmd.Parameters.AddWithValue("@CustomerName", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@Address", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@City", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@Zipcode", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@RegistrationDate", SqlDbType.DateTime);
            cmd.Parameters.AddWithValue("@Email", SqlDbType.VarChar);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cmd.ExecuteNonQuery();
            //GridView1.DataSource = ds;
            //GridView1.DataBind();
            GridView1.EditIndex = -1;
            bindgrid();
            con.Close();
            
        }
    
    "S.P"
    [usp_update]
    
    (@CustomerID int,@CustomerName varchar(50),@Address Varchar(50),@City varchar(50),@Zipcode varchar(50),@Email Varchar(50),@RegistrationDate Datetime)
    AS
    update Customer set CustomerName=@CustomerName, Address=@Address, City=@city, Zipcode=@Zipcode, Email=@Email, RegistrationDate=@RegistrationDate where CustomerID=@CustomerID
    

    Design Goes like this............
    <asp:GridView ID="GridView1" runat="server" align="center" Width="90%" 
                    CellPadding="4" AutoGenerateColumns="False" 
                            onrowcancelingedit="GridView1_RowCancelingEdit" 
                            onrowediting="GridView1_RowEditing" ForeColor="#333333" GridLines="None" 
                            AllowPaging="True" AllowSorting="True" onrowupdating="GridView1_RowUpdating" 
                            PageSize="8" onpageindexchanging="GridView1_PageIndexChanging">
                    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                    <Columns>
                    
                        <asp:TemplateField HeaderText="CustomerName">
                            <ItemTemplate>
                                <asp:Label ID="Label1" runat="server" Text='<%# Bind("CustomerName") %>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("CustomerName") %>'></asp:TextBox>
                            </EditItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Address">
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Address") %>'></asp:TextBox>
                                <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("StateNames") %>'></asp:TextBox>
                                <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("City") %>'></asp:TextBox>
                                <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("Zipcode") %>'></asp:TextBox>
                                
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label2" runat="server" Text='<%# Bind("Address") %>'></asp:Label>
                                <asp:Label ID="Label3" runat="server" Text='<%# Bind("StateNames") %>'></asp:Label>
                                <asp:Label ID="Label4" runat="server" Text='<%# Bind("City") %>'></asp:Label>
                                <asp:Label ID="Label5" runat="server" Text='<%# Bind("Zipcode") %>'></asp:Label>
                                
                                 </ItemTemplate>
                            
                            
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Email">
                            <ItemTemplate>
                                <asp:Label ID="Label6" runat="server" Text='<%# Bind("Email") %>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("Email") %>'></asp:TextBox>
                            </EditItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="RegistrationDate">
                            <ItemTemplate>
                                <asp:Label ID="Label7" runat="server" Text='<%# Bind("RegistrationDate") %>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox7" runat="server" Text='<%# Bind("RegistrationDate") %>'></asp:TextBox>
                            </EditItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Products">
                            <ItemTemplate>
                                <asp:Label ID="Label8" runat="server" Text='<%# Bind("Products") %>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBox8" runat="server" Text='<%# Bind("Products") %>'></asp:TextBox>
                            </EditItemTemplate>
                        </asp:TemplateField>
                        <asp:CommandField ShowEditButton="True" />
                        <asp:CommandField ShowDeleteButton="True" />
                    </Columns>
                    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <AlternatingRowStyle BackColor="White" />
            </asp:GridView>

    Monday, January 30, 2012 1:54 AM

Answers

All replies

  • User-1542018982 posted

    Please refer this link:
    http://msdn.microsoft.com/en-us/library/ms972948.aspx

    I think the reason it's not getting updated is because the Parameters' values are not set properly.

    AddWithValue method accepts parameterName and value. The code posted is passing parameterName and data type.

    You need to find the row's values and pass to the stored procedure.

    Please refer this link to find the control in TemplateField:
    http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.templatefield.edititemtemplate.aspx

    Hope it helps.

    Monday, January 30, 2012 4:04 AM
  • User-2001765250 posted

    Record not updating because of paramerters u mentions for the command.

    AddWithValue method of SQLCommand requires two paramerters Column Name and values.

    eg: 

     cmd.Parameters.AddWithValue("@CustomerName", txtCustomerName.Text.Trim());
    or 
    cmd.AddParameter("@CustomerName",
     txtCustomerName.Text.Trim(),
     SqlDbType.VarChar);
    
    
    The columnnames you are specifiyng (CustomerName) is same as used in
    usp_update procedure.
    Remaining is correct.
    Monday, January 30, 2012 4:15 AM
  • User1407015516 posted

     

    Still not working friend....!
    By the way there is problem that its getting inserted twice everytime in database.....!!

    protected void Page_Load(object sender, EventArgs e)
    {
    if (!IsPostBack)
    {

    SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
    SqlCommand cmd = new SqlCommand("usp_state", con);
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    cmd.ExecuteScalar();
    ddlstate.DataTextField = "StateNames";
    ddlstate.DataValueField = "StateID";
    ddlstate.DataSource = ds;
    ddlstate.DataBind();
    ddlstate.Items.Insert(0, new ListItem("Select"));
    con.Close();

    SqlConnection con1 = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
    SqlCommand cmd1 = new SqlCommand("usp_products", con1);
    con1.Open();
    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    DataSet ds1 = new DataSet();
    da1.Fill(ds1);
    cmd1.ExecuteScalar();
    ddlproducts.DataTextField = "Products";
    ddlproducts.DataValueField = "ProductID";
    ddlproducts.DataSource = ds1;
    ddlproducts.DataBind();
    ddlproducts.Items.Insert(0, new ListItem("Select"));
    con1.Close();
    }
    bindgrid();
    }

    protected void btnsubmit_Click(object sender, EventArgs e)
    {
    SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
    SqlCommand cmd = new SqlCommand("usp_insert", con);
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@CustomerName", txtcustname.Text);
    cmd.Parameters.AddWithValue("@Address", txtadd.Text);
    cmd.Parameters.AddWithValue("@City", txtcity.Text);
    cmd.Parameters.AddWithValue("@Zipcode", txtzip.Text);
    cmd.Parameters.AddWithValue("@RegistrationDate", txtregdate.Text);
    cmd.Parameters.AddWithValue("@Email", txtemail.Text);
    cmd.Parameters.Add("@StateID",SqlDbType.Int).Value = ddlstate.SelectedItem.Value;
    cmd.Parameters.Add("@ProductID",SqlDbType.Int).Value = ddlproducts.SelectedItem.Value;
    DataSet ds = new DataSet();
    da.Fill(ds);
    cmd.ExecuteNonQuery();
    GridView1.DataSource = ds;
    GridView1.DataBind();
    con.Close();
    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
    GridView1.EditIndex = e.NewEditIndex;
    bindgrid();
    }
    public void bindgrid()
    {
    SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
    SqlCommand cmd = new SqlCommand("usp_join", con);
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    cmd.ExecuteScalar();
    GridView1.DataSource = ds;
    GridView1.DataBind();
    con.Close();
    }
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
    GridView1.EditIndex = -1;
    bindgrid();
    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
    SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
    SqlCommand cmd = new SqlCommand("usp_update", con);
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    cmd.Parameters.AddWithValue("@CustomerID", SqlDbType.Int);
            cmd.Parameters.AddWithValue("@CustomerName", txtcustname.Text.Trim());
            cmd.Parameters.AddWithValue("@Address", txtadd.Text.Trim());
            cmd.Parameters.AddWithValue("@City", txtcity.Text.Trim());
            cmd.Parameters.AddWithValue("@Zipcode", txtzip.Text.Trim());
            cmd.Parameters.AddWithValue("@RegistrationDate", txtregdate.Text.Trim());
            cmd.Parameters.AddWithValue("@Email", txtemail.Text.Trim());
    DataSet ds = new DataSet();
    da.Fill(ds);
    cmd.ExecuteNonQuery();
    //GridView1.DataSource = ds;
    //GridView1.DataBind();
    GridView1.EditIndex = -1;
    bindgrid();
    con.Close();

    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
    GridView1.PageIndex = e.NewPageIndex;
    bindgrid();
    }
    }

    "S.P"
    [usp_join]

    SELECT Customer.CustomerName,Customer.Address, State.StateNames,Customer.City,Customer.Zipcode,Customer.Email,Customer.RegistrationDate,Product.Products
    From Customer
    Left outer join State
    on Customer.StateID = State.StateID
    Left outer join Product
    on Customer.ProductID = Product.ProductID

    "S.P"
    [usp_insert]

    (@CustomerName varchar(50),@Address varchar(50), @City varchar(50), @Zipcode varchar(6),@RegistrationDate Datetime, @Email varchar(50),@StateID int,@ProductID int)
    AS
    insert into Customer
    (CustomerName
    ,Address
    , City
    , Zipcode
    ,RegistrationDate
    , Email
    ,StateID
    ,ProductID)
    values
    (@CustomerName
    ,@Address
    , @City
    , @Zipcode
    ,@RegistrationDate
    , @Email
    ,@StateID
    ,@ProductID)


    "S.P"
    [usp_update]

    (@CustomerID int,@CustomerName varchar(50),@Address Varchar(50),@City varchar(50),@Zipcode varchar(50),@Email Varchar(50),@RegistrationDate Datetime)
    AS
    update Customer set CustomerName=@CustomerName, Address=@Address, City=@city, Zipcode=@Zipcode, Email=@Email, RegistrationDate=@RegistrationDate where CustomerID=@CustomerID

     

    Monday, January 30, 2012 5:14 AM
  • User-2001765250 posted

    Friend you have used the both Excecute NOnQuery and DataAdapter to add the record.

    So keep one of this. 

    So for that instance comment the cmd.ExceuteNonQuery() line.

    I thnk it will works.

    Monday, January 30, 2012 8:48 AM
  • User1407015516 posted

    Thanks i have checked removing that. 

    It solves inserting data twice into database but not UPDATING issue.

    Plz check again.... I think there is a problem with Editing.

    Have taken <asp:CommandField ShowEditButton="True" />
                        <asp:CommandField ShowDeleteButton="True" />

    After clicking edit command field, Its displaying rows with textboxes to edit but after cliking update  it results nothing. And i have a table above which asks to insert  values into table after clicking update commandfield.

     



    <table cellpadding="3" cellspacing="3" width="80%" align="center"> <tr align="center"> <td colspan="2"> <asp:Label ID="lblsidehead" runat="server" Text="ADD CUSTOMER DETAILS" Font-Bold="True" Font-Size="Medium" ForeColor="#FF0066"></asp:Label> </td> </tr> <tr> <td align="right"> <asp:Label ID="lblcustname" runat="server" Text="Customer Name:" Font-Bold="True" ForeColor="#009999"></asp:Label> <asp:RequiredFieldValidator ID="rfvname" runat="server" ControlToValidate="txtcustname" ErrorMessage="Enter Name">*</asp:RequiredFieldValidator> </td> <td > <asp:TextBox ID="txtcustname" runat="server"></asp:TextBox> </td> </tr> <tr> <td align="right"> <asp:Label ID="lbladd" runat="server" Text="Address:" Font-Bold="True" ForeColor="#009999"></asp:Label> <asp:RequiredFieldValidator ID="rfvadd" runat="server" ControlToValidate="txtadd" ErrorMessage="Enter Address">*</asp:RequiredFieldValidator> </td> <td> <asp:TextBox ID="txtadd" runat="server"></asp:TextBox> </td> </tr> <tr> <td align="right"> <asp:Label ID="lblcity" runat="server" Text="City:" Font-Bold="True" ForeColor="#009999"></asp:Label> <asp:RequiredFieldValidator ID="rfvcity" runat="server" ControlToValidate="txtcity" ErrorMessage="Enter City">*</asp:RequiredFieldValidator> </td> <td> <asp:TextBox ID="txtcity" runat="server"></asp:TextBox> </td> </tr> <tr> <td align="right"> <asp:Label ID="lblZip" runat="server" Text="Zipcode:" Font-Bold="True" ForeColor="#009999"></asp:Label> <asp:RequiredFieldValidator ID="rfvzip" runat="server" ControlToValidate="txtzip" EnableViewState="False" ErrorMessage="Enter ZipCode">*</asp:RequiredFieldValidator> </td> <td> <asp:TextBox ID="txtzip" runat="server"></asp:TextBox> </td> </tr> <tr> <td align="right"> <asp:Label ID="lblstate" runat="server" Text="State:" Font-Bold="True" ForeColor="#009999"></asp:Label> <asp:RequiredFieldValidator ID="rfvstate" runat="server" ControlToValidate="ddlstate" ErrorMessage="Select Any Options" InitialValue="Select">*</asp:RequiredFieldValidator> </td> <td> <asp:DropDownList ID="ddlstate" runat="server"> </asp:DropDownList> </td> </tr> <tr> <td align="right"> <asp:Label ID="lblregdate" runat="server" Text="Registration Date:" Font-Bold="True" ForeColor="#009999"></asp:Label> <asp:RequiredFieldValidator ID="rfvregdate" runat="server" ControlToValidate="txtregdate" ErrorMessage="Enter Registration Date">*</asp:RequiredFieldValidator> </td> <td> <asp:TextBox ID="txtregdate" runat="server"></asp:TextBox> </td> </tr> <tr> <td align="right"> <asp:Label ID="lblEmail" runat="server" Text="Email:" Font-Bold="True" ForeColor="#009999"></asp:Label> <asp:RequiredFieldValidator ID="rfvemail" runat="server" ControlToValidate="txtemail" ErrorMessage="Enter Email">*</asp:RequiredFieldValidator> </td> <td> <asp:TextBox ID="txtemail" runat="server"></asp:TextBox> </td> </tr> <tr> <td align="right"> <asp:Label ID="lblproducts" runat="server" Text="Products:" Font-Bold="True" ForeColor="#009999"></asp:Label> <asp:RequiredFieldValidator ID="rfvproducts" runat="server" ControlToValidate="ddlproducts" ErrorMessage="Select Any Product" InitialValue="Select">*</asp:RequiredFieldValidator> </td> <td> <asp:DropDownList ID="ddlproducts" runat="server"> </asp:DropDownList> </td> </tr> <tr align="center"> <td colspan="2"> <asp:Button ID="btnsubmit" runat="server" Text="SUBMIT" BackColor="Blue" Font-Bold="True" ForeColor="White" onclick="btnsubmit_Click" />

     

     

    Monday, January 30, 2012 9:05 AM
  • User1407015516 posted

    I'm getting an error

     

    DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'CustomerID'.

    As i haven't taken CustomerID in StoredProcedure [usp_join] which was autoincremented and i dont want to show in my grid.

    But i had to use it in row_updating event.......which resulted in this error. plz suggest me where i'm going wrong?

    Monday, January 30, 2012 1:33 PM
  • User-1542018982 posted

    CustomerID is required.
    You can retrieve CustomerId but not showing the value in the grid. Set DataKeyNames of GridView as CustomerId.
    To get the value of DataKey (CustomerId):

    string customerId = MyGridView.DataKeys[editItemIndex].Value.ToString();

     

    Monday, January 30, 2012 10:21 PM
  • User1407015516 posted

    Error at EditItemIndex

    Monday, January 30, 2012 11:41 PM
  • User-2001765250 posted

    Add DataKeyNames proerty of gridview to CustomerId.

    And at GridView_RowUpdating event add

      int cid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);//This is CustomerId in your Case.

    pass it as a customerId parameter value.

    Refer the Link

    http://www.google.com/support/forum/p/blogger/thread?tid=78e601cc3a2d17d8&hl=en

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 31, 2012 12:26 AM
  • User1407015516 posted

    Can i work with these rather using Button?? then how?

    <asp:CommandField ShowEditButton="True" />
                        <asp:CommandField ShowDeleteButton="True" />

     

    Tuesday, January 31, 2012 12:42 AM