Passing value between form and retrieving data from database

Question

In my windows form application, I have one form which user does input such as name address...blah..

When user press add entry, all the input entry will be added to the database and at the same time, order number will be passed to the another form where I have a SQL query to fetch the data of that particular order and populate them in respective textboxes.

But when I pass the order number from form1 to form2, and pass that order number to the method that retrieves data from the database, it shows me that there is no such row in the database even though the entry is already in the database. if I manually hardcode the order number in form 2 it works.

So how can retrieve the data from the database as soon as I entered those data in the database.

Basically form1 gives user the order number and form 2 uses that order number to fetch data. Sequence of launch is first form 1 and then form 2.

Code to retrieve

Form1 m = new Form1();
string number = m.orderNumber();
//  string number = "ORD1012013";

string InvSql = "SELECT  (Customer.[Title] + SPACE(2)  + Customer.[Customer's Name]) as CustomerName, Customer.[Customer's Ebayname], Customer.[Email Address], Customer.[Phone Number], (Customer.[Address 1] + SPACE(2)  +Customer.[Address 2] + SPACE(2)  + Customer.[City] + SPACE(2)  + Customer.[Post Code]+  SPACE(2)  + Customer.[Country]) as Address, Customer.[Item Purchased], Customer.[Purchased Date], Customer.[Total Price], Customer.[OrderNumber] FROM Customer WHERE Customer.[OrderNumber]= '" + number + "'";

OleDbConnection cnn = new OleDbConnection(connString);
OleDbCommand cmdOrder = new OleDbCommand(InvSql, cnn);

cnn.Open();

OleDbDataReader rdrOrder = cmdOrder.ExecuteReader();

rdrOrder.Read();

custName.Text = rdrOrder["CustomerName"].ToString();
ebayName.Text = rdrOrder["Customer's Ebayname"].ToString();
email.Text = rdrOrder["Email Address"].ToString();
phone.Text = rdrOrder["Phone Number"].ToString();
address.Text = rdrOrder["Address"].ToString();
item.Text = rdrOrder["Item Purchased"].ToString();
date.Text = Convert.ToString(Convert.ToDateTime(rdrOrder["Purchased Date"]));
price.Text = rdrOrder["Total Price"].ToString();
order.Text = rdrOrder["OrderNumber"].ToString();

rdrOrder.Close();
cnn.Close();

Code to add entry

private void button1_Click(object sender, EventArgs e)
        {
            foreach (Control c in this.Controls)
            {
                if (c is MaskedTextBox)
                {
                    MaskedTextBox textBox = c as MaskedTextBox;
                    if (textBox.Text == string.Empty)
                    {

                        string result = MyMessageBox.ShowBox("Please Enter All Fields", "Warning");
                    }

                    else
                    {
                        SaveAllListItems();
                        this.Close();
                        PrintOrder m = new PrintOrder();
                        m.Show();

                    }
                }
            }

For SaveAllListItems

 private void SaveAllListItems()
        {
            string listItems = string.Empty;
            foreach (var listBoxItem in listBox1.Items)
            {

                listItems += listBoxItem.ToString();

                if (listBox1.Items.IndexOf(listBoxItem) < listBox1.Items.Count - 1)
                {
                    listItems += ", ";
                }
            }

            InsertUser(maskedTextBox1.Text, comboBox1.Text, maskedTextBox2.Text, maskedTextBox3.Text, maskedTextBox4.Text, maskedTextBox5.Text,
                       maskedTextBox6.Text, maskedTextBox7.Text, maskedTextBox8.Text, maskedTextBox9.Text, listItems, DateTime.Now, maskedTextBox10.Text, orderNumber(), get_next_id());

;
        }

For Insert User

public string InsertUser(string custName, string title, string cust,  string emailAddress, string phoneNumber, string address1, string address2, string city, string postCode, string country, string itemPurchased, DateTime datePurchased, string price,string orderNumber, int id)
{

    // Create connection objecte

    //string datePurchased = DateTime.Now.ToString("dd/MM/yyyy");


    int ix = 0;
    string rTurn = "";
    OleDbConnection oleConn = new OleDbConnection(connString);
    try
    {
        oleConn.Open();
        string sql = "INSERT INTO [Customer]([Customer's Ebayname], [Title],  [Customer's Name], [Email Address] ,[Phone Number], [Address 1], [Address 2], [City], [Post Code], [Country] , [Item Purchased], [Purchased Date], [Total Price], [OrderNumber], [NumGenerate])" +
                 "VALUES ( @custName, @title, @cust, @emailAddress, @phoneNumber, @address1, @address2, @city, @postCode, @country , @itemPurchased, @datePurchased, @price, @orderNumber, @id)";
        OleDbCommand oleComm = new OleDbCommand(sql, oleConn);

        oleComm.Parameters.Add("@custName", OleDbType.Char).Value = custName;
        oleComm.Parameters.Add("@title", OleDbType.Char).Value = title;
        oleComm.Parameters.Add("@cust", OleDbType.Char).Value = cust;
        oleComm.Parameters.Add("@emailAddress", OleDbType.Char).Value = emailAddress;
        oleComm.Parameters.Add("@phoneNumber", OleDbType.Char).Value = phoneNumber;
        oleComm.Parameters.Add("@address1", OleDbType.Char).Value = address1;
        oleComm.Parameters.Add("@address2", OleDbType.Char).Value = address2;
        oleComm.Parameters.Add("@city", OleDbType.Char).Value = city;
        oleComm.Parameters.Add("@postCode", OleDbType.Char).Value = postCode;
        oleComm.Parameters.Add("@country", OleDbType.Char).Value = country;
        oleComm.Parameters.Add("@itemPurchased", OleDbType.Char).Value = itemPurchased;
        oleComm.Parameters.Add("@datePurhcased", OleDbType.Date).Value = datePurchased;
        oleComm.Parameters.Add("@price", OleDbType.Char).Value = price;
        oleComm.Parameters.Add("@orderNumber", OleDbType.Char).Value = orderNumber;
        oleComm.Parameters.Add("@id", OleDbType.Integer).Value = id;



        ix = oleComm.ExecuteNonQuery();
        if (ix > 0)
            rTurn = "User Added";
        else
            rTurn = "Insert Failed";
    }
    catch (Exception ex)
    {
    }
    finally
    {
        oleConn.Close();
    }
    return rTurn;
    }

To generate OrderNumber

  public string orderNumber()
        {
           string ord = "ORD" + get_next_id() + DateTime.Now.Year;
           return ord;
       }

Answer 1

It looks like you call m.orderNumber(); when adding and again when retrieving, and within that method you are calling get_next_id(). So the orderNumber is being bumped up on retrieval from the value that you saved. (i.e. saving order #1, retrieving order #2)

You should probably be storing the last persisted order # in a static property or passing it directly to the Form for retrieval.

Answer 2

have you checked the collation of the OrderNumber field? This defines how text is stored/compared within SQL Server. Another thing to check is the case of the letters. Do they have to be upper/lower case?