Click here to Skip to main content
14,392,678 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,

I have to create table to insert ONE SUPPLIER WITH MULTIPLE CHEMICALS

I mean: i have created separate table for supplier and separate table for chemicals, when i want to buy chemicals from one supplier
how should i create insert query for one supplier with many chemicals... and aslo ASP.NET page.


eg:
For Order Id 1, there can be 3 Items(say Item1,Item2,Item3)
For Order Id 2, there can be 5 Items(say Item 1.. Item5, all are comma seperated).

Next you want to insert the values into your database table

So the output will be like

TblOrders

OrderID Items
---------- -------- 
1 Item1,Item2,Item3

2 Item1,Item2,Item3,Item4,Item5


i dont know, how many items will b selected by user, based on dat i have write INSERT query...

plzzz suggest FRONT END as well.


thnx
abdul subhan
Posted
Updated 28-Dec-13 1:14am
v3
Rate this:
Please Sign up or sign in to vote.

Solution 4

Using a stored proc, your best bets are to pass your data in as SQL and do an insert joining to the table that creates, or to use a tabled valued parameter[^]. I am preparing an article on these options at the moment, but it's about a week away.

You seem to be asking us to write everything for you. The front end can be anything you like, so long as it creates a list of values at the end which you then insert any way you like. If the system is low use ( like a school project ) you could get away with calling a single insert proc in a loop. Whatever you do, don't do anything that involves building SQL as text and executing it, that's always a mess.
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

Either issue multiple INSERT commands (preferably within a Transaction) or do a multiuple row insert:
INSERT INTO MyTable (Col1, Col2) VALUES
(1, 2)
(1, 3)
(1, 4)
(2, 1)
(2, 2)
...
Each set of values will get a new row.
   
Comments
abdul subhan mohammed 28-Dec-13 6:20am
   
how to should i send specific column value from ASP.NET to stored procedure
OriginalGriff 28-Dec-13 6:28am
   
A stored procedure? That's more problematic, because they don't support multiple "sets" of parameters.
However, you can pass a DataSet to an SP:
http://www.codeproject.com/Tips/214492/Passing-a-datatable-to-a-stored-procedure-in-Sql-S
which should do what you want.
Rate this:
Please Sign up or sign in to vote.

Solution 2

   
Comments
abdul subhan mohammed 28-Dec-13 6:21am
   
how to should i send specific column value from ASP.NET to stored procedure
JoCodes 28-Dec-13 6:25am
   
Can use Dictionary<> or hashtable.
Rate this:
Please Sign up or sign in to vote.

Solution 3

Try below procedure:
CREATE PROCEDURE InsertOrder
    @items VARCHAR(255)
AS
BEGIN
    INSERT INTO OrdersTable (Items)
    VALUES(@items)
END

where @items variable should be passed as: 'Item1, Item2, Item3', etc.
   
Comments
abdul subhan mohammed 28-Dec-13 7:32am
   
but what if item 3 is null(means, not selected, then????)
Maciej Los 28-Dec-13 8:28am
   
Do you really nedd help with SQL? I think, you don't know how to get the collection of selected items. Am i right?
Christian Graus 28-Dec-13 14:40pm
   
Does this work ? I didn't think it did ?
Maciej Los 28-Dec-13 15:03pm
   
It depends... If Items is varchar field, then it should works.
Christian Graus 28-Dec-13 15:21pm
   
Oh, I see. But, is that what he wants, to store his comma separated list as one row ?
Maciej Los 28-Dec-13 15:25pm
   
I'm not sure... When you look at TblOrders, it looks like it is a varchar data type.
Christian Graus 28-Dec-13 15:30pm
   
You're right. I am sure the user does not what this ( and they are stupid if they do ), but the question is a long way from clear.

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




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