INSERT Among 3 Tables And 1 Is A Junction Table

Sep 23, 2007

Looking for some help here, so thanks for any input. I'm a painfully new newbie to SQL scripting.

Situation:
I have a simple database to handle an organization's events. Those
events are categorized and may have more than one category assigned to
each event. I need a maintenance Web Form to update their events.

Set
Up (so far): I have a CATEGORIES table. It has an auto incrementing UID
and a Category name field. This table will be updated so infrequently,
I plan to update it manually (no need for a maintenance Web Form). Next
is the EVENTS table. It also has an auto incrementing UID along with
several fields (Title, Location, DateTime, etc.). The junction table is
named jEVENTSCATEGORIES. It has its own auto incrementing UID along
with 2 fields named for the primary keys (UIDs) in the other 2 tables
(EventsID and CategoryID).

Goal: On the Web Form, I have a
CheckBoxList control that's populated by the CATEGORIES table. One or
more categories can be checked for each event. I have a FormView
control that allows Edits and Inserts.

Need: I need to know the
INSERT statement(s) required to insert a new record in the EVENTS table
and then to update one or more rows in the junction table
(jEVENTSCATEGORIES).

My Assumptions: I know how to create
SELECTs and INSERTs and whatnot, but I'm not certain how to create a
second INSERT statement that is based on a variable (or output) from a
previous action. So any help would be MUCH appreciated. Thanks for your
time!

View 7 Replies


ADVERTISEMENT

Insert Statement For Junction Table On Many To Many Relationship Ms Sql Server 2005

Oct 10, 2007

Hello,

This seems like such a simple problem but I am new developer even through I have been on the administration end of things for some time. I will go into more detail about my tables and there relationships below. Anyway, I am trying to create a many-to-many relationship within ms sql server 2005. I have created both of my primary tables and also a junction table per the directions on microsoft's website all per ms's instructions as stated here...

http://msdn2.microsoft.com/en-us/library/ms178043.aspx

At then end of these instruction it states as a NOTE: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a table, see How to: Create Insert Results Queries (Visual Database Tools).

http://msdn2.microsoft.com/en-us/library/ms189098.aspx

and these directions do not go into detail on how to do an insert on a junction table. And I cant find out how to do this anywhere on the internet... I did create a T-SQL INSERT statement in a trigger as listed below but I end up getting an error AS LISTED BELOW....

Here is how I set everything up...

PetitionSet table consists of:

PetitionSetID int auto-increment primary key
PetitionSetName varchar(50) no nulls
PetitionSetScope varchar(50) no nulls


the Petition table consists of:

PetitionID int auto-increment primary key
PetitionSetID int no nulls
PetitionName varchar(50) no nulls


the SetToPetitionJunction table consists of:
PetitionSetID int
PetitionID int

And, there is a composite key made up of both the PetitionSetID and PetitionID fields.

I have created the foreign key relationships with DEFAULT VALUES from the SetToPetitionJunction table to each column's respective corresponding column in each of the tables: PetitionSet and Petition.


The trigger is on the Petition table and it has the following code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER .[SetToPetitionJunctionTrigger]
ON .[dbo].[Petition]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO SetToPetitionJunction
(PetitionID, PetitionSetID)
SELECT Petition.PetitionID, PetitionSet.PetitionSetID
FROM Petition INNER JOIN
PetitionSet ON Petition.PetitionSetID = PetitionSet.PetitionSetID

END

I have created an asp.net 2.0 front end to insert values into the PetitionSet table and the Petition Table. And in the detailsview for the Petition table I manually insert the PetitionSetID field to the number that corresponds to an auto-generated number on the primary key of the PetitionSet table. So I am maintaining referential integrity...

The first time it works and inserts one record in the Junction table containing the PetitionSetID from the PetitionSet table and the PetitionID from the petition table.

Then when I try to add in another petition for the same petition set number just like I did the first time and then I get this error...

Violation of PRIMARY KEY constraint 'PK_SetToPetitionJunction'. Cannot insert duplicate key in object 'dbo.SetToPetitionJunction'.
The statement has been terminated.



David

