Insert multiple rows to table based on values from other 2 tables.

I have a form to assign JOB SITES to previously created PROJECT.  The  JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.

I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES"  which has only 2 columns:  "ProjectId" and "SiteId".

What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked.  The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.

Sample:

ProjectId    -    SiteId

1   -   5

1    -   9

1    -   16

1    -   18

1    -   20

1    -   27

1    -   31

ProjectId stays the same, only values for SiteId are being different.

I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.

0
alex3003
11/21/2007 3:44:30 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

10 Replies
792 Views

Similar Articles

[PageSpeed] 2

Are the checkbox values also in the database?  If you you'll be able to write a stored procedure, pass in the ProjectId as a parameter and

INSERT INTO PROJECTSITES SELECT @ProjectId as ProjectId, js.SiteId as SiteId FROM JOBSITES js WHERE js.CheckBit = '1'


I love to display the non-secure items...
Charlie Asbornsen
Dont forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
Charles
11/21/2007 3:56:51 PM

No, the checkboxes are being populated along with the Site Address. It is a DataList template that populates as many times as there many sites available matching my select statement. The checkboxes is only used to select which sites you want to assign to the project and which SiteIds will be inserted to the new table along with the same ProjectId.

0
alex3003
11/21/2007 4:01:44 PM

That makes it a little clunkier. Perhaps you can foreach through the DataList.Rows collection and where the checkbox is checked, pass the ProjectId and SiteId as parameters to a stored procedure that inserts them to your table.


I love to display the non-secure items...
Charlie Asbornsen
Dont forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
Charles
11/21/2007 4:34:14 PM

 Charles is right.  Now, if you think that there might be so many loops and on each successful loop ( the check box is checked ) calling the SP to insert the data, what you can do is make a comma separated variable having all checked site ( like 5,8,10,15 ) and then pass that value to stored procedure after the foreach loop gets finished and process them all at once.  I've encountered a similar situation before and I did that and it served well for me.  By doing this you'll make just one db trip and your job is done.  If you want the SP which I used recursively, I can post it as a reference for you.


Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."

"Mark as Answer" the post(s) which helped you solve the problem
0
dhimant
11/22/2007 5:52:58 AM

I will agree with dhimant for passing the ID with comma separated. But i will suggest to write a function (UDF) which returns a table by extracting the data from comma separated value.

This table we can use in sub query to extract the data.


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
11/22/2007 9:20:20 AM

When u will submit the page after the selection of the desired checkboxes..

You can store the is in a string "comma seperated" and you can pass this string to the procedure whhich can have a   "insert into t2 (c1,c2) select c1,c2 from t1 where project_id in (@stringpassed)"

 

Hope this is will help.....

 

Sumit Batra


Sumit Batra
Sr. Software Programmer
0
sumitbatra1981
11/22/2007 9:37:43 AM

Thank you for help. I really would like to see that stored procedure you mentioned. It all sounds good and I just need something to start with. Same thing with the foreeach loop. I never done that and seems like unable to acustom samples found, to my situation.

0
alex3003
11/26/2007 3:09:21 PM

This thread has been marked as resolved.  If you still want help, you need to mark it as unresolved.  I just happened to notice your question by accident. 

0
david
11/26/2007 3:41:26 PM

Thank you, I marked it back as unresolved. I apreciate everyones effort. I really don't look for an easy solution here, I just trying to get starting point code. To be honest, baised on the solutions and descriptions I don't know where to start. I shoud've mention that I have a very limited experience in programming (about 5 month from the start). Help would be greatly appreciated. 

0
alex3003
11/26/2007 5:43:04 PM

Here is the stored procedure and an user defined function. Please call the Procedure from .Net by passing projectId and list of sites Ids separated by comma.

Create Procedure InsertProjectSites(@ProjectId int, @SiteIds varchar(500))
AS 
BEGIN
           INSERT INTO ProjectSites(ProjectId, SiteId)
    SELECT @ProjectId, data from dbo.Split(@SiteIds, ',')
END

 CREATE FUNCTION dbo.Split
(
 @RowData nvarchar(2000),
 @SplitOn nvarchar(5)

RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS 
BEGIN
 Declare @Cnt int
 Set @Cnt = 1

 While (Charindex(@SplitOn,@RowData)>0)
 Begin
  Insert Into @RtnValue (data)
  Select
   Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
  Set @Cnt = @Cnt + 1
 End
 
 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))

 Return @RtnValue
END

Please note that if the SiteId datatype is int or other than varchar, please use either Cast/Convert function while inserting into ProjectSite table.

 Hope this helps you.


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
11/27/2007 2:31:16 PM
Reply:

Similar Artilces:

Select rows from a table based on multiple rows of another table #2
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. Sorry technical problems! I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. I have a table which holds all of my project info and I have another table which holds discriptive project codes like project type, project category, progect life cycle.So in the pro...

Is having a trigger that inserts a row in Table 'A', when a row in same table is inserted by ADo.Net code?
I want to insert a row for a Global user  in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a s...

Select rows from a table based on multiple rows of another table
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?    The direct answer to your question is: You insert it like this:   ...

Inserting values from multiple SqlDataSource(dropdownlist) to a single row in SQL Table
Hi, I am new to ASP.net and VB programming. I use mostly the wizards and dragging of controls to the design view, so I'm not able to do code-behind programming yet. What I've done is mostly from looking at sample code and editing. The page I am having problems with is as follows; I have a 4 DropDownList controls on my page. These contain values which are taken from the SQL database, using SqlDataSource. Each control has its own Datasource. The four DDLs each take values from 4 different tables. Upon choosing all 4 values, the user clicks on the submit button and 3(yes 3) ...

