locked
Updating problem in GridView RRS feed

  • Question

  • User908689340 posted

     Hi all,

    I have taken a gridview whenever I open the page which has gridview displays the gridview with all the previous values retrieved from the database table.

    There will be a update button. I can change the every row of the Gridview row. Whenever i click the update button that must be updated in Gridview and database.

    please tell me how to do that. Here I am providing the code:

    please help and tell me how to identify the row which has modified and how to update that row. 

     

     

    using System;
    using System.Data;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using System.Data.SqlClient;
    using System.Text;
    public partial class AcademicAffairs_SubjectManagement : System.Web.UI.Page
    {
        private bool isEditMode = true;
        string updatequery = "";
        string selectquery = "";
        String sConnStrMAIN = "GRIET_MAINConnectionString";
        String sConnStrIT = "GRIET_ITConnectionString";
        String sConnStrCSE = "GRIET_CSEConnectionString";
        String sConnStrECE = "GRIET_ECEConnectionString";
        String sConnStrEEE = "GRIET_EEEConnectionString";
        String sConnStrMECH = "GRIET_MECHConnectionString";
        String sConnStrBME = "GRIET_BMEConnectionString";
        String sConnStrBT = "GRIET_BTConnectionString";
        string sconnstrDept;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                int deptcode = System.Convert.ToInt32(Request.QueryString["Dept"]);
                if (deptcode == 12)
                {
                    sconnstrDept = sConnStrIT;
                    dept.Text = "Department of Information Technology";
                }
                else if (deptcode == 5)
                {
                    sconnstrDept = sConnStrCSE;
                    dept.Text = "Department of Computer Science Engineering";
                }
                else if (deptcode == 3)
                {
                    sconnstrDept = sConnStrMECH;
                    dept.Text = "Department of Mechanical Engineering";
                }
                else if (deptcode == 4)
                {
                    sconnstrDept = sConnStrECE;
                    dept.Text = "Department of Electronics & Communications Engineering";
                }
                else if (deptcode == 2)
                {
                    sconnstrDept = sConnStrEEE;
                    dept.Text = "Department of Electrical & Electronics Engineering";
                }
                else if (deptcode == 23)
                {
                    sconnstrDept = sConnStrBT;
                    dept.Text = "Department of Bio-Technology";
                }
                else if (deptcode == 11)
                {
                    sconnstrDept = sConnStrBME;
                    dept.Text = "Department of Bio-medical Engineering";
                }
                BindData();
    
            }
        }
        private void BindData()
        {
            int i = 0;
            int year = System.Convert.ToInt32(Request.QueryString["year"]);
            int sem = System.Convert.ToInt32(Request.QueryString["sem"]);
            string regcode = System.Convert.ToString(Request.QueryString["RegCode"]);
            string temp = department();
    
            Status.Text = "Subjects management for " + System.Convert.ToString(year) + " year & " + sem + " Sem  <br/>";
            Status.Text = Status.Text + "For the Regulation " + Convert.ToString(regcode);
            selectquery = "Select * from Sub_code where Year=" + Convert.ToString(year) + " and Sem=" + Convert.ToString(sem) + " and Regulation_Code='" + Convert.ToString(regcode)+"'";
    
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[temp].ConnectionString);
            SqlDataAdapter ad = new SqlDataAdapter(selectquery, myConnection);
            DataSet ds = new DataSet();
            ad.Fill(ds);
            Subjects.DataSource = ds;
            Subjects.DataBind();
            myConnection.Close();
            
        }
        protected bool IsInEditMode
        {
    
            get { return this.isEditMode; }
    
            set { this.isEditMode = value; }
    
        }
        protected void UpdateBtn_Click(object sender, EventArgs e)
        {
            string query = "";
            string temp = department();
            int year = System.Convert.ToInt32(Request.QueryString["year"]);
            int sem = System.Convert.ToInt32(Request.QueryString["sem"]);
            string regcode = System.Convert.ToString(Request.QueryString["RegCode"]);
            foreach (GridViewRow row in Subjects.Rows)
            {
                query = "UPDATE [Sub_Code] SET [Sub_Code] = " + Convert.ToInt32((row.FindControl("txtSubCode") as TextBox).Text) + ",";
    
                query = query + "[Sub_Code_Jntu] = '" + (row.FindControl("txtJntuSubCode") as TextBox).Text.ToUpper()  + "',";
                query = query + "[Sub_Name] = '" + (row.FindControl("txtSubName") as TextBox).Text.ToUpper()  + "',";
                query = query + "[Max_Marks] = " + (row.FindControl("txtMax") as TextBox).Text + ",";
                query = query + "[Min_Marks] = " + (row.FindControl("txtMin") as TextBox).Text + ",";
                query = query + "[Sub_Type] = '" + (row.FindControl("txtSubType") as TextBox).Text.ToUpper()  + "',";
                query = query + "[Sub_Credits] = " + (row.FindControl("txtCredits") as TextBox).Text;
    
                query = query + " where [Sub_Code] = " + Convert.ToString((row.FindControl("txtSubCode") as TextBox ).Text);
                query = query + " and [Year] = " + Convert.ToString (year) +" and [sem] = "+ Convert.ToString (sem) + " and [Regulation_code]="+ Convert.ToString(regcode) ;
    
                SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[temp].ConnectionString);
                SqlCommand myCommand = new SqlCommand(query, myConnection);
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
            BindData();
            Response.Write("&lt;script>alert('Successfull Entry into the Database')</script>");
        }
        private string department()
        {
            int deptcode = System.Convert.ToInt32(Request.QueryString["Dept"]);
            if (deptcode == 12)
            {
                sconnstrDept = sConnStrIT;
                dept.Text = "Department of Information Technology";
            }
            else if (deptcode == 5)
            {
                sconnstrDept = sConnStrCSE;
                dept.Text = "Department of Computer Science Engineering";
            }
            else if (deptcode == 3)
            {
                sconnstrDept = sConnStrMECH;
                dept.Text = "Department of Mechanical Engineering";
            }
            else if (deptcode == 4)
            {
                sconnstrDept = sConnStrECE;
                dept.Text = "Department of Electronics & Communications Engineering";
            }
            else if (deptcode == 2)
            {
                sconnstrDept = sConnStrEEE;
                dept.Text = "Department of Electrical & Electronics Engineering";
            }
            else if (deptcode == 23)
            {
                sconnstrDept = sConnStrBT;
                dept.Text = "Department of Bio-Technology";
            }
            else if (deptcode == 11)
            {
                sconnstrDept = sConnStrBME;
                dept.Text = "Department of Bio-medical Engineering";
            }
            return sconnstrDept;
        }
        protected void Insert_Click(object sender, EventArgs e)
        {
            string temp = department();
            int year = System.Convert.ToInt32(Request.QueryString["year"]);
            int sem = System.Convert.ToInt32(Request.QueryString["sem"]);
            string regcode = System.Convert.ToString(Request.QueryString["RegCode"]);
            int deptcode = System.Convert.ToInt32(Request.QueryString["Dept"]);
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[temp].ConnectionString))
            {
                try
                {
                    SqlCommand cmdTest = new SqlCommand("SubCodes", connection);
                    cmdTest.CommandType = CommandType.StoredProcedure;
                    cmdTest.Parameters.Add ("@SubCode",SqlDbType.Char ).Value =Convert.ToString( InsSubCode.Text);
                     cmdTest.Parameters.Add ("@SubName",SqlDbType.Char ).Value = Convert.ToString( InsSubName.Text);
                     cmdTest.Parameters.Add("@JntuSubCode", SqlDbType.Char).Value = Convert.ToString(InsJntuSubCode.Text);
                     cmdTest.Parameters.Add("@RegCode", SqlDbType.Char).Value = regcode;
                     cmdTest.Parameters.Add("@DeptCode", SqlDbType.Int).Value = deptcode;
                     cmdTest.Parameters.Add("@Max", SqlDbType.Int).Value = Convert.ToInt32(InsMaxMarks.Text);
                     cmdTest.Parameters.Add("@Min", SqlDbType.Int).Value = Convert.ToInt32(InsMinMarks.Text);
                     cmdTest.Parameters.Add("@Year", SqlDbType.Int).Value = year;
                     cmdTest.Parameters.Add("@SubType", SqlDbType.Char).Value = Convert.ToString(InsSubType.Text);
                     cmdTest.Parameters.Add("@SubCredits", SqlDbType.Int).Value = Convert.ToInt32(InsSubCredits.Text);
                     cmdTest.Parameters.Add("@Sem", SqlDbType.Int).Value = sem;
                    connection.Open();
                    int rows = cmdTest.ExecuteNonQuery();
                    
                    connection.Close();
                    if (rows > 0)
                    {
                        Response.Write("&lt;script>alert('Successfull Entry into the Database')</script>");
                        BindData();
                    }
                    else
                        Response.Write("&lt;script>alert('Failure Try Again')</script>");
                    
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
        }
    }
    
      

     

     

    Thursday, November 1, 2007 12:44 AM

