Insert Multiple Items From An ArraryList Into Db

Jan 4, 2008

Hi
I am working on a site right now. Basicly how it works is they choose from a list of checkboxes what they want to be quized on. This all gets stored in an arrayList. Once they done that they have an option to save there settings so they don't have to go through the list and choose everything again if they want to come back and do that same quiz.
So I am trying to take all those options in the arrayList and put them into the database.
So I have 4 questions.
 Question 1.
This is how I was thinking my database design should be.

This table that all this stuff is going to be inserted into is called QuickLinks. Originally I was thinking of having a PK to FK relationship with the Asp membership table called Membership(or User Table was another posibility). I am now thinking that is a waste of time and pointless. My first reasoning was well this way I can get the UserID from that table but now I realize no I actually need the UserID to be in the QuickLink table before I try to join the two tables together. With joining the 2 tables I don't get anything out of it since if I just do a check if the User is logged in before I insert the data into the database I can just slip the userID in there as well. Then the QuickLink table should have everything it needs.
 
So My question is I am thinking of having the quick Link talbe to operate like this. Say you have 50 items in the array(this is all from the same item). Each item will gets its own row that will have the UserID,CharacterName,CharacterImagePath, QuickLinkName plus the LinkID.
So when I need to call that information I just have to look at 2 pieces of information. Find all the rows that have the same QuickLink name and UserID. So is this a good way to store the data?
 Question 2.
Currently right now I have UserID as unquieIdentifer but  I am guessing if I have 50 rows with  the  same UserID  it won't like that very much since its not Unquie. Should I just change that to a varChar?
LinkID      UserID                                                                                               CharacterName CharacterImagePath
----------- ---------------------------------------------------------------------------------------------------- ------------- --------------------------------------------------
1           15bde69a-c6fe-4159-b60f-405a013ae4c3                                                                 hi            image
2           15bde69a-c6fe-4159-b60f-405a013ae4e2                                                                 hi            image
3           15bde69a-c6fe-4159-b60f-405a013ae4c3                                                                 hi            image

