Error:Column 'FieldName' Is Constrained To Be Unique. Value '0' Is Already Present
Jan 18, 2008
Hi,
Can anyone help me?
when I am inserting a record to a dataset I am getting Column 'FieldID' is constrained to be unique. Value '0' is already present.
I have 2 fields in table like FieldID,FieldName where FieldID id is Primary key and identity column
I am not inserting any value to FieldID as it is identity column.
DataRow newDR= dbDataSet.Tables[strTable].NewRow();for (int i = 0; i < e.Row.Cells.Count; i++)
{ if ( e.Row.Cells[i].Key != "FieldID" )
{if (e.Row.Cells[i].Value != null)
{
newDR[e.Row.Cells[i].Key] = e.Row.Cells[i].Value;dbDataSet.Tables[ strTable].Rows.Add(newDR);
}
}
Some times it is woking fine with out any error............
thanks..
View 3 Replies
ADVERTISEMENT
Jul 27, 2007
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
View 2 Replies
View Related
May 1, 2007
Hi,
I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).
When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:
STRTOMEMBER(@DimOrganisationBUSADDRSTATE, CONSTRAINED).
When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.
How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?
Thanks
View 10 Replies
View Related
Mar 8, 2006
hi,i am a learner of ms -sql server 2000, i had a doubt in storedproceduressuppose i have a data base having 20 tables, all the tables have acolumn named--DATEcan we write a store procdure to find out the data ---i mean i wantthe data enteredbetween two days ---- if i call the stored procedure in any one of thetable i need to get the answerpls help me how to write the stored proceduresatishkumar.g
View 2 Replies
View Related
Jul 5, 2015
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
View 6 Replies
View Related
Jun 7, 2007
Hi !
I have such a message after trying to do replication with SQL Server 2005 using custom Business Logic Handler for a Merge Article (RMO Programming).
Message Replication-Replication Merge Subsystem: agent 'job' failed. No signature was present in the subject.
I am using C# with RMO, merge replication.
Any ideas ? How to solve this problem ?
Thanks.
View 2 Replies
View Related
Jan 3, 2008
I am getting this error even though there should be data present. When I test the SQL statement in query analyzer, it returns 1 row.
<%@ Import Namespace="System.Data.SqlClient" %><%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Blog" %>
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim strConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("currentConnection").ToString 'currentConnection is defined in web.config and works when used in other pages on the siteDim dbConn As SqlConnection = New SqlConnection(strConnection)
dbConn.Open()
Dim strSelectCommandFirstEntry As String = "SELECT MAX(blogEntryId) as maxID FROM site_Blog"Dim cmdFirstEntry As SqlCommand = New SqlCommand(strSelectCommandFirstEntry, dbConn)Dim rdrFirstEntryData As SqlDataReader = cmdFirstEntry.ExecuteReader()Dim strFirstEntryData As Int32 = rdrFirstEntryData(0) 'error occurs here, i have also tried rdrFirstEntryData("maxID") with same errorrdrFirstEntryData.Close()dbConn.Close()
</script>
When debugging this code and stopping on this line:
Dim strFirstEntryData As Int32 = rdrFirstEntryData(0)
rdrFirstEntryData has the following values:hasRows = True, FieldCount=1, Item=In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. and when i click the refresh button...Item = Overload resolution failed because no accessible 'Item' accepts this number of arguments.
I suspect that the note for "Item" is my clue to the source of the problem, but I don't know what it means. Please, help.
View 2 Replies
View Related
Sep 4, 2007
I am having a problem implementing constrained delegation for SSRS. I have followed the (very good) instructions located here:
http://sqlblogcasts.com/blogs/stevechowles/archive/2007/06/08/reporting-services-2005-for-the-dba-iis-security.aspx
I have chosen the option of running the application pool for SSRS under a domain user account. This is the same account that I use to run the SSRS service.
I have the authentication providers for the site set to "Negotiate,NTLM".
I also made sure that the application pool user account has rights on the ReportManager and ReportServer directories.
If browse to the URL while logged on to the SSRS server then I am able to access the site
My problem is when I try to access the site from anywhere but locally on the SSRS server:
I get a logon prompt if I try to access the SSRS URL from a different workstation. After three tries to login I get: "You are not authorized to view this page". Even with an account that is local admin on the SSRS Server.
If I set the authentication providers for the site to "NTLM" then I am able to access the site from a different workstation but of couse constrained delegation does not work.
Have i overlooked something? What could be causing the login prompt?
View 3 Replies
View Related
May 1, 2015
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. Â To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.Â
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1Â
View 2 Replies
View Related
Nov 21, 2004
/************************************************/
Database db=new Database();
db.setConnection(new com.borland.dx.sql.dataset.ConnectionDescriptor(this.JdbcOdbc,
"", "", false, "sun.jdbc.odbc.JdbcOdbcDriver"));
SQL=” SELECT * FROM tableName WHERE fieldname is like ‘*value *’”;
QueryDataSet q= new QueryDataSet();
q.setQuery(new com.borland.dx.sql.dataset.QueryDescriptor(db, s, null, true, Load.UNCACHED));
/************************************************/
This return me a data set containing rows where fieldname is exactly like ‘*value *’. The expected data set is set of rows where fieldname=”value 1”or fieldname=”the value 1” or fieldname=”dgdgvalue 04121”, for example.
Here JBuilder considers the character ‘*’ not a special one (idem for the character ‘?’).
This problem is also faced with any other programming language (If I don’t make an error!!)
Please help me to get returned the right data set returned.
Thanks…
View 3 Replies
View Related
Nov 9, 2006
I get this error when, i try to set a parameter for user input, it doesnt
matter what i type in, it says this:
<<Query (1,408) The Restrictions imposed by the constrained flag in the
STRTOSET function were violated>>
what exactly does this mean? and how do i fix it?
My parameter is for dates, but i have the datetype set to "String", when i
change it to" DATETIME" , i get the same error when i select from the
calendar. So far the only date option that is working right now. Is a long
drop down list, that could go for days at a time. I just want users to be
able to input dates, such as "Jan 9, 2000" or 10/10/1999. What can i do about
this?
View 2 Replies
View Related
Apr 16, 2007
I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?
View 7 Replies
View Related
Sep 23, 2007
How can i just select filed name from the name without data. What query should we write foe that.
View 2 Replies
View Related
Jan 28, 1999
hi, I have a table that contain(custname,ordno,ordchange,ordlocation,reaso n)
I want to determine the following:
how many orders per customer,how many orders changed per customer,how many order location per customer, what reason for change per customer,
here what I wrote as a query, tell me if I am right. I thank you for your help
select custname, COUNT(DISTINCT ordno) ,
COUNT(DISTINCT ordchange) ,
COUNT(DISTINCT ordlocation) ,
COUNT( reason)
from customers a, orders b
where a.custname =b.custname
group by custname
View 1 Replies
View Related
Jul 23, 2005
Hello everyone, I am new to ERWIN and I need helps from the experts outthere.We are using ERWin 4.1.2771 and have reversed engineered some MS SQLServer 2000 databases.The problem we are having is that we have a FK on a column to a tablewhere the PK of the referencing table is on another column (such as anidentity column). We have a unique index on the column in the PK tableand SQL Server allows you to build a FK reference even though thecolumn is not defined as the PK.Does anyone know how to create this type of FK within Erwin?Thank You
View 2 Replies
View Related
Apr 20, 2001
Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.
I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?
Thanks,
Oliver
CREATE PROCEDURE test
@ContractCode varchar(10),
@dtFrom datetime,
@dtTo datetime,
@Umbrella int
AS
declare @option varchar(900)
if @umbrella = 0
set @option = 'c.contractcode'
else
set @option = 'cb.employercode'
select
c.claimsno,
c.attenddoctor,
c.patientcode,
p.sex,
cb.employercode
into #SavingsData1
from claimsa c inner join Patient p
on c.patientcode = p.patientcode
inner join claimsb cb on c.claimsno = cb.claimno
where
@option = @ContractCode and c.dateentered between @dtFrom and @dtTo
and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'
select * from #SavingsData1
View 1 Replies
View Related
Feb 16, 2007
Hello everyone!
I'm having a problem in using MS SQL 2005 as an ODBC in Powerbuilder 10.
My query which seems to work in Powerbuilder 5 does not work in Powerbuilder 10.
When I execute my query, I got this message "multi-part identifier alias.fieldname could not be bound. I don't know what's seems wrong. I even tried putting no alias but still it won't work..
help me please...
select sc.transaction_code,
case when :as_transtype = '' then drh.so_number else case when :as_transtype = 'SI' then drh.si_number else case when :as_transtype = 'DR' then drh.dr_number else :ad_documentnumber end end end document_number,
case when :as_transtype = '' then drh.so_date else case when :as_transtype = 'SI' then drh.si_date else case when :as_transtype = 'DR' then drh.dr_date end end end transaction_date,
pcd.item_number,
pcd.serial_number,
pcd.serv_tag,
pcd.rev_no,
space(10) supplier_name,
c.customer_name,
pcd.pcc_number,
pcd.supplier_refdocno,
pcd.ref_date,
pcd.original_tag,
pcd.item_description
from pcc_detail pcd
inner join pcc_header pch
on pch.pcc_number = pcd.pcc_number
inner join delivery_receipt_header drh
on pch.so_number = drh.so_number
inner join stock_card sc
on sc.so_number = drh.so_number and
sc.item_number = pcd.item_number
inner join customer c
on drh.customer_code = c.customer_code
where sc.transaction_code in ('DR', 'SE') and
sc.transaction_type <> 'RS' and
sc.item_number like case when :as_itemnumber = '' then sc.item_number else '%' + :as_itemnumber + '%' end and
pcd.serv_tag like case when :as_servicetag = '' then pcd.serv_tag else '%' + :as_servicetag + '%' end and
pcd.serial_number like case when :as_serialnumber = '' then pcd.serial_number else '%' + :as_serialnumber + '%' end and
pch.customer_name like case when :as_customer = '' then pch.customer_name else '%' + :as_customer + '%' end and
:as_transtype in ('SO','SI','DR','PC','') and
sc.transaction_code = case when :as_transtype = 'SI' then 'SE' else 'DR' end and
( pch.so_number = case when :ad_documentnumber = 0 then pch.so_number else :ad_documentnumber end or
pch.si_number = case when :ad_documentnumber = 0 then pch.si_number else :ad_documentnumber end or
pch.pcc_number like case when :ad_documentnumber = '' then pch.pcc_number else '%' + :as_documentnumber + '%' end or
pch.dr_number = case when :ad_documentnumber = 0 then pch.dr_number else :ad_documentnumber end
) and
pcd.item_description like case when :as_itemdesc = '' then pcd.item_description else '%' + :as_itemdesc + '%' end
this is my query
View 3 Replies
View Related
Jun 22, 2015
Is it possible to rename a field-name from ( tabular powerpivot ) model to a different Presented-name for the end user ?
usualy the names in the source model are Understandable to the developer , and its Custom to his understanding and nees , but for the end user its not - its not understandable , to long and so far form the regular terms in his Business , and also the language Might be non-English..
View 6 Replies
View Related
Feb 3, 2008
I am looking for advice on how to handle the following situation.Data being saved to a database table. Unique index on column in table. If the constraint is violated, what is the best way in alerting the user? Catch the exception and display error message to user? How do I know the exception is because of a unique index violation?
View 1 Replies
View Related
Jul 11, 2004
Hi,
How can I assign or retrieve a unique ID from a msSQL table where there isn't a unique column.
Is there a hidden 'raw id' i can retrieve from msSQL for each record?
I'm not allowed to change the current database at all, but the developers who created it managed to use it without Unique IDs.... which doesn't help me coming from a mySQL background where UID's are kind of mandatory.
Thanks
View 2 Replies
View Related
Feb 6, 2007
This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.
Can anyone shed any light on the problem?
Thanks.
View 5 Replies
View Related
Oct 8, 2007
hi,
In the below code, i am trying to insert the identity value into the 2nd column of the Symp_Quote table. Schema of the table Symp_Quote is as follows.
CRAETE TABLE Symp_Quote
(
QuoteRevision_ID INT PK,
DocumentSeq TINYINT PK,
DocumentDisplayName VARCHAR (255)
)
1st column data is coming from the select statement. but for 2nd column i have to insert identity or some unique value. to do that i was trying withe the following code, but it failed.
INSERT INTO Symp_Quote
(
QuoteRevision_ID,
DocumentSeq,
DocumentDisplayName
)
SELECT
S.QuoteRevision_ID,
IDENTITY (INT,1,1) AS DocumentSeq,
T.Name,
FROM Symp S
INNER JOIN Trilogy T
ON
SQR.tril_gid = FQR.tril_gid
kindly correct me whr I am wrong.........
Thanks,
Rahul Jha
View 5 Replies
View Related
Jun 6, 2008
I like to use the management console to design tables, but have
been unable to figure out how to add a unique column constraint.
I have a primary key defined in col1 and want to make col2, char(10),
unique, but keep col1 as the primary key.
I have tried the Check constraint menu path, but do not know how
to write the expression.
If I go along the Indexes/Key path, I am forced to change the primary
key which I do not want to do.
Can anyone help?
TIA, Joe
View 5 Replies
View Related
Jan 22, 2008
Absolute SQL Beginner here
I am using SQL Server 2005 Express Edition in conjunction with asp.net 2.0
Is there any way to guarantee when INSERTing data, that all values in a given column (which is of a char data type) are unique? Or do I have to do this programatically at the asp layer?
Thanks in advance
Larry
View 4 Replies
View Related
Nov 3, 2015
I just started with SQL Server Express and was playing around with the Management Studio.
I created a query to make a column unique:
ATLTER TABLE Table_Name
ADD UNIQUE (Column_Name)
That worked perfect, if I fill in the same data in the next row of that column, I get an error as expected. But, how do I remove the UNIQUE property on that column? I can find it in the column properties within Management studio, neither I can't find the command to undo this action.
View 5 Replies
View Related
Nov 23, 2007
Hello!
This is not really a question, maybe a start of discussion if this could be a feature in coming versions...
Let's say I have a table like this:
id text1 text2
int varchar varchar
1 abc ghi
2 abc ghi
3 def ghi
and I want to query using group by, I would write SELECT text1, text2 FROM table GROUP BY text1, text2
This is not too bad, but imagine I got several columns of text I need to query, then I need to put them in both places, SELECT and GROUP BY. If I do use functions like SUBSTRING or concatenation on the columns, I need to repeat these in the GROUP BY as well.
It would be nice if I could say "GROUP BY id", and since id is unique on the table it is obvious that all columns taken from this table are already grouped and can be queried without having to have them in group as well.
For functions this is of course only possible if all parts only refer to columns from this table, or absolute entries, like LEFT(varchar ,3) or int+1 or such.
With this example, the benefit is not visible: if I added the id to the GROUP BY, I get every single row of course. GROUP on unique is useless... It does make sense if you query another table and join in this one as a lookup, and the id gets "multiple" this way.
As I said, just an idea to make the GROUP BY section more transparent and avoid repeating sections!
I'd like to know what you think of that.
Ralf
View 4 Replies
View Related
Jul 16, 2004
I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance. Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better. First, write a trigger. Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record. EXAMPLE DML:CREATE TABLE test ( ssn CHAR(9) NULL, testId INT identity(1,1) NOT NULL, ComputedConstraint AS CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9)) ELSE ssn END, UNIQUE (ComputedConstraint)) Any comments would be greatly appreciated.
View 6 Replies
View Related
May 5, 2014
I am very new to sql and I have got this doubt.
CREATE TABLE employee
{ EMP-ID VARCHAR UNIQUE,
DESIG VARCHAR UNIQUE,
SALARY INT };
Probably this is worst table ever created :).Now How to drop UNIQUE constraint on only column lets DESIG.
if i write
ALTER TABLE employee
DROP CONSTRAINT myuniqueconstraint;
will drop constraints on both columns which I dont want.
Any query that remove UNIQUE CONSTRAINT only on DESIGN column.
and one more clarification,
ALTER TABLE employee
MODIFY SALARY INT NOT NULL;
ALTER TABLE employee
ALTER COLUMN SALARY INT NOT NULL;
which of the above two is right query to add NOT NULL constraint to the above employee TABLE?
View 1 Replies
View Related
Nov 20, 2007
Hi All,
I am having problems with unique contraint, say I have a table called customer
create table customer (
id int identity(1,1) not null,
name nvarchar(50) not null,
id_card_no nvarchar(20) unique,
);
so when I do mutilple null inserts
insert into customer(name, id_card_no)
values("Customer 1", NULL);
insert into customer(name, id_card_no)
values("Customer 2", NULL);
I get an error duplicated keys on the unique column.
In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.
I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...
Are there any ways to archieve that in sql sever compact?
View 2 Replies
View Related
Nov 23, 2007
I don't immediately find if this is possible but hope someone can give me an answer:
is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?
Example: table (tableid, instancetype, instancename, ..)
instancetype can be A or B
if it is A then instancename must be unique
but for B instancename is not unique as these are copies from A
only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..
are there other solutions to make a constraint like this ?
Aeneas.
View 1 Replies
View Related
Oct 29, 2005
Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed
View 3 Replies
View Related
Dec 6, 2007
Hello there,
I am testing out the performance benefits of utilizing include column(s) to avoid RID lookups and so far it looks very promising. I am able to reduce the cost of the query significantly and execution time is very good. However my non-clustered index is part of the primary key constraint and I am wondering if it is possible to create the include column as part of the primary key. Or do I have to drop the pk constraint and create unique non-clustered index with include column?
Thanks,
Sankar
View 1 Replies
View Related
Oct 8, 2007
I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error.
Here is my table definition:
Column Name Data Type Allow Nulls
--------------------------------------------------------------------------------------------
Column1(PrimaryKey) int No
Column2 nvarchar(MAX) No
View 9 Replies
View Related