insert query for one supplier multiple chemicals

Question

0.00/5 (No votes)

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 27-Dec-13 23:38pm

abdul subhan mohammed

Updated 28-Dec-13 0:14am

v3

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Answer 1 · 2013-12-28T08:43:00

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.

OriginalGriff · Answer 2 · 2013-12-28T00:06:00

Solution 1

Either issue multiple INSERT commands (preferably within a Transaction) or do a multiuple row insert:

SQL

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.

Posted 28-Dec-13 0:06am

OriginalGriff

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.

JoCodes · Answer 3 · 2013-12-28T00:13:00

Solution 2

Refer

http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/[^]

Posted 28-Dec-13 0:13am

JoCodes

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.

Maciej Los · Answer 4 · 2013-12-28T01:00:00

Solution 3

Try below procedure:

SQL

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.

Posted 28-Dec-13 1:00am

Maciej Los

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.