AddNew then getting Unique ID

I have an auto-incremental field in my sql database table. After I add a
new record I need to get that ID. My below code adds the record with no
problems but the ID field I request always comes back empty. If I look in
the table the new record is there with the auto ID field.
hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
hr = pRstPubInfo->Open("messages",
_variant_t((IDispatch*)pConnection,true)
,
adOpenKeyset,adLockOptimistic,adCmdTable
);
pRstPubInfo->AddNew();
hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
hr = pRstPubInfo->Update();
_variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
//DBID = EMPTY.Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
are not optimal for performing inserts!
Anyway, then you could do this in one transaction and retrieve the output
variable:
CREATE PROCEDURE dbo.AddRow
@.value VARCHAR(32),
@.idOut INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT table(column) SELECT @.value;
SELECT @.idOut = SCOPE_IDENTITY();
END
GO
(Alternatively, you could use a scalar/resultset or, since it is an INT, you
could buck standard practice and use the return value.)
A
"Bob" <msgdev@.hotmail.com> wrote in message
news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>I have an auto-incremental field in my sql database table. After I add a
>new record I need to get that ID. My below code adds the record with no
>problems but the ID field I request always comes back empty. If I look in
>the table the new record is there with the auto ID field.
>
> hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
> hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
> hr = pRstPubInfo->Open("messages",
> _variant_t((IDispatch*)pConnection,true)
,
> adOpenKeyset,adLockOptimistic,adCmdTable
);
> pRstPubInfo->AddNew();
> hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
> hr = pRstPubInfo->Update();
> _variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
> //DBID = EMPTY.
>|||I am adding a binary object to the database. It could be very large so I
thought using AddChunk would be better. Is there a way to add binary data
using stored procedures? Is there a way to add chunks? May be I am doing
this all wrong. Any help would be appreciated.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
> Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
> are not optimal for performing inserts!
> Anyway, then you could do this in one transaction and retrieve the output
> variable:
> CREATE PROCEDURE dbo.AddRow
> @.value VARCHAR(32),
> @.idOut INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT table(column) SELECT @.value;
> SELECT @.idOut = SCOPE_IDENTITY();
> END
> GO
> (Alternatively, you could use a scalar/resultset or, since it is an INT,
> you could buck standard practice and use the return value.)
> A
>
> "Bob" <msgdev@.hotmail.com> wrote in message
> news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||AppendChunk can be used for Parameter objects as well as Field objects.
Bob wrote:
> I am adding a binary object to the database. It could be very large
> so I thought using AddChunk would be better. Is there a way to add
> binary data using stored procedures? Is there a way to add chunks?
> May be I am doing this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CREATE PROCEDURE sp_InsertBLOB
@.ID int = NULL OUT,
@.BLOB image = NULL
AS
SET NOCOUNT ON
INSERT INTO BLOBTable ( BLOB ) VALUES( @.BLOB )
SELECT @.ID = SCOPE_IDENTITY()
END
GO
The strange thing to me is that you are worried about the efficiency of
sending a "large binary object" to the server, but you are willing to pull
down an entire table full of them just to perform an insert?
John
"Bob" wrote:

> I am adding a binary object to the database. It could be very large so I
> thought using AddChunk would be better. Is there a way to add binary data
> using stored procedures? Is there a way to add chunks? May be I am doing
> this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
>
>

Adding unique tags to associate records