All Rights Reserved in All Media







View 3 Replies View Related

SQL Query Question : How To Add Data To Two Tables Connected By Third Junction-table.

May 1, 2008


Hello specialists.

Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.


Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E

How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within junction-table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.

The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo

Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison

Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C

4 Group_E

Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4


I hope you can help me cracking this nut.

Thx in advance. Greetings Fred

View 7 Replies View Related

Design Question Regarding Junction Tables

Jan 18, 2008

I'm designing a database with 3 tables called Function, Test andScene.A Function has multiple Tests, but a Test has only one Function. Amany to many relationship exists between Test and Scene therefore Ineed a junction table between these two tables - giving 4 tables intotal. The Test table would store a foreign key, the primary key ofthe Function table.There is a problem with design though and that is that Functions andScenes are actually defined before the Test is defined. Therefore itshould be possible to create a Function and add to id its Scenes,before Tests have been defined. In other words, Scenes are as much apart of a Function as they are of Tests. Tests are in fact only ofrelavence to testers. Anyway, to satisfy this scenario, a Junction boxis also needed beween Function and Scene. This creates a loop betweenall tables.Is this a good approach? Any other suggestions or advice on thematter? Any advice regarding data integrity?Thanks,Barry

View 1 Replies View Related

Junction Table SQL Sentence - Please Help Me Out

Feb 17, 2006

I have two tables. A table called users (content speaks by it self) and a table called groups.
Since i want every user to be able to be a member of several groups and, of course, a group to have several users i have made a junction table called jt.
The junction table contains userId's and groupId's according to the user-group bindings. The primary key(identity) in the junction table is a int named jtId.
Now i want to take out all posts from the junctiontable and the corrresponding userName (s) from the users-table and the corresponding groupName from the groups-table.
Can somebody please help me to make a SQL command that will di that for me. I have tried with INNER JOIN and several SELECTS in the same command.
Thanks in advance, Greetings from Esben

View 4 Replies View Related

Junction Table/ Many To Many Relationships

May 9, 2007

I am trying to update 2 tables at the same time by adding new records to them and then making sure that they are related on my junction table?



Table1

packageID

Packagename



Table2 (JunctionTable)

PackageID

JobID



Table3

JobID

JobName



And I want to create a new package and new Job Name at the same time I would need to make multiple insert statements



First take care of the new Package

INSERT INTO Table1 (PackageName) Value (@PackageName)


Then Take Care of the JobName

INSERT INTO Table3 (JobName) Value (@JobName)



Finally marry the two together

but how?



View 7 Replies View Related

Retrieving Data From A Junction Table

Jan 30, 2008

Hi i have a junction table(UserGroups) which is linking my users table with my groups table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change




UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: MUFC

UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC
Below is my SQL statement; 
SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')

View 6 Replies View Related

SQL 2012 :: Junction Table In Many To Many Relationship

May 5, 2014

Why do we need a junction table in a many to many relationship? Why can't everything be in just 2 tables?

View 9 Replies View Related

Junction Table Design Options

Jul 20, 2005

As an example, I am building an authentication mechanisim that will usedata in the 3 left tables to determine rights to objects in adestination table, diagrammed below. In this structure, multiplerecords in the left tables will point to multiple records in the righttable. Normally, I would approach this problem using junction tables(LeftID, RightID) to create many-to-many joins.However, given the structure of each table is nearly identical (as faras the linking IDs are concerned), I could also use a single junctiontable with columns for each available table ID (LeftID1, LeftID2,LeftID3, RightID). In this table, only two IDs would be utilized perrow (LeftIDx -> RightID).In both designs, the needed rights information is returned from fairlysimple views, thus the end result is equivalent. The advantage to thesecond, multi-ID junction table design, is a simpler databasestructure. However, never using this approach before, I am unsure ofthe potential future performance impacts.Any significant downsides to this second design? Examples of anabbreviated structure follow:Data Tables-----------LeftTable1LeftID1 (int)Data1LeftTable2LeftID2 (int)Data2LeftTable3LeftID3 (int)Data3DestinationTableRightID (int)DataLinking tables option 1-----------------------JunctionTable1LeftID1RightIDJunctionTable2LeftID3RightIDJunctionTable3LeftID3RightIDLinking table option 2----------------------JunctionID1 (int)ID2 (int)ID3 (int)DestinationID (int)

