I have a form in that bankname,accountno,accounttype,checkno and checkbookno.What i want is if i select the bank name combobox accontno,accounttype and chequeno,checkbookno should be filled how to do that.
In bankcheques table i will insert the chequestartno,noofleaves and chequeendno.After i will use all the cheques i will update the status. I will insert new row.
What i want is while using chqleaves of first row show the chequebookno 1.
After totalnoofleaves is completed using and another row insert into the chequetable and update status of the firstrow.I want to show checkbookno 2.How to do that Please help me on this.I tried this.But i am not getting how to increment the checkbook no.And show the message for cheques completed.
Thanks in Advance.
What I have tried:
int id;
int chequeno;
int chqlvscount;
int chqendno;
int lastchequeno;
int totalckbno;
int ckbno;
private void FillCheckNo()
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT [bank_Id],[bank_Chqstartno],[bank_Chqlvscount],[bank_Chqendno] FROM [ChequeManager].[dbo].[bankcheques] where bank_Id=@bankid and bank_Stat=0", con);
cmd.Parameters.AddWithValue("@bankid", cmbBankname.SelectedValue.ToString());
SqlCommand cmd1 = new SqlCommand("SELECT top 1 [id],[bank_Id],[ChequeNo] FROM [ChequeManager].[dbo].[Cheques] where bank_Id=@bankid order by id desc", con);
cmd1.Parameters.AddWithValue("@bankid", cmbBankname.SelectedValue.ToString());
SqlDataAdapter sd = new SqlDataAdapter(cmd1);
DataTable dt1 = new DataTable();
sd.Fill(dt1);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
id = Convert.ToInt32(row["bank_Id"].ToString());
chequeno = Convert.ToInt32(row["bank_Chqstartno"].ToString());
chqlvscount = Convert.ToInt32(row["bank_Chqlvscount"].ToString());
chqendno = Convert.ToInt32(row["bank_Chqendno"].ToString());
if (dt1.Rows.Count > 0)
{
DataRow row1 = dt1.Rows[0];
lastchequeno = Convert.ToInt32(row1["ChequeNo"].ToString());
}
if (lastchequeno == null || lastchequeno == 0)
{
lastchequeno = Convert.ToInt32(row["bank_Chqstartno"].ToString());
txtcheckno.Text = Convert.ToString(lastchequeno);
}
else if (chqendno >= lastchequeno)
{
txtcheckno.Text = Convert.ToString(lastchequeno + 1);
lastchequeno = lastchequeno + 1;
if (lastchequeno > chqendno)
{
txtcheckno.Text = "";
}
}
con.Close();
}
}
private void FillChequeBookNo()
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT [bank_Id],chqs_Chequebookno,count([chqs_Chequebookno]) as totalcheques FROM [ChequeManager].[dbo].[chequedetails] where bank_Id=@bankid group by bank_Id,chqs_Chequebookno", con);
cmd.Parameters.AddWithValue("@bankid", cmbBankname.SelectedValue.ToString());
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sd.Fill(dt);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
totalckbno = Convert.ToInt32(dr["totalcheques"].ToString());
ckbno = Convert.ToInt32(dr["chqs_Chequebookno"].ToString());
txtckbno.Text = Convert.ToString(ckbno);
}
if (chqlvscount == totalckbno)
{
txtckbno.Text =Convert.ToString(ckbno);
}
if(totalckbno>chqlvscount)
{
txtckbno.Text=Convert.ToString(ckbno + 1);
}
else if (chqlvscount > totalckbno)
{
txtckbno.Text = Convert.ToString(ckbno);
}
}