Dear All,
May i know how to show the alert message when matching the data is not accurate in SQL with C#?
Based on below SQL script show that if recordcount is true then update the QTY - @QTY else insert the data.
How to write the statement is the input @QTY is more that existing QTY, then pop up alert message show that "QTY is not available, there is more than existing QTY" else if the input @qty is 0, then show alert message "NOt able to key in 0 QTY".
Then redirect Or return to main form(NOt able to do transaction).
USE [CIMProRPT01]
GO
/****** Object: StoredProcedure [dbo].[MMSIssue_InsertOrUpdate] Script Date: 12/30/2013 16:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MMSIssue_InsertOrUpdate]
@INV_TRANS_ID VARCHAR(40)
,@INV_ID VARCHAR(40)
,@INV_LOCATION VARCHAR(40)
,@INV_QTY FLOAT
,@INV_TRANS_REQUESTOR VARCHAR(40)
,@INV_TRANS_REFNO VARCHAR(40)
,@INV_TRANS_REMARK VARCHAR(255)
,@INV_REASON_ID VARCHAR(40)
,@INV_REASON_REMARK VARCHAR(255)
,@INV_CREATE_DATE DATETIME
,@INV_CREATE_USER VARCHAR (255)
--,@INV_VENDORS VARCHAR (40)
,@INV_FROMLOC VARCHAR (40)
,@RecordFound INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM OTH_INV_QTY_LOC WHERE INV_ID = @INV_ID and INV_LOCATION = @INV_LOCATION)
BEGIN
UPDATE OTH_INV_QTY_LOC SET [INV_ID] = @INV_ID,INV_LOCATION = @INV_LOCATION , INV_QTY = INV_QTY - @INV_QTY WHERE INV_ID = @INV_ID AND INV_LOCATION = @INV_LOCATION
INSERT INTO OTH_INV_TRANSACTION (INV_TRANS_ID,INV_ID,INV_TRANS_LOCATION,INV_TRANS_QTY,INV_TRANS_REQUESTOR,INV_TRANS_REFNO,INV_TRANS_REMARK,INV_REASON_ID,INV_REASON_REMARK,INV_CREATE_DATE,INV_CREATE_USER,INV_FROMLOC)VALUES (@INV_TRANS_ID,@INV_ID,@INV_LOCATION,@INV_QTY,@INV_TRANS_REQUESTOR,@INV_TRANS_REFNO,@INV_TRANS_REMARK,@INV_REASON_ID,@INV_REASON_REMARK,@INV_CREATE_DATE,@INV_CREATE_USER,@INV_FROMLOC)
SELECT @RecordFound = 1
END
ELSE
BEGIN
SELECT @RecordFound = 0
END
END
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_Issue : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SystemDate();
TransID();
if (!Page.IsPostBack)
{
QueryDetTBL.Visible = false;
GridViewMMSI.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) + 1 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 = 'ISSUE'";
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("VendorName", typeof(String))); //11
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_Requestor.Text;
dr[5] = Inv_Ref.Text;
dr[6] = Inv_Remark.Text;
dr[7] = Inv_ReasonID.SelectedValue;
dr[8] = Inv_ReasonRemark.Text;
dr[9] = MMSCDATE.Text;
dr[10] = MMSCUSER.Text;
// dr[11] = Inv_Vendorname.Text;
dr[11] = 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_Requestor.Text;
dr[5] = Inv_Ref.Text;
dr[6] = Inv_Remark.Text;
dr[7] = Inv_ReasonID.SelectedValue;
dr[8] = Inv_ReasonRemark.Text;
dr[9] = MMSCDATE.Text;
dr[10] = MMSCUSER.Text;
// dr[11] = Inv_Vendorname.Text;
dr[11] = 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)
{
GridViewMMSI.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 Requestor = oItem.Cells[5].Text;
string RefNum = oItem.Cells[6].Text;
string Remark = oItem.Cells[7].Text;
string ReasonID = oItem.Cells[8].Text;
string ReasonRemark = oItem.Cells[9].Text;
string CreateDate = oItem.Cells[10].Text;
string CreateUser = oItem.Cells[11].Text;
string ToLocation = oItem.Cells[12].Text;
insertData(TransID, Item, FromLocation, Qty, Requestor, RefNum, Remark, ReasonID, ReasonRemark, CreateDate, CreateUser, ToLocation);
}
Response.Redirect("MMS_Issue.aspx?stat=insert");
}
public void insertData(string TransID, string Item, string FromLocation, string Qty, string Requestor, 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_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 OR INV_QTY <= '-1'";
SqlCommand cmd3 = new SqlCommand(DeleteWMMRSQL, _cn);
cmd3.ExecuteNonQuery();
_cn.Close();
}
}
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
DataTable dt = (DataTable)ViewState["CurrentData"];
dt.Rows[e.RowIndex].Delete();
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