Hello,
Is there anyway to embed tags that use puctuation into a coulmn of an FTS
table and be able to serach for these tags using the Contains predicate? For
example, I would like to add the tag,"CDL=Y", to a column and search for this
value using the Contains predicate. I'm not sure how this tag gets indexed
but there are a couple of problems using the Contains predicate. The First
problem is puncuation is ignored. The next problem is "Y" is noise. This
means that "CDL=Y" is equal to "CDL". I would like to have the entire
word/tag ("CDL=Y") be a unique value so I could identify records with this
specific feature. Thanks in advance.
Dave
You will have to use underscores for this as SQL FTS does not index
properties and their values. So replace this CDL=Y with this CDL_Y in your
content and queries.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DaveS" <DaveS@.discussions.microsoft.com> wrote in message
news:4B924102-F70F-4300-8CFF-7901722B35C0@.microsoft.com...
> Hello,
> Is there anyway to embed tags that use puctuation into a coulmn of an FTS
> table and be able to serach for these tags using the Contains predicate?
For
> example, I would like to add the tag,"CDL=Y", to a column and search for
this
> value using the Contains predicate. I'm not sure how this tag gets
indexed
> but there are a couple of problems using the Contains predicate. The
First
> problem is puncuation is ignored. The next problem is "Y" is noise. This
> means that "CDL=Y" is equal to "CDL". I would like to have the entire
> word/tag ("CDL=Y") be a unique value so I could identify records with this
> specific feature. Thanks in advance.
> Dave
>
|||Another option is to use XQuery in SQL 2005 and store the doc in an XML data
type column. You could do a contains query against the document and then
refine the search to an particular element or attribute having this value.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DaveS" <DaveS@.discussions.microsoft.com> wrote in message
news:4B924102-F70F-4300-8CFF-7901722B35C0@.microsoft.com...
> Hello,
> Is there anyway to embed tags that use puctuation into a coulmn of an FTS
> table and be able to serach for these tags using the Contains predicate?
For
> example, I would like to add the tag,"CDL=Y", to a column and search for
this
> value using the Contains predicate. I'm not sure how this tag gets
indexed
> but there are a couple of problems using the Contains predicate. The
First
> problem is puncuation is ignored. The next problem is "Y" is noise. This
> means that "CDL=Y" is equal to "CDL". I would like to have the entire
> word/tag ("CDL=Y") be a unique value so I could identify records with this
> specific feature. Thanks in advance.
> Dave
>
|||Dave,
To understand your environment and to help troubleshoot this issue, could
you reply with the results of the following T-SQL code?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
The OS platform that you have SQL Server installed on as well as the
OS-supplied wordbreaker is an important aspect of understanding this issue,
for more details see: http://groups.google.com/groups?q=langwrbk+infosoft
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"DaveS" <DaveS@.discussions.microsoft.com> wrote in message
news:4B924102-F70F-4300-8CFF-7901722B35C0@.microsoft.com...
> Hello,
> Is there anyway to embed tags that use puctuation into a coulmn of an FTS
> table and be able to serach for these tags using the Contains predicate?
For
> example, I would like to add the tag,"CDL=Y", to a column and search for
this
> value using the Contains predicate. I'm not sure how this tag gets
indexed
> but there are a couple of problems using the Contains predicate. The
First
> problem is puncuation is ignored. The next problem is "Y" is noise. This
> means that "CDL=Y" is equal to "CDL". I would like to have the entire
> word/tag ("CDL=Y") be a unique value so I could identify records with this
> specific feature. Thanks in advance.
> Dave
>
sql

adding unique keys

Would anyone please instruct how to prevent the duplicate record by
setting the unique keys on the ms sql server? i've been checking the
duplicate record as front-end and i found out if there is an internet
delay or some other reasons, it has a chance to store the duplicated
data into the database. so i realized it has to be done on the back-end
side.
for example, if i have three columns (office code, office id, office
section) as a unique key, how can i setup this? thanks in advance.create a primary key or a unique constraint

