Answered by:
HOW do I insert & update data into mapping table

Question
-
Hi Folks !
Any one there can help me ?
I have two tables as NEWS table & WEBSITE table
Third table is mapping table named WEBSITE_NEWS_MAP
News Table - news_id int not null(PK), news_description varchar(200), news_date datetime.
Website Table :- Website_id int not null (PK), Website_Name varchar(200), Website_Description varchar(1000)
Website_News_Map Table:- Website_News_Map_id int not null (PK), news_id int (FK), Website_id int (FK)
Now, I have an ADMIN PANEL in my website , where I use to add same news for multiple website.
I have created an stored procedure that successfully add the news into the NEWS table , but not into the Website_News_map table.
Below is the stored procedure , Iam using :-
ALTER
PROCEDURE [dbo].[test]
@news_heading
as text,
@news_item
as text,
@news_text
as text,
@news_date
as datetime,
@news_published_date
as datetime,
@news_create_date
as datetime,
@news_status
as tinyint,
@news_sub_text
as text,
@WebSite_ID
varchar(200)
AS
Declare
@news_id int
BEGIN
insert into news
(news_heading,
news_item
,
news_text
,
news_date
,
news_published_date
,
news_create_date
,
news_status
,
news_sub_text
)
values
(@news_heading,
@news_item
,
@news_text
,
@news_date
,
@news_published_date
,
@news_create_date
,
@news_status
,
@news_sub_text
)
set
@news_id = @@identity
Update
news
set
news_createdby = @news_id
where
news_id = @news_id
INSERT
INTO
WebSiteNews_Map
(
news_id
,
WebSite_ID
)
SELECT
news_id
,
WebSite_ID
FROM
WebSiteNews_Map
INNER
JOIN
dbo
.Split(@WebSite_ID,',') AS A ON WebSiteNews_Map.WebSite_ID = A.Element
In the above code , the Split function is being used .(I have no idea how it is being used as I havent used FUNCTIONS in SQL SERVER. I have just copy/pasted it from INTERNET).
Below is the function :-
ALTER
FUNCTION [dbo].[Split] ( @vcDelimitedString nVarChar(4000),
@vcDelimiter
nVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@vcDelimitedString - The string to be split
@vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
--- ---------- ---------------------------------------------------
***************************************************************************/
RETURNS
@tblArray TABLE
(
ElementID
smallint IDENTITY(1,1) not null primary key, --Array index
Element
nVarChar(1200) null --Array element contents
)
AS
BEGIN
DECLARE
@siIndex
smallint,
@siStart
smallint,
@siDelSize
smallint
SET
@siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE
LEN(@vcDelimitedString) > 0
BEGIN
SET
@siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF
@siIndex = 0
BEGIN
INSERT
INTO @tblArray (Element) VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT
INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET
@siStart = @siIndex + @siDelSize
SET
@vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
GO
If possible any one can help me ?
Monday, July 12, 2010 7:03 AM
Answers
-
Hi Ajay,
Thank you for your post ! I would suggest you to post your question in one of the Microsoft forum
Home › ASP.NET Forums » Data Access » DataSource Controls - SqlDataSource, ObjectDataSource, LinqDataSourceLocated here : http://forums.asp.net/54.aspx?DataSource+Controls+-+SqlDataSource%2c+ObjectDataSource%2c+LinqDataSourceRegards,
Shrikant Maske Tier 2 Application Support Server and Tools Online Operations Team- Proposed as answer by Shrikant Maske Thursday, October 7, 2010 8:31 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Friday, April 27, 2012 3:56 AM
Thursday, October 7, 2010 8:31 AM