how to insert multiple value in database

[Manoj semwal .]

Hai .....

i am doing shopping cart project the project is almost done but i have little proble after selecting the item the user's information and item information should be submit in database how can i submit it in database database is like that ..
user information name,allthose thing and product informtaion is like productId booksname,Qu,Price like that if user purchased 5 diffeent book .
so how can i enter the entries in same user name.

pl help.

[Sid Murrey]

Originally posted by manoj semwal:
Hai .....

i am doing shopping cart project the project is almost done but i have little proble after selecting the item the user's information and item information should be submit in database how can i submit it in database database is like that ..
user information name,allthose thing and product informtaion is like productId booksname,Qu,Price like that if user purchased 5 diffeent book .
so how can i enter the entries in same user name.

pl help.

Man, learn how to spell properly. Regarding your question, take a look at java.sql.PreparedStatement.

[Campbell Ritchie]

Originally posted by Cedric Meury:

Man, learn how to spell properly.

I beg your pardon? :roll: Lots of people can't spell.

[Paul Clapham]

Originally posted by manoj semwal:
like that .. user information name,allthose thing and product informtaion is like productId booksname,Qu,Price like that

Is that supposed to be a description of your database schema? I don't see any table which contains user ID and product ID. You would need such a table if you wanted to keep track of which users purchased which books.

You might want to provide some more details, if you think we aren't understanding your problem. (Which is what I think.) The way you post, it seems like you're holding your breath as you type. Take it easy and think about what you want to say.

[Sid Murrey]

Originally posted by Campbell Ritchie:
I beg your pardon? Lots of people can't spell.

I was just browsing around the forum looking for posts with zero replies. I tried reading his question but stopped in the middle of it, because I could not follow what he was trying to say. I'm not a native speaker.
Sorry to have offended you.

[Campbell Ritchie]

I am sure you didn't intend to give offence. Apology accepted
[ July 10, 2008: Message edited by: Campbell Ritchie ]

[Bear Bibeault]

We do urge people to post using real words -- correct spelling can be considered part of that. I too can get frustrated when trying to read postings with typos. But let's bear in mind that for many posters here, English is not their first language, so let's all be sure that we couch such coaching in pleasant terms.

"Please use real words" and "your post would be more readable with fewer typos" are nice ways of putting things.

[Muhammad Saifuddin]

any ways, we are welcome you (manoj) to Javaranch.

and expecting you to write down the details (as paul suggested) because sometimes less information isn't enough to understand the whole criteria.

you can follow this pattern to define the database schema to make your query more clear for readers.

[name of the table]
-------------------
[Field Name]
[Field Name]

Best of Luck with your question.

[Manoj semwal .]

Thanks Friend for your reply

My table is like
OrderId USer Name,User Address,UserEmail,USerPhone ,BooksId,BooksName,BooksQuanity,Books Price Total Price,Total Tax

So how can i enter Multiple books Selection entry the above database.

thanks

Manoj
[ July 10, 2008: Message edited by: manoj semwal ]

[Sid Murrey]

From the point of database design, it seems to me that three separate tables (Users, Orders, Books) could be very helpful.

[Manoj semwal .]

Thanks Cedric Meury for your reply

Actually iam using one table. table name is orderForm having userinformation and orderinformation. so how can i append multiple choice entry in one record .


for your waiting manoj

[Sid Murrey]

As I have already said, take a look at PreparedStatement:

http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html

[Campbell Ritchie]

Originally posted by Cedric Meury:
From the point of database design, it seems to me that three separate tables (Users, Orders, Books) could be very helpful.

Agree. In Orders you would have foreign keys linked to the User and Book tables.

[Manoj semwal .]

hai campbell
i sent to you a mail regarding the problem pl find the mail.
also sent to muery
pl check it

[Campbell Ritchie]

Originally posted by manoj semwal:
hai campbell
i sent to you a mail regarding the problem pl find the mail.
also sent to muery
pl check it

Please don't use e-mail. The idea of a forum is that everybody can take part in the discussion. Please post what you sent here. Then you will get more useful comments.

[Manoj semwal .]

Sorry campbell