Find it actually kinda weird that it allowed 2 of the same UserIDs in. 
Question 3.
Right now my primary key is LinkID. I just made that up but right now I have no use for it since I can't think of anything that would be useful to be a primary key but I figured I need a primary key. So does anyone have any ideas if I should leave that as the primary key or not have one? Or what I should do?
Question 4
I been playing around on a new webpage I made so I don't have so much cludder code to get in the way I don't screw anything up(I just find it sometimes easier to make a watered down version of what I want and get that work before I tackle the big version and have no clue what is going on. I am not sure if this is the best approach). So right now I  have text box that I type in the UserID into(I have gotten  the part to work where it checks the login  of the user and grabs it but  I don't want use it for now since it might complicate things a bit).
I have then a textbox where you type in the image path and then 2 other textboxes that you type in the characterName. Those 2 textBoxes for the characterName gets stored in array(so it is almost like the real thing I want to do). I then tired to put my code in a for loop and tired to keep inserting it in until everything from the array was out.
 However it does not work.
 I get this error
The variable name '@UserID' has already been declared. Variable names must be unique within a query batch or stored procedure.
 
I am not sure what it means or wants. 
 My code.
ArrayList InsertCharcter = new ArrayList();
InsertCharcter.Add(TextBox1.Text);
InsertCharcter.Add(TextBox2.Text);

SqlCommand comm;
string holdInsert = "INSERT INTO QuickLinks (UserID, CharacterName, CharacterImagePath) VALUES (@UserID,@CharacterName,@CharacterImagePath)";
comm = new SqlCommand(holdInsert, getConnection());
Guid userID = new Guid(txtuserID.Text);


comm.Connection.Open();
for (int i = 0; i < InsertCharcter.Count; i++)
{
comm.Parameters.Add("@UserID", SqlDbType.VarChar);
comm.Parameters["@UserID"].Value = txtuserID.Text;
comm.Parameters.Add("@CharacterName", SqlDbType.VarChar);
comm.Parameters["@CharacterName"].Value = InsertCharcter[i].ToString();
comm.Parameters.Add("@CharacterImagePath", SqlDbType.VarChar);
comm.Parameters["@CharacterImagePath"].Value = txtImage.Text;

comm.ExecuteNonQuery();
         }
        comm.Connection.Close();

 
 Thanks

View 2 Replies


ADVERTISEMENT

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

One Row From Multiple Tables With Multiple Items

Mar 11, 2008

I have two tables, Personnel and Emails.
Personnel
*per_personnelID (int)
per_name (varchar)
per_office (varchar)
per_cell (varchar)

Emails
*eml_emailID (int)
eml_personnelID (int)
eml_name (varchar)

I can have a user that has multiple emails but I need to return them all in one row in addition to the name, office and phone. And I need to do this regardless of the number of emails they have. Please, can anyone help the newbie?

View 4 Replies View Related

Search For Items Across Multiple Table...

Oct 24, 2007

I have been working on a database and I have imported a number of lists into multiple tables.

I am having a lot of trouble writing the SQL code to be able to query across multiple tables. For instance, I want to know if john doe is on all five lists or if he is only on 3 lists (search by student name).

Can someone help me with this?

Luin.

View 2 Replies View Related

Multiple Instances Of Menu Items

Dec 10, 2006

When using SQL Server Management Studio Express I get multiples of all the standard Windows menu items as well as in the dropdowns. I have also experienced this in the Visual Basic 2005 Exprerss edition IDE. What is causing this?

View 10 Replies View Related

How To Show Multiple Items In A Textbox?

Nov 27, 2007

In a multi-parameters report I want to show the parameter(s) user selected in a text box. How to set this up. For example user selected A, B, C in the parameter dropdown etc in the text box I want to show A, B, C.
Thanks

View 1 Replies View Related

Multiple Items On A Report Problem?

Nov 30, 2007

We are trying to do something basic with SRS but we can't seem to get iit to work. Here's the scenario: We want to send out a monthly report that consists of numerous subreports. Each subreport stands on its own withh its own data source.

On our main report we drag the subreports into position as we want them to display. But when we run the report sometimes we get 2 subreports on 1 page, other times we get 1.... Bottom line is that we want to get 7-8 subreports on one page. Any ideas on what we can do to get everything on one page?

View 4 Replies View Related

How Do I Store Multiple Selected Listbox Items

Jan 12, 2008

Hi There
This is probably realy simple but since im still a newbie some help would be appreciated.  I have a listbox bound to an sqldatasource which has names  of people from my sql database employeeDetails table.  I simply want to allow someone to select to select multiple names and insert them into another field in my database. 
I have this bit workng, ie they can select multiple people on the list, it does insert, however only the first selected name, not the others.Can you please help me out
Kind Regads
Dan

View 3 Replies View Related

Insert Values And Selected Items

Oct 2, 2007

Can this be done?
I have a procedure where I have Values and Selected Table items that have to be inserted into a nother table that require them to be stored in the same record to the data correctly placed?
 

View 1 Replies View Related

Simple Explanation To Concatenating Multiple Items Into One Column

Jan 17, 2008

Hi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
 Regards

View 2 Replies View Related

Display Multiple Items On One Line Per Order (was Query)

Jun 27, 2005

I have table1 with orderID and demographic info.
Table2 with orderID and items.
I would like to have a results display like this:
OrderIDDemographicInfo Item1Item2Item3....ect
One line per order. When I do a join I displaying all items in different rows.

View 1 Replies View Related

Select Query To Fetch Multiple Checkbox Items

Nov 15, 2013

I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.

Checkbox tag:
<asp:CheckBoxList ID="cblGroup" Style="vertical-align: baseline" runat="server" CssClass="chkbox">
</asp:CheckBoxList>

Select Query:SqlCommand cmd = new SqlCommand("select * from AppInvent_Test where Designation= '" + cblGroup.SelectedValue + "'", con);

View 2 Replies View Related

Creating Query To Update A Field For Multiple Items

May 5, 2015

Looking to write an query that will update a field for multiple items, like 1,500.

something like:

UPDATE INMAST
SET FPRICE = 111.11

WHERE
INMAST.FPARTNO = 'xxx'

only issue I'm having is a need to do a JOIN because there's one more condition that must be met from another table, i've tried this:

SET FPRICE = 111.11
JOIN INVCUR
ON
(inmast.fpartno + inmast.frev)= (invcur.fcpartno + invcur.fcpartrev)

WHERE
INMAST.FPARTNO = 'NRE'
AND
invcur.flanycur = 'TRUE'

but that is giving me an error around the JOIN

View 11 Replies View Related

Insert Into Sql Database With Checkbox List Items

May 1, 2008

I am trying to insert into a database Checkbox list items, I get good values up till the checkBox List and then it gives me all O's, My fields in my database are bit columns so I need the checkbox list itmes to be converted to this format.  This is what i have so far.  One thing is I don;t think I am inserting into the correct database columns.  bitLOD, bitInjury, bitIllness, bitreferral are the database fileds.Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
'Check for the page ID to make sure not an update page then either insert new data or update existing data.
Dim id As String
Dim LOD As Byte
Dim Injury As Byte
Dim Illness As Byte
Dim Referral As Byte
id = Trim(Request.QueryString("ID"))For Each LItems As ListItem In CheckBoxList1.Items
If LItems.Selected = True ThenSelect Case LItems.ValueCase "1"
LOD = 1Case "2"
Injury = 1Case "3"
Illness = 1Case "4"
Referral = 1
End Select
End If
Next
'Put data into the Database
If id = "" Then
'save data into the database
sql = "INSERT tblSADHealth (intTaskForceID, intUICID, strSSN, dtInjury, strNotes, LOD, Injury, Illness, Referral,) " _
& "VALUES (" & ddlTaskForce.SelectedValue & ", " & DDLUIC.SelectedValue & ", '" & txtSSN.Text & "', '" & txtStatus.Text & "', " _
& "'" & txtDate.Text & "','" & txtNotes.Text & "', " & LOD & ", " & Injury & ", " & Illness & ", " & Referral & ")"
Response.Write(sql)
Response.End()

View 4 Replies View Related

Delete Statement For A List Of Items With Multiple Columns Identifying Primary Key

Jul 7, 2006

I frequently have the problem where I have a list of items to delete ina temp table, such asProjectId Description------------- ----------------1 test12 test43 test34 test2And I want to delete all those items from another table.. What is thebest way to do that? If I use two IN clauses it will do it where itmatches anything in both, not the exact combination of the two. I can'tdo joins in a delete clause like an update, so how is this typicallyhandled?The only way I can see so far to get around it is to concatenate thecolumns like CAST(ProjectId as varchar) + '-' + Description and do anIN clause on that which is pretty nasty.Any better way?

View 2 Replies View Related

Summing Invoice Items - The Multi-part Identifier Items.TAX Could Not Be Bound

Apr 17, 2007

Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record.  My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam  ALTER PROCEDURE [dbo].[UpdateInvoiceSummary]    @Invoice_ID intAS    DECLARE @Amount intBEGIN    SELECT     Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX    FROM         InvoiceItems AS items    GROUP BY Invoice_ID    HAVING      (Invoice_ID = @Invoice_ID)    Update Invoices SET Amount = items.Amount    WHERE Invoice_ID =@Invoice_IDEND

View 3 Replies View Related

SQL Server 2012 :: Identify Sets That Have Same Items (where Set ID And Items In Same Table)

Feb 25, 2015

I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:

SetIdItemCode
1A
1B
24
28
26
310
312
410

[code]....

You can see that there are some sets that have the same members:

- 1 and 10
- 2 and 11
- 7, 8 & 9

What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.

View 8 Replies View Related

Reporting Services :: Group And Sum Items / Sub-items Into One Record

Apr 10, 2015

I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).

