Here when i am selecting any of the value from serial_no combo it should automatically display the value for textbox(gas_name) when selecting the any one of serial_no.
C#code:
protected void btn1_Click(object sender, EventArgs e)
{
lbl3.Visible = true;
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString());
SqlCommand command = new SqlCommand("gas2", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@action", SqlDbType.VarChar).Value = ddl1.SelectedValue.Trim();
command.Parameters.Add("@serial_no", SqlDbType.VarChar).Value = ddl2.SelectedValue.Trim();
command.Parameters.Add("@gas_name", SqlDbType.VarChar).Value = txtGasname.Text.Trim();
con.Open();
command.ExecuteNonQuery();
fillSerialNo();
con.Close();
}
catch (SqlException ex)
{
Console.WriteLine("SQL Error" + ex.Message.ToString());
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('" + ex.Message.ToString() + "')", true);
}
}
#endregion
#region Page Method
private void fillSerialNo()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString());
string queryString = "Select serial_no,gas_name from 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 table:
Procedure:
alter procedure gas2
(
@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())
--SET @Newserial_no = SCOPE_IDENTITY()
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
--select serial_no ,gas_name from tbl_Cyl_gas_master where serial_no=@serial_no
UPDATE tbl_Cyl_gas_master set gas_name=@gas_name,Modified_date=GETDATE() WHERE serial_no=@serial_no
select serial_no ,gas_name from tbl_Cyl_gas_master 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
--return -1;
RAISERROR ('Transaction already Authorised or Deleted',16,1)
end
ENd
ELSE if @action='delete'
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 tran_stat='DE',gas_name='Deleted',
Modified_date=getdate() where serial_no=@serial_no
--DELETE gas_name from tbl_Cyl_gas_master where serial_no=@serial_no
end
else
begin
RAISERROR('Cannot delete.,Transaction is in Authorised Status',16,1)
end
end
else
select serial_no,gas_name,tran_stat from tbl_cyl_gas_master where serial_no=@serial_no
END