Dear All,
May i know how to write a code to check the Transaction ID from SQL Table in view state gridview for each row data ?
For Example, select transID from Table.Current ID is 14481.
So now when i add in the view state grid view, it show the same trans number
Is it any method to write the + values in add row function in c# to increase the values ? as below is the c# code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;
using System.IO;
namespace acmkweb.MM
{
public partial class MMS_LocationTrans : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SystemDate();
TransID();
if (!Page.IsPostBack)
{
QueryDetTBL.Visible = false;
GridViewMMSLocTrans.Visible = false;
FillReasonID();
FillFromLocation();
FillToLocation();
DateTypeRD_Select();
}
ScriptManager scriptManager = ScriptManager.GetCurrent(this.Page);
}
protected void TransID()
{
string UniIDSQL = "SELECT ISNULL(MAX(CAST(INV_TRANS_ID AS INT)),0) AS TRANSID FROM CIMProRPT01.dbo.OTH_INV_TRANSACTION";
using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(UniIDSQL, con))
{
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
MMSNewID.Text = reader["TRANSID"].ToString();
reader.Close();
}
}
catch (Exception err)
{
lblResults.Text = "Error getting Unique ID ";
lblResults.Text += err.Message;
}
con.Close();
}
}
}
private void SystemDate()
{
MMSCDATE.Text = "";
MMSCUSER.Text = "";
MembershipUser currentUser = Membership.GetUser();
string selectSQL = "SELECT GETDATE() AS SYSTEMDATE";
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
MMSCDATE.Text = reader["SYSTEMDATE"].ToString();
MMSCUSER.Text = currentUser.ToString();
}
reader.Close();
}
catch (Exception err)
{
lblResults.Text = "Error getting System Date ";
lblResults.Text += err.Message;
}
finally
{
con.Close();
}
}
private void FillReasonID()
{
Inv_ReasonID.Items.Clear();
string selectSQL = "SELECT INV_REASON_ID,INV_REASON_DESC,INV_REASON_TYPE FROM [CIMProRPT01].[dbo].[OTH_INV_REASON] WHERE INV_REASON_ID = 'Transfer'";
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
Inv_ReasonID.Items.Add("--Select Type--");
while (reader.Read())
{
ListItem newItem = new ListItem();
newItem.Text = reader["INV_REASON_ID"].ToString() + " - " + reader["INV_REASON_DESC"].ToString(); ;
newItem.Value = reader["INV_REASON_ID"].ToString();
Inv_ReasonID.Items.Add(newItem);
}
reader.Close();
}
catch (Exception err)
{
lblResults.Text = "Error reading list of Transaction Type. ";
lblResults.Text += err.Message;
}
finally
{
con.Close();
}
}
private void FillFromLocation()
{
Inv_FromLoc.Items.Clear();
string selectSQL = "SELECT INV_LOCATION,INV_DESC FROM [CIMProRPT01].[dbo].[OTH_INV_LOCATION]";
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
Inv_FromLoc.Items.Add("--Select Type--");
while (reader.Read())
{
ListItem newItem = new ListItem();
newItem.Text = reader["INV_LOCATION"].ToString();
newItem.Value = reader["INV_LOCATION"].ToString();
Inv_FromLoc.Items.Add(newItem);
}
reader.Close();
}
catch (Exception err)
{
lblResults.Text = "Error reading list of Transaction Type. ";
lblResults.Text += err.Message;
}
finally
{
con.Close();
}
}
private void FillToLocation()
{
Inv_ToLoc.Items.Clear();
string selectSQL = "SELECT INV_LOCATION,INV_DESC FROM [CIMProRPT01].[dbo].[OTH_INV_LOCATION]";
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
Inv_ToLoc.Items.Add("--Select Type--");
while (reader.Read())
{
ListItem newItem = new ListItem();
newItem.Text = reader["INV_LOCATION"].ToString();
newItem.Value = reader["INV_LOCATION"].ToString();
Inv_ToLoc.Items.Add(newItem);
}
reader.Close();
}
catch (Exception err)
{
lblResults.Text = "Error reading list of Transaction Type. ";
lblResults.Text += err.Message;
}
finally
{
con.Close();
}
}
protected void DateTypeRD_Select()
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT INV_TYPE FROM CIMProRPT01.dbo.OTH_INV_CATEGORY ORDER BY INV_TYPE DESC", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
Inv_Type.DataSource = ds;
Inv_Type.DataTextField = "INV_TYPE";
Inv_Type.DataValueField = "INV_TYPE";
Inv_Type.DataBind();
Inv_Type.Items.Insert(0, new ListItem("--Select--", "0"));
}
protected void Item_SelectedIndexChanged(object sender, EventArgs e)
{
QueryDetTBL.Visible = true;
string InvType = Inv_Type.SelectedValue;
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT INV_ID,INV_TYPE + '-' + INV_SHORTDESC AS DESCRIPTION FROM CIMProRPT01.dbo.OTH_INV_DETAILS WHERE INV_TYPE ='" + InvType + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
// DataSet ds = new DataSet();
DataTable ds = new DataTable();
da.Fill(ds);
Inv_ID.DataSource = ds;
Inv_ID.DataTextField = "INV_ID";
Inv_ID.DataValueField = "INV_ID";
con.Close();
Inv_ID.DataBind();
Inv_ID.Items.Insert(0, new ListItem("--Select--", "0"));
if (Inv_ID.SelectedValue == "0")
{
// Inv_Vendorname.Items.Clear();
// Inv_Vendorname.Items.Insert(0, new ListItem("--Select--", "0"));
}
}
protected void Vendor_SelectedIndexChanged(object sender, EventArgs e)
{
string InvID = Inv_ID.SelectedValue;
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT INV_ID,INV_DESC,INV_UNIT, ALLVENDOR FROM ( SELECT INV_ID,INV_DESC,INV_UNIT,INV_VENDOR1 AS ALLVENDOR FROM OTH_INV_DETAILS UNION ALL SELECT INV_ID, INV_DESC, INV_UNIT,INV_VENDOR2 AS ALLVENDOR FROM OTH_INV_DETAILS UNION ALL SELECT INV_ID, INV_DESC, INV_UNIT,INV_VENDOR3 AS ALLVENDOR FROM OTH_INV_DETAILS UNION ALL SELECT INV_ID, INV_DESC,INV_UNIT, INV_VENDOR4 AS ALLVENDOR FROM OTH_INV_DETAILS UNION ALL SELECT INV_ID, INV_DESC,INV_UNIT, INV_VENDOR5 AS ALLVENDOR FROM OTH_INV_DETAILS)VENDOR WHERE INV_ID = '" + InvID + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "[CIMProRPT01].[dbo].[OTH_INV_DETAILS]");
con.Close();
// Inv_Vendorname.DataSource = ds;
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow Dr in ds.Tables[0].Rows)
{
//Inv_Vendorname.Items.Add(new ListItem(Dr["ALLVENDOR"].ToString()));
Inv_Desc.Text = Dr["INV_DESC"].ToString();
Inv_Unit.Text = Dr["INV_UNIT"].ToString();
}
}
}
private void BindGrid(int rowcount)
{
DataTable dt = new DataTable();
DataRow dr;
dt.Columns.Add(new System.Data.DataColumn("Trans_ID", typeof(String))); // 1
dt.Columns.Add(new System.Data.DataColumn("INV_ID", 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("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
if (ViewState["CurrentData"] != null)
{
for (int i = 0; i < rowcount + 1; i++)
{
dt = (DataTable)ViewState["CurrentData"];
if (dt.Rows.Count > 0)
{
dr = dt.NewRow();
dr[0] = dt.Rows[0][0].ToString();
}
}
dr = dt.NewRow();
dr[0] = MMSNewID.Text;
dr[1] = Inv_ID.SelectedValue;
dr[2] = Inv_FromLoc.SelectedValue;
dr[3] = Inv_Qty.Text;
dr[4] = Inv_Ref.Text;
dr[5] = Inv_Remark.Text;
dr[6] = Inv_ReasonID.SelectedValue;
dr[7] = Inv_ReasonRemark.Text;
dr[8] = MMSCDATE.Text;
dr[9] = MMSCUSER.Text;
dr[10] = Inv_ToLoc.SelectedValue;
dt.Rows.Add(dr);
}
else
{
dr = dt.NewRow();
dr[0] = MMSNewID.Text;
dr[1] = Inv_ID.SelectedValue;
dr[2] = Inv_FromLoc.SelectedValue;
dr[3] = Inv_Qty.Text;
dr[4] = Inv_Ref.Text;
dr[5] = Inv_Remark.Text;
dr[6] = Inv_ReasonID.SelectedValue;
dr[7] = Inv_ReasonRemark.Text;
dr[8] = MMSCDATE.Text;
dr[9] = MMSCUSER.Text;
dr[10] = Inv_ToLoc.SelectedValue;
dt.Rows.Add(dr);
}
// If ViewState has a data then use the value as the DataSource
if (ViewState["CurrentData"] != null)
{
GridView1.DataSource = (DataTable)ViewState["CurrentData"];
GridView1.DataBind();
}
else
{
// Bind GridView with the initial data assocaited in the DataTable
GridView1.DataSource = dt;
GridView1.DataBind();
}
// Store the DataTable in ViewState to retain the values
ViewState["CurrentData"] = dt;
}
protected void Add_Click(object sender, EventArgs e)
{
GridViewMMSLocTrans.Visible = true;
// Check if the ViewState has a data assoiciated within it. If
if (ViewState["CurrentData"] != null)
{
DataTable dt = (DataTable)ViewState["CurrentData"];
int count = dt.Rows.Count;
BindGrid(count);
}
else
{
BindGrid(1);
}
// Inv_Qty.Text = string.Empty;
// Inv_Requestor.Text = string.Empty;
Inv_Ref.Text = string.Empty;
Inv_ReasonRemark.Focus();
Inv_Remark.Focus();
// Inv_Qty.Focus();
// Inv_Requestor.Focus();
Inv_Ref.Focus();
Inv_ReasonRemark.Focus();
Inv_Remark.Focus();
}
protected void Submit_Click(object sender, EventArgs e)
{
foreach (GridViewRow oItem in GridView1.Rows)
{
string TransID = oItem.Cells[1].Text;
string Item = oItem.Cells[2].Text;
string FromLocation = oItem.Cells[3].Text;
string Qty = oItem.Cells[4].Text;
string RefNum = oItem.Cells[5].Text;
string Remark = oItem.Cells[6].Text;
string ReasonID = oItem.Cells[7].Text;
string ReasonRemark = oItem.Cells[8].Text;
string CreateDate = oItem.Cells[9].Text;
string CreateUser = oItem.Cells[10].Text;
string ToLocation = oItem.Cells[11].Text;
insertData(TransID, Item, FromLocation, Qty, RefNum, Remark, ReasonID, ReasonRemark, CreateDate, CreateUser, ToLocation);
}
}
public void insertData(string TransID, string Item, string FromLocation, string Qty, 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("MMSLocTrans_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_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();
string DeleteWMMRSQL = "DELETE FROM [CIMProRPT01].[dbo].[OTH_INV_QTY_LOC] WHERE INV_QTY = 0 OR INV_QTY is null";
SqlCommand cmd3 = new SqlCommand(DeleteWMMRSQL, _cn);
cmd3.ExecuteNonQuery();
_cn.Close();
}
string check_existLoc = ConfigurationManager.ConnectionStrings["CIMProRPT01ConnectionString"].ConnectionString;
using (SqlConnection _cn1 = new SqlConnection(check_existLoc))
{
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);
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Location Transfer Completed');window.location.href = 'MMS_LocationTrans.aspx'", true);
}
}
catch
{
}
finally
{
_cn1.Close();
}
}
}
}
}
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
DataTable dt = (DataTable)ViewState["CurrentData"];
dt.Rows[e.RowIndex].Delete();
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