are you read the mail. or i have to paste here again .

actually there are some snapshot so i sent the mail.

Manoj
[ July 11, 2008: Message edited by: manoj semwal ]

[Campbell Ritchie]

Of course I have read it, but it ought to be discussed here. I think you can get away without all the pictures, but there is an image button below the message window.

[Manoj semwal .]

once again thanks for your reply
but campbell all thing are there .in second image the above pic have the selected books by user and second pic having the user information. so how can i add it in to the database . how you can do it if you have this project.

[Paul Clapham]

Manoj, pictures don't have much to do with this question. At least I don't think so, you haven't posted any pictures here. If you want to add a record to the table then you should use an SQL Insert statement. If you want to add five records to the table then you should call the Insert statement five times.

But I can't tell which of those things you want to do. It sounds like you want to add one entry to the table for each of the books purchased. So you would read the input (wherever it's coming from -- is this a web application or what?) and for each book input, execute an Insert statement.

But a clear description of the problem would help. So far we don't have one.

[Campbell Ritchie]

I think Paul is correct that the pictures aren't that important. The whole e-mail you sent is 328kB, so you shouldn't find it that hard to submit the images, however.
As I told you earlier, we discuss things on the forum, not on e-mail. Please find the document you sent me, highlight all its text and put it into a posting with ctrl-c ctrl-v. You may need code tags round the bit you drew in boxes.

[Manoj semwal .]

Thanks Paul for your reply.

That is Web Application Online books selling project. and iam using here shopping cart. i want to enter shoping cart data in to the database. through SQL query its ok no problem. each and every book has their ownId(ProductID).if User purchase only one book easily it would be submit in database. but when same user purchase more than one book how we can enter the data.

it is possible we can stop the null value to enter in database through for loop. if yes ,how we can do it.
[ July 11, 2008: Message edited by: manoj Rksh ]

[Campbell Ritchie]

You need to work out a basic SQL table structure.

User --- order --- book order --- book.

[Manoj semwal .]

hai campbell

My submitorder.jsp file is like this
<%

try{

String ID1=request.getParameter("ID_1");//value is coming from Text field (text field may be maximum five or may be one so iam taking maximum five field)
String ID2=request.getParameter("ID_2");
String ID3=request.getParameter("ID_3");
String ID4=request.getParameter("ID_4");
String ID5=request.getParameter("ID_5");

String QUANTITY1=request.getParameter("QUANTITY_1");
String QUANTITY2=request.getParameter("QUANTITY_2");
String QUANTITY3=request.getParameter("QUANTITY_3");
String QUANTITY4=request.getParameter("QUANTITY_4");
String QUANTITY5=request.getParameter("QUANTITY_5");

String PRICE1=request.getParameter("PRICE_1");
String PRICE2=request.getParameter("PRICE_2");
String PRICE3=request.getParameter("PRICE_3");
String PRICE4=request.getParameter("PRICE_4");
String PRICE5=request.getParameter("PRICE_5");


String SUBTOTAL=request.getParameter("SUBTOTAL");
String TOTAL=request.getParameter("TOTAL");


String first=request.getParameter("b_first");
String lastname=request.getParameter("b_last");
String address=request.getParameter("b_addr");
String city=request.getParameter("b_city");
String state=request.getParameter("b_state");
String zip=request.getParameter("b_zip");
String phone=request.getParameter("b_phone");
String fax=request.getParameter("b_fax");
String email=request.getParameter("b_email");

String id[]={ID1,ID2,ID3,ID4,ID5};
String Q[]={QUANTITY1,QUANTITY2,QUANTITY3,QUANTITY4,QUANTITY5};
String P[]={PRICE1,PRICE2,PRICE3,PRICE4,PRICE5};

no_of_rows_shead = qryObj.OrderForm(first,lastname,address,city,state,zip,phone,fax,email);
no_of_rows_shead = qryObj.Orderdetails(id,Q,P,SUBTOTAL,TOTAL);
}
catch(Exception e)
{}

%>


My qyery bean.java file is

