Insert From Formview And Checking Database To Avoid A Duplicate Entry
I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.
View Complete Forum Thread with Replies
Related Forum Messages:
Checking If An Entry Exists In A Database
I would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)
View Replies !
Duplicate Entry
Hi All, I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar] (2)-ReceivedTime [datetime] (3)-Status [char]. Now here how to remove duplicate entry of same mobile number to MobileNumber field? Regards Shaji
View Replies !
How To Avoid Duplicate Value
Hello, I have the following query, SELECT GroupInfo.GroupID, GroupInfo.GroupName FROM GroupInfo INNER Join DeviceGroup ON(DeviceGroup.GroupID=Groupinfo.GroupID) INNER Join Deviceinfo ON (Deviceinfo.SerialNumber=DeviceGroup.SerialNumber ) It's out put is as follow: Group ID GroupName 0 Abc 1 Beta 0 Abc 0 Abc 0 Abc 1 Beta 2 Alpha Now, I want to make such query which will give me result as a Group ID and Group Name but not in repeating manner, Like, Group ID GroupName 0 Abc 1 Beta 2 Alpha Hope I explained what I need to see in result pane. Thanks, Junior
View Replies !
Duplicate Entry In A Primary Key Field
Hi everybody couldn't get through with saving my data on the table with two primary keys... my table structure is this pubidintUnchecked (primary key) pubchar(1)Unchecked publchar(1)Unchecked pubcodechar(2)Unchecked (primary key) a sample data is here pubid pub publ pubcode 1 a b ab 1 b b bb 2 a b ab 2 b b bb when i save this table modifying the pubid and pubcode as primary keys the following error displays... Unable to create index 'PK_PUBS3'. CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'. Could not create constraint. See previous errors. The statement has been terminated. what i understand is that on the primary key duplicates are not allowed how could i allow it? thanks
View Replies !
How Duplicate Data Can Make An Entry????
Hi, I want to know the different sources, mediums and ways using which the duplicate recoprds or adta can make an entry in our database tables. I found 4 ways for this from many articles on net that are as follows: Duplicate data might arrive at your database via an interface to another system Data is loaded into table from other sources because during data loads, the integrity constraints are disabled Merging data from disparate systems Inheriting a poorly designed databaseBut is there any other ways also present????? Please tell me??? Thanks,
View Replies !
Preventing Duplicate Entry For A Given Foreign Key In Db Table
Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
View Replies !
Table With Duplicate Entry With Primary Keys
We have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?
View Replies !
Removing Duplicate Rows With CTE And Partition - Need Most Recent Entry
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry? /*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table by Chuck King 20070928 ***/ ;WITH CTE as ( SELECT ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID ,DischDate ,AdmDiagCode ,ID ,AdmDiagCodeDesc ,AdmittingDiagnosis ,AnyDx ,DischDx ,ECode ,IntOOS ,PrinDxAnesDesc ,PrinDxAnesInd ,PrinDxCode ,PrinDxCondDesc ,PrinDxCondInd ,PrinDxDesc ,PrinDxEqualsAdmDxYnu ,SecDx10AnesDesc ,SecDx10AnesInd ,SecDx10Code ,SecDx10CondDesc ,SecDx10CondInd ,SecDx10Description ,SecDx11AnesDesc ,SecDx11AnesInd ,SecDx11Code ,SecDx11CondDesc ,SecDx11CondInd ,SecDx11Description ,SecDx12AnesDesc ,SecDx12AnesInd ,SecDx12Code ,SecDx12CondDesc ,SecDx12CondInd ,SecDx12Description ,SecDx13AnesDesc ,SecDx13AnesInd ,SecDx13Code ,SecDx13CondDesc ,SecDx13CondInd ,SecDx13Description ,SecDx14AnesDesc ,SecDx14AnesInd ,SecDx14Code ,SecDx14CondDesc ,SecDx14CondInd ,SecDx14Description ,SecDx15Code ,SecDx15Description ,SecDx1AnesDesc ,SecDx1AnesInd ,SecDx1Code ,SecDx1CondDesc ,SecDx1CondInd ,SecDx1Description ,SecDx2AnesDesc ,SecDx2AnesInd ,SecDx2Code ,SecDx2CondDesc ,SecDx2CondInd ,SecDx2Description ,SecDx3AnesDesc ,SecDx3AnesInd ,SecDx3Code ,SecDx3CondDesc ,SecDx3CondInd ,SecDx3Description ,SecDx4AnesDesc ,SecDx4AnesInd ,SecDx4Code ,SecDx4CondDesc ,SecDx4CondInd ,SecDx4Description ,SecDx5AnesDesc ,SecDx5AnesInd ,SecDx5Code ,SecDx5CondDesc ,SecDx5CondInd ,SecDx5Description ,SecDx6AnesDesc ,SecDx6AnesInd ,SecDx6Code ,SecDx6CondDesc ,SecDx6CondInd ,SecDx6Description ,SecDx7AnesDesc ,SecDx7AnesInd ,SecDx7Code ,SecDx7CondDesc ,SecDx7CondInd ,SecDx7Description ,SecDx8AnesDesc ,SecDx8AnesInd ,SecDx8Code ,SecDx8CondDesc ,SecDx8CondInd ,SecDx8Description ,SecDx9AnesDesc ,SecDx9AnesInd ,SecDx9Code ,SecDx9CondDesc ,SecDx9CondInd ,SecDx9Description ,VisitTypeCode ,accountnumber ,DischVisitTypeCode FROM ampfm.rpt_AdtVisitDiag ) --Select * From CTE Delete From CTE Where ROWID > 1
View Replies !
How To Avoid Duplicate Cells When Trying To Join 3 Tables?
Hi all, Below are my tables: Rowid Name 1 John 2 Peter 3 Jack Table Rowid Rowid1 1 1 1 2 1 3 2 1 2 2 3 2 3 3 Table1 Rowid1 Country 1 USA 2 UK 3 JAPAN Table2 I tried to get the Country for all the people in the first table. My SQL statement is: SELECT Table.Name, Table2.Country FROM Table Left Join Table1 ON Table.Rowid = Table1.Rowid Left Join Table2 ON Table1.Rowid1 = Table2.Rowid1 My final result is shown on Table2. But is it possible if I can generate the results without the duplicate Names (as shown below)? Name Country John USA UK JAPAN Peter USA UK Jack UK JAPAN Any advice will much appreciated.
View Replies !
Searching A Column For A Value To Avoid Inserting A Duplicate Value
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!
View Replies !
Duplicate Checking
Hi Hope you can help....What would be the most efficient way to de dup a tableI have like 10 columns i need to Check against....our business rule is any incomming informationcan't be within today -90 , ie 90 daysa Example would be helpfulThanks
View Replies !
Duplicate Data Entry When Using The ASP.NET Web Site Administration Tool For Logins
Hello All, I am using the Web Site Administration Tool with the ASP.NET login controls to create/manage user logins & roles for my site. I have exported the ASPNETDB.MDF into my SQL Database ready to use with my app. The problem that I'm having is that whenever I add a user, using either the Web Site Administration Tool or the Classes available in the code-behind. The entry is doubled up in the database. This is not a problem when logging on....however, when it comes to deleting a user it will only take one of the entries out and leave the duplicate in there. So if the administrator has deleted a user from the app and then tried to create another one with the same UserName (Which whomever is well within his/her rights to be able to do), they cannot because the entry which has "stay behind" in the database conflicts with the entry. Any help on this matter would be greatly apprieciated. If any more info on this matter is required please ask (It was quite hard to try and explain this :-)
View Replies !
Checking Duplicate Values Two Tables
Hi All, Is it possible in SQL to Restrict value in one table checking a value on anather tables. Scenerio-1. I have two table let say, Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value. Scenerio -2.[/B] I have table called [B]SIC. This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns. Please Advise. Vijay
View Replies !
FormView Insert Query
Hello,I am trying to determine the best way to do the following. For simplicity we have two tables Master and Awards. These share a common pk UFID. Master contains columns (UFID, AccountName...) Awards contains columns (ID, UFID, AwardingAgency, Amount) When a user submits a new award in Formview I would like to populate the UFID automatically so that the user does not have to enter this each time. I am currently using the logged in username to select records for that user only. The value of this username matches Master.AccountName. Since I am hoping to use this same logic across many tables, I would appreciate any suggestions as to how best handle this.Thanks,Ken
View Replies !
Best Method Of Checking For Duplicate Entries In SQL Server
Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional. In my Favorites table, the 3 fields are.....favoriteId (set as primary key)userIdcarId I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives. 1) Check proactively by doing a.....SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table. 2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique. Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice? TIA for your help.
View Replies !
Slow Procedure While Checking Duplicate Records
hello friends i m stuck up with a problem...actually i dont have much experience in database line....i m new to this line....i have recently joined the job & this problem is like a test of me....if i will be able to give the solution then everything is fine otherwise i will be fired & im not in a condition to leave this job as this is my first job in software development....i have got this chance with lots of difficulty....so please help me if u can... the problem is....>> i m using a procedure to check the duplicatye records by using string comparison against address of persons..allover the country.... i m using SQL server 7.0 i have a single table(name of table is DATA) which contains 350000 records( i mean address entries) there are about 35 columns but i have to check duplicate records only against address field...for that first of all i remove special characters from the address field.....then i compare first 20 characters for duplicate entries... for this i m generating another table(name of another table is RESULT)... how the logic works...initially the data table contains the records but the result table is totally blank....first of all i pick first entry of address from DATA table then...check it with the entry in RESULT table if the entry exists... it compares the address if the record is same then it generates a refference of this address and make an entry....means a refference of that entry....(as far as very first record is concerned there will be no entry in the RESULT table so it will enter the address over there...then it picks up the second record...checks it in the RESULT table...now this record will be compared with the one & only entry in the RESULT table....if the entry is same then the refference will be entered... otherwise it will be entered as second record in the RESULT table....) now where lies the problem.....initially the procedure is very fast.... but it gradually slows down .....because(when it checks the 10th record for duplication it compares the entry in RESULT table for 9 times only *** similarly when it checks the 100th record it compares it for 99 times *** similarly when it checks the 10000th record it compares it for 9999 times so here lies the problem.... when it checks the 100000th record it gets dammm slow... what i have get till now is that i have checked.....>>>>> 5000 records in 4 mins.... 25000 records in 22 mins.... and 100000 records in 20 hours....(means initially its faster but it gradually slows down) ************************************************** ************************ here i m giving the code for the procedure...... ************************************************** ************************* CREATE PROCEDURE pro1 as SET NOCOUNT ON Declare @IvgId as numeric(15) Declare @Address as nvarchar(250) Declare @AddressClean as nvarchar(250) Declare @MaxLen as INT Declare @Add as nvarchar(250) Declare @Ic as int Declare @FoundIvgId as numeric(15) Declare @NewIvgId as numeric(15) /* here 'N' is for keeping track for some system failures etc */ Declare CurData CURSOR forward_only FOR Select IvgId, Address From Data Where ProcessClean = 'N' OPEN CurData FETCH NEXT FROM CurData INTO @IvgId, @Address WHILE @@FETCH_STATUS = 0 Begin /*here i m doing string cleaning by removing special characcters */ Select @MaxLen = len(LTRIM(RTRIM(@Address))) Select @Address = LOWER(@Address) Select @Ic = 1 Select @AddressClean = ' ' While @Ic <= @MaxLen /* here @MaxLen is the maximum length of the address field but i have to compare only first 20 characters */ Begin Select @Add = Substring(@Address, @Ic, 1) If ascii(@Add) > 47 AND ascii(@Add) <= 64 AND @Add <> ' ' Begin Select @AddressClean = @AddressClean + @Add End If ascii(@Add) > 90 AND @Add <> ' ' Begin Select @AddressClean = @AddressClean + @Add End Select @Ic = @Ic + 1 End /* now we have removed special characters , for failure checking i m using this 'Y' */ Update Data Set AddressClean = @AddressClean, ProcessClean = 'Y' Where IvgId = @IvgId FETCH NEXT FROM CurData INTO @IvgId, @Address End PRINT 'Cleaning Done.............................' Close CurData Deallocate CurData /* till now procedure doesnt take too much time & cleans all the 3 lack records in abt 40 mins but next part is giving trouble */ Declare CurData CURSOR FOR Select IvgId, Address, AddressClean From Data Where ProcessDup = 'N' OPEN CurData FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean Select @NewIvgId = 100 WHILE @@FETCH_STATUS = 0 Begin If EXISTS (Select IvgId From Result Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20)) Begin Update Result Set DupIvgId = @IvgId Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20) End ELSE Begin Insert Into Result Values (@NewIvgId, @Address, @AddressClean,0) Select @NewIvgId = @NewIvgId + 1 End Update Data set ProcessDup = 'Y' Where IvgId = @IvgId FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean End Close CurData Deallocate CurData SET NOCOUNT OFF Print 'Done................................' ************************************************** ************************** now the procedure is over....now i m writing the SQL script of DATA & RESULT table ************************************************** ************************ CREATE TABLE [dbo].[DATA] ( [IVGID] [numeric](18, 0) NOT NULL , [Title] [varchar] (10) NULL , [FirstName] [varchar] (50) NULL , [MiddleName] [varchar] (10) NULL , [LastName] [varchar] (30) NULL , [Add1] [varchar] (150) NULL , [Add2] [varchar] (50) NULL , [Add3] [varchar] (50) NULL , [City] [varchar] (30) NULL , [State] [varchar] (30) NULL , [Country] [varchar] (20) NULL , [Pincode] [varchar] (10) NULL , [OffPhone] [varchar] (20) NULL , [OffFax] [varchar] (20) NULL , [ResPhone] [varchar] (20) NULL , [ResFax] [varchar] (20) NULL , [EMail] [varchar] (50) NULL , [Source] [varchar] (20) NULL , [MODEL] [varchar] (20) NULL , [PNCD] [varchar] (6) NULL , [DupKey] [decimal](18, 0) NULL , [Duplicate] [int] NULL , [HouseHoldID] [varchar] (50) NULL , [YearSlab] [varchar] (10) NULL , [CleanStatus] [int] NULL , [AddStatus] [int] NULL , [BatchNo] [varchar] (20) NULL , [ModelStatus] [int] NULL , [Month] [int] NULL , [Year] [int] NULL , [SapStatus] [int] NULL , [ErrCase] [int] NULL , [cmpCity] [varchar] (50) NULL , [Product] [varchar] (1) NULL , [cmpPinCode] [varchar] (6) NULL , [Address] [nvarchar] (250) NULL , [AddressClean] [nvarchar] (250) NULL , [DupIvgId] [numeric](18, 0) NULL , [ProcessClean] [nvarchar] (1) NULL , [ProcessDup] [nvarchar] (1) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DATA_TEST] Script Date: 15/06/2001 8:36:21 PM ******/ CREATE TABLE [dbo].[DATA_TEST] ( [IVGID] [numeric](18, 0) NOT NULL , [Address] [nvarchar] (50) NULL , [AddressClean] [nvarchar] (50) NULL , [DupIvgId] [numeric](18, 0) NULL , [ProcessClean] [nvarchar] (1) NULL , [ProcessDup] [nvarchar] (1) NULL ) ON [PRIMARY] GO so now i have given the whole description of my problem....i m eagerly waiting for reply...... if anybody can help....i will be very thankful..... bye for now Bhupinder singh
View Replies !
Sqldatasource And Formview Insert Problem
I m trying to INSERT a record to my Consultants table using sqldatasource and formview: here is my sqldatasource code: <asp:SqlDataSource ID="sqlDS1" runat="server" ConnectionString="<%$ ConnectionStrings:myDB %>"InsertCommand="INSERT INTO [Consultants] ([firstName],[lastName],[skillCategoryID],[resourceManagerID],[AMgroupID],[skillSet],[statusID],[location],[comments],[profile],[isAvailable],[dateModified],[focusID]) VALUES (@FNAME, @LASTNAME, 1, @RESOURCEMANAGERID, @AMGROUPID, @SKILLSET, @STATUSID, @LOCATION,@COMMENTS,@PROFILE,@ISAVAILABLE,getdate(),1)"> <InsertParameters> <asp:FormParameter FormField="txtFName" Name="FNAME" /> <%--other parameters--%></InsertParameters></asp:SqlDataSource> now in my formview i have this:<asp:FormView DefaultMode="Insert" ID="FormView1" runat="server" DataSourceID="sqlDS1" DataKeyNames="id"> <table border="0" cellspacing="5" cellpadding="0"><tr> <td class="blacktextbold">First Name:</td> <td class="blacktext"><asp:TextBox ID="txtFName" CssClass="txtfield" Width="200" runat="server" Text="<% #Bind('firstName')%>" /> <asp:RequiredFieldValidator ControlToValidate="txtFName" runat="server" ValidationGroup="gpInsert" ErrorMessage="Required Field" Display="Dynamic" CssClass="redtextsmallbold" /> </td></tr> </table> THE PROBLEM:no matter what i put the txtFName field its ALWAYS null and i get INSERT error as column firstName cant accept NULLany ideas? ur help would be appreciated
View Replies !
Insert Two Rows Into Two Tables At The Same Time From A Formview
I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.
View Replies !
SqlDataSource With Different Number Of Columns And Insert Parameters And FormView
Dear All, i have a SqlDataSource with a simple select command(e.g. "select a,b,c from foo"). The insert command is a stored procedure and takes less parameters than there are columns in select statement (e.g. "insertFoo(a char(10))"). When used in combination with form view, i get "Procedure or function insertFoo has too many arguments specified" error. It seems that form view always posts all columns as parameter collection (breakpoint in formview_inserting event shows this) to insert command. Am I doing something wrong or is this by design? Is the only solution to manualy tweak parameters in formview_inserting event? TIA Jernej
View Replies !
Having Difficulty With FormView, Stored Procedures And Insert Parameter
I setup the databse and Visual Web Developer to use stored procedures when the insert command is used. The database also uses the field UserName that I pass using a SessionParameter within the InserParameter block from the Membership.GetUser.Username from the aspnet_ tables. My difficulty is when using the "Formview" as the body to with which to insert (I wanted the design versatility of FormView) I get an error: "system.formatExpression: Input string was not in a correct format" when I leave the textboxes empty and invoke the insert command. I first assumed that the bound textboxes are not being converted correctly when left blank, so I used the ConvertEmptyStringToNull=True, with no success. I then coupled that with the Type=[correct format] still with the same error. Alas, my final attempt was to set defaults in the "ALTER PROCEDURE" within the stored procedure itself. Any help would be most appreciated. Thanks. .
View Replies !
Formview - Exception Handling A Sql Server Raised Error On Insert
I'm trying to get to grips with the formview control and have a problem regarding where an exception is raised if SQL server raises an error when an insert is taking place. I am new to both asp.net & c# I have put the following code in the SqlDataSource2_Inserted & Formview1_Inserted events as a way of determining where the exception is raised: if (e.ExceptionHandled){ string exceptionmessage = e.Exception.Message; lblStatus.Text = exceptionmessage;} These two events are the only one's that have the ExceptionHandled and Exception properties. I run the code with data that will raise a duplicate key error from SQL Server but the error is not being caught by either of these two handlers. All I get is a page that starts with Server Error in '/SourceCode' Application. Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated.... Is there somewhere else I should be looking to handle this sort of thing? I thought using the databound controls would make life easier but I'm actually finding it more of a pain than coding everything myself.
View Replies !
Formview Does Not Update Database
HiI'm using a FormView to allow my administrator to add/edit/remove questions/answers to the FAQ of my helpdesk.Now I figured out how to use the FormView to adjust the data, the only thing the FormView does NOT do, is actually update the database, it does not give any errors, it just doesn't do anything.This is the code: <asp:SqlDataSource ID="sdsAdminFaqDetails" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" DeleteCommand="DELETE FROM [Faq] WHERE [QuestionID] = @QuestionID" InsertCommand="INSERT INTO [Faq] ([Question], [Answer]) VALUES (@Question, @Answer)" SelectCommand="SELECT * FROM [Faq]" UpdateCommand="UPDATE [Faq] SET [Question] = @Question, [Answer] = @Answer WHERE [QuestionID] = @QuestionID"> <DeleteParameters> <asp:Parameter Name="QuestionID" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Question" /> <asp:Parameter Name="Answer" /> <asp:Parameter Name="QuestionID" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="Question" /> <asp:Parameter Name="Answer" /> </InsertParameters> </asp:SqlDataSource> <asp:FormView ID="FormView1" runat="server" DataSourceID="sdsAdminFaqDetails"> <ItemTemplate> <table border="0"> <tr> <td style="width: 189px"><asp:Label ID="lblQuestionLabel" runat="server" Text="Vraag:"></asp:Label></td> </tr> <tr> <td style="width: 189px"><asp:TextBox ReadOnly="True" ID="txtQuestion" runat="server" Text='<%# Eval("Question") %>' Width="309px"></asp:TextBox></td> </tr> <tr> <td style="width: 189px"><asp:Label ID="lblAnswerLabel" runat="server" Text="Antwoord:"></asp:Label></td> </tr> <tr> <td style="width: 189px; height: 40px"><asp:TextBox ReadOnly="True" ID="txtAnswer" runat="server" Text='<%# Eval("Answer") %>' Height="160px" TextMode="MultiLine" Width="457px"></asp:TextBox></td> </tr> <tr> <td><asp:Button ID="btnEdit" runat="server" Text="Wijzig" CommandName="Edit" /> <asp:Button ID="btnInsert" runat="server" Text="Nieuw" CommandName="New" /></td> </tr> </table> </ItemTemplate> <EditItemTemplate> <table border="0"> <tr> <td style="width: 189px"><asp:Label ID="lblQuestionLabel" runat="server" Text="Vraag:"></asp:Label></td> </tr> <tr> <td style="width: 189px"><asp:TextBox ID="txtQuestion" runat="server" Text='<%# Bind("Question") %>' Width="309px"></asp:TextBox></td> </tr> <tr> <td style="width: 189px"><asp:Label ID="lblAnswerLabel" runat="server" Text="Antwoord:"></asp:Label></td> </tr> <tr> <td style="width: 189px; height: 40px"><asp:TextBox ID="txtAnswer" runat="server" Text='<%# Bind("Answer") %>' Height="160px" TextMode="MultiLine" Width="457px"></asp:TextBox></td> </tr> <tr> <td><asp:Button ID="btnUpdate" runat="server" Text="Bevestig" CommandName="Update" /> <asp:Button ID="btnDelete" runat="server" Text="Verwijder" CommandName="Delete" /></td> </tr> </table> </EditItemTemplate> <InsertItemTemplate> <table border="0"> <tr> <td style="width: 189px"><asp:Label ID="lblQuestionLabel" runat="server" Text="Vraag:"></asp:Label></td> </tr> <tr> <td style="width: 189px"><asp:TextBox ID="txtQuestion" runat="server" Width="309px"></asp:TextBox></td> </tr> <tr> <td style="width: 189px"><asp:Label ID="lblAnswerLabel" runat="server" Text="Antwoord:"></asp:Label></td> </tr> <tr> <td style="width: 189px; height: 40px"><asp:TextBox ID="txtAnswer" runat="server" Height="160px" TextMode="MultiLine" Width="457px"></asp:TextBox></td> </tr> <tr> <td><asp:Button ID="btnInsert" runat="server" Text="Voeg toe" CommandName="Insert" /> <asp:Button ID="btnCancel" runat="server" Text="Annuleer" CommandName="Cancel" /></td> </tr> </table> </InsertItemTemplate> <EmptyDataTemplate> U hebt niet geselecteerd welke vraag u wilt wijzigen. </EmptyDataTemplate> </asp:FormView> hope one of you sees where i made a mistake, or forgot about something...thx for your help
View Replies !
Help With Logic - VB - Formview - Multiple Database Tables
Guys, here is my scenario: I have 3 tables 1) Vendor 2) Service 3) Service Product Each vendor has the ability to have any number of services, all with any number of service products. How should I setup my page so I can edit/insert vendors, and tie a list of service products and services to them? My plan was to have a Formview with either a checkboxlist, or multilist (to select multiple items - but they don't work that way) and then store selected items in another table (each item selected with its own record) to hold onto the vendor id and tie it to a service id and a product id, but I was unsure how to do this. Any help would be greatly appreciated. FYI I am writing in VB.
View Replies !
Updating Database In Cs File - Using Sqldatsource And Formview
Hi I am new to asp.net world. I have a page with two formviews bound to two sqldatsources. One datasource connects to sql database and other to access. The information from both the databases is quite similar. The default mode for formview1 is edittemplate and for formview2 it is itemtemplate. I want to give the user an option to update formview1 data based on information retrevied in formview2 on a click of a button. I want to do this in code behind .cs file. I am not sure how can I access the values from formview templates e.g formview1.itemtemplate... etc? Can anyone please suggest a way to acheive this? Thanks
View Replies !
Howto Get Avoid Bulk Insert Data Conversion Error?
hi, i having a problem in bulk insert , which is regard the text file that to insert into database, when insertion processing, if my textfile have NULL value, it give me Bulk insert data conversion error for example in my text file c:mytest.txt , it contains data NULL 123 studentname NULL can we let bulk insert detect NULL value ? i have try on putting "KEEPNULLS" , but it doesn't help , caused some fields in table may in datetime type BULK INSERT [mytable] FROM c:mytest.txt WITH (FIELDTERMINATOR = '' '', ROWTERMINATOR = '''', KEEPNULLS )' thank you
View Replies !
Checking To See If A Record Exists Else Insert
I am checking to see if the source record is available in the target table using a lookup transformation and if not found i have to insert this record. I have connected the error flow of the lookup transformation to the target. I am acheiving expected results, but is this the best practise? I have not connected to the green arrow to any task.
View Replies !
Checking To See If A Record Exists And If So Update Else Insert
I've decided to post this as a sticky given the frequency this question is asked. For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you. http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx Thanks Jamie! If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here: http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/ Phil
View Replies !
INSERT INTO... SELECT... Cannot Insert Duplicate Key...
I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?
View Replies !
Checking Specific Columns Were NULL On Previous Insert (Using If EXISTS)
Thanks for your time: Background: After Insert Trigger runs a sproc that inserts values into another table IF items on the form were populated. THose all work great, but last scenario wont work: Creating a row insert based on Checking that all 22 other items from the prior insert (values of i.columns) were NULL: IF EXISTS(select DISTINCT i.notes, i.M_Prior, i.M_Worksheet, ... from inserted i WHERE i.notes IS NOT NULL AND i.M_Prior = NULL AND i.M_Worksheet = NULL AND...) BEGIN Insert into dbo.Items2Fix ... From inserted i END
View Replies !
DateTime From Database Entry
Hi, I know this is probably very simple but I am pretty new to this and have tried looking but cant seem to get the search criteria right. I have a database with a storeDate field which is of shortdatetime type. I am connecting to the database (MSSQL) via a stored procedure and returning all the records. I then use the code foreach (DataRow dr in ds.Tables[0].Rows) { DateTime dtTo = DateTime.Now; DateTime dtFrom = DateTime.Parse(dr["storeDate"].ToString()); TimeSpan diff = dtTo.Subtract(dtFrom); } I am basically trying to find out the age of the database entry by subtracting it from the current DateTime so i can delete records over a certain age. The problem (at least one of them!) is retrieving the "storeDate" object from the database and storing it in the dtFrom object. I have tried just assigning it directly as dtFrom = dr["storeDate"] and various other methods but I just don't know enough to assign it! Can anyone help me with this or spot any other mistakes in this process of removing old files automatically. Greatly Appreciated,Sean.
View Replies !
Identical Database Entry Already?
Here's some code that says it should identify if a user already exists in my database. I have changed the code to match my database, but it seems to have somewhat the opposite affect, rejecting all names (even new ones) or accepting all names (including existing ones). The switch in situations occurs in the "if" statement towardsd the end, when I change the sign of objDR.RecordsAffected. Do you have any idea what could be wrong? Thanks. Function DoesUserExist(ByVal userName As String) As Boolean Dim connectionString As String = "server='(local)Netsdk'; trusted_connection=true; Database='AuthorizedUsers'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [Users].[UserName] FROM [Users] WHERE ([Users].[UserName] = @UserName)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) Dim Cmd as New SQLCommand(queryString, sqlConnection) With Cmd.Parameters .Add(New SQLParameter("@username", username)) End With sqlConnection.Open Dim blHasRows As Boolean Dim objDR As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection) if objDR.RecordsAffected > 0 then blHasRows="True" else blHasRows="False" End If Return blHasRows End Function
View Replies !
Null Entry In The Database
I am using a script component to retrieve some values from a server. This script component using Script task which uses VB.NET code. I have tested the VB.NET code and it returns a non null value for a string field 'sentence'. The 'sentence' feild is then passed on to an OLE DB Command transformation to be stored into the database along with a couple of other feilds. The OLE DB Command transformation uses a stored procedure to store these feilds into the database. But when I check my table in the database, all other feilds are stored properly other than the 'sentence' feild which is null. I am wondering what could be causingthis problem. Thanks
View Replies !
Cannot Insert Duplicate Key
I am using SQL Server 2000. I have a table (tbDupes) with duplicate records. I want to populate another table (tbUnique) with unique records. My tbDupes has about 50 columns and three of them make up my primary key in tbUnique. I thought I could get unique records by using the following SQL statement. Code Snippet INSERT INTO tbUnique SELECT * FROM tbDupes I received this error: Cannot insert duplicate key Is there a database setting that will allow me to insert the first record and prevent any duplicates from being inserted? I thought this was the default behavior and think that maybe the DBA changed it. I know I can achieve this by creating a cursor with tbDupes and then something like "IF NOT EXISTS(...) THEN INSERT". However, I would prefer to avoid using a cursor.
View Replies !
How To Prevent The Insertion Of The Same Entry Of An Unique Value Into The Database
how to prevent the insertion of the same entry of an unique value into the database? assume that I have a primary key username " abc" in the database and then I insert the "abc" again ,the debuggging error msg pops out saying the primary key cannot be duplicated.. how can I do an if--else statement to check the database against this unintentional inputs of the same unique data " abc"???
View Replies !
Cannot Insert Duplicate Key Row In Msrepl_transactions
We have an issue that our SQL Replication between our Application Server (holding 13 months of data) and our Reporting Server (holding 5-6 years of data). Both systems are running on NT4, SQL Server 7 Ent SP3. Our issue is that we used to run SQL Replication but then stopped using it, and used another method of replicating our data from the App Svr to Rpt Svr. However, we have now been told that we have to use SQL Replication. We set it up as usual, but when the log reader starts up, it finds the 1st transaction and moves it into the distribution database, but it then fails when attempting to do the 2nd transaction with the error "Cannot insert duplicate key row in object MSrepl_transactions with unique index ucMSrepl_transactions. We have checked the tables in the publisher db and they don't hold any duplicates, so I can only assume the key it is talking about is the key field in the distribution database, but this is a Binary field which I believe is populated by replication itself!! Is this correct? If so, why is it trying to enter duplicates? And can we resync replication to fix it?
View Replies !
Don't Insert Duplicate Rows
Hi, I need to insert rows into table1 from table2 and table3 but I don't want to insert repeated combinations of col2, col3. So, table1 has the primary key col2, col3. This the table1: create table table1( col1 int not null, col2 int not null, col3 int not null, constraint PK_table1 primary key (col2, col3) ) This is my "insert" code: INSERT INTO table1 SELECT table2.col1,table2.col2, table3.col3 FROM table2, table3 WHERE table2.col1 = table3.col1 Wich conditions shoud i add to this code? Thanks. fmilano.
View Replies !
Cannot Insert Duplicate Key Row In Object
The following quote is the exact error message that I get when I attempt to create a pull subscription. The STAppointment is one of a 15 databases that need to replicate inorder for the application SalonTouch to run properly. What is causing this error? Is it user rights? Is it a problem with sql2005? Am I missing a step? Is there a problem with the database I am working with? What else? TITLE: New Subscription Wizard ------------------------------ SQL Server could not create a subscription for Subscriber 'D6LHWQ91PORTSUN9'. ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot insert duplicate key row in object 'dbo.MSmerge_replinfo' with unique index 'uc1MSmerge_replinfo'. The subscription could not be created. The subscription properties table 'MSsubscription_properties' does not exist in the current database. Changed database context to 'STAppointment'. The statement has been terminated. (Microsoft SQL Server, Error: 2601) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=2601&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
View Replies !
NORTHWIND Database Was Re-created From A Different Database:How Can I Change The Entry In Sysdatabases For Database 'NORTHWIND'?
Hi all, From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code: --UpdateSPforNWcustomersTable.sql-- USE NORTHWIND GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo') DROP PROCEDURE dbo.[SelectCustomers] GO CREATE PROCEDURE dbo.[SelectCustomers] AS SET NOCOUNT ON; SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo') DROP PROCEDURE dbo.InsertCustomers GO CREATE PROCEDURE dbo.InsertCustomers ( @CustomerID nchar(5), @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24) ) AS SET NOCOUNT OFF; INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID) GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo') DROP PROCEDURE dbo.UpdateCustomers GO CREATE PROCEDURE dbo.UpdateCustomers ( @CustomerID nchar(5), @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24), @Original_CustomerID nchar(5) ) AS SET NOCOUNT OFF; UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID)); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID) GO ==================================================================================== I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages: Msg 911, Level 16, State 1, Line 1 Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name. Make sure that the name is entered correctly. =============================================================================================================== I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise. Thanks in advance, Scott Chang
View Replies !
Trying To Append A Field If An Insert Happens To Contain A Duplicate
I deliberately intend to add some duplicates to one of my tables. For eg Job User IsAdmin JobID 235User ID 1 JobID 235User ID 5 JobID 235User ID 9 JobID 235User ID 5 JobID 235User ID 2 JobID 235User ID 9 JobID 235User ID 10 JobID 235User ID 1 I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like: Job User IsAdmin JobID 235User ID 1 JobID 235User ID 5 JobID 235User ID 9 JobID 235User ID 5 True JobID 235User ID 2 JobID 235User ID 9 True JobID 235User ID 10 JobID 235User ID 1 True Thanks
View Replies !
|
TRACKER
