Getting the primary keyy

I'm inserting a sort of data from a formview into the database. The primary key is set to increment automatically.

After the insertion I'm trying to get the primary key in order to use in another formview (same page) to insert data on different table.

The code:

asp:SqlDataSourceID="newSchemaSqlDataSource"runat="server"ConnectionString="<%$ ConnectionStrings:logprocConnectionString1 %>"

InsertCommand="INSERT INTO [LogSchema] ([Title], [Type], [InitPattern], [EndPattern], [SetupDate]) VALUES (@.Title, @.Type, @.InitPattern, @.EndPattern, @.SetupDate); SELECT @.NewID = SCOPE_IDENTITY()" OnInserted="newSchemaSqlDataSource_Inserted">

<InsertParameters>

<asp:ParameterName="Title"Type="String"/>

<asp:ParameterName="Type"Type="String"/>

<asp:ParameterName="InitPattern"Type="String"/>

<asp:ParameterName="EndPattern"Type="String"/>

<asp:ParameterName="SetupDate"Type="DateTime"/>

<asp:ParameterName="NewID"Type="Int32"/>

</InsertParameters>

Backend:

protectedvoid newSchemaSqlDataSource_Inserted(object sender,SqlDataSourceStatusEventArgs e)

{

int newid = (int)e.Command.Parameters["@.NewID"].Value;

Response.Write(newid.ToString());

}

The problem is the e.Command.Parameters["@.NewID"].Value is returning NULL as I could see at the debug. What am I missing?

Try setting the Direction property of your "NewID" Parameter to "Output".

<asp:parameter direction="Output" name="NewID" type="Int32" />

|||Thanks a lot... it works!!!!

getting primary key out of insert into statement

Is there a way to get the primary key that is causing a primary key violation when inserting via a insert into with a select statement. This violation is being caught using a try/catch statement. Is there anyway, short of doing yourself via brute force coding, to get what the values are that are causing the error. Basically, I'm doing:

begin try

insert into TableA

select col1, col2, col3

from TableB

end try

begin catch

--Catch occasional primary key violation.

end catch

not sure if this is what you want to do, but have you considered something like the following?

lets say you have a schema like this (psuedo metadata for brevity sake) and you want to insert table1 into table2

