SQL tool for MSDE: inserted

Showing posts with label inserted. Show all posts

Monday, February 13, 2012

AFTER INSERT TRIGGER PLEASE HELP

I need to set up a trigger that updates a field in a record directly
after it is inserted. I have been burning some serious cycles on this
and can't figure it out. Any help would be apreciated.
Here is what I have so far:
CREATE TRIGGER DateMod ON tablename
AFTER INSERT
AS
DECLARE @.RECORDID VARCHAR (20)
SELECT @.RECORDID = SELECT MAX(recordid) FROM tablename
UPDATE field2_newdate SET field2_newdate = (field1_olddate)+1
WHERE recordid = @.RECORDID;
As you can tell by the code, I am a newbie to sql triggers. Because of
this, I will provide a
more detailed explanation of what I am trying to accomplish.
****************************************
********************************
tablename
(before update)This is what the end result should look like
recordid field1_olddate field2_newdate
1 01/01/2000 02/02/2000
****************************************
********************************
Now lets add a record:
recordid field1_olddate field2_newdate
2 01/04/2000
****************************************
*******************************
The trigger should add 1 day to the field1_olddate and set the value of
field2_newdate to 01/05/2000
I need the trigger to add one day to the date in the field1_olddate and
then update field2_newdate in the same record with the new value
directly after the record is submitted.
Please help!
sql trigger newbiesteven@.mindspring.com,
The trigger is executed per statement instead per row, so you have to keep
in mind that the statement could take several rows. Try:
CREATE TRIGGER DateMod ON tablename
AFTER INSERT
AS
UPDATE tablename
SET field2_newdate = (select dateadd(day, 1, i.field1_olddate) from inserted
as i where i.recordid = tablename.recordid)
where exists(select * from inserted as i where i.recordid =
tablename.recordid)
go
AMB
"steven@.mindspring.com" wrote:

> I need to set up a trigger that updates a field in a record directly
> after it is inserted. I have been burning some serious cycles on this
> and can't figure it out. Any help would be apreciated.
> Here is what I have so far:
> CREATE TRIGGER DateMod ON tablename
> AFTER INSERT
> AS
> DECLARE @.RECORDID VARCHAR (20)
> SELECT @.RECORDID = SELECT MAX(recordid) FROM tablename
> UPDATE field2_newdate SET field2_newdate = (field1_olddate)+1
> WHERE recordid = @.RECORDID;
>
> As you can tell by the code, I am a newbie to sql triggers. Because of
> this, I will provide a
> more detailed explanation of what I am trying to accomplish.
>
> ****************************************
********************************
> tablename
> (before update)This is what the end result should look like
> recordid field1_olddate field2_newdate
> 1 01/01/2000 02/02/2000
>
> ****************************************
********************************
> Now lets add a record:
>
> recordid field1_olddate field2_newdate
> 2 01/04/2000
> ****************************************
*******************************
> The trigger should add 1 day to the field1_olddate and set the value of
> field2_newdate to 01/05/2000
> I need the trigger to add one day to the date in the field1_olddate and
> then update field2_newdate in the same record with the new value
> directly after the record is submitted.
> Please help!
> sql trigger newbie
>|||Thank you. You are a GOD! Your code works perfectly. Could you pleas
point me toward a good resource to learn about sql triggers?
Thanks again

After Insert Trigger Issue

Trying to create a trigger that will based on the recorded inserted into an enrollment table will update a schedule table.

My syntax is as listed:

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

Where dbo.tblClassSchedule.CourseNumber = inserted.EnrollCourseNumber

End

I keep getting msg 4101 'the multi-part identifier could not be bound'

Not really sure how to fix this, I have attempted also referencing the table that trigger is fired from, along with referencing an exists statement ; and of course when I didn't place a where statement the trigger will create itself, but will increase the enrollment count on every record

*** I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions***

Try something like this:

Code Snippet

CREATE TRIGGER IncreaseEnrollments

ON dbo.tblEnrollRegistrations

AFTER INSERT

IF @.@.ROWCOUNT = 0

RETURN

BEGIN

SET NOCOUNT ON;

UPDATE dbo.tblClassSchedule

SET EnrollNumber = ( EnrollNumber + 1 )

FROM dbo.tblClassSchedule c

JOIN inserted i

ON c.CourseNumber = i.EnrollCourseNumber

END

