How can i check if bill no. already stored in database then Message Box show bill no. already exist where company id ,I've 1 billno.textbox for enter bill no. if i'm not enter any no. that automatically increase 1 by 1. if i enter any other no like:11 that store and start increasing 11, one by one.
here i'm trying.It run successfuly but it not check no. the number is exists or not
<pre> private int billno(string p)
{
Int32 Billno = 0;
cmd = new SqlCommand(p, con);
int k;
con.Open();
if (txtbill.Text != "")
{
Billno = Convert.ToInt32(txtbill.Text);
k = Billno;
s = k;
k = (Int32)cmd.ExecuteScalar();
con.Close();
return Billno;
}
else
{
Billno = s;
Billno = (Int32)cmd.ExecuteScalar();
con.Close();
return Billno;
}
semidisc = semidisc + Convert.ToDouble(g1.Cells[8].FormattedValue.ToString());
overallttl = overallttl + Convert.ToDouble(g1.Cells[9].FormattedValue.ToString());
// overallttl_wtax = overallttl_wtax + Convert.ToDouble(g1.Cells[16].FormattedValue.ToString());
}
}
catch (Exception)
{
MessageBox.Show("alredy ext");
}
double discount = 0;
if (discounttxt.Text != "" && chk_DiscPerct.Checked == true)
{
discount = (_subTotal * Convert.ToDouble(discounttxt.Text)) / 100;
}
else if (discounttxt.Text != "")
{
discount = Convert.ToDouble(discounttxt.Text);
}
SqlCommand cmd1 = new SqlCommand("insert into MainBillForm (Billno,Name,Date,Shipped_Details,Order_Number,Sno,Product_Name,Description,Hsn_Code,Qty,Unit,Rate,Amount,Discount,Taxable_Amount" +
"Addcost,Sub_Total,Sub_Discount,Total_Amount,Company_ID) " +
" Values ('" + Billno + "','" + custid + "','" + dateTimePicker1.Text + "','" + comboShipped.SelectedValue.ToString() + "','" + ONtxt.Text + "','" + 0 + "','" +"0" + "','"
+ "0" + "','" + "0" + "','" + "0" + "','" + "0" + "','" +0 + "','"
+ semittl + "','" + semidisc + "','" + overallttl + "','" + Convert.ToDouble(isEmpty(addcosttxt.Text)) + "','" + Convert.ToDouble(isEmpty(totalamounttxt.Text)) + "','" + discount+ "','" + Convert.ToDouble(isEmpty(txtNewTotal.Text)) + "'," + GlobalVariable.companyId + ")", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
comboBox_Custname.Text = "";
//dateTimePicker1.Value = DateTime.Now; ONtxt.Text = "";
totalamounttxt.Text = "";
comboBoxproduct.Text = "";
dectxt.Text = "";
combounit.Text = "";
qtytxtprice.Text = "";
txtqty.Text = "";
txttax.Text = "";
discounttxt.Text = "";
addcosttxt.Text = "";
txtNewTotal.Text = "";
Subtxt.Text = "";
txtdiscount.Text = "";
dataGridView.Rows.Clear();
MessageBox.Show("Records Inserted SuccessFully");
}
What I have tried:
<pre>private void Insertbtn_Click(object sender, EventArgs e)
{
if (comboBox_Custname.SelectedIndex == 0)
{
MessageBox.Show("You must select a ComboBox Values type");
comboBox_Custname.Focus();
return;
}
if (dataGridView.RowCount == 0)
{
MessageBox.Show("there are no items in the list to add");
return;
}
string custid = "";
if (comboBox_Custname.SelectedIndex < 0)
{
try
{
cmd = new SqlCommand("insert into Customer_Details(CustName) values(@CustName)", con);
con.Open();
cmd.Parameters.AddWithValue("@CustName", comboBox_Custname.Text);
cmd.ExecuteNonQuery();
con.Close();
}
catch { }
try
{
SqlCommand cmd = new SqlCommand("SELECT max(CustID)FROM Customer_Details", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
custid = dt.Rows[0][0].ToString();
}
catch { }
}
else
{
custid = comboBox_Custname.SelectedValue.ToString();
}
int Billno = billno("IF NOT EXIST( Select ISNULL(Max(Billno+1),1) From MainBillForm where Company_ID=)" + GlobalVariable.companyId) ;
double semittl=0, semidisc=0, overallttl = 0 , overallttl_wtax = 0;
try
{
foreach (DataGridViewRow g1 in dataGridView.Rows)
{
SqlCommand cmd = new SqlCommand("insert into MainBillForm (Billno,Name,Date,Shipped_Details,Order_Number,Sno,Product_Name,Description,Hsn_Code,Qty,Unit,Rate,Amount,Discount,Taxable_Amount" +
"Addcost,Sub_Total,Sub_Discount,Total_Amount,Company_ID) " +
" Values ('" + Billno + "','" + custid + "','" + dateTimePicker1.Text + "','" + comboShipped.SelectedValue.ToString() + "','" + ONtxt.Text + "','" + Convert.ToInt16(g1.Cells[0].FormattedValue.ToString()) + "','" + g1.Cells[17].FormattedValue.ToString() + "','"
+ g1.Cells[2].FormattedValue.ToString() + "','" + g1.Cells[3].FormattedValue.ToString() + "','" + g1.Cells[4].FormattedValue.ToString() + "','" + g1.Cells[18].FormattedValue.ToString() + "','" + g1.Cells[6].FormattedValue.ToString() + "','"
+ g1.Cells[7].FormattedValue.ToString() + "','" + g1.Cells[8].FormattedValue.ToString() + "','" + g1.Cells[9].FormattedValue.ToString() + "','" + g1.Cells[10].FormattedValue.ToString() + "','" + g1.Cells[11].FormattedValue.ToString() + "','"
+ g1.Cells[12].FormattedValue.ToString() + "','" + g1.Cells[13].FormattedValue.ToString() + "','" + g1.Cells[14].FormattedValue.ToString() + "','" + g1.Cells[15].FormattedValue.ToString() + "','" + 0.00 + "','" + 0.00 + "','" + 0.00 + "','" + g1.Cells[16].FormattedValue.ToString() + "','" + GlobalVariable.companyId + "')", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
semittl = semittl + Convert.ToDouble(g1.Cells[7].FormattedValue.ToString());