View 1 Replies View Related

Stopping Duplicate Values From A Junction Table

Feb 28, 2008

Hi i have a junction table(UserGroups) which is linking my "users" table with my "groups" table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change.




UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: MUFC

UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC
Below is my SQL statement; 
SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')

View 11 Replies View Related

Stopping Duplicate Values From A Junction Table

Mar 1, 2008

Hi i have a junction table(UserGroups) which is linking my "users" table with my "groups" table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change.

UserName: Edwin Carols
UserAge: 28
JobTitle: Manager
GroupName: MUFC


UserName: Edwin Carols
UserAge: 28
JobTitle: Manager
GroupName: AFC


Below is my SQL statement;


Code:

SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')



And my table structure is like;

Users; UserID, UserName, UserAge
Groups; GroupID, GroupName
UserGroups; UserGroupID, UserID, GroupID

View 3 Replies View Related

Junction/link Table Primary Key: Best Practice Using SQL Server

Nov 30, 2005

Hi all,
This is a bit of a general question regarding SQL Server link tables that i hope someone can find the time to answer.

I am looking for the best practice. If I have 3 tables

1) tblCompetition
2) tblTeams
3) tblTeamsInCompetition (this is the link table)

I know the link table should have a related CompetitionID and TeamID,
but should the link table also have a primary key that is an identity
autoincrement by 1 just as you would with the other two tables. In
Access I have never done this, however in SQL Server I have read that
you should do this (but now I can't find the documentation again which
prompts me to ask the question here).

Thanks for taking the time to answer this question.

View 7 Replies View Related

How To Write A Select Query To Return Records In A Many-to-Many Relationship - Junction Table???

Oct 26, 2006

Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!  

View 6 Replies View Related

Insert Data Into A Table From Two Tables Into A Single Table Along With A Hard Coded Value?

Feb 9, 2012

I'm trying to insert data into a table from two tables into a single table along with a hard coded value.

insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)

I get the following-

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

Do I need to use a cursor?

View 5 Replies View Related

Table Insert For Three Tables??

Oct 16, 2007

I have 3 tables, Type, Subtype, and Subtype2. All which are related by the ID of preceding table.  Using a 3 textboxes and a button I want to insert new Types and subtypes to the tables.  How do I go about identifying the new types new ID insert it in to the other tables?  I am using ASP.net (vb) with sql server.

View 3 Replies View Related

Insert From One Table Into 2 Tables

May 20, 2008

Hi, i need to create a stored procedure that will take data from one table and insert it into one tabe.

I have 3 tables
tblTest is the table that i need to take the informations from.

intD1 to intD4 is a score

I don't know if iexplain my sefl correctly tks in advance!
tblTest
1 , 2 , 'A', 'B', 0 , 1 ,2 , 4

i need this

tblResultat
1, 2, 'A','B'

tblResultatXReponse
1,1,0,0
2,1,1,1
3,1,2,2
4,1,4,3

tblTest
intTestId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2
intD1
intD2
intD3
intD4

tblResultat
intResultatsId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2


tblResultatXReponse
intResultatXReponseId autonumber
intResultatsId
intReponseDesc
intOrdre

View 5 Replies View Related

Transact SQL :: Insert Records To A Table From Different Tables

Aug 30, 2015

Lets say we are executing this query below to retrieve each customer and the amount associated to a table 

"INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT from
tblSaleStatementCustomer  WHERE CustomerType = 'Dealer' GROUP BY CustomerName"

Which display the data like below

DEALER                AMOUNT
------------------------------------------------
ABC                          2000
XYZ                          1000    

However I have one more table TABLE2 which contains two columns

DEALER                OPENING
-------------------------------------------------------
ABC                               5000
XYZ                               7000

I want to modify my query in such a way that AMOUNT column should also add

OPENING from TABLE2 So that I must get the result like below

