If bill number exists then show message already exists

Question

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 20-Jun-17 0:12am

Deekshaa Singh Chauhan

Updated 20-Jun-17 10:38am

v3

Add a Solution

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.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Patrice T · Answer 1 · 2017-06-20T10:38:00

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[^]

Dave Kreskowiak · Answer 2 · 2017-06-20T02:17:00

Solution 2

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.

Posted 20-Jun-17 2:17am

Dave Kreskowiak

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.

Thomas Nielsen - getCore · Answer 3 · 2017-06-20T02:11:00

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();

If bill number exists then show message already exists

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0