Click here to Skip to main content
13,300,002 members (58,732 online)
Rate this:
Please Sign up or sign in to vote.
See more:
I create a Project in C# winform application on sales billing. So I create a table sales_details and fields are ->
Bill_no,B_date,Pa_name,P_addr,ph_no, pr_nam1,batch1,exp_dt1,drg_mfg1,qty1,mrp1,g_amt1,disc1,net_amt1, pr_nam2,batch2,exp_dt2,drg_mfg2,qty2,mrp2,g_amt2,disc2,net_amt2 (so on...up to 5 product details)

I don't want to create another table and also can't delete this fields from database cause it will need in future..
So,in my application i used each and every fields name(9*5) times for save and retrieve the fields values..
Please tell me if there is another way to do this.....
I'm using access database with OLEDB.......
Posted 5-Feb-13 6:54am
Updated 5-Feb-13 6:55am
Kiran Susarla 5-Feb-13 11:59am
Any specific reason of why you cannot create another table or modify the existing schema?
Jayanta Chatterjee 5-Feb-13 12:06pm
I think it will be more complicated.....
PIEBALDconsult 5-Feb-13 12:42pm
Not in the long run.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

1) Forget OLEDB, switch to EF or an other object persistence engine, but at least add a business logic layer and a model above database layer.
2) If you think in objects with properties, instead of tables with fields - it is not so hard to imagine complex structure (like property of collection of an other class) instead of flat tuples (rows in a table).
3) Once you have that, you might consider using structured datatypes as storage primitives. Like XML[^]. You might than find out that you have a powerful tool in your hand, that allows you to bring a high dynamism in your model - by not being forced to predefine fields even before using them.
4) You might even go further, and figure out that there is a world beyond relational model known as NoSQL[^], whose basic concept is the document: a structured, but absolutely dynamic entity, very close to the real world...
thatraja 5-Feb-13 14:45pm
Jayanta Chatterjee 5-Feb-13 21:49pm
Sir can you please give me some code or example??
because I'm new in C# application.
Zoltán Zörgő 6-Feb-13 6:39am
Well, that's not so easy. Especially for a beginner. For the start you should follow Mehdi Gholam's suggestion.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Bad design.

Just break your table into two tables a master and a detail table, and the (pr_nam1,batch1,exp_dt1,drg_mfg1,qty1,mrp1,g_amt1,disc1,net_amt1) values can have a foreign key to the master (you can add a LINE_NO instead of the 1,2... etc in the names).

This way you are not limited to 9 items and the design is much simpler and more performant.

Master table
Details table
Foreign KEY reference : detailstable.Master_ID -> mastertable.ID
Jayanta Chatterjee 5-Feb-13 12:12pm
Sir, Can You give me example of the query and table design structure???
Mehdi Gholam 5-Feb-13 12:16pm
I have updated the solution.
Jayanta Chatterjee 5-Feb-13 12:24pm
But Sir when i insert and retrieve the product details from Details table then i need to define each and every fields name in my applications...
like if i show all the product details in a listView then i do the same thing....
Mehdi Gholam 5-Feb-13 12:38pm
I have no idea what you are saying! You can get all the products for the master by doing "select * from mastertable where master_id=X" where X is the ID you want.
Jayanta Chatterjee 5-Feb-13 12:48pm
Sir, suppose i need a print of all the fields in printDocument then My query is "select * from mastertable where master_id=X" after that i need the fields values then My code will be
so on..

and also when i insert the value into master table then my query is :
"insert into mastertable values(9*5 times)"..
that is my problem, i don't want this long statement... :-(
Please help me....
Mehdi Gholam 5-Feb-13 12:51pm
Sorry my mistake the query should be "select * from detailstable where master_id=X"
Jayanta Chatterjee 5-Feb-13 13:03pm
Sir Your query and suggestion is too good, but my problem is
too long insert statements and if i update that product fields then it will be too long..
is there a way to concise the insert and update statement with loosing any info???
thatraja 5-Feb-13 14:45pm
Mehdi Gholam 6-Feb-13 0:57am

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.171207.1 | Last Updated 5 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100