Answers

  • User-874886737 posted

    It means that when updating, you are trying to set a value of 'RR        ' (a varchar value) in a field that has the data type int.

    Either you are updating with a wrong value, or you are updating in the wrong column.

    Jos

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 1, 2007 11:44 AM
  • User1223819962 posted

    HI

    I think you tring to update field which is int type and u try to insert varchar type in it.so

    Check Field type in sql server table Defination or insert only int value

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 2, 2007 1:00 AM

All replies

  • User-874886737 posted

    This is a common question. Try Google:

    http://www.google.com/search?q=gridview+editing+multiple+rows

    Jos

    Thursday, November 1, 2007 5:13 AM
  • User1223819962 posted

    Check this

     

    <asp:GridView ID="gvUsers" AutoGenerateColumns="false" runat="server" OnRowDataBound="gvUsers_RowDataBound">

     

    <Columns>

     

    <asp:TemplateField HeaderText="User ID">

    <ItemTemplate>

    <asp:Label ID="lblUserID" runat="server" Text='<%# Eval("UserID") %>' />

    </ItemTemplate>

     

    </asp:TemplateField>

     

    <asp:TemplateField HeaderText="First Name">

    <ItemTemplate>

    <asp:Label ID="lblFirstName" Visible='<%# !(bool) IsInEditMode %>' runat="server" Text='<%# Eval("FirstName") %>' />

    <asp:TextBox ID="txtFirstName" Visible='<%# IsInEditMode %>' runat="server" Text='<%# Eval("FirstName") %>' />

     

    </ItemTemplate>

     

    </asp:TemplateField>

     

    <asp:TemplateField HeaderText="Last Name">

     

    <ItemTemplate>

    <asp:Label ID="lblLastName" Visible='<%# !(bool) IsInEditMode %>' runat="server" Text='<%# Eval("LastName") %>' />

     

    <asp:TextBox ID="txtLastName" Visible='<%# IsInEditMode %>' runat="server" Text='<%# Eval("LastName") %>' />

     

    </ItemTemplate>

     

    </asp:TemplateField>

     

    </Columns>

     

    </asp:GridView>

     

     

    Code Side:

     

    private bool isEditMode = false;

    protected void Page_Load(object sender, EventArgs e)

    {

    if (!Page.IsPostBack)

    {

    BindData();

    }

    }

     

    private void BindData()

    {

    string connectionString = "Server=localhost;Database=School;Trusted_Connection=true";

    SqlConnection myConnection = new SqlConnection(connectionString);

    SqlDataAdapter ad = new SqlDataAdapter("SELECT UserID, FirstName, LastName FROM Users", myConnection);

    DataSet ds = new DataSet();

    ad.Fill(ds);

    gvUsers.DataSource = ds;

    gvUsers.DataBind();

    }

    // This method will put the GridView in the edit mode

    protected void Button1_Click(object sender, EventArgs e)

    {

    isEditMode = true;

    BindData();

    }

    protected bool IsInEditMode

    {

    get { return this.isEditMode; }

    set { this.isEditMode = value; }

    }

    protected void Button2_Click(object sender, EventArgs e)

    {

    isEditMode = false;

    BindData();

    }

     

     

     

    When the button is clicked I simply change the isEditMode to true or depending depending that if I want to view the GridView in edit mode or view mode.

    Hope this will help You 

     

    Thursday, November 1, 2007 5:30 AM
  • User908689340 posted

    Can anyone tell me what this exception means:

     

    The exception is:

    System.Data.SqlClient.SqlException was caught
      Message="Conversion failed when converting the varchar value 'RR        ' to data type int."
      Source=".Net SqlClient Data Provider"
      ErrorCode=-2146232060his exception
      Class=16
      LineNumber=1
      Number=245
      Procedure=""
      Server="\\\\.\\pipe\\D77A9EAF-457A-4F\\tsql\\query"
      State=1
      StackTrace:
           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
           at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
           at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
           at AcademicAffairs_SubjectManagement.UpdateBtn_Click(Object sender, EventArgs e) in d:\GCAP\AcademicAffairs\SubjectManagement.aspx.cs:line 127
     

    Thursday, November 1, 2007 6:47 AM
  • User-874886737 posted

    It means that when updating, you are trying to set a value of 'RR        ' (a varchar value) in a field that has the data type int.

    Either you are updating with a wrong value, or you are updating in the wrong column.

    Jos

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 1, 2007 11:44 AM
  • User908689340 posted

    Hi Jos Branders How are you?

    What you said is right now I changed the value to string.

    I make the query as:

     UPDATE    Sub_Code
    SET Sub_Code = 3204, Sub_Code_Jntu = 'EE05321', Sub_Name = 'INSTRUMENTATIONSSS', Max_Marks = 100, Min_Marks = 40, Sub_Type = 'THEORY', Sub_Credits = 4
    WHERE     (Sub_Code = 3204) AND (Year = 3) AND (Sem = 2) AND (Regulation_Code = '05')

     

    Here I changed the value with single quotations It worked well.

    But it is not updating in the database.Please tell if i written the correct query or not.

    Regards,

    Bharath 

    Friday, November 2, 2007 12:27 AM
  • User1223819962 posted

    HI

    I think you tring to update field which is int type and u try to insert varchar type in it.so

    Check Field type in sql server table Defination or insert only int value

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 2, 2007 1:00 AM
  • User-1752241410 posted

    DIm cmd as New SqlCommand

    cmd.connection=con

    '''''' Sql Connection Note that sql connection must be open

     cmd.commandText="update Statememt"

    cmd.executeNonQuery

    Note that to reflect changes in  the datagrid u have to bind It again....

    Hope this helps u.... 

     

    Friday, November 2, 2007 1:00 AM
  • User-874886737 posted

    As far as I can see (I don't know what your table looks like) this query is correct.

    If you don't get an error, and it is not updating, maybe there is no record with Sub_Code 3204, year 3, Sem 2 and Regulation_Code '05'.

    Jos

    Friday, November 2, 2007 7:19 AM
  • User-1132163444 posted

     hi,

    i have a question about the code that you just gave,

    what code should i add in the code behind if ever the values that will populate the gridview  will be a query from a dropdown list?

    example,

    dropdown list --->1 Man

                              2 Cow

                               3 Dog

     

    If 2 is selected, an info about the "Cow" will be shown in the gridview and the fields are Edit, Insert, Delete enabled.

     

    please help, thanks
     

    Monday, January 28, 2008 9:56 PM