ALTER TABLE [dbo].[YourTable] WITH NOCHECK ADD
CONSTRAINT [YourTable_PK] PRIMARY KEY CLUSTERED
(
[office code],
[office id],
[office section]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

or

ALTER TABLE dbo.YourTable ADD CONSTRAINT
IX_YourTable UNIQUE NONCLUSTERED
(
[office code],
[office id],
[office section]

) ON [PRIMARY]

Denis the SQL Menace
http://sqlservercode.blogspot.com/
HandersonVA wrote:

Quote:

Originally Posted by

Would anyone please instruct how to prevent the duplicate record by
setting the unique keys on the ms sql server? i've been checking the
duplicate record as front-end and i found out if there is an internet
delay or some other reasons, it has a chance to store the duplicated
data into the database. so i realized it has to be done on the back-end
side.
for example, if i have three columns (office code, office id, office
section) as a unique key, how can i setup this? thanks in advance.

Adding Unique data

Dear Folks,
I have a table like
Invno Amount
55 100
55 200
56 50
57 150
57 300
57 50
I need to query to make it as
55 - 300
56 - 50
57 - 500
Please help me...
Thanks for your replies in advance.
Regards,
Selvarathinam.SELECT InvNo, SUM(Amount)
FROM Table
GROUP BY InvNo
Selvarathinam wrote:
> Dear Folks,
> I have a table like
> Invno Amount
> 55 100
> 55 200
> 56 50
> 57 150
> 57 300
> 57 50
> I need to query to make it as
> 55 - 300
> 56 - 50
> 57 - 500
> Please help me...
> Thanks for your replies in advance.
> Regards,
> Selvarathinam.|||Select invno, sum(amount) group by invno
"Selvarathinam" <s.selvarathinam@.gmail.com> wrote in message
news:1149799832.127814.236410@.y43g2000cwc.googlegroups.com...
> Dear Folks,
> I have a table like
> Invno Amount
> 55 100
> 55 200
> 56 50
> 57 150
> 57 300
> 57 50
> I need to query to make it as
> 55 - 300
> 56 - 50
> 57 - 500
> Please help me...
> Thanks for your replies in advance.
> Regards,
> Selvarathinam.
>|||Thanks a ton...
itz working
Tracy McKibben wrote:
> SELECT InvNo, SUM(Amount)
> FROM Table
> GROUP BY InvNo
>
> Selvarathinam wrote:

adding UNIQUE Constraint to existing column

Hello,
I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
duplicate key using WITH NOCHECK in SQL Server 2000.
Here is my SQL syntax:
ALTER TABLE user_email WITH NOCHECK
ADD CONSTRAINT unq_user_email_email UNIQUE (email)
Query Analyzer keeps giving me the error message that duplicate key was foun
d:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 4. Most significant primary key is 'user1@.abc.com'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
I learned from Books Online that "WITH NOCHECK" allows to add an unverified
constraint and prevents the validation against existing rows.
I tried using SQLServer Enterprise Manager, Manage Indexes and got the same
error message even though that I had checked the "Ignore Duplicate Values"
checkbox.
Can someone please tell me how i can add a UNIQUE CONSTRAINT without
removing the duplicate keys.
Thank you so much for your help!
--
MitraSQL-Server uses a unique index to enforce a UNIQUE constraint. A unique
index does not allow duplicates, under no circumstances.
The following text is also part of the BOL article:
"The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY
and UNIQUE constraints."
HTH,
Gert-Jan
mitra wrote:
> Hello,
> I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
> duplicate key using WITH NOCHECK in SQL Server 2000.
> Here is my SQL syntax:
> ALTER TABLE user_email WITH NOCHECK
> ADD CONSTRAINT unq_user_email_email UNIQUE (email)
> Query Analyzer keeps giving me the error message that duplicate key was fo
und:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 4. Most significant primary key is 'user1@.abc.com'.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> The statement has been terminated.
> I learned from Books Online that "WITH NOCHECK" allows to add an unverifie
d
> constraint and prevents the validation against existing rows.
> I tried using SQLServer Enterprise Manager, Manage Indexes and got the sam
e
> error message even though that I had checked the "Ignore Duplicate Values"
> checkbox.
> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
> Thank you so much for your help!
> --
> Mitra|||> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
Thankfully you can't. From BOL:
"IGNORE_DUP_KEY
Controls what happens when an attempt is made to insert a duplicate key
value into a column that is part of a unique clustered index. If
IGNORE_DUP_KEY was specified for the index and an INSERT statement that
creates a duplicate key is executed, SQL Server issues a warning and ignores
the duplicate row."
When it says ignores the duplicate row, it means it doesn't insert it.
If you want to do what you are suggesting you will need to build a trigger.
Then just join to the parent table to see if the values already exist (make
sure to take care of the case where duplicate values are inserted via the
INSERT statement. If you want more information about how to do this, I
could help for out (as could others here :).
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:3BB0328C-8FC1-40A8-A614-D8E2C0B43859@.microsoft.com...
> Hello,
> I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
> duplicate key using WITH NOCHECK in SQL Server 2000.
> Here is my SQL syntax:
> ALTER TABLE user_email WITH NOCHECK
> ADD CONSTRAINT unq_user_email_email UNIQUE (email)
>
> Query Analyzer keeps giving me the error message that duplicate key was
> found:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 4. Most significant primary key is 'user1@.abc.com'.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> The statement has been terminated.
>
> I learned from Books Online that "WITH NOCHECK" allows to add an
> unverified
> constraint and prevents the validation against existing rows.
> I tried using SQLServer Enterprise Manager, Manage Indexes and got the
> same
> error message even though that I had checked the "Ignore Duplicate Values"
> checkbox.
> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
> Thank you so much for your help!
> --
> Mitra|||On Thu, 03 Feb 2005 21:18:09 +0100, Gert-Jan Strik wrote:

> SQL-Server uses a unique index to enforce a UNIQUE constraint. A unique
> index does not allow duplicates, under no circumstances.
> The following text is also part of the BOL article:
> "The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY
> and UNIQUE constraints."
I guess to achieve the effect you want, you could use a trigger:
create table user_email (email varchar(20) not null)
insert into user_email values ('rpresser@.nowhere.com')
insert into user_email values ('rpresser@.nowhere.com')
insert into user_email values ('rpresser@.nowhere.com')
go
select * from user_email
go
CREATE TRIGGER user_email_add
ON user_email
FOR INSERT
AS
IF EXISTS (select * FROM user_email U, inserted WHERE U.email =
inserted.email)
BEGIN
RAISERROR ('Email must be unique',16,1)
ROLLBACK TRANSACTION
END
go
insert into user_email values ('rpresser@.nowhere.com')
Results in this output:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
email
--
rpresser@.imtek.com
rpresser@.imtek.com
rpresser@.imtek.com
(3 row(s) affected)
Server: Msg 50000, Level 16, State 1, Procedure user_email_add, Line 7
Email must be unique|||Not quite. Two problems. First, the trigger fires after the insert. No
matter what you insert it will fail because whatever is in the inserted
table will already be in the table. Second, you are forgetting the case
where the user inserts duplicates in the insert statement:
insert into user_email
select 'bob@.bob.com'
union
select 'bob@.bob.com'
Here you have to consider the uniqueness of the values in the inserted
table.
Using his table, this code will work:
CREATE TRIGGER user_email_add
ON user_email
FOR INSERT, UPDATE
AS
IF EXISTS ( select user_email.email
FROM inserted
join user_email
on user_email.email = inserted.email
group by user_email.email
having count(*) > 1 )
begin
RAISERROR ('Email must be unique.',16,1)
ROLLBACK TRANSACTION
return
end
go
Note that I changed it to an UPDATE trigger also, since otherwise:
UPDATE user_email
SET email = 'fred@.server.com'
would work just fine. I would still suggest against this approach. Clean
up the data and life will be peachy :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:l5jr9dfyygw7$.dlg@.rpresser.invalid...
> On Thu, 03 Feb 2005 21:18:09 +0100, Gert-Jan Strik wrote:
>
> I guess to achieve the effect you want, you could use a trigger:
> create table user_email (email varchar(20) not null)
> insert into user_email values ('rpresser@.nowhere.com')
> insert into user_email values ('rpresser@.nowhere.com')
> insert into user_email values ('rpresser@.nowhere.com')
> go
> select * from user_email
> go
> CREATE TRIGGER user_email_add
> ON user_email
> FOR INSERT
> AS
> IF EXISTS (select * FROM user_email U, inserted WHERE U.email =
> inserted.email)
> BEGIN
> RAISERROR ('Email must be unique',16,1)
> ROLLBACK TRANSACTION
> END
> go
> insert into user_email values ('rpresser@.nowhere.com')
>
> Results in this output:
> (1 row(s) affected)
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> email
> --
> rpresser@.imtek.com
> rpresser@.imtek.com
> rpresser@.imtek.com
> (3 row(s) affected)
> Server: Msg 50000, Level 16, State 1, Procedure user_email_add, Line 7
> Email must be unique

adding identifier number to records

I have a table already created and need to add an autonumber/ unique identif
ier
field.
If the table already contains records can I just add the new autonumber fiel
d
and run a query to populate this field for all records with unique values?
how do I do this?
are their other ways of doing this ? and how?
thanks
ChrisChris,
When you add and identity column to your table, SQL Server automatically
populates the column. You can not update an identity column.
Example:
use northwind
go
create table t (
colA char(1) not null unique
)
go
insert into t values('a')
insert into t values('b')
insert into t values('c')
go
select * from t
go
alter table t
add colB int not null identity
go
select * from t
go
drop table t
go
AMB
"Chris" wrote:

> I have a table already created and need to add an autonumber/ unique ident
ifier
> field.
> If the table already contains records can I just add the new autonumber fi
eld
> and run a query to populate this field for all records with unique values?
> how do I do this?
> are their other ways of doing this ? and how?
> thanks
> Chris|||I added the unique identifier column using table design.
However when I queried the database the values were all null.
Also since there are a lot of records in the table and generating the
alpha-numeric values does take processing time, I noticed adding the column
did not require any processing, (i.e. no hour glass).
Also I would prefer to just use a big int as it will take up less space and
be faster.
How can I use @.@.identity to update each record? is this possible?
Gracias Alejandro,
Tu eres muy asusto. He vido muchas siertos y respuestas de usted en los
forums.
Perdone mi espanol, estoy apprendiendo.
Por favor, constesta en ingles :)
"Alejandro Mesa" wrote:
> Chris,
> When you add and identity column to your table, SQL Server automatically
> populates the column. You can not update an identity column.
> Example:
> use northwind
> go
> create table t (
> colA char(1) not null unique
> )
> go
> insert into t values('a')
> insert into t values('b')
> insert into t values('c')
> go
> select * from t
> go
> alter table t
> add colB int not null identity
> go
> select * from t
> go
> drop table t
> go
>
> AMB
>
> "Chris" wrote:
>|||As I told you, you can not update an identity column.
AMB
"Chris" wrote:
> I added the unique identifier column using table design.
> However when I queried the database the values were all null.
> Also since there are a lot of records in the table and generating the
> alpha-numeric values does take processing time, I noticed adding the colum
n
> did not require any processing, (i.e. no hour glass).
> Also I would prefer to just use a big int as it will take up less space an
d
> be faster.
> How can I use @.@.identity to update each record? is this possible?
> Gracias Alejandro,
> Tu eres muy asusto. He vido muchas siertos y respuestas de usted en los
> forums.
> Perdone mi espanol, estoy apprendiendo.
> Por favor, constesta en ingles :)
> "Alejandro Mesa" wrote:
>|||okay, so a big int field that has been filled with a value using @.@.identity
can not be edited?
"Alejandro Mesa" wrote:
> As I told you, you can not update an identity column.
>
> AMB
> "Chris" wrote:
>|||I need to create a table with unique primary key on only one field.
How does one create or load a table with unique values in a primary key
field if
you have not been been provided unique values.
I'm happy to use just a big int data type to hold my unique values
"Chris" wrote:

> I have a table already created and need to add an autonumber/ unique ident
ifier
> field.
> If the table already contains records can I just add the new autonumber fi
eld
> and run a query to populate this field for all records with unique values?
> how do I do this?
> are their other ways of doing this ? and how?
> thanks
> Chris|||Correct.
Example:
use northwind
go
create table t (
colA char(1) not null unique
)
go
insert into t values('a')
insert into t values('b')
insert into t values('c')
go
select * from t
go
alter table t
add colB int not null identity
go
select * from t
go
-- this will give an error
update t
set colB = 4
where colB = 2
go
drop table t
go
AMB
"Chris" wrote:
> okay, so a big int field that has been filled with a value using @.@.identit
y
> can not be edited?
>
> "Alejandro Mesa" wrote:
>

Adding entry to DB and getting unique ID at the same time

Hi all,

I'm writing a website with Cold Fusion and when a user submits a
request and it's stored in the MS SQL database, I want the unique ID
(Identity field in table) to be given to the user on screen plus
emailed to user.

Now can I store data to the database (where the ID is created) and
return this as a variable in the same statement? I've seen this done
on many websites, but I have no idea how to do it in one step.

Thanks,

Alex.Return to the user the value from @.@.identity (check this out in the BOL).
You can also have SQL trigger off the email if this fits within your
project's stated performance requirements. Send the email via
xp_smtp_sendmail(http://sqldev.net/xp/xpsmtp.htm) But basically your
requirement to do it in 1 step could all be handled with a stored procedure.

hth
Eric

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0401291136.6ec0ee16@.posting.google.c om...
> Hi all,
> I'm writing a website with Cold Fusion and when a user submits a
> request and it's stored in the MS SQL database, I want the unique ID
> (Identity field in table) to be given to the user on screen plus
> emailed to user.
> Now can I store data to the database (where the ID is created) and
> return this as a variable in the same statement? I've seen this done
> on many websites, but I have no idea how to do it in one step.
> Thanks,
> Alex.

Adding An Index To A View

I need to create an index for a view that I have recently created.
My problem is that none of the columns currently in the view have unique
values therefore I am assuming it is impossible to index. What I would
like to add a column to this view with all unique values (autonumbered)
and index the view based on this added column. Does anyone know if this
is possible to do. If not then how does anyone index a view?
I would appreciate any help on this a lot.
Thanks.
--
Posted via http://dbforums.comHi Zoltar,
Please don't most the same question independently to multiple newsgroups,
you can post it to multiple newsgroups at once.
Your question has been answered in .programming
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"zoltar" <member34126@.dbforums.com> wrote in message
news:3334410.1062764149@.dbforums.com...
> I need to create an index for a view that I have recently created.
> My problem is that none of the columns currently in the view have unique
> values therefore I am assuming it is impossible to index. What I would
> like to add a column to this view with all unique values (autonumbered)
> and index the view based on this added column. Does anyone know if this
> is possible to do. If not then how does anyone index a view?
> I would appreciate any help on this a lot.
> Thanks.
>
> --
> Posted via http://dbforums.com