[NB: may have errors as I didn't run this through QA, but the concept is sound]

legend: TableName , primary key fields

table1
A1
A2
A3

table2
B1
B2
B3

then the table1 records that will be a violation can be selected via

select distinct table1.* into #violations
from table1 inner join table2
on table1.A1 = table2.B1
and table1.A2 = table2.B2

Then you can insert the valid records via:

insert into table2
select table1.*
from table1 left join table2
on table1.A1 = table2.B1
and table1.A2 = table2.B2
where table2.B1 is null

then, you can do

select * from #violations

and do what ever you want with these - write to another table/send in an email, etc. . .

lastly you will want to delete the temporary #violations table

drop table #violations

|||Check out the OUTPUT option of the INSERT statement.

Getting Identity when inserting a record from Stored Procedure....

I have a stored procedure that inserts a record into a table.
I need to get the Identity seed or Key that was just created by this
insert. Can I do this inside the stored procedure, returning the just
created key?
Here is the stored procedure...
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1
AS
INSERT INTO a_TixDetail
(SvcTixKey, Date, [Performed by], [Detail Summary],
External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
RETURN
TixKey is a field in the table that is the Primary key. It will be
genereated when the Insert is executed. How can I get this returned from
this procedure?
Thanks,
RogIn SQL Server 2000 use SCOPE_IDENTITY() to return the inserted IDENTITY
value. In SQL Server 7.0 use @.@.IDENTITY.
David Portas
SQL Server MVP
--|||Roger,
Create an output parameter and get the value from the function
SCOPE_IDENTITY().
Example:
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1,
@.new_id int output
AS
set nocount on
INSERT INTO a_TixDetail (SvcTixKey, Date, [Performed by], [Detail
Summary],External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
set @.new_id = scope_identity()
RETURN
go
declare @.i int
declare @.rv int
exec @.rv = R_InsertTicketDetail ..., @.i output
print @.rv
print @.i
go
AMB
"Roger" wrote:

> I have a stored procedure that inserts a record into a table.
> I need to get the Identity seed or Key that was just created by this
> insert. Can I do this inside the stored procedure, returning the just
> created key?
> Here is the stored procedure...
> ALTER PROCEDURE R_InsertTicketDetail
> @.SvcTixKey int,
> @.OpenDate datetime,
> @.PerformedBy nvarchar(50),
> @.Detail ntext,
> @.External bit = -1
> AS
> INSERT INTO a_TixDetail
> (SvcTixKey, Date, [Performed by], [Detail Summary],
> External)
> VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
> RETURN
>
> TixKey is a field in the table that is the Primary key. It will be
> genereated when the Insert is executed. How can I get this returned from
> this procedure?
> Thanks,
> Rog
>
>

Getting ID after Insert from AutoIncrement column in MS Access

I am inserting new records stored in SQL Server into a legacy MS Access application using SSIS. During the transformation, I need to get the ID MS Access assigned to the autoincrement column in the MS Access table I am inserting the row into. Is this possible? Can someone give me an example?

Thanks,

Steve

Not possible in a batch type insert like SSIS does.

Why not make your own "AutoIncrement" column inside SSIS? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Thanks Phil. I did not think so, but thought I would ask. I need the actual ID from the Access table so I can update the record in SQL server. The solutions is migrating to SQL server but in the meantime, information can be updated in either Access or a web interface to SQL Server. We have to sync the data between the two.

|||It may be possible if you insert one row at a time, but I'm not sure how to return the last AutoIncrement value in Access. In SQL Server it's @.@.identity, but not sure in Access.

You can still calculate your own autoincrement number. If nothing else is inserting into that Access table, you can turn auto-increment off. Then using the page I listed earlier, you calculate the max value which seeds the starting number for your upcoming inserts. Just a thought.|||

How can I force the transaction to complete within a dataflow? To solve this problem of retrieving the primary key assigned by Access, I added another column to the Access table to write the SQL key. When I add the record from SQL Server to Access, I write the SQL Server key to this column. The next step of the data flow is to re-read the record using the SQL key and retrieve the Access key assigned to the autonumber column. The problem seems to be that when I get to this step of the dataflow, the record hasn't actually been written so it does not complete the Lookup transformation.

Is there a way to force the transaction or do I need to move this step to a new Control Flow?

Thanks,

Steve

|||You'll have to move it to a new data flow. While the data flow does process "row by row", rows are processed in buffers. So one buffer (of ~10000 rows by default) has to be processed through the lookup before the same buffer can be written to the destination.

Getting error description

Hi all,
I need to get the error description from the SQL Server in a SP. For ex:

I have one insert statement which is inserting some values in a tabUserMaster table. If user tries to insert any duplicated row then following error is retruned [in Query analyzer].

Server: Msg 2601, Level 14, State 3, Procedure csp_ProvisionUser, Line 70
Cannot insert duplicate key row in object 'CoreUser' with unique index 'IDX_CoreUser_UserName'.
The statement has been terminated.

I want to trap this whole message in a variable. How to do this.... :(Hope someone has the answer, if it's possible to do this.

If not, can't you use @.@.ERROR to just trap the kind of error and then print the details about the object, index etc manually ?|||...since I'm not sure what you'd like to do once you have captured it, I'm not sure how to answer...but if you lookt at BOL and search on error it will bring up several topics which might apply (e.g. @.@.error)...|||hi,
<your-DML-qry>
select @.in_ErrorNumber=@.@.error()
if @.in_ErrorNumber > 0
Begin
insert into <table> values (dbo.fun_error_desc(@.err_no,@.vr_JobNumber))
End

create function fun_error_desc(@.err_no int)
returns varchar(2000)
as
Begin
declare @.err_message varchar(200)
select @.err_message = description from master.dbo.sysmessages where error=@.err_no
return @.err_message
End

pavan|||With this method, you'll get the generic message, without any parameter substitution?|||I am even stuck in the same situation.
If u have a single table involved in DML queries,then better send the table-name as a parameter and replace it accordingly.