I have a table of items (i.e. products).  In some cases, items can be components of another item (called "Kits").  In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items".  In our Items table, we have a field called "Parent_Item_Id".  Records for Child Items contain the Item Id of the parent.  So a sample of my database would be the following:

ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100

Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.

So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item.  So an example of the report would be the following:

Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100

How I can setup my report to group properly?

View 6 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID  key1  key2  key3  key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

DELETE Items Where Count(items) &>1

May 12, 2006

I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)


idserialisOk
-------------------
2AAA1
3BBB0
5dfds0
6CCC1
7fdfd 0
8AAA0
9CCC0


I want to DELETE each Row IN



SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1



and WHERE isOK = 0

in my exemple , after deleting, my table must look like



idserialisOk
-------------------
8AAA1
9CCC1
3BBB0
5dfds0
7fdfd0



thank you for helping

View 10 Replies View Related

Multiple Insert Into Multiple Tables With A Stored Procedure

Mar 1, 2007

Hello
I am building a survey application.
 I have 8 questions. 
 Textbox -  Call reference
 Dropdownmenu  - choose Support method
 Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
 

View 9 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Bulk Insert Multiple Files To Multiple Tables - How?

Feb 15, 2008

I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?

Here is XML file:




Code Snippet
<ReferenceFiles>

<File>


