How to check each row of data in view state grid view in C#

caulson

SOLVED CLOSED ACTIVE ANSWERED
User: caulson
Posted: on Jan 09, 2014 10:48 PM
Forum: Data Controls
Answer: 1
Views: 7845

Dear All,

May i know how to write the condition in C# check each row of data in view state gridview ?

Based on the below code statement , it will check for the first add in row from DB. if not exist, then will pop up " "Message", "alert(' " + Item + " not exists in Location-[" + FromLocation + "], please check again!!"

How if add more than one row in view state gridview, and check it one by one, if not exist,then highlight the row record ?

  protected void Add_Click(object sender, EventArgs e)
        {

            GridViewMMSI.Visible = true;

            if (ViewState["CurrentTable"] == null)
            {
                int NewTransID = Convert.ToInt32(ViewState["Trans_ID"]) + 1;
                ViewState["Trans_ID"] = NewTransID;
                DataTable dt = new DataTable();
                dt.Columns.Add(new System.Data.DataColumn("Trans_ID", typeof(String))); // 1
                dt.Columns.Add(new System.Data.DataColumn("InvID", typeof(String))); // 1
                dt.Columns.Add(new System.Data.DataColumn("Location", typeof(String)));// 4
                dt.Columns.Add(new System.Data.DataColumn("Quantity", typeof(String)));// 8
                dt.Columns.Add(new System.Data.DataColumn("Requestor", typeof(String)));// 6
                dt.Columns.Add(new System.Data.DataColumn("IssueStatus", typeof(String)));// 6
                dt.Columns.Add(new System.Data.DataColumn("Ref_Number", typeof(String)));// 7
                dt.Columns.Add(new System.Data.DataColumn("TransactionRemark", typeof(String))); //11
                dt.Columns.Add(new System.Data.DataColumn("ReasonID", typeof(String)));// 9
                dt.Columns.Add(new System.Data.DataColumn("ReasonRemark", typeof(String))); //10
                dt.Columns.Add(new System.Data.DataColumn("CreateUser", typeof(String))); //12
                dt.Columns.Add(new System.Data.DataColumn("CreateDate", typeof(String))); //13
                dt.Columns.Add(new System.Data.DataColumn("ToLocation", typeof(String))); //11

                dt.Rows.Add(NewTransID, this.Inv_ID.Text, this.Inv_FromLoc.SelectedValue, this.Inv_Qty.Text, this.Inv_Requestor.Text, this.Inv_IssueID.SelectedValue, this.Inv_Ref.Text, this.Inv_Remark.Text, this.Inv_ReasonID.SelectedValue, this.Inv_ReasonRemark.Text, this.MMSCUSER.Text, this.MMSCDATE.Text, this.Inv_ToLoc.SelectedValue);
                ViewState["CurrentTable"] = dt;
                this.GridView1.DataSource = dt;
                this.GridView1.DataBind();
            }
            else
            {
                int NewTransID = Convert.ToInt32(ViewState["Trans_ID"]) + 1;
                ViewState["Trans_ID"] = NewTransID;
                DataTable dt2 = (DataTable)ViewState["CurrentTable"];
                int count = dt2.Rows.Count;
                dt2.Rows.Add(NewTransID, this.Inv_ID.Text, this.Inv_FromLoc.SelectedValue, this.Inv_Qty.Text, this.Inv_Requestor.Text, this.Inv_Ref.Text, this.Inv_Remark.Text, this.Inv_ReasonID.SelectedValue, this.Inv_ReasonRemark.Text, this.MMSCUSER.Text, this.MMSCDATE.Text, this.Inv_ToLoc.SelectedValue);


                this.GridView1.DataSource = dt2;
                this.GridView1.DataBind();
                ViewState["CurrentTable"] = dt2;
            }


            Inv_Qty.Text = string.Empty;
            Inv_Ref.Text = string.Empty;
            Inv_Requestor.Text = string.Empty;
            Inv_ReasonRemark.Text = string.Empty;
            Inv_Remark.Text = string.Empty;


            Inv_Qty.Focus();
            Inv_Ref.Focus();
            Inv_Requestor.Focus();
            Inv_ReasonRemark.Focus();
            Inv_Remark.Focus();


        }






        protected void Submit_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)ViewState["CurrentTable"];

            int TransID;


            string Item, FromLocation, Qty, Requestor, IssueStatus, RefNum, Remark, ReasonID, ReasonRemark, CreateDate, CreateUser, ToLocation;


            foreach (DataRow row in dt.Rows)
            {

                TransID        = int.Parse(row["Trans_ID"].ToString());
                Item           = row["InvID"].ToString();
                FromLocation   = row["Location"].ToString();
                Qty            = row["Quantity"].ToString();
                Requestor      = row["Requestor"].ToString();
                IssueStatus    = row["IssueStatus"].ToString();
                RefNum         = row["Ref_Number"].ToString();
                Remark         = row["TransactionRemark"].ToString();
                ReasonID       = row["ReasonID"].ToString();
                ReasonRemark   = row["ReasonRemark"].ToString();
                CreateDate     = row["CreateDate"].ToString();
                CreateUser     = row["CreateUser"].ToString();
                ToLocation     = row["ToLocation"].ToString();


                this.insertData(TransID, Item, FromLocation, Qty, Requestor, IssueStatus,  RefNum, Remark, ReasonID, ReasonRemark, CreateDate, CreateUser, ToLocation);


            }

        }



        private void insertData(int TransID, string Item, string FromLocation, string Qty, string Requestor, string IssueStatus, string RefNum, string Remark, string ReasonID, string ReasonRemark, string CreateDate, string CreateUser, string ToLocation)
        {



            string constr = ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString;
            using (SqlConnection _cn = new SqlConnection(constr))
            {
                using (SqlCommand _cmd = new SqlCommand("MMSIssue_InsertOrUpdate", _cn))
                {


                    using (SqlDataAdapter da = new SqlDataAdapter(_cmd))
                    {
                        _cn.Open();
                        _cmd.CommandType = CommandType.StoredProcedure;
                        _cmd.Parameters.AddWithValue("@INV_TRANS_ID", TransID);
                        _cmd.Parameters.AddWithValue("@INV_ID", Item);
                        _cmd.Parameters.AddWithValue("@INV_LOCATION", FromLocation);
                        _cmd.Parameters.AddWithValue("@INV_QTY", Qty);
                        _cmd.Parameters.AddWithValue("@INV_TRANS_REQUESTOR", Requestor);
                        _cmd.Parameters.AddWithValue("@INV_TRANS_SPEC",IssueStatus);
                        _cmd.Parameters.AddWithValue("@INV_TRANS_REFNO", RefNum);
                        _cmd.Parameters.AddWithValue("@INV_TRANS_REMARK", Remark);
                        _cmd.Parameters.AddWithValue("@INV_REASON_ID", ReasonID);
                        _cmd.Parameters.AddWithValue("@INV_REASON_REMARK", ReasonRemark);
                        _cmd.Parameters.AddWithValue("@INV_CREATE_DATE", CreateDate);
                        _cmd.Parameters.AddWithValue("@INV_CREATE_USER", CreateUser);
                        _cmd.Parameters.AddWithValue("@INV_FROMLOC", ToLocation);
                        _cmd.Parameters.Add("@RecordFound", SqlDbType.Int, 0);
                        _cmd.Parameters["@RecordFound"].Direction = ParameterDirection.Output;
                        _cmd.ExecuteNonQuery();

                     

                        _cn.Close();


                    }



                    string check_existsqty = ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString;
                    using (SqlConnection _cn1 = new SqlConnection(check_existsqty))
                    {
                        using (SqlCommand _cmd1 = new SqlCommand("SELECT  INV_ID,INV_LOCATION,INV_QTY FROM OTH_INV_QTY_LOC WHERE INV_ID=@INV_ID AND INV_LOCATION = @INV_LOCATION", _cn1))
                        {


                            using (SqlDataAdapter da = new SqlDataAdapter(_cmd1))
                            {



                                _cmd1.Parameters.AddWithValue("@INV_ID", Item);
                                _cmd1.Parameters.AddWithValue("@INV_LOCATION", FromLocation);
                                _cmd1.Parameters.AddWithValue("@INV_QTY", Qty);

                                try
                                {
                                    _cn1.Open();
                                    object obj = _cmd1.ExecuteScalar();


                                
                                        if (obj == null || obj == DBNull.Value)
                                        {


                                            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert(' " + Item + " not exists in Location-[" + FromLocation + "], please check again!!')", true);


                                        }


                                    }
                                


                                catch
                                {


                                }

                                finally
                                {
                                    string DeleteWMMRSQL = "DELETE FROM [CIMProRPT01].[dbo].[OTH_INV_QTY_LOC] WHERE INV_QTY = 0 OR INV_QTY is null";
                                    SqlCommand cmd3 = new SqlCommand(DeleteWMMRSQL, _cn1);
                                    cmd3.ExecuteNonQuery();
                                    _cn1.Close();

                                }





                                float INV_QTY = Convert.ToInt32(_cmd.Parameters["@RecordFound"].Value.ToString());
                                if (INV_QTY == 2)
                                {


                                    ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('" + Item + " Qty in Location-[" + FromLocation + "] is more than On hand Qty, Please Check Again!!')", true);




                                }



                                else
                                {
                                    ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Issues Transaction completed');window.location.href = 'MMS_Issue.aspx';", true);



                                }




                            }

                        }
                    }

                }

            }

        }

