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...
>
>