Click here to Skip to main content
15,356,336 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
            //s = Billno;
            cmd = new SqlCommand(p, con);
            int k;
            con.Open();

            if (txtbill.Text != "")
            {

                //check();

                 //cmd = new SqlCommand("IF NOT EXISTS (SELECT Billno FROM MainBillForm WHERE Company_ID = Company_ID)Select ISNULL(Max(Billno+1),1) From MainBillForm where Company_ID=" + GlobalVariable.companyId, con);
                 Billno = Convert.ToInt32(txtbill.Text);
                 k = Billno;

                 s = k;

                 k = (Int32)cmd.ExecuteScalar();
                 con.Close();
                 return Billno;
              
            }
            else
            {
                //s = k + 1;
                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)//Nothing selected
           {
               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)
               {
                   //double ttl = (Convert.ToDouble(g1.Cells[4].FormattedValue.ToString()) * Convert.ToDouble(g1.Cells[6].FormattedValue.ToString()));

                   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());
Posted
Updated 20-Jun-17 10:38am
v3
Comments
Richard MacCutchan 20-Jun-17 8:00am
   
Your code is wide open to SQL injection. Use proper parameterised queries from validated inputs, not concatenated strings. Also, do not use strings to hold dates/times, use the proper Date or DateTime types.

Quote:
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.

The whole idea is wrong because it is not compatible with multi-user apps.
SQL AUTO INCREMENT a Field[^]

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
   
First question: WHY?

There is no reason to do this at all. Users should not be able to generate their own bill numbers. Instead, they should be assigned by the database.

Also, doing a "SELECT MAX(id) + 1" does not work in a multi-user environment. More than one client can execute that code at the same time and you will end up having multiple clients trying to use the same ID number at the same time. How are you going to handle that?

You don't. It's easily avoidable.
   
Comments
Deekshaa Singh Chauhan 21-Jun-17 1:44am
   
it generate automatically but when i assign value using textbox it take that number as a assigning value and start increasing that number
Dave Kreskowiak 21-Jun-17 8:46am
   
Why are you assigning a key value at all? That's not what an autogenerated column is for at all and will just cause you problems.
Deekshaa Singh Chauhan 21-Jun-17 1:46am
   
it perfectly run but i want do check the textbox number is exists in database or not
Deekshaa Singh Chauhan 21-Jun-17 1:49am
   
this following code check value but i'm confused where i placed this code



private void txtbill_Validated(object sender, EventArgs e)
{
if (txtbill.Text != "" )
{

SqlDataAdapter sda = new SqlDataAdapter("SELECT Billno FROM MainBillForm WHERE Billno = '" + txtbill.Text + "' AND Company_ID =" + GlobalVariable.companyId, con);
DataTable dt = new DataTable();
sda.Fill(dt);

if (dt.Rows.Count.ToString() != "1")
{

MessageBox.Show("Already Exists");
txtbill.Text = string.Empty;
}
else
{

}
}
}
Dave Kreskowiak 21-Jun-17 8:47am
   
How should I or anyone else know where this code should go? We have no idea what it's being used for.
Change from:

//cmd = new SqlCommand("IF NOT EXISTS (SELECT Billno FROM MainBillForm WHERE Company_ID = Company_ID)Select ISNULL(Max(Billno+1),1) From MainBillForm where Company_ID=" + GlobalVariable.companyId, con);


TO:

//To get the next number, presuming your GlobalVariable.companyId is a number.
cmd = new SqlCommand("SELECT max(Billno) + 1 FROM MainBillForm WHERE Company_ID = "+ GlobalVariable.companyId, con);
int nextBillNo = (int)cmd.ExecuteScalar();
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900