Select rows from a table based on multiple rows of another table #3
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Select rows from a table based on multiple rows of another table #4
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Showing table columns from multiple tables in Formview, but insert/update/delete rows of Master table
I have a page on which I have GridView and FomView control. On selection of a row in GridView the row gets displayed in the Item template of the FormVIew. The values are the result of inner join from multiple tables i.e. table Stock,Product and Workshop.  Please let me know how I could insert/update/delete data for the base table i.e. Stock table, but be able to see the column values from the associated tables on the Itemtemplate,Edittemplate and Inserttemplate of the FormView. Thanks,Anita Hi:   Take a look at this tutorial. It's for DetailsView and same ...

Inserting Data Into Two Tables (Getting ID from Table 1 and inserting into Table 2)
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.  Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)  This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!  Insert Table1(Title,Description,Link,Whatever)Values(@title,@descri...

Insert Multiple Rows into SQL table
I am trying to insert multiple rows into a table getting data from a web form. I have 2 fields being passed from a web form to the below stored procedure @FormBidlistID Sample Data --> 500 @CheckBoxListContractors Sample Data --> 124,125,145,154,156, The below DELETE function is working great. However for some reason or another the INSERT INTO sql command is not putting seperating the string and adding rows to the database? I am not sure where the error is occuring. In the end I need the data to go into the database columns like so Table: BidlistContracto...

Insert multiple rows at once into SQL table
Hi All!Can anyone share experience or give me a hint. I'm trying to figure out a fast and convinient way (for user) to insert multiple rows into SQL table. The table has two columns ID(int, pk, identity) and Val(int) - the Val should be inserted. First I tried to use GridView. VWD generates InsertCommand, but I don't see how I can enable insert button in GridView (unlike select, delete, update buttons). Then I thought of sending MuliLine textbox values directly to db and got stuck. Finally, I remembered T-SQL 'bulk insert' command from txt o...

Inserting rows from a dataset (source
I have a question... I have a dataset that I fill from multiple datasets... basically 3 tables and 2 views fill this dataset. Now, I want to stick all that data into a database temp table. If I was doing it table to table, I could use the SELECT INTO or INSERT statement, but the views (and there is no way around using them) throw the wrench into the gears. So with that in mind I have a dataset that fills just nicely with the data from both view and tables and now I need to insert that dataset into a table. Any idea how to do this?...

insert multiple values from one table into one column from another table?
Hi, I'm attempting to create an insert statement that gets the values from multiple columns and insert them into one column from another table, is there a simple solution to do this?Here is something similar to what i'm wanting done, just it's not the correct way to do it, i want value1, value2 and value3 from one table to be combined and put into the value1 column of another table, what am i doing wrong? INSERT INTO tablename2 (value1 + value2 + value3)SELECT value1FROM tablename1 thanks in advance! INSERT INTO tablename2 (SELECT val1 + val2 + val3 FROM tabl...

Add values to a table based on values in another table
When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated. Thanks You could do it in a trigger.  They're really not hard.  It's just SQL you want to run whenever a certain action happens on your table.  Pretty st...

Web resources about - Insert multiple rows to table based on values from other 2 tables. - asp.net.sql-datasource

Multiple sequence alignment - Wikipedia, the free encyclopedia
A multiple sequence alignment (MSA) is a sequence alignment of three or more biological sequences , generally protein , DNA , or RNA . In many ...

Police officer Daniel Holtzclaw guilty of raping multiple women
For months, accusers, authorities and now an Oklahoma jury say, police officer Daniel Holtzclaw preyed on vulnerable women.

James Deen's Porn Company Faces Workplace-Safety Probe After Multiple Rape Claims 0
The California watchdog agency tasked with ensuring safety in the workplace confirmed Wednesday it is investigating porn actor James Deen's production ...

Facebook Tweaks Power Editor for Users With Multiple Ad Accounts
Facebook rolled out a new feature for its Power Editor that should greatly speed up the process for users with multiple advertising accounts. ...

Apple Needs to Offer Own TV Content after Multiple ‘Speed Bumps,’ Says FBR
FBR & Co. analyst Daniel Ives this morning reflects on reports by Bloomberg and others that Apple ( AAPL ) has shelved an effort to create a ...

Google Ventures reportedly closed its European fund after multiple stalled deals
Earlier this week, Google Ventures announced it was closing down its dedicated European fund . It had been assigned $125 million to invest, but ...

Multiple McDonald’s Locations Forced To Close After Prank Callers Convince Workers To Test Fire System ...
When I think of prank calls, I conjure up images of teenage girls huddled around their clear plastic phones, calling boys in their class and ...

OKC Police Officer Found Guilty On Multiple Counts Of Raping Black Women
Former Oklahoma City Police officer Daniel Holtzclaw will be going to prison for a very, very long time. After deliberating for well over 45 ...

Cowboys Fan Beats Up Multiple Attackers In Parking Lot Brawl
That's the best defense anyone wearing a Tony Romo jersey has ever played

Report: Robert Nkemdiche Fell From A 4th-Story Window And Suffered "Multiple Cuts"
Probable top-five NFL draft pick and Ole Miss star Robert Nkemdiche reportedly fell from the 4th floor of a building, and suffered “multiple ...

Resources last updated: 12/16/2015 9:42:35 PM