Azim

0 5

Reply
ANSWER
User: Azim
Replied: on Jan 10, 2014 02:48 AM
Modified: on Jan 10, 2014 02:49 AM
Report

Please refer this example:

HTML:

<form id="form1" runat="server">
    <div>
        Enter Name
        <asp:TextBox ID="txtName" runat="server" />
        <br />
        Enter City
        <asp:TextBox ID="txtCity" runat="server" />
        <br />
        <asp:Button ID="btnAdd" Text="Add" OnClick="Add" runat="server" />
        <asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_OnRowDataBound"
            AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField DataField="Id" HeaderText="Id" />
                <asp:BoundField DataField="Name" HeaderText="Name" />
                <asp:BoundField DataField="City" HeaderText="City" />
            </Columns>
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" OnClick="Save" Text="Save" />
    </div>
    </form>

C#:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            this.GetMaxId();
            this.GetPersons();
        }
    }

    private void GetPersons()
    {
        string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Person", con))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        this.GridView1.DataSource = dt;
                        this.GridView1.DataBind();
                        ViewState["CurrentTable"] = dt;
                    }


                }
            }
        }
    }

    private void GetMaxId()
    {
        string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT MAX(Id) AS ID FROM Person", con))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        string str = string.Empty;
                        if (str != dt.Rows[0]["ID"].ToString())
                        {
                            int id = Convert.ToInt32(dt.Rows[0]["ID"].ToString());
                            ViewState["Id"] = id;
                        }
                    }
                }
            }
        }
    }

    protected void Add(object sender, EventArgs e)
    {
        if (ViewState["CurrentTable"] == null)
        {
            int id = Convert.ToInt32(ViewState["Id"]) + 1;
            ViewState["Id"] = id;
            DataTable dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("City", typeof(string));
            dt.Rows.Add(id, this.txtName.Text, this.txtCity.Text);
            ViewState["CurrentTable"] = dt;
            this.GridView1.DataSource = dt;
            this.GridView1.DataBind();
        }
        else
        {
            int id = Convert.ToInt32(ViewState["Id"]) + 1;
            ViewState["Id"] = id;
            DataTable dt2 = (DataTable)ViewState["CurrentTable"];
            dt2.Rows.Add(this.txtName.Text, id, this.txtCity.Text);
            this.GridView1.DataSource = dt2;
            this.GridView1.DataBind();
            ViewState["CurrentTable"] = dt2;
        }
    }

    protected void Save(object sender, EventArgs e)
    {
        DataTable dt = (DataTable)ViewState["CurrentTable"];
        string name, city;
        int id;
        foreach (DataRow row in dt.Rows)
        {
            name = row["Name"].ToString();
            city = row["City"].ToString();
            id = int.Parse(row["Id"].ToString());
            this.InsertRows(name, city, id);
        }
    }

    private void InsertRows(string name, string city, int id)
    {
        string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("Insert_Person", con))
            {
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@City", city);
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    protected void GridView1_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            int id = int.Parse(e.Row.Cells[0].Text);
            string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT * FROM Person Where Id = @Id", con))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        cmd.Parameters.AddWithValue("@Id", id);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        if (dt.Rows.Count <= 0)
                        {
                            e.Row.BackColor = Color.Red;
                        }
                    }
                }
            }
        }
    }

SQL:

CREATE PROCEDURE Insert_Person
    @Id INT,
    @Name VARCHAR(50),
    @City VARCHAR(50)
AS
BEGIN  
    SET NOCOUNT ON;
    IF NOT EXISTS(SELECT * FROM Person WHERE Id = @Id)
    BEGIN
        INSERT INTO Person (Id,Name,City) VALUES(@Id,@Name,@City)
    END   
END
GO

CREATE TABLE [dbo].[Person](
	[name] [varchar](50) NULL,
	[Id] [int] NULL,
	[City] [varchar](50) NULL
) ON [PRIMARY]

GO

NameSpace:

using System.Configuration;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;

Thank You.

How to check each row of data in view state grid view in C#

Company

Explore

Follow us

Contact Us