Dear All,
As following SQL Script statement is if recordcount is 1 then record is updated otherwise it will be stored.
May i know how if have a different location, then how to update it ? how if there is have existing Quantity amount, then update with the previous quantity amount ?
For example, If existing item A in location A Quantity is 20, if update 10 , then total quantity of item A is 30 in location A.
Based on the image show that record two same row item, is it possible record same item in one row?
ALTER PROCEDURE InsertUpdate
@Id INT,
@Name VARCHAR(50),
@City VARCHAR(50),
@Status CHAR(1),
@RecordFound INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM employee WHERE Id =@Id)
BEGIN
UPDATE employee SET [Name] = @Name,City = @City ,Status = @Status WHERE Id = @Id
SELECT @RecordFound = 1
END
ELSE
BEGIN
INSERT INTO employee Values(@Id,@Name,@City,@Status)
SELECT @RecordFound = 0
END
END
GO
protected void Insert(object sender, EventArgs e)
{
string constr = ConfigurationManager.ConnectionStrings["ConString2"].ConnectionString;
using (SqlConnection _cn = new SqlConnection(constr))
{
using (SqlCommand _cmd = new SqlCommand("InsertUpdate", _cn))
{
using (SqlDataAdapter da = new SqlDataAdapter(_cmd))
{
_cn.Open();
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.Parameters.AddWithValue("@Id", this.txtId.Text.Trim());
_cmd.Parameters.AddWithValue("@Name", this.txtName.Text.Trim());
_cmd.Parameters.AddWithValue("@City", this.txtCity.Text.Trim());
_cmd.Parameters.AddWithValue("@Status", this.chkStatus.Checked ? "1" : "0");
_cmd.Parameters.Add("@RecordFound", SqlDbType.Int, 0);
_cmd.Parameters["@RecordFound"].Direction = ParameterDirection.Output;
_cmd.ExecuteNonQuery();
int recordCount = Convert.ToInt32(_cmd.Parameters["@RecordFound"].Value);
Label1.Text = recordCount == 1 ? "Updated" : "Inserted";
_cn.Close();
}
}
}
}
}
}
