I write this code but sometime it is throwing null reference exception on line
tr.Rollback();and sometimes it throws connection closed exception...requires an open connection on line
cmd.ExecuteNonQuery(); please tell me where is the problem
protected void register()
{
string epin = generatepin();
mid = getmaxmemid("tbl_member", "mem_id");
levelid = getmaxmemid("tbl_level", "level_id");
con = new SqlConnection(sqlcon);
con.Open();
tr = con.BeginTransaction();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "add_member";
cmd.Parameters.Add("@mem_id", SqlDbType.Int).Value = mid;
cmd.Parameters.Add("@userid", SqlDbType.NVarChar).Value = txtuser.Text.Trim();
cmd.Parameters.Add("@mem_pwd", SqlDbType.NVarChar).Value = txtpwd.Text.Trim();
cmd.Parameters.Add("@mname", SqlDbType.NVarChar).Value = txtname.Text.Trim();
cmd.Parameters.Add("@madd", SqlDbType.NVarChar).Value = txtadd.Text.Trim();
cmd.Parameters.Add("@pan", SqlDbType.NVarChar).Value = txtpan.Text.Trim();
cmd.Parameters.Add("@bank_name", SqlDbType.NVarChar).Value = txtbname.Text.Trim();
cmd.Parameters.Add("@ac_num", SqlDbType.NVarChar).Value = txtac.Text.Trim();
cmd.Parameters.Add("@ac_holder", SqlDbType.NVarChar).Value = txtholder.Text.Trim();
cmd.Parameters.Add("@ifsc", SqlDbType.NVarChar).Value = txtifsc.Text.Trim();
cmd.Parameters.Add("@referal_id", SqlDbType.NVarChar).Value = txtref.Text.Trim();
cmd.Parameters.Add("@epin", SqlDbType.NVarChar).Value = epin;
cmd.Parameters.Add("@plan_id", SqlDbType.Int).Value = Convert.ToInt16(ddlplan.SelectedValue);
try
{
int check = check_referal_existance();
if (check == 1)
{
int ref_id = getminimumid("tbl_member", "mem_id");
setrefid:
string query = "select mem_id from tbl_level where referal_id=" + ref_id;
cmdlev = new SqlCommand(query, con);
con.Open();
records_aftcd = 0;
SqlDataReader dr = cmdlev.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.HasRows)
{
while (dr.Read())
{
records_aftcd = records_aftcd + 1;
}
if (records_aftcd >= 2)
{
ref_id = ref_id + 1;
con.Close();
goto setrefid;
}
else
{
dr.Close();
string insert_query = "insert into tbl_level(level_id,mem_id,referal_id) values(" + levelid + "," + mid + "," + ref_id + ")";
cmd_setlevel = new SqlCommand(insert_query, con);
cmd.Transaction = tr;
cmd_setlevel.Transaction = tr;
cmd_setlevel.ExecuteNonQuery();
cmd.ExecuteNonQuery();
tr.Commit();
erdiv.InnerHtml = "Member Has Been Successfully Registered";
}
}
else
{
dr.Close();
string insert_query = "insert into tbl_level(level_id,mem_id,referal_id) values(" + levelid + "," + mid + "," + ref_id + ")";
cmd_setlevel = new SqlCommand(insert_query, con);
tr = con.BeginTransaction();
cmd.Transaction = tr;
cmd_setlevel.Transaction = tr;
cmd_setlevel.ExecuteNonQuery();
cmd.ExecuteNonQuery();
tr.Commit();
erdiv.InnerHtml = "Member Has Been Successfully Registered";
}
}
else
{
erdiv.InnerHtml = "Referal not exists!";
}
}
catch (Exception ex)
{
tr.Rollback();
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}