Help Getting Unique ID When Using Rs.Addnew
Jan 9, 2005
Hello, I have the following problem with MS SQL and ASP:
When adding a new code with the code below I need to get the automatically generated ID. I know there are threads discussing this but I have found none which discuss it when using the rs.AddNew method. I don't think "SELECT @@IDENTITY" works for me. Here's the code:
Code:
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorType = 2
rs.LockType = 3
StrSQL = "SELECT * FROM table1"
rs.Open strSQL, objConn
rs.Addnew
rs("Field1") = "Value1"
rs("Field2") = "Value2"
rs.Update
rs.Movelast
RowID = rs("ID")
rs.Close
I though the rs.Movelast will work and it did most of the time, but I found that it does not work ALL of the time. What happens is that sometimes a record may be added before the last record in the table and "RowID" would actually contain that records ID. So I need your help. Thanks
View 6 Replies
ADVERTISEMENT
May 8, 2006
Hi,We have been using ADO and the AddNew method for a long time as a meansto add records to the database. It has always worked fine - no problem.But - we recently started using INSERT triggers that simply call a fewstored procs (they're actually SSNS stored procs that send new eventinfo to notification services). Anyway, these triggers do not seem tofire at all! If I execute an insert command manually from QueryAnalyser, no problems. But the trigger does not fire at all from myapplication!Does anyone know why this could be? For info, my connection string usedby the ADO connection object looks like this: Provider=SQLOLEDB.1;DataSource=XXX;Initial Catalog=YYYAnd my AddRecord ADO code looks like this:With rs.Open sSQL, ConnectionString, adOpenKeyset, adLockOptimistic,adCmdTable And adExecuteNoRecords.AddNewAm I mnissing something obvious here? Any help appreciated!
View 1 Replies
View Related
Jun 12, 2006
Hi,
I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @@IDENTITY statement.
What is the reason for this behavior?
How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?
Best regards,
George
View 21 Replies
View Related
Mar 26, 2006
The application is running ADO (MDAC) version 2.81.1117.0.
The application gets an recordset created by SQLServer by an select statement. In this case the recordset is empty.
The application adds a record to the recordset by AddNew(). Works fine. But when assigning the first field (smalldatetime in database) I get ADO error -2147217887.
I have inspected the recordset and I found a difference between the working one from SQLServer2000 and the new one from SQLServer2005: The Attributes item for the fields in the recordset has the flag adFldUnknownUpdatable set in the working SQL2000 version but is not set from SQL2005.
The database on SQL2005 is copied from the SQL2000 system and attatched to the SQL2005 system. I Have checket that the compability flag for the database on the SQL2005 system is set to SQL2000 compability.
Nils Nordenbrink
Winkonsult AB
Sweden
View 4 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
Sep 22, 2004
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies
View Related
Jul 20, 2005
HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies
View Related
Jun 24, 2006
What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
View 1 Replies
View Related
Mar 7, 2001
Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?
Which one do one use ?
thanks
sonali
View 4 Replies
View Related
Jan 20, 2006
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View 2 Replies
View Related
Mar 26, 2008
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
View 12 Replies
View Related
Mar 12, 2008
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code
Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword
and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
Thank You
Dominic Mancl
View 1 Replies
View Related
Nov 13, 2007
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies
View Related
May 1, 2008
Here is the table I created:
create table Test (
[recId] [int] identity(1, 1) not null,
[code] [varchar](50) not null,
[prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)),
constraint [PK_Test] primary key clustered
(
[recId]
) with fillfactor = 90 on [primary]
) on [primary]
go
insert into Test (code, prime) values ('AVA', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('CAR', cast(1 as bit))
insert into Test (code, prime) values ('CAR', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(1 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
select *
from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail
insert into Test (code, prime) values ('RLW', cast(1 as bit))
Thanks for you help!
Regards,
Anand
View 11 Replies
View Related
Apr 5, 2007
in sql server 2000
i know how to make primary key using enterprise manager
i want to make one of the columns foreign key,how to do that using enterprise manager.and what is the difference between both.
View 3 Replies
View Related
Jan 19, 2001
I have one table called agents.
In this table there are two columns, one called "company number" (NUMBER) and one called "company name" (VARCHAR). I have an index called agents_PK that are unique and indexes both columns with company number as first and company name as second in the column order.
But when I look at the data in the column company number its not unique, I find several rows with the same number.
How come ?
Regards Mattias
View 1 Replies
View Related
Nov 3, 2005
How do I write a select statement that finds items in a table that are not unique between two fields using the following?
Select A.KEY, AP.[EXPIREDATE]
From ACTKEY A Left Outer Join ACTKEY_PRODUCT AP
On A.KEY = AP.KEY
View 2 Replies
View Related
May 13, 2004
I've always used the identity field in SQL server to maintain the unique id for a table. With the new DB design at work we brought in a dba and she made us move away from allowing SQL maintain the unique field and having us maintain the unique field in code. To do that we had to begin a transaction, do a select max(id) + 1, insert into table, commit transaction. Doing it this way, I'm starting to see deadlocks due to the transactions locking the table.
Getting down to what I wanted to know, what is the pro's/con's you guys see in maintaining the unique ID this way and is there a better way of creating an unique id in T-SQL code?
Thanks
View 2 Replies
View Related
May 14, 2008
I know that primary key is a unique key .I read somewhere that a table can have both primary key and unique key at a time .Am I Right?Pl give clarification as asap.
Thank U
vedavathi zend
View 4 Replies
View Related
Jan 31, 2007
I am using the sql function unique id ( create a new default call it newid and then give it the value newid() ) The problem is I'm trying to automatically populate that uniqueid field with the random 36 bit character. if I create a new record it will create the 36bit character, but how do I add the 36bit character to an existing table?
View 1 Replies
View Related
Jun 1, 2007
Dear experts,
if i've created one unique key on one column, will it creates an index on that? if so, is there any way to find how every index was created?
i mean wether it was created with create index or it was created while the primary key or unique key creation.
thank you very much....i've been using the PBGUY query
Select * from
(select object_name(si.id) [Object],index_col(object_name(si.id), si.indid, sk.keyno) [Column_Name],
case when (si.status & 16)<> 0 then 'clustered'
else 'nonclustered' end [Index Type]
from sysindexes si join sysindexkeys sk
on si.id = sk.id and si.indid = sk.indid and si.indid between 1 and 254 and (si.status & 64)=0 ) as b
where Column_Name is not null
thank you very much
View 3 Replies
View Related
Mar 29, 2008
Hi guys,
I need your help!
May I know what can I do to get a single name using the sample table below?
toptable
name account
a 1
a 2
a 3
b 1
b 2
c 2
d 3
e 1
e 2
result will be
toptable
name account
a 1
b 2
c 2
d 3
e 2
I just want to get all single names
View 7 Replies
View Related
Jul 24, 2007
Hello SQL gurus, can you help me with a problem.
I have an Access Database and a table containing a list of currency values.
I want to query this table to find out if there are any unique values, that is, an amount that only appears once in the table. Its probably very simple but I cannot work it out. The table is called Bids and the Field that I want to query is called Bids_value.
Any ideas would be greatly appreciated.
View 1 Replies
View Related
Jul 24, 2007
Lorsque tu as edité un nouveau projet, tu a sans doute du prendre le même nom qu'un précédent. Lors du déploiement, l'ID du model ne peux pas être actualisé, alors il veux creer un nouveaux sur le server de rapport. Or, c'est impossible puisqu'il existe déja...
Suprime le rapport qui porte le meme identifiant et re déploie le, ca fonctionnera sans souci, j'éspère
View 1 Replies
View Related
Oct 29, 2007
Hi
I have a table as follows
ID Cat Date
--------------------
Id1 cat1 d1
Id2 cat1 d2
Id3 cat1 d3
Id4 cat2 d4
I wanted to retrived unique Cat for given ID's. I accept two ID's always
Select distinct(Cat) from Table where ID='id1' or ID='Id2'
This works fine for me and returns distincts values.
Result
--------
Cat1
But i also want the date along with Cat ( which ever is greater ) from theresult set.
Expected result
----------
Cat1 D2( latest)
Can any one please let me know the query
~Mohan
View 1 Replies
View Related
Oct 30, 2006
Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks
View 13 Replies
View Related
Nov 10, 2007
I've got a table name Messages and each message has a MessageID (the primary, auto-generated column) and a ThreadID (for grouping replies together). When I create a new message, I want to insert a new ThreadID if none is given to the stored procedure. I'm looking for something like NewID() as the default value but that inserts a NewInt. Do I have to write my own trigger?And I know ThreadID should be a foreign key to a Threads table but I'm keeping it simple for now. I have the option of making that later if I like. Thanks.
View 1 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
Sep 28, 2004
<disclaimer>I'm only using Web Matrix and have very little SQL knowledge</disclaimer>
I am doing an INSERT into a file where the primary key (UniqueID) is automatically generated. However, as soon as the record is inserted I need the UniqueID for further processing.
The only way I can see for me to get the UniqueID is to do a SELECT immediately after the INSERT, which seems a bit of an overhead.
So, just checking really, is this my only option or is there a super impressive way of passing the UniqueID back via the INSERT.
Thanks
Mark
View 2 Replies
View Related
Sep 21, 2005
I have a list of zones and IP addresses assigned to those zones. On the page that manages those zones and their IP addresses needs to only display the RANGES of IP addresses that exist for that zone. For example if this is my data for Zone 1:IPAddress (removed first octet)002.202.148002.202.149187.202.243187.202.244187.202.245187.202.246187.202.247187.202.248187.203.187I want to display it as this on the management page:Zone 1 : IPAddress Ranges From 2.202.148 to 2.202.149 From 187.202.243 to 187.202.248 From 187.203.187 to 187.203.187Is there any way to make the SQL statement list the mins and maxes for every range? Or is this something I just HAVE to do in the code? I would like a record to return:MIN | MAX2.202.148 | 2.202.149187.202.243 | 187.202.248187.203.187 | 187.203.187I think I'm trying to expect a lot from SQL but I thought i would ask anyway. Thanks!~Cattrah~
View 1 Replies
View Related
May 13, 2006
Hi,I want to add unique contraint to EXISTING column in EXISTING table.I have ms sql 2005.How to do that?
View 7 Replies
View Related
Jun 2, 2006
Hi again,
When developing web sites with Asp, DreamWeaver and Access, I've always used Unique IDs for setting things like userID or productID. However, with VWD Express and a .mdf db, this doesn't seem like an easy thing to do, at least not when inserting test data during development. The reason is that the uniqueIDs are 32 or so digits, and I have no idea what to write, and it's tiresome anyway.
I used a book from Wrox (Begninning Asp.Net 2.0) to learn VWD and Asp.Net, and in their examples they actually use ints for setting their productIDs and the like. I'm really surprised to see that in a text book, but that's the way they've done it.
So what's the best practice? What should I do? Please help me out here as I'm really confused!
Thanks in advance,
Pettrer
View 3 Replies
View Related
May 31, 2001
I have a bit field only alow one row as 1 all other rows should always 0.
How can I control that -- trigger or constraint?? How?
View 1 Replies
View Related