public int Orderdetails(String id[],String Qu[],String Pri[],String SUBTOTAL,String TOTAL)
{

String select_query = "";
int return_value=0;
try
{

String maxVal = common_data_object.getString("select max(OrderId) from OrderForm");
double tid=0;
if(maxVal==null || maxVal.equals("0"))
{
tid=8070001;
}
else
{
tid=Integer.parseInt(maxVal)+1;
}
System.out.println("MAX ID FIRST"+maxVal);
select_query = "insert into Orderdetails values("+tid+",";
loop:{ ///for enter the Product value
for(int i=0;i<id.length;i++)
{
select_query+="'"+id[i]+"',";
System.out.println("select_query>>>> "+id[i]);
}
}

loop:{ //for enter the quantity value of product
for(int i=0;i<Qu.length;i++)
{
select_query+="'"+Qu[i]+"',";
System.out.println("select_query>>>> "+Qu[i]) }
}
loop:{//for enter the price of product
for(int i=0;i<Pri.length;i++)
{
select_query+="'"+Pri[i]+"',";
System.out.println("select_query>>>> "+Pri[i]);
}

}
select_query+="'"+SUBTOTAL+"','"+TOTAL+"')";

iam using two database frist user information who is purchase the books or items and second is order details which is having the users oder details .

in user details data is submit sucessfuly no problem.

orderid database is like

orderid |prductid |quantity| price| subtotal| total
in inserting data through loop but next productid is searching in next productid column. same in quantity and price.

so how can i solve this problem

[Campbell Ritchie]

Why have you changed your displayed name? I don't think it conforms to our naming policy.

I am afraid you have misunderstood the idea of a relational database completely.

You have a customer and the customer might have several orders.
So you need a table for customers with an id number, first_name and last_name (like our naming policy).
You need a table for orders with customer's id, book id, date, and number ordered.
You need a table for books with book id (you could use ISBN as id if you wish; you can find ISBNs on any book website), title, author.
The customer id in the order table links to the customer table and the book id in the order table links to the book table.


Your customer table might look like this
1 | Campbell | Ritchie
2 | Manoj | Semwal
etc etc

Your books table might look like this
1 | "K Sierra, B Bates" | "Head First Java"
2 | "Joshua Bloch" | "Effective Java 2/e"
3 | "Goetz and Bloch" | "Java Concurrency in Practice"
4 | "Bloch and Gafter" | "Java Puzzlers"

in which case orders might read
1 | 1 | 2 | 2008-07-17
1 | 4 | 1 | 2008-07-17

meaning that no 1 (Campbell Ritchie) has ordered no 1 (Head First Java) twice, and no 1 (Campbell Ritchie) has ordered no 4 (Java Puzzlers) 1 copy, both today.
It would work better if you divide up my "order" table into an order table and an "order line" table.

You are going to have to find a book or course about how to design a relational database, because you can't make sense out of the Java application until the underlying database is working.

[Manoj semwal .]

Thanks campbell for your reply
I understand your table structure but it is not a problem.
Frist I want to insert the data in to the table. like user selected the 3 books like this
--------------------------------------------------
Productid | booksname | Quantity | Price | Total
102456 Java 5 50.00 250.00
105456 VB 1 150.00 150.00
145454 C++ 4 45.00 180.00
----------------------------------------------------

Then

Customer Name: Campbell
E-mail ID: Campbell@
PhNo : 123456
Address : abc

When the user submit the information the data is going two table

userinfo.dbf
---------------------------------------------------
orderid |Customername | E-mailID | Phno | Address
---------------------------------------------------
101 campbell abc@ 123456 abc

---------------------------------------------------

Second table structure is orderinfo.dbf

---------------------------------------------------
orderid |ProductID |Quantity | Price | Total
---------------------------------------------------
101 102456 5 50.00 250.00
101 105456 1 150.00 150.00
101 145454 4 45.00 180.00
---------------------------------------------------

the entry should be like above entry
through orderid we can trace the coustomer in both table. and get the information.

so tell me how can i enter the data like in the orderinfo.dbf
i have sent the script above post so pl. tell me how can i insert in to he database.

Thanks

[Campbell Ritchie]

.dbf files? What sort of database are you using?

When I did my database course Malcolm who was lecturing said "Access isn't a database because it doesn't support transactions."

I think your principal problem is getting the SQL to work.
What you actually need to do is set up a stored procedure which incorporates a transaction. Well, I think you do; there are probably other ways to do it.

You have to find the customer number from the customer table, and the book number from the book table and then insert customer number, book number, etc into the order table. In fact, looking back to my last post on 12th July 2008, I suggested then that you need two tables to encapsulate an order; I still think that is correct.

Then you can use JDBC code to call that stored procedure from the GUI. If you are selecting books from JTable rows on the GUI that will give you the book number directly, similarly for customer names.

[Manoj semwal .]

campbell iam using MSAccess .
frist i want to know how can i insert the query
productid quantity and price in database orderinfo, where productid is array variable and price and quantity also.

[Muhammad Saifuddin]

Originally posted by manoj Rksh:
how can i insert the query productid quantity and price in database orderinfo, where productid is array variable and price and quantity also.

didn't get what you mean by productid is array variable, can you make it simpler.

[Campbell Ritchie]

Can't you get separate tables in Access? I thought you could, with the GUI tool.
The problem is that a relational database has to have relations between its tables.

[Manoj semwal .]

saiffudin

here productid having more than one value so iam using here array value here. also same in quantity and price.

still my problem is not solved.

simply i want to know how to insert the three array value in database in single insert command .
how can i use for loop for productid,quantity,and price.

pl help

[Campbell Ritchie]

Originally posted by manoj Rksh:
saiffudin

here productid having more than one value so iam using here array value here. also same in quantity and price.

still my problem is not solved.

simply i want to know how to insert the three array value in database in single insert command .
how can i use for loop for productid,quantity,and price.

pl help

I am finding your query difficult to understand.

We have discussed this several times on this thread.

You need a "customer" table, an "order" table, an "order_line" table and a "book" table.

You then insert the customer number into the order table, and get an order_id.
You insert the order_id and product_id and quantity into the order_line table.
You get the price from the book table.

You can't directly insert an array into the table, but you can use a for-each loop to insert order_lines for the same order from an array.

Actually an array is probably an inappropriate data type because the size of an array is fixed and your orders may vary in size.

[Manoj semwal .]

hai campbell

iam using same but iam using two table one table for customer details and second table for orderdetail.

in "orderdetails" table my field are as follows:
--------------------------------------------------
Orderid | ProductId | Quantity | Price | Total
---------------------------------------------------

i want to insert the data like frist book record then second book record then third book record ........n record its depends on the customer selection.

it is same like if you have filled up the education form and there are more then one field like

sno|University|Subject|Percentage|yearof passing|
1 x abc % 2000
2 xII cde % 2002
3 Graduation dddd % 2006

the data is insert in single table.
i want to use same here in ordertable.
Manoj

[Campbell Ritchie]

It won't work properly with two tables; you will get a many to many cardinality.

You want 4 tables. That way you can get cardinalities like this

Customer<0..1---1..n>Order<1..1---1..n>Order_line<0..n---1..1>book

[Manoj semwal .]

hai campbell can you help me for that for loop

for(int i=0;i<id.length;i++)
for(int j=0;j<Q.length;j++)
{
if(id[i].equals("Null"))
{
break;
}
else
{
if(Q[j].equals("Null"))
{
break;
}
else
{
out.println("ProductID: " + id[i] + "Quantity :" +Q[j]+ "<BR>");


}



}
break;

}

result is coming like

ProductID: 1592401880 Quantity :1
ProductID: 0679447385 Quantity :1
ProductID: 051716308X Quantity :1

here product id is incresed but quantity is not increased it is repeated first quantity value.
[ August 02, 2008: Message edited by: manoj Rksh ]

[Campbell Ritchie]

Please use code tags and indentation because your for loop is illegible without.
Of course you go to the first member of Q again. Then your many "break;" statements confuse things; you never get on to Q[1] because of them.
You will have to go back to fundamentals and go through that nested loop with a pencil and paper and write down where you go if i==0 then i==1 then i==2, similarly for j==0, j==1, etc. There is a bit about for loops in the Java� tutorials.

[Manoj semwal .]

Thanks campbell for the nice java tutorial link.

i had tried hard to insert the value in database as according to you in diffrent table but its taking only one value not iterating again.
pl see the code below in my OrderSubmit.jsp file.

<%@ page language="java"%>
<%@ page import="java.io.*,java.sql.*,java.util.*"%>
<jsp:useBean id="qryObj" scope="page" class="com.iGrandee.Common.QueryBean" />
<%!

String data="";
int no_of_rows_shead =0;
String s="";
%>


<%

try{

String ID1=request.getParameter("ID_1");
String ID2=request.getParameter("ID_2");
String ID3=request.getParameter("ID_3");
String ID4=request.getParameter("ID_4");
String ID5=request.getParameter("ID_5");

String PRICE1=request.getParameter("PRICE_1");
String PRICE2=request.getParameter("PRICE_2");
String PRICE3=request.getParameter("PRICE_3");
String PRICE4=request.getParameter("PRICE_4");
String PRICE5=request.getParameter("PRICE_5");

String SUBTOTAL=request.getParameter("SUBTOTAL");
String TOTAL=request.getParameter("TOTAL");

String id[]={ID1,ID2,ID3,ID4,ID5};
String Q[]={QUANTITY1,QUANTITY2,QUANTITY3,QUANTITY4,QUANTITY5};
String P[]={PRICE1,PRICE2,PRICE3,PRICE4,PRICE5};
/*

here iam taking the Productid and quantity and price value from the URL and stored in array.here iam in trouble to enter the value in database.


no_of_rows_shead = qryObj.Orderdetails(s,Q,P,SUBTOTAL,TOTAL);
// campbell can you tell after getting the value in array how can i insert in database pl give me code to insert in database.
iam using the bean fils like below
// here the above command is linked with the querbean.java file

//QueryBeam.java//
public int Orderdetails(String id,String Qu[],String Pri[],String SUBTOTAL,String TOTAL)
{

String select_query = "";


int return_value=0;
int return_value1=0;




try
{
System.out.println("INSIDE ADD TRIAL METHOD");

String maxVal = common_data_object.getString("select max(OrderId) from OrderForm");
int tid=0;
if(maxVal==null || maxVal.equals("0"))
{
tid=8070001;
}
else
{
tid=Integer.parseInt(maxVal)+1;
}
System.out.println("MAX ID FIRST"+maxVal);


//here i also used the for loop to insert the value. but its not work
select_query = "insert into Orderdetails values("+tid+",'"+id+"','"+Qu+"','"+Pri+"','"+SUBTOTAL+"','"+TOTAL+"')";

System.out.println("select_query>>>> "+select_query);

return_value = common_data_object.insertUpdateDeleteRecord(select_query);

}
catch(Exception e){}
return return_value;
}

//iam tried to use the for loop to insert the value iam also use the diffreent database to insert the value in databse . but after single value its not iterate.

pl send some solution. iam also search in google but did'nt get any good solution.
pl send some code to insert the value in database.
[ August 04, 2008: Message edited by: manoj Rksh ]

[Campbell Ritchie]

You will never get it to work until you sort out the structure of your database.
Please Google for a basic databases/SQL tutorial.

You need 4 tables for such an order: customer, order, order_line and book.
Three of those tables have a primary key, and at least two of them have foreign keys joining the four together.

You set up a stored procedure which incorporates a transaction (unless you insist on using Access which doesn't support transactions) which,

1: Inserts a customer's name etc., and records that customer's number.

2: Creates an order referring to that customer and records the order number.

3: Creates an order_line for each book ordered referring to the order and the book.

4: Repeats stage 3 as often as necessary.

You can now set up procedures to select on various details, eg who has ordered which book.
Then you can use JDBC to connect to it from a Java application.

[Manoj semwal .]

Dear Campbell iam using the same database structure .
which is you written the past post.
here iam using the table customerdetails,ProductId, BooksQuantity(purchasedbook quantity),

but my problem is how to insert the value simultenously in the three database. while the value in array.

Manoj