DEALER                AMOUNT
------------------------------------------------
ABC                            7000
XYZ                             8000    

View 10 Replies View Related

Select From Multiple Tables, Insert In Temp Table

Feb 18, 2004

What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?


Any ideas?

Thanks in advance.

View 6 Replies View Related

Duplicate Tables Insert/Update In Another Table? Triggers?

Mar 6, 2002

I want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns?

Thanks for help.

View 2 Replies View Related

Insert Items From One To Table To Multiple Smaller Tables

Nov 15, 2004

I have a table that I filled with data imported from another database.

What I need to do is now take this huge table and break apart the information and put it into 5 smaller tables.

So I have a huge insert statement.

I have one main table called Property with two keys. One key is a "Prop_ID" and the other is "owner" where Prop_Id is a automated unique ID. Once the information is inserted into that table, I then get the Unique ID that it was given, and I then used that ID to insert into the other tables.

The problem I am encountering is I keep getting the following error

Violation of PRIMARY KEY constraint 'PK_Prop_Res_Detail'. Cannot insert duplicate key in object 'Prop_Res_Detail'.
The statement has been terminated.

I have an idea what might be going wrong, but I am not sure. What I want to happen is that I want the query to look at the first row of the huge table and then do all 4 of the inserts, and then go to the next row. But I think it is trying to all the inserts into the property table, and then go on to the Prop_Res_Detail table and that is why I am getting that error.

Any help is greatly appreicated.

here is the code..


Code:

CREATE PROCEDURE [dbo].[Insert_Properties]

AS

DECLARE @Prop_ID Int

SET NOCOUNT ON

INSERT INTO Property(Acres,
Assoc_Phone,
Assoc_Cell,
AppraisalForm,
Area,
Assess_Account,
AttachDetach,
Block,
City,
County,
Directions,
DOM,
ER_EA,
FloodZone,
Import_From,
Import_ID,
Insert_Date,
LandSQFT,
LandSQFTDim,
LegalRemarks,
ListAppraiser_ID,
ListAssoc_ID,
ListBroker_ID,
ListDate,
Listing_Office_Remarks,
ListPrice,
Lot,
Map,
Num_Images,
Office_Phone,
Original_ListPrice,
Owner,
Pending_Date,
PhotoName,
PropSubType,
Prop_Type,
Quad,
Remarks,
State,
Status,
StreetDir,
StreetNum,
StreetName,
Township,
UnitNumber,
ZipCode)

SELECT CONVERT(FLOAT(8), Acres),
CONVERT(Varchar(25), Assoc_Phone),
CONVERT(Varchar(25),Assoc_Cell),
CONVERT(Varchar(50), AppraisalForm),
CONVERT(Varchar(10), Area),
CONVERT(Varchar(50), Assess_Account),
CONVERT(Varchar(20), AttachDetach),
CONVERT(Varchar(20), Block),
CONVERT(Varchar(40), City),
CONVERT(Varchar(50), County),
CONVERT(Varchar(1000), Directions),
CONVERT(int, DOM),
CONVERT(Varchar(10), ER_EA),
CONVERT(Varchar(50), FloodZone),
CONVERT(Varchar(20), Import_From),
CONVERT(Varchar(20), Import_ID),
CONVERT(datetime, Insert_Date, 101),
CONVERT(Varchar(20), LandSQFT),
CONVERT(Varchar(50), LandSQFTDim),
CONVERT(Varchar(2000), LegalRemarks),
CONVERT(Varchar(50), ListAppraiser_ID),
CONVERT(Varchar(50), ListAssoc_ID),
CONVERT(Varchar(50), ListBroker_ID),
CONVERT(varchar(11), ListDate),
CONVERT(Varchar(1000), Listing_Office_Remarks),
CONVERT(Varchar(10), ListPrice),
CONVERT(Varchar(20), Lot),
CONVERT(Varchar(10), Map),
CONVERT(Varchar(10), Num_Images),
CONVERT(Varchar(25), Office_Phone),
CONVERT(Varchar(10), Original_ListPrice),
CONVERT(Varchar(50), Owner),
CONVERT(datetime, Pending_Date, 101),
CONVERT(Varchar(50), PhotoName),
CONVERT(Varchar(25), PropSubType),
CONVERT(Varchar(20), Prop_Type),
CONVERT(Varchar(10), Quad),
CONVERT(Varchar(1000), Remarks),
CONVERT(Varchar(25), State),
CONVERT(Varchar(10), Status),
CONVERT(Varchar(4), StreetDir),
CONVERT(Varchar(15), StreetNum),
CONVERT(Varchar(50), StreetName),
CONVERT(Varchar(20), Township),
CONVERT(Varchar(6), UnitNumber),
CONVERT(Varchar(20), ZipCode )