As a side note, using 'tbl' as a table name prefix is quite out of 'style'. You will usually know from context if it is a table, as the wasted three keystrokes every time you type is serves no purpose.

I hope you are all creating a trigger to decrease the EnrollNumber upon a registration cancellation.

|||

The following queries also work,

Code Snippet

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

Wheredbo.tblClassSchedule.CourseNumber in (select EnrollCourseNumber from inserted)

End

--or

Code Snippet

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

WhereExists (select 1 from inserted as I Where i.EnrollCourseNumber = tblClassSchedule.CourseNumber)

End

|||

Can you have multiple course numbers in the registrations table? If so, none of the posted solutions will produce the correct results. You need to do the following:

Code Snippet

update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + (select count(*) from inserted as i

where i.EnrollCourseNumber = dbo.tblClassSchedule.EnrollCourseNumber);

The solutions with joins or exists (IN approach is same as EXISTS) will not count duplicate course numbers - for example, if there are more than one registrations per course which is likely.

|||

Nathan,

From your previous message

My scenario is listed below, with some sample code suggested. I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions

, please let us know exactly what you are "attempting to accomplish".

If you don't give any feedback, we can't tune our suggestions to help you find a solution.

|||

nothing is happening ... thats the problem ... I am attempting to increase an enrollment head count with the triggers listed ... i know i can do it with Visual Basic, but the code that I know it will take seems to be more complicated than what a trigger would allow ...

my first issue with my original trigger was that it wasn't recognizing the column referenced, which basically the 3 suggestions should have fixed. the 'enrollment number' does increase when i don't restrict the reference to a specific record, of course the problem with that is that it increases (and when i add the decrement trigger, decrease) the value of all records, which of course is not a result I want

thx

|||

For none of the suggestions to be working properly, there must be something about the tables and/or data that hasn't been disclosed and that would be helpful to find a working solution.

Please post the table DDL for ClassSchedule and EnrollRegistrations, and a few rows of sample data in the form of INSERT statements (see this link for ideas).

|||

Here Are The Table Structures. Pretty Much I am Working With Little Data Right Now, Because It Hasn't Been Rolled out yet ...

tblClassSchedule

