Got a Java program that has two tables in MS Access DB. It's a Customers database with a Customers table and a Orders table. Customers: CustomerName, CustomerID, Address, City, State, Zip, PhoneNumber. / Orders: ID, ProductName, OrderNumber, Quantity, UnitCost.
In my program I need to be able to pull up a users ID with their Personal Information and also their Order History. I have this somewhat but its not linking properly, or I am not linking it properly in my statements. I am trying to figure out if anyone can help me with what statement(s) i should be specifically using.
It will display the CustomerID in the CustomerID field, but the CustID doesnt match the ID in the orders and it doesnt link the correct personal info to the order info.
Might be confusing but here's a snippet of the code, if you need the full thing let me know.
I would suggest you to read a jdbc tutorial as i feel some basic stuff like closing the statement , resultset ,connection is missing from this code.
1)Do you want to fetch only the customerIds from the customer table , change the query accordingly.
2)Why are you passing the ResultSet object to the next method , if you want to add those customerIds to the list , do it after you get the resultset.
Please let us know the relationship between the 2 tables and will try to help out.
Should I be having a customerID field in each Customer and Orders table to link them? Right now I have a CustomerID in Customers Table that is supposed to link ID field in the Orders Table, not sure if this is appropriate set-up or not. Ive deal with other programs with access multiple tables but I cant figure out how to link information from the two.
I need to be able to select a CustomerID from the Java GUI and then that will display the personal info from the Customers table and also the Order history from the Orders table that corresponds with the ID.
BillyBob Thorton wrote:...I need to be able to select a CustomerID from the Java GUI and then that will display the personal info from the Customers table and also the Order history from the Orders table that corresponds with the ID...
Ok, then you need a foreign key to the customer table from where you have stored the Order details for the customers.
BillyBob Thorton wrote:Should I be having a customerID field in each Customer and Orders table to link them? Right now I have a CustomerID in Customers Table that is supposed to link ID field in the Orders Table, not sure if this is appropriate set-up or not.
First of all, if you thought posting 500 lines of code would help to explain that question, let me say that it was completely unhelpful.
As for that question, yes, you should have something in the Order table to say what customer the order is for. If that's your ID column, then your query should select records where Customer.CustomerID = Order.ID. However if the ID column is an order number and not the customer number, then that join will produce random data at best.
You need the customer number in the order somewhere. Then you need to match the customer number in the customer table to the customer number in the order table. That's all. No rocket science involved.
It looks like that's what you have. But you think it isn't working? Well, that 500 lines of code is far too large to test with. Not to mention there's a whole lot of code which is unrelated to that. So put that big program aside and write about 50 lines of code which tests what you want to test.
So say I select CustID: #2 from the drop box. Instead of displaying CustID #2 it will display CustID #3 Personal Information and with that CustID it will link to ID: 3 in the Orders table and display that correct Order info..
Please post the sqls /code that are fired in the above case?
Any ideas? My idea would be to take that SQL statement which you posted, and see what it returns. Is it correct data? Wrong data? I'm just shooting in the dark because at this point I have no idea what you think your problem is or what you have done to work on it. You just posted an SQL query with no explanation.
And were you under the impression that the query you posted would return data for only one customer? Earlier your problem seemed to be that you were getting data for the wrong customer, or something like that, but the query you posted should return data for all customers.