Have a table that is used during a conversion. It is hit pretty heavily with inserts by mulitple session ids. For performance reasons I cannot add a unique constraint on the column that is getting duplicates (it is an encrypted cell in varbinary).
I do not want duplicates in this Encrypted Column. So before each insert the insert programs reads the table and verifies that the Encrypted Column value does not already exist. But unfortunately several duplicates are falling through the cracks. What are my options for not allowing duplicates?
I would like to know what options I have with regards to trapping a duplicate record before it tries to post to a SQL database. I have set the column to unique in SQL. But when I try to use ASP and post a duplicate record I get a system error. I would like to just create a referential error to notify the user that they cannot post a duplicate record please try again. Any help would be greatly appreciated.
I have a web app that calculates tax filing status and then stores data about the person.
Facts The insert is done through a stored procedure. All the sites that this program is being used are connecting through a VPN so this is not an external site. The duplicate records are coming from multiple sites (I am capturing there IP address). I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.
Any help would be greatly appreciated.
There are many sqlcmdInsert.Parameters("@item").Value =
I have a question regarding duplicate records, the thing is I'm able to query for duplicated records if I type the following:
select ColumnName from TableName where ColumnName in ( select ColumnName from TableName group by ColumnName having count(*) > 1 )
That gives me duplicate records for one column, but I need find duplicate records in more than one column (4 columns to be exact), but the way I need to find these records is they all have to be duplicate, what I'm trying to say is I don't don't want to find the following:
First Last Age Email John Smith 25 jsmith@hotmail.com John Smith 26 jsmith@hotmail.com John Smith 25 jsmith4@hotmail.com
I need to find the following:
First Last Age Email John Smith 25 jsmith@hotmail.com John Smith 25 jsmith@hotmail.com John Smith 25 jsmith@hotmail.com
So all the columns must be exactly the same, that's the only condition I want to show the records, is there any way to do this?
For the record, I'm using MS SQL Server 2000, thank you.
Hi, I am a rookie at sql and asp.net. I have another post is the asp.net section http://forums.asp.net/thread/1589094.aspx. Maybe I posted it in the wrong section. I am collecting multiple rows from a gridview. I validated/confirmed I am capturing the correct values. How ever, I am having major problems passing these to an sql database. The problems that I know of is declaring my parameters correctly and then adding the values through a for each statement. I added the post over 30 hours ago with only me as the replier trying to refining or clarifying the problem. Part 1 is the code that works, part 2 is my problem, I have tried may different ways to resolve this but no luck. Regards! Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click**************** PART1 **************************Dim gvIDs As String = ""Dim ddIDs As String = ""Dim chkBox As Boolean = FalseDim chkBox1 As Boolean = False'Navigate through each row in the GridView for checkbox items ddIDs = DropDownList1.SelectedValue.ToStringFor Each gv As GridViewRow In GridView1.RowsDim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox) If addChkBxItem.Checked Then chkBox = True gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString Response.Write(ddIDs + " " + gvIDs + " ") If addChkBx1Item.Checked Then chkBox1 = True Response.Write("Defaut Item") End If Response.Write("<br />")End IfNext******************** Part 2 *****************************************************Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)If chkBox ThenTry Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)" Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn) cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString) cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString) cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox)) cn.Open() For Each ..... Problems here also Next cmd.ExecuteNonQuery()Catch err As SqlException Response.Write(err.Message.ToString)Finally cn.Close()End TryEnd If
I have the Temporary table:ItemDetailID (int)FieldID (int)FieldTypeID (int)ReferenceName (Varchar(250))[Value] (varChar(MAX))in one instance Value might equal: "1, 2, 3, 4"This only happens when FieldTypeID = 5.So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the Table FieldListValues(ItemDetailID, [value])I have created a function to split the [Value] into a table of INTs Any Advice?
I need to insert records in two tables, one is main table and another is child table. From my aspx page I need to pass info. for one records in main table, insert that record into main table, get the is of the inserted table.
Then insert 15 records in the child table.
Everything must be in a transaction, either everything works or everything fails. Should I do it with aspx or should I pass arrays to a stored procedure?
Hi.. I really need a help. Is there any way to insert multiple records into a table in one go?
I have a table named Fruit. It contains FruitId,OwnerId,Colour
The list of colour is got from another table name FruitColour. FruitColour Consists of 2 column, FruitName and Colour.
Is it possible to insert multiple records into Fruit table with one query if only the colour is changed.
Sample case I have an Apple. Fruit id=2 OwnerId=2 Colour -- > Select Colour From FruitColour where FruitName='Apple' (Will return multiple records)
I tried to insert using this query: Insert into Fruit(FruitId,OwnerId,Colour) Values (2,2,Select Colour from FruitColour where FruitName='Apple').
Gives me this error Subqueries are not allowed in this context. Only scalar expressions are allowed.
I need to do this because actually I am inserting multiple fruit at one time, and each have multiple colour. If I need to insert the colour one by one for each fruit it will takes a very long time.
Any suggestion are welcomed. Thank you in advanced.
I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.
Is there a way to insert multiple records into a database table when you're just given "count" of the number of rows you want? I want to do this in ONE insert statment, so I don't want a solution that loops round doing 100 inserts - that would be too inefficient.
For example, suppose I want to create 100 card records starting it card number '1234000012340000'. Something like this ...
declare @card_start dec(16) set @card_start = '1234000012340000' declare @card_count int set @card_count = 100
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Hi I'm having some issues using the decryptbykey method via multiple connections. When I run the below test script simultaneously on two machines the sum function is always less then the known amount (ie 14945490 and 36382777). Does anyone know of any locking method or alternative way to sum an encrypted column?
Thanks in advance Waz
open symmetric key HR01 decryption by password='yes' DECLARE @Bonus decimal DECLARE @Salary decimal DECLARE @Errors int DECLARE @Success int DECLARE @LoopCount int SET @Errors = 0 SET @Success = 0 SET @LoopCount = 0
WHILE (@LoopCount < 40) BEGIN
SELECT @Bonus = SUM(convert(float,convert(varchar(80),decryptbykey(Bonus)))), @Salary = SUM(convert(float,convert(varchar(80),decryptbykey(Salary)))) FROM ChallengeEmployee WHERE ChallengeID = 5
IF(@Bonus <> 14945490 OR @Salary <> 36382777) BEGIN PRINT 'Bonus ' + CAST(@Bonus AS varchar(80)) PRINT 'Salary ' + CAST(@Salary AS varchar(80)) SET @Errors = @Errors + 1 END ELSE SET @Success = @Success + 1
SET @LoopCount = @LoopCount + 1
END
PRINT 'Finish' PRINT 'Errors ' + CAST(@Errors AS varchar(80)) PRINT 'Success ' + CAST(@Success AS varchar(80)) close symmetric key HR01
This is probably a silly question to most of you, but I'm in the processof splitting off years from a large DB to several smaller ones. Some ofthe existing smaller DBs already have most of the data for theirrespective years. But some of the same data is also on the source DB.If I simply do an insert keying on the year column, and a row beinginserted from the source DB already exists in the target DB, will aduplicate row be created?And if so, how can I avoid that?Thanks,John Steen*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have a table storing only 2 FKs, let's say PID, MID Is there any way that I can check distinct data before row is added to this table? For example, current data is PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449 If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue?
Hi I am trying to insert entries in a table which has a composite primary key and i am inserting it on UID basis.
INSERT INTO TABLE_B (TABLE_B_UID,NUM_MIN, NUM_MAX,BIN, REGN_CD, PROD_CD, CARD) (SELECT UID,LEFT(NUM_MIN,16),LEFT(NUM_MAX,16),BIN, REGN_CD, PROD_CD, CARD FROM TABLE_A WHERE UID NOT IN (SELECT TABLE_B_UID FROM TABLE B))
When i insert it tries to insert a duplicate entries and gives me an error. Since I am new to SQL SERVER 2000 i need some help. I tried IF NOT EXISTS, EXCEPT but i guess i am wrong at the syntax.
I have installed SQL Server Express Edition. I have migrated a set of tables from Oracl10g (by using Microsoft's Migration Tool Kit).While I am trying the following simple update command, the session hangs and it never finishes !!!!!!!!!!!!
Hi there, newbie here. I'm building a web application that allows for tagging of items, using ASP.NET 2.0, C# and SQL Server. I have my USERS, ITEMS and TAGS separated out into three tables, with an intersection table to connect them. Imagine a user has found an item they are interested in and is about to tag it. They type their tag into a textbox and hit Enter. Here's what I want to do: I want to search the TagText column in my TAGS table, to see if the chosen tag is already in the table. If it is, the existing entry will be used in the new relationship the user is creating. Thus I avoid inserting a duplicate value in this column and save space. If the value is not already in the column, a new entry will be created. Here's where I'm up to: I can type a tag into a textbox and then feed it to a query, which returns any matches to a GridView control. Now I'm stuck... I imagine I have to use "if else" scenario, but I'm unsure of the code I'll have to use. I also think that maybe ADO.NET could help me here, but I have not yet delved into this. Can anyone give me a few pointers to help me along? Cheers!
I am duplicating a record from my asp.net page in to the database. When i click on save I am getting the following error message Violation of PRIMARY KEY constraint 'PK_clientinfo'. Cannot insert duplicate key in object 'clientinfo'. The statement has been terminated. The above message i am getting since i have tried to duplicate the clientname field of my table which is set as the primary key. What i want is instead of this message in the browser i need to display an alert saying "the clientname entered already exists" by checking the value from the database. Here is my code. pls modify it to achieve the said problem if(Page.IsValid==true) { conn.Open(); SqlCommand cmd = new SqlCommand("insert into clientinfo (client_name, address) values ('"+txtclientname.Text+"', '"+txtaddress.Text+"')", conn); cmd.ExecuteNonQuery(); conn.Close(); BindData(); txtclear(); System.Web.HttpContext.Current.Response.Write("<script>alert('New Record Added!');</script>"); Response.Redirect("Clientinfo.aspx"); }
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
Yes, I know this subject has been exhausted, but I need help in locating the discussion which took place a few months ago. Sharon relayed to the group a piece of software (expensive) which would help in my particular situation. I grabbed a demo and have gotten the approval for purchase. Unfortunately, I don't have the thread with me at work.
The problem:
Number Fname Lname Age ID 123 John Franklin 43 1 123 Jane Franklin 40 2 123 Jeff Franklin 12 3 124 Jean Simmons 39 4 125 Gary Bender 37 5 126 Fred Johnson 29 6 126 Fred Johnson 39 7 127 Gene Simmons 47 8
The idea would be to get only unique records from the Number column. I don't care about which information I grab from the other columns, but I must have those fields included. If my resultant result set looked as follows, that would be fine. Or any other way, as long as all of the fields had information and there were only unique values in the Number field.
Number Fname Lname Age ID 123 Jeff Franklin 12 3 124 Jean Simmons 39 4 125 Gary Bender 37 5 126 Fred Johnson 39 7 127 Gene Simmons 47 8
If anyone remembers this discussion, mainly the date, I would really appreciate it.
I have two tables, one contains all work orders, the second contains records on work orders that are linked to customoer orders. I'm trying to create a query that will return specific fields from the table that contains orders in the linked order table, and only the work orders in the all order table that (work_order) do not exist in the linked order table (demand_supply_link). I have tried several queries and cannot get the results I desire. Here is the query I am currently trying.
SELECT DISTINCT WORK_ORDER.DESIRED_WANT_DATE as 'Want Date', DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID as 'WO Id', WORK_ORDER.DESIRED_QTY as 'End Qty', DEMAND_SUPPLY_LINK.SUPPLY_PART_ID as 'Part Id', CUST_ORDER_LINE.CUSTOMER_PART_ID as 'Cust Part', OPERATION.RESOURCE_ID as Resource, PART.DESCRIPTION as Description, CUSTOMER.NAME as Name FROM ((((DEMAND_SUPPLY_LINK INNER JOIN CUST_ORDER_LINE ON DEMAND_SUPPLY_LINK.DEMAND_BASE_ID = CUST_ORDER_LINE.CUST_ORDER_ID) INNER JOIN WORK_ORDER ON DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = WORK_ORDER.BASE_ID) INNER JOIN OPERATION ON WORK_ORDER.BASE_ID = OPERATION.WORKORDER_BASE_ID) INNER JOIN PART ON WORK_ORDER.PART_ID = PART.ID) INNER JOIN (CUSTOMER INNER JOIN CUSTOMER_ORDER ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID) ON CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID WHERE WORK_ORDER.DESIRED_WANT_DATE Is Not Null AND OPERATION.RESOURCE_ID in ('ASSY','FAB 1','PLAY TRK') AND WORK_ORDER.STATUS='R'
UNION SELECT distinct work_order.desired_want_date as 'Want Date', work_order.BASE_id as 'WO Id', work_order.desired_qty as 'End Qty', work_order.part_id as 'Part Id', operation.resource_id as Resource, part.description as Description FROM WORK_ORDER INNER JOIN PART ON PART_ID=WORK_ORDER.PART_ID INNER JOIN OPERATION ON WORK_ORDER.BASE_ID=OPERATION.WORKORDER_BASE_ID WHERE WORK_ORDER.DESIRED_WANT_DATE IS NOT NULL AND OPERATION.RESOURCE_ID IN ('ASSY','FAB 1', 'PLAY TRK') AND WORK_ORDER.STATUS='R'
This is the error I receive: Server: Msg 205, Level 16, State 1, Line 1 All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.
The all orders table (work_order) will not have the other fields to link to as there is no customer order linked to them.
Can someone tell me the best procedure when trying to find duplicate records within a table(s)?
I'm new using SQL server and I have been informed that there maybe some DUPS within unknown tables. I need to find these DUPS.
If someone can tell me how to perform this procedure I would apprciate it. And if you reply can also include examples that i could follow for my records.
Table1 has shop# and shop_id. Every shop# should have only one shop_ID. There has been a few data entry errors where a shop# has duplicate a shop_id. How to write a query for shop#s that have more than one shop_id?