USE [NHSMS]
GO
/****** Object: Table [dbo].[tblClassSchedule] Script Date: 07/26/2007 15:56:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblClassSchedule](
[ClassInstID] [int] IDENTITY(1,1) NOT NULL,
[CourseNumber] [int] NULL,
[CStartDate] [smalldatetime] NULL,
[CEndDate] [smalldatetime] NULL,
[MaxEnroll] [smallint] NULL CONSTRAINT [DF_tblClassSchedule_MaxEnroll] DEFAULT ((0)),
[EnrollNum] [int] NULL CONSTRAINT [DF_tblClassSchedule_EnrollNum] DEFAULT ((0)),
[InstName] [int] NULL,
[ClassType] [int] NULL CONSTRAINT [DF_tblClassSchedule_ClassType] DEFAULT ((1)),
[DaySchedule] [bit] NULL CONSTRAINT [DF_tblClassSchedule_Active1] DEFAULT ((1)),
[ClassStatus] [int] NULL CONSTRAINT [DF_tblClassSchedule_ClassStatus] DEFAULT ((1)),
[ClassRoom] [int] NULL,
[Notes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Active] [bit] NULL CONSTRAINT [DF_tblClassSchedule_Active] DEFAULT ((1)),
[EntryDate] [smalldatetime] NULL,
[WebSch] [bit] NULL CONSTRAINT [DF_tblClassSchedule_WebSch] DEFAULT ((1)),
[CLength] [int] NULL CONSTRAINT [DF_tblClassSchedule_CLength] DEFAULT ((0)),
[CLocation] [int] NULL,
[CScheduleType] [int] NULL,
[Day1] [smalldatetime] NULL,
[Day2] [smalldatetime] NULL,
[Day3] [smalldatetime] NULL,
[Day4] [smalldatetime] NULL,
[Day5] [smalldatetime] NULL,
[Day6] [smalldatetime] NULL,
[Day7] [smalldatetime] NULL,
[Day8] [smalldatetime] NULL,
[Day9] [smalldatetime] NULL,
[Day10] [smalldatetime] NULL,
[Day11] [smalldatetime] NULL,
[Day12] [smalldatetime] NULL,
[CStartTime] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CEndTime] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tblClassSchedule] PRIMARY KEY CLUSTERED
(
[ClassInstID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [NHSMS]
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblClassRooms] FOREIGN KEY([ClassRoom])
REFERENCES [dbo].[tblClassRooms] ([ClassRmID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblClassStatus] FOREIGN KEY([ClassStatus])
REFERENCES [dbo].[tblClassStatus] ([CStatusID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblCourses] FOREIGN KEY([CourseNumber])
REFERENCES [dbo].[tblCourses] ([CourseID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblCourseSchType] FOREIGN KEY([CScheduleType])
REFERENCES [dbo].[tblCourseSchType] ([CSchTypeID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblCourseType] FOREIGN KEY([ClassType])
REFERENCES [dbo].[tblCourseType] ([SchItemTypeID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblLocation] FOREIGN KEY([CLocation])
REFERENCES [dbo].[tblLocation] ([LocID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblTimeValue] FOREIGN KEY([CStartTime])
REFERENCES [dbo].[tblTimeValue] ([TimeValue])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblTimeValue1] FOREIGN KEY([CEndTime])
REFERENCES [dbo].[tblTimeValue] ([TimeValue])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblUsers] FOREIGN KEY([InstName])
REFERENCES [dbo].[tblUsers] ([UserID])

tblEnrollReg

USE [NHSMS]
GO
/****** Object: Table [dbo].[tblEnrollReg] Script Date: 07/26/2007 15:56:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEnrollReg](
[EnrollID] [int] IDENTITY(1,1) NOT NULL,
[EStudentNum] [int] NULL,
[ECourseNumber] [int] NULL,
[PayMethod] [int] NULL,
[ClassPrice] [money] NULL,
[EnrollDate] [smalldatetime] NULL,
[EnrollBy] [int] NULL,
[EActive] [bit] NULL CONSTRAINT [DF_tblEnrollReg_EActive] DEFAULT ((1)),
[CancelDate] [smalldatetime] NULL,
[CancelBy] [int] NULL,
[ClassStartDate] [smalldatetime] NULL,
[ClassEndDate] [smalldatetime] NULL,
[CourseLocation] [int] NULL,
[EnrollNotes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approved] [bit] NULL,
[ApprovedBy] [int] NULL,
[ApprovalDate] [smalldatetime] NULL,
CONSTRAINT [PK_tblEnrollReg] PRIMARY KEY CLUSTERED
(
[EnrollID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [NHSMS]
GO
ALTER TABLE [dbo].[tblEnrollReg] WITH CHECK ADD CONSTRAINT [FK_tblEnrollReg_tblClassSchedule] FOREIGN KEY([ECourseNumber])
REFERENCES [dbo].[tblClassSchedule] ([ClassInstID])
GO
ALTER TABLE [dbo].[tblEnrollReg] WITH CHECK ADD CONSTRAINT [FK_tblEnrollReg_tblStudent] FOREIGN KEY([EStudentNum])
REFERENCES [dbo].[tblStudent] ([StuRecID])

|||

Well, first off you didn't include the the table in question, but in this table, the key of the table you are relating too is ClassInstId, not CourseNumber. CourseNumber is nullable. So it would be better if the

dbo.tblEnrollRegistrations table had the ClastInstId, rather than the CourseNumber.

Second, I would suggest you don't do this in a trigger and just count them as needed, unless there are few inserts. This way ends up locking way more things than needed, usually. You will have to implement UPDATE and DELETE triggers also

But consider this example, with code modified from Umachandar's post:

CREATE TABLE [dbo].[tblClassSchedule](
[ClassInstID] [int] IDENTITY(1,1) NOT NULL,
[CourseNumber] [int] NOT NULL UNIQUE,
EnrollNumber int default 0
)
go
Create table dbo.tblEnrollRegistrations
(
enrollRegistrationId int identity primary key,
CourseNumber INT NOT NULL
)
go
create Trigger increaseEnrollments
on dbo.tblEnrollRegistrations
After Insert As
Begin
set nocount on;

update dbo.tblClassSchedule
set EnrollNumber = EnrollNumber +
(select count(*)
from inserted as i
where i.CourseNumber =
dbo.tblClassSchedule.CourseNumber);

End
go

insert into [tblClassSchedule](CourseNumber)
select 100
union
select 101
go
insert into dbo.tblEnrollRegistrations (CourseNumber)
values (100)

select *
from [dbo].[tblClassSchedule]
go

ClassInstID CourseNumber EnrollNumber
--
1 100 1
2 101 0

insert into dbo.tblEnrollRegistrations (CourseNumber)
select 100
union all
select 100
union all
select 100
union all
select 101
union all
select 101

select *
from [dbo].[tblClassSchedule]
go

ClassInstID CourseNumber EnrollNumber
--
1 100 4
2 101 2

Works as expected.

After Insert Trigger Issue

Trying to create a trigger that will based on the recorded inserted into an enrollment table will update a schedule table.

My syntax is as listed:

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

Where dbo.tblClassSchedule.CourseNumber = inserted.EnrollCourseNumber

End

I keep getting msg 4101 'the multi-part identifier could not be bound'

*** I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions***

Try something like this:

Code Snippet

CREATE TRIGGER IncreaseEnrollments

ON dbo.tblEnrollRegistrations

AFTER INSERT

IF @.@.ROWCOUNT = 0

RETURN

BEGIN

SET NOCOUNT ON;

UPDATE dbo.tblClassSchedule

SET EnrollNumber = ( EnrollNumber + 1 )

FROM dbo.tblClassSchedule c

JOIN inserted i

ON c.CourseNumber = i.EnrollCourseNumber

END

I hope you are all creating a trigger to decrease the EnrollNumber upon a registration cancellation.

|||

The following queries also work,

Code Snippet

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

Wheredbo.tblClassSchedule.CourseNumber in (select EnrollCourseNumber from inserted)

End

--or

Code Snippet

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

WhereExists (select 1 from inserted as I Where i.EnrollCourseNumber = tblClassSchedule.CourseNumber)

End

|||

Can you have multiple course numbers in the registrations table? If so, none of the posted solutions will produce the correct results. You need to do the following:

Code Snippet

update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + (select count(*) from inserted as i

where i.EnrollCourseNumber = dbo.tblClassSchedule.EnrollCourseNumber);

The solutions with joins or exists (IN approach is same as EXISTS) will not count duplicate course numbers - for example, if there are more than one registrations per course which is likely.

|||

Nathan,

From your previous message

My scenario is listed below, with some sample code suggested. I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions

, please let us know exactly what you are "attempting to accomplish".

If you don't give any feedback, we can't tune our suggestions to help you find a solution.

|||

thx

|||

For none of the suggestions to be working properly, there must be something about the tables and/or data that hasn't been disclosed and that would be helpful to find a working solution.

Please post the table DDL for ClassSchedule and EnrollRegistrations, and a few rows of sample data in the form of INSERT statements (see this link for ideas).

|||

Here Are The Table Structures. Pretty Much I am Working With Little Data Right Now, Because It Hasn't Been Rolled out yet ...

tblClassSchedule

tblEnrollReg

|||

dbo.tblEnrollRegistrations table had the ClastInstId, rather than the CourseNumber.

But consider this example, with code modified from Umachandar's post:

update dbo.tblClassSchedule
set EnrollNumber = EnrollNumber +
(select count(*)
from inserted as i
where i.CourseNumber =
dbo.tblClassSchedule.CourseNumber);

End
go

insert into [tblClassSchedule](CourseNumber)
select 100
union
select 101
go
insert into dbo.tblEnrollRegistrations (CourseNumber)
values (100)

select *
from [dbo].[tblClassSchedule]
go

ClassInstID CourseNumber EnrollNumber
--
1 100 1
2 101 0

insert into dbo.tblEnrollRegistrations (CourseNumber)
select 100
union all
select 100
union all
select 100
union all
select 101
union all
select 101

select *
from [dbo].[tblClassSchedule]
go

ClassInstID CourseNumber EnrollNumber
--
1 100 4
2 101 2

Works as expected.

Sunday, February 12, 2012

After an insert, how do I get the primary key of the new row?

I am using C# and ADO.NET

After executing an INSERT, I would like to retrieve the primary key of the last row inserted. I've tried running SELECT @.@.IDENTITY in a query, but I get an OleDbException with the message: {"Syntax error. in query expression 'SELECT @.@.IDENTITY'."}. does anyone know what to do?Hi,

have a look here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconretrievingidentityorautonumbervalues.asp

If you are on SQL2k5 you can use the new OUPUT parameter and put the information back via this technology.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

You can use DataTables and DataAdapter to make insertion in the database after the insertion into the database the DataAdapter retrive the values from database for automatically.

You have to use CommandBuilder for this purpose.
Make changes to the DataTable and then run UpdateDataBase method on that table of the DataAdaper..check MSDN for details.

Or you can create Stord procedure to Insert in DB after inseration it will get the PK and will return it for you.

Just Select Max(pk_id) From Table1

|||Akbar,

Select Max(pk_id) seems like a simple way to do this. In fact, I feel stupid for not thinking of it. One followup question to this method: Does this method fail if there are multiple writers writing to this database? This fails if someone else writes to the database before you send your second query, correct?|||The recommended ways of doing that is by using scope_identity()|||The aggregate MAX is a function that was used in the old days to get the highest / recent value. Its not practical / preferable / suggested / recommended / whatever in days of writing with multiple users to a database. Like Joyei said, I would rather use the SCOPE_IDENTITY() approach.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Someone asked me the same thing today and after a couple of hours I've came up with this (the code is in VB but I will try to explain the concept as well as I can):

I'll use a sample table from a SQL Server (I work with an SQL 2000 right now). The table is called _FluxModels and has two fields FluxModelID (an identity field) and FluxModel (a varchar (50) field).

In order to update the table I use a SqlConnection, a SqlDataAdapter, a DataTable and a DataRow.

Instead of generating the InsertCommand with a CommandBuilder, I declare a separate SqlCommand and use this for getting the CommandText and Parameters.

Then I pass the CommandText and the Parameters to the InsertCommand of the DataAdapter.

I added a new parameter "@.ID" (you can use any name you like) of type SqlDbType.Int.

I added this text to the CommandText of the InsertCommand : select @.ID=SCOPE_IDENTITY()

I set the UpdatedRowSource property of the InsertCommand to UpdateRowSource.OutputParameters (actually it works without this setting).

Now, whenever I add a new record, I have the FluxModelID returned in the @.ID parameter of the InsertCommand of the SqlDataAdapter.

Maybe this is not the best way for doing this but it works.

Here is the code I used:

Dim cn As New SqlConnection("Data Source=[YOUR SQL SERVER];Initial Catalog=[YOUR DATABASE NAME];Integrated Security=True")

Dim da As New SqlDataAdapter("Select * from _FluxModels", cn)

Dim db As New SqlCommandBuilder(da)

Dim dt As New DataTable

Dim drr() As DataRow 'array of DataRows used for updating

Dim dr As DataRow

Dim NewCmd As SqlCommand

Dim i As Integer

Dim p As SqlParameter

cn.Open()

'Use the NewCmd instead of da.InsertCommand

NewCmd = db.GetInsertCommand

'Initialize the da.InserCommand as a new SqlCommand

da.InsertCommand = New SqlCommand

'Pass the parameters from the generated command

For i = 0 To NewCmd.Parameters.Count - 1

p = New SqlParameter

p.ParameterName = NewCmd.Parameters(i).ParameterName

p.SourceColumn = NewCmd.Parameters(i).SourceColumn

p.Direction = NewCmd.Parameters(i).Direction

p.DbType = NewCmd.Parameters(i).DbType

p.Value = NewCmd.Parameters(i).Value

da.InsertCommand.Parameters.Add(p)

'Pass the connection to the InsertCommand

da.InsertCommand.Connection = da.SelectCommand.Connection

'and the Commandtext

da.InsertCommand.CommandText = NewCmd.CommandText

'modify the CommandText

da.InsertCommand.CommandText = da.InsertCommand.CommandText & " select @.ID=SCOPE_IDENTITY()"

'add the parameter for the identity

da.InsertCommand.Parameters.Add("@.ID", SqlDbType.Int, 4, "")

'set the parameter direction

da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Direction = ParameterDirection.Output

'works without the next line

'da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters

'Fill the DataTable

da.Fill(dt)

'Add the new record

dr = dt.NewRow

dr("FluxModel") = "BBB"

dt.Rows.Add(dr)

'Use an array of 1 DataRow to update the database

ReDim drr(0)

drr(0) = dr

da.Update(drr)

'Test if the parameter contains a valid value

If Not da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value Is System.DBNull.Value Then

'Display the identity field of the new record

MsgBox(da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value)

End If

'Close the connection to the database

cn.Close()

I hope this will help you.