A common ASP.NET database problem

I have a common problem that I do not no how to solve in the most efficient way.

I have a table named Orders which has a PK named OrderID, and an FK named OrderInfoID which links to a PK named OrderInfoID in a table named OrderInfo. Basic as it comes.

OrderInfo has three fields besides OrderInfoID. Phone, Address, Dispatcher. When an order is placed, I want most values from the order form to go to the Orders table, but I want Phone, Address, and Dispatcher to go to the OrderInfo table, and the ID of this entry to go to the OrderInfo column of Orders. Still pretty straightforward.

However, I want uniqueness in the OrderInfo table. So if ALL three values, Phone, Address and Dispatcher, already exist in the table, I want to note the ID of that record, and use it to add the OrderInfo entry to the new Orders table record. If it does not exist, I want to add a record using the form values, and then add that record's ID to the OrderInfo column of the new Orders table record.

Hope I explained this clearly. Thanks.
LVL 9
BobHavertyComhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
Hi,

1. Write a method which checks for Uniqueness of  these columns Phone, Address and Dispatcher.

2. If record found return boolean TRUE else FALSE.

3. While Inserting the Orders then call the Method and check if TRUE then pass the existing ID else Create new ID.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
This is OK. And what is the question?

Simply query the OrderInfo table for the three values combination before the Order record saving. You'll either obtain the OrderInfoID or nothing. If you obtain nothing then you have to save the OrderInfo record first and use its OrderInfoID for the new Order record.

Also, the OrderInfo table contents is a little bit "non-deterministic". Just imagine people at the phone recording the order and phones and addresses. How do you ensure the phone number correct format? How do you avoid typo bugs in addresses? All this will cause multiplicities in OrderInfo table and the regular cleanup will be necessary etc. etc.
BobHavertyComhAuthor Commented:
Hi Lokesh. This is what I have been trying, but I can't seem to figure out the rest. I'm kind of close.

myConnection.Open();
            try
            {
                SqlDataReader myReader = null;
                SqlCommand OrderInfo = new SqlCommand("Select PhoneNumber, Address, FROM OrderInfo", myConnection);
                myReader = OrderInfo.ExecuteReader();

            while (myReader.Read())
            { }

            SqlCommand insertOrderInfo = new SqlCommand("INSERT INTO OrderInfo (PhoneNumber, Address, DispatcherID) SELECT PhoneNumber, Address FROM Temp WHERE Billable = 1 AND NOT EXISTS (SELECT 1 FROM Addresses WHERE OrderInfo.PhoneNumber = Temp.PhoneNumber AND OrderInfo.Address = Temp.Address AND OrderInfo.Dispatcher = Temp.Dispatcher )", myConnection);

                insertOrderInfo.ExecuteNonQuery();
                
                SqlCommand insertOrder = new SqlCommand("INSERT INTO Orders SELECT OrderInfo, DollarAmt, Created, CreatedBy, Next, Delivered, Problem, Deleted, Modified, Edits, ModifiedBy, Billable, FROM Temp Where Billable = 1", myConnection);
                SqlCommand delete = new SqlCommand("DELETE FROM Temp", myConnection);
                insertOrder.ExecuteNonQuery();
                delete.ExecuteNonQuery();
                myConnection.Close();

            }
            catch (Exception err)
            {
                Response.Write("<p>" + err + "</p>");
                myConnection.Close();
            }

Open in new window


I really have no idea of how to handle getting the ID val what happened on the OrderInfo table (existing record, or added one), so I can use it for the Orders table.

Am I at least getting close?
BobHavertyComhAuthor Commented:
Hi pcelba, Thanks for the reply.

This is OK. And what is the question?

Mainly, to find out whether a new record was created on OrderInfo, or whether it already existed, and either way, grab the ID of the appropriate record and add it to the OrderInfo column of the Orders table.

Simply query the OrderInfo table for the three values combination before the Order record saving. You'll either obtain the OrderInfoID or nothing. If you obtain nothing then you have to save the OrderInfo record first and use its OrderInfoID for the new Order record.

Also, the OrderInfo table contents is a little bit "non-deterministic". Just imagine people at the phone recording the order and phones and addresses. How do you ensure the phone number correct format? How do you avoid typo bugs in addresses? All this will cause multiplicities in OrderInfo table and the regular cleanup will be necessary etc. etc.

Excellent points. Form validation can take care of the phone number problem. There's really very little validation that can be done with Addresses as far as I can tell because it really has so many forms it can take. To solve this, I am using an html5 datalist linked to my input field, that goes out and queries the OrderInfo table for past addresses that match the phone number entered, and populated it as the user types. I've also managed to have our results mesh with google's as well so that if this is a new address, they will hopefully select what comes up for them, therefore entering in proper form. That's the best I can think of for now. Completely open to any ideas you might have. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.