<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>

<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>






Thanks.

View 1 Replies View Related

Wants To Insert Into Multiple Table By A Single Insert Query

Apr 11, 2008

Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks

View 3 Replies View Related

Multiple Insert Call For A Table Having Insert Trigger

Mar 1, 2004

Hi

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1

Thanks

View 10 Replies View Related

Select List Contains More Items Than Insert List

Mar 21, 2008

I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?

Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,

CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,

tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)

View 2 Replies View Related

Insert Only New Items From A List But Get ID's For New And Existing In The List.

Feb 12, 2008

Hi,

I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.

Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'

My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.

performance is a not that great so I'm thinking that my code can be improved.

SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)

TIA





Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO

CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)

--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token

INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0

return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO




View 5 Replies View Related

How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..

Oct 30, 2007

Hi...
 I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
 
this is my sproc...
 ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.

AS

SET NOCOUNT ON

DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int

SET @Sproc = OBJECT_NAME(@@ProcId)

SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END

SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END

SET NOCOUNT OFF
 
So how do i do multiple inserts and updates using this stored procedure...
 
Regards
Karen

View 5 Replies View Related

Multiple Insert

Jan 11, 2008

Hi...I have two database table. we shall call them table 1 and table 2I want to insert data into table 1 and table 2 on the same page, so that when i click a submit button data goes into both tables. So what i will like to see happening is table 1 gets updated first then using that value it just inserted it gets used in table 2.Table 1 has an ID field that Table 2 needs inorder to insert its data. The ID in table 1 is a primary key and its a foreign key in table 2.Does anybody know how i can go about doing this in a nice and simple way. I am very new to sql and asp.net, hence a simple solution will be very helpful. I hope my problem make sense.. Thanx in advance.  

View 4 Replies View Related

Multiple Insert

May 30, 2008

 In my project I need to reserve rows of data in a table. I need to do this to make sure that unique automtic number, Identifier of that table will correspond with a sample that needs to be proces. Later that Id will be used for further upadate etc. 
I know I can insert new row by using code link to the button_click class
  protected void ReserveSample_Click(object sender, EventArgs e)
    {        SampleReserved.Insert(); 
    } but what if I would like to have a user to choose from a dropdown on the page amount of rows that needed to be inserted at once. Is there a way of tieing dropdown value, let say ‘20’ to sql insert statement to run the insert 20 times of the same values  Here is the insert statement  
 INSERT INTO flower                      (reserved, reserved_date)VALUES     (@reserved, GETDATE())
 

View 6 Replies View Related

Multiple Insert With The Same ID

Apr 15, 2004

Here is my problem:

I would like to execute 10 inserts and every insert in this batch should have the same ID. The next batch would have the next sequential number (ID) and so on...

I can do this by using a secondary table where I keep track of my IDs.

Is there another way of doing it?

Thanks.

dg

View 1 Replies View Related

Multiple Insert

Jun 12, 2005

hi friends,
i am having a problem here. I am using msde and i have a database with 3 tables.
Here is what i want to do.
1- I want to insert posted data to table 1 ( i can do this step)
2- I want to insert posted data to table 2 ( i can do this step, too)
3- I want to insert the primary keys of the rows inserted to table 1 and table 2 , to table 3 . I dont know anything about stored procedures . Can i do this in my code-behind using C# ?  If you can show me the way i will be glad.Thanks in advance

View 3 Replies View Related







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