FROM Imported_Closed_Property_From_MLS


SET @Prop_ID = @@Identity

/*Property Res Table */
INSERT INTO Prop_Res_Detail(Prop_ID,
Addition,
Appliances,
Basement_Area,
BasementDesc,
Builder,
Construction,
Cool,
Dining,
District_School,
Energy,
Exterior_Features,
Fence,
Floors,
Foundation,
FP,
FP_Type,
Garage_Attach_Detach,
Garage_Cap,
Handicap,
Heat,
HOA,
HOA_Fee,
HOA_Inc,
HOA_Period,
Inlaw_Plan,
Interior_Features,
Livestock,
Lot_Desc,
Mechanical,
NumLivingArea,
Num_Baths,
Num_Beds,
Num_Levels,
Other_Info,
OvenDesc,
Owner,
Parking,
Patio,
Patio_Dim,
Perc_Basement_Com,
Pool,
Pool_Type,
Prop_Faces,
Range,
RangeDesc,
Remodeled,
Rental,
RentalAmount,
Roof_Type,
Roof_Year,
RoomOther,
Sect,
SQFT,
SQFTSource,
Style,
Tax_Amount,
Tot_Rooms,
UtilityAvailable,
WindowType,
Year_Built)

SELECT @Prop_ID,
CONVERT(Varchar(50), Addition),
CONVERT(Varchar(100), Appliances),
CONVERT(Varchar(25), Basement_Area),
CONVERT(Varchar(100), BasementDesc),
CONVERT(Varchar(50), Builder),
CONVERT(Varchar(50), Construction),
CONVERT(Varchar(20), Cool),
CONVERT(Varchar(10), Dining),
CONVERT(Varchar(60), District_School),
CONVERT(Varchar(100), Energy),
CONVERT(Varchar(100), Exterior_Features),
CONVERT(Varchar(40), Fence),
CONVERT(Varchar(100), Floors),
CONVERT(Varchar(40), Foundation),
CONVERT(Varchar(50), FP),
CONVERT(Varchar(40), FP_Type),
CONVERT(Varchar(50), Garage_Attach_Detach),
CONVERT(Varchar(25), Garage_Cap),
CONVERT(Varchar(20), Handicap),
CONVERT(Varchar(20), Heat),
CONVERT(Varchar(40), HOA),
CONVERT(Varchar(30), HOA_Fee),
CONVERT(Varchar(100), HOA_Inc),
CONVERT(Varchar(20), HOA_Period),
CONVERT(Varchar(20), Inlaw_Plan),
CONVERT(Varchar(100), Interior_Features),
CONVERT(Varchar(40), Livestock),
CONVERT(Varchar(400), Lot_Desc),
CONVERT(Varchar(100), Mechanical),
CONVERT(Varchar(10), NumLivingArea),
CONVERT(Varchar(5), Num_Baths),
CONVERT(Varchar(5), Num_Beds),
CONVERT(Varchar(30), Num_Levels),
CONVERT(Varchar(100), Other_Info),
CONVERT(Varchar(100), OvenDesc),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(100), Parking),
CONVERT(Varchar(25), Patio),
CONVERT(Varchar(50), Patio_Dim),
CONVERT(Varchar(25), Perc_Basement_Com),
CONVERT(Varchar(20), Pool),
CONVERT(Varchar(20), Pool_Type),
CONVERT(Varchar(40), Prop_Faces),
CONVERT(Varchar(20), Range),
CONVERT(Varchar(100), RangeDesc),
CONVERT(Varchar(50), Remodeled),
CONVERT(Varchar(10), Rental),
CONVERT(Varchar(10), RentalAmount),
CONVERT(Varchar(20), Roof_Type),
CONVERT(Varchar(5), Roof_year),
CONVERT(Varchar(100), RoomOther),
CONVERT(Varchar(10), Sect),
CONVERT(Varchar(10), SQFT),
CONVERT(Varchar(50), SQFTSource),
CONVERT(Varchar(100), Style),
CONVERT(Varchar(10), Tax_Amount),
CONVERT(Varchar(5), Tot_Rooms),
CONVERT(Varchar(100), UtilityAvailable),
CONVERT(Varchar(50), WindowType),
CONVERT(Varchar(5), Year_Built)
FROM Imported_Closed_Property_From_MLS

/*Sold Info Table */
INSERT INTO Sold_Info(Prop_ID,
Buy_Pts,
Closed_Date,
Closed_Price,
Closed_Price_SQFT,
COOP_Sales,
Days_On_Market,
InterestRate,
Lender,
LoanAmount,
LoanTerms,
Loan_Years,
Origination_Fee,
Owner,
SellerConcessions,
LoanType,
Sold_Remarks)

SELECT @Prop_ID,
CONVERT(Varchar(10), Buy_Pts),
CONVERT(datetime, Closed_Date, 101),
CONVERT(Varchar(10), Closed_Price),
CONVERT(Varchar(50), Closed_Price_SQFT),
CONVERT(Varchar(50), COOP_Sales),
CONVERT(Varchar(5), DOM),
CONVERT(Varchar(10), InterestRate),
CONVERT(Varchar(50), Lender),
CONVERT(Varchar(10), LoanAmount),
CONVERT(Varchar(50), LoanTerms),
CONVERT(Varchar(10), Loan_Years),
CONVERT(Varchar(10), Origination_Fee),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(100), SellerConcessions),
CONVERT(Varchar(25), LoanType),
CONVERT(Varchar(1000), Sold_Remarks)
FROM Imported_Closed_Property_From_MLS

/*Remarks Table */
INSERT INTO Remarks(Prop_ID,
App_Date,
App_Remark,
Contract_Date,
Inspection_Type,
Owner,
PendingSalesPrice,
PendingSaleComments)

SELECT @Prop_ID,
CONVERT(datetime, App_Date, 101),
CONVERT(Varchar(1000), App_Remark),
CONVERT(datetime, Contract_Date, 101),
CONVERT(Varchar(50), Inspection_Type),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(10), PendingSalesPrice),
CONVERT(Varchar(1000), PendingSaleComments)
FROM Imported_Closed_Property_From_MLS

GO

View 2 Replies View Related

Merge Data From Two Tables Into One Table - No Updates/only Insert

Sep 10, 2007


Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.
I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:
Table A
--------------
1 test test1 test2 test3 test4 test5
2 test test6 test7 test8 test9 test10

Table X
------------
1 test test1 test2 test99 test4 test5
2 test test98 test97 test 96 test95 test94
--------------------------------------------------------
Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..
The new Table A should look like

NEW Table A'
-----------------

test test1 test2 test3 test4 test5
test test6 test7 test8 test9 test10
test test98 test97 test 96 test95 test94


------------------------------------
I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

View 6 Replies View Related

Insert Multiple Rows To Table Based On Values From Other 2 Tables.

Nov 21, 2007

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.

View 10 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 9 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 12 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE &&amp; BUKL INSERT:How To Designate The Primary-Foreign Keys &&amp; Set Up Relationship?

Jan 28, 2008

Hi all,

I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:

-- ImportCSVprojects.sql --

USE ChemDatabase

GO

CREATE TABLE Projects

(

ProjectID int,

ProjectName nvarchar(25),

LabName nvarchar(25)

);

BULK INSERT dbo.Projects

FROM 'c:myfileProjects.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=======================================
-- ImportCSVsamples.sql --

USE ChemDatabase

GO

CREATE TABLE Samples

(

SampleID int,

SampleName nvarchar(25),

Matrix nvarchar(25),

SampleType nvarchar(25),

ChemGroup nvarchar(25),

ProjectID int

);

BULK INSERT dbo.Samples

FROM 'c:myfileSamples.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=========================================
-- ImportCSVtestResult.sql --

USE ChemDatabase

GO

CREATE TABLE TestResults

(

AnalyteID int,

AnalyteName nvarchar(25),

Result decimal(9,3),

UnitForConc nvarchar(25),

SampleID int

);

BULK INSERT dbo.TestResults

FROM 'c:myfileLabTests.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.

2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?

Please help and advise.

Thanks in advance,
Scott Chang

View 6 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

Apr 9, 2007

Hello
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"


Could You please tell me what am I missing here ?
Thanks a lot.
 

View 5 Replies View Related

Insert Records From Foxpro Tables To SQL Server Tables

Apr 22, 2004

Hi,

Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:

1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.

I only know the following ways to import Foxpro data into SQL Server:

#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables

I'm thinking whether the following choices will be better than the current way:

1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2

Thank you for any suggestion.

View 2 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

How Do You Insert Into Two Tables From One Insert? Or Even How Would You Using Two Inserts?

Mar 18, 2007

I currently insert into one table with:                 SqlCommand comm = new SqlCommand("INSERT INTO UsersTable (UserName, Password, Email) VALUES (@person, @pass, @email)", sqlConnection);                comm.Parameters.AddWithValue("@person", usrnmeLbl.Text);                comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text);                comm.Parameters.AddWithValue("@email", hemailLbl.Text);but I realized that there's another table related to this table and I need to have something go in it so that the users data will be recorded at the same pace. So I tried:                 SqlCommand comm = new SqlCommand("INSERT INTO UsersTable, FatherHistTable (UserName, Password, Email), (Father) VALUES (@person, @pass, @email), (@father)", sqlConnection);                comm.Parameters.AddWithValue("@person", usrnmeLbl.Text);                comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text);                comm.Parameters.AddWithValue("@email", hemailLbl.Text);                comm.Parameters.AddWithValue("@father", fthrsNmeLbl.Text);Not working, so I am thinking I must do two inserts:                  SqlCommand comm = new SqlCommand("INSERT INTO
UsersTable (UserName, Password, Email) VALUES (@person, @pass,
@email)", sqlConnection);
                comm.Parameters.AddWithValue("@person", usrnmeLbl.Text);
                comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text);
                comm.Parameters.AddWithValue("@email", hemailLbl.Text);                 SqlCommand comm2 = new SqlCommand("INSERT INTO
FatherHistTable (Father) VALUES (@father)", sqlConnection);
                comm2.Parameters.AddWithValue("@father", fthrsNmeLbl.Text); Is that the only way to go about it then? Thanks in advance for any explanations. 

View 26 Replies View Related

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).

Jan 31, 2008

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? 
 

View 3 Replies View Related

Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage

Apr 21, 2008

An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
 
thanks

View 7 Replies View Related

OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.

Mar 24, 2008

Is there a way to avoid entering column names in the excel template for me to create an excel file froma  dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
 Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'  
IF @File_Name = ''      Select @fn = 'C:Test1.xls'     ELSE      Select @fn = 'C:' + @File_Name + '.xls'        -- FileCopy command string formation     SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn     
-- FielCopy command execution through Shell Command     EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT        -- Mentioning the OLEDB Rpovider and excel destination filename     set @provider = 'Microsoft.Jet.OLEDB.4.0'     set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn   
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet1$]'')      '+ @sql1 + '')         exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet2$]'')      '+ @sql2 + ' ')   
 
 

View 4 Replies View Related

Insert Command Fails When I Want To Insert Records In Data Table

Apr 20, 2008

On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer  form that logged in users have access to.
it has 2 questions including a text box for Q1 and  dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
 
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
 Be very helpful if somebody could check the code and advise where the problem is??
 
 
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
 
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
 
</tr>
</table>
  Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
 
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
 
 
</asp:SqlDataSource>
</asp:WizardStep>
 
 Event Handler
 
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
 SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
 
}
 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved