your final solution:
HTML:
<div>
<table>
<tr>
<td>
Action:
<asp:DropDownList ID="ddl1" runat="server" ClientIDMode ="Static" AutoPostBack="false">
<asp:ListItem Value ="add">Add</asp:ListItem>
<asp:ListItem Value ="update">Update</asp:ListItem>
<asp:ListItem Value ="delete">Delete</asp:ListItem>
<asp:ListItem Value ="authorise">Authorise</asp:ListItem>
</asp:DropDownList>
</td>
<td>
Serial No:
<asp:DropDownList ID="ddl2" runat="server" ClientIDMode ="Static">
</asp:DropDownList>
</td>
<td>
Tans Status:
<asp:Label ID="lblTranStatus" runat="server" ClientIDMode ="Static"></asp:Label>
</td>
<td>
Gas Name
<asp:TextBox ID="txtGasname" runat="server" ClientIDMode ="Static"></asp:TextBox>
</td>
</tr>
</table>
<asp:Button ID="btnUpdate" runat="server" Text="Action"
onclick="btnUpdate_Click" />
</div>
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fillSerialNo();
}
}
#region Controls Event
protected void btnUpdate_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ToString());
SqlCommand command = new SqlCommand("gas_name", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@action", SqlDbType.VarChar).Value = ddl1.SelectedValue.Trim();
command.Parameters.Add("@gas_name", SqlDbType.VarChar).Value = txtGasname.Text.Trim();
command.Parameters.Add("@serial_no", SqlDbType.VarChar).Value = ddl2.SelectedValue.Trim();
con.Open();
command.ExecuteNonQuery();
con.Close();
}
catch (SqlException ex)
{
Console.WriteLine("SQL Error" + ex.Message.ToString());
}
}
#endregion
#region Page Method
private void fillSerialNo()
{
string queryString = "SELECT serial_no FROM dbo.tbl_cyl_gas_master";
SqlCommand command = new SqlCommand(queryString, con);
SqlDataAdapter adapter = new SqlDataAdapter(queryString, con);
con.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dt = new DataTable();
dt.Load(dr);
ddl2.DataSource = dt;
ddl2.DataTextField = "serial_no";
ddl2.DataValueField = "serial_no";
ddl2.DataBind();
ddl2.Items.Insert(0, new ListItem("-- Add --", "0"));
}
#endregion
SQL SIDE
ALTER procedure gas_name
(
@action varchar(20),
@serial_no int,
@gas_name varchar(50)
)
AS
BEGIN
declare @Newserial_no int
declare @tran_stat varchar(10)
SET @tran_stat=''
IF(@tran_stat ='') OR (@tran_stat =null)
SET @tran_stat = CASE when @action='add' then 'FR' END
IF(@action ='add')
BEGIN
IF (@serial_no = 0)
begin
IF NOT EXISTS(SELECT * FROM tbl_Cyl_gas_master WHERE gas_name = @gas_name AND tran_stat = 'FR')
INSERT INTO tbl_Cyl_gas_master(gas_name,tran_stat,tran_date) VALUES (@gas_name,@tran_stat,GEtDATE())
end
ELSE
begin
Raiserror('Gas Name or transaction exist',16,1)
end
END
ELSE IF @action='update'
BEGIN
IF EXISTS(SELECT * FROM tbl_Cyl_gas_master WHERE serial_no = @serial_no AND tran_stat = 'FR')
begin
UPDATE tbl_Cyl_gas_master set gas_name=@gas_name,Modified_date=GETDATE() WHERE serial_no=@serial_no
end
ELSE
RAISERROR ('Transaction not is fresh',16,1);
END
ELSE IF @action='authorise'
BEGIN
IF EXISTS(SELECT * FROM tbl_Cyl_gas_master WHERE serial_no = @serial_no AND tran_stat = 'FR')
begin
UPDATE tbl_Cyl_gas_master SET gas_name=@gas_name,tran_stat='AU',Modified_date=getdate()
WHERE serial_no=@serial_no
end
ELSE
begin
RAISERROR ('Transaction already Authorised',16,1)
end
ENd
ELSE if @action='delete'
begin
update tbl_Cyl_gas_master set tran_stat='DE',gas_name='Deleted',
Modified_date=getdate() where serial_no=@serial_no
end
else
select * from tbl_cyl_gas_master
END
hope your problem is solved