recommended way to get id of just added record

  • Thread starter Thread starter djc
  • Start date Start date
D

djc

2 tables. The ID field in table1 is primary key in table1 and foriegn key in
table2. In older MS access apps I used something like the following for
handling the need to have the autonumber field automatically generated by
Access to create the next set of records in a related table.
-----------------------------------------------------------
With rst 'table one
.AddNew
.Fields("Name") = UCase(Trim(Me.txtImageName))
.Fields("Sysprepped") = Trim(Me.cboSysprepped)
.Fields("MachineType") = UCase(Trim(Me.txtMachineType))
.Fields("TechID") = UCase(Trim(Me.cboTechID))
.Fields("OSID") = Trim(Me.cboOSID)
.Fields("SPLevel") = Trim(Me.txtSPLevel)
.Fields("DateCreated") = Trim(Me.txtDateCreated)
.Fields("Notes") = Trim(Me.txtNotes)
.Update
ID = .Fields("ImageID") 'here is where I snag the auto ID
number field
.Close
End With

sql = "SELECT * FROM [tblImage-Software] WHERE
[tblImage-Software.ImageID] = 0"
rst.Open sql, options:=adCmdText

i = 0 'reinitialize
With rst 'table two
For i = 0 To lstInstalledSoftware.ListCount - 1 'clear selections
from source lst box
.AddNew
.Fields("ImageID") = ID 'Here is where I use the previously
snagged ID
.Fields("SoftwareID") = lstInstalledSoftware.ItemData(i)
.Update
Next i
.Close
End With
----------------------------------------------------------------------

This part:
..Update
ID = .Fields("ImageID")
..Close

is where I stored the autonumber id field from that record to use later in
this code to enter several records into a seperate related table.

1) can this be done with ado.net in the same way? is so, is it ok
(recommended) to do it this way?
1a) could someone give me a short example using an ado.net dataset?

2) is using the select @@identity query a better way?

I know ado.net is 'disconnected' so I don't know if the method shown in the
sample code will work anymore. I have also never used the @@identity method
and don't know if it is recommended/reliable since several users could be
adding records at the same time... how do you know you got yours? Aside from
really needing answers to 1) and 2) above, I am wondering if the same
applies to sql server identity columns?

any info is appreciated. Thanks.
 
Thank you for the reply.

I must say I am shocked at how complex a task this seems. It should be so
simple, and was so simple in Access. I can only assume the difference is due
to ado.net being 'disconnected'. What I need to do is so common: insert a
record in one table, store the ID that was just used, use that ID to create
related records in a different table.

I am very frustrated and disappointed. With all the power offered by
asp.net/ado.net why do I have to write pages and pages of code for what
should not even be one page worth?

sorry for the rant. I do appreciate the reply. Thank you agian.


Val Mazur said:
Hi,

Check next KB with some examples

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629&Product=adonet

--
Val Mazur
Microsoft MVP


djc said:
2 tables. The ID field in table1 is primary key in table1 and foriegn key
in
table2. In older MS access apps I used something like the following for
handling the need to have the autonumber field automatically generated by
Access to create the next set of records in a related table.
-----------------------------------------------------------
With rst 'table one
.AddNew
.Fields("Name") = UCase(Trim(Me.txtImageName))
.Fields("Sysprepped") = Trim(Me.cboSysprepped)
.Fields("MachineType") = UCase(Trim(Me.txtMachineType))
.Fields("TechID") = UCase(Trim(Me.cboTechID))
.Fields("OSID") = Trim(Me.cboOSID)
.Fields("SPLevel") = Trim(Me.txtSPLevel)
.Fields("DateCreated") = Trim(Me.txtDateCreated)
.Fields("Notes") = Trim(Me.txtNotes)
.Update
ID = .Fields("ImageID") 'here is where I snag the auto ID
number field
.Close
End With

sql = "SELECT * FROM [tblImage-Software] WHERE
[tblImage-Software.ImageID] = 0"
rst.Open sql, options:=adCmdText

i = 0 'reinitialize
With rst 'table two
For i = 0 To lstInstalledSoftware.ListCount - 1 'clear selections
from source lst box
.AddNew
.Fields("ImageID") = ID 'Here is where I use the previously
snagged ID
.Fields("SoftwareID") = lstInstalledSoftware.ItemData(i)
.Update
Next i
.Close
End With
----------------------------------------------------------------------

This part:
.Update
ID = .Fields("ImageID")
.Close

is where I stored the autonumber id field from that record to use later in
this code to enter several records into a seperate related table.

1) can this be done with ado.net in the same way? is so, is it ok
(recommended) to do it this way?
1a) could someone give me a short example using an ado.net dataset?

2) is using the select @@identity query a better way?

I know ado.net is 'disconnected' so I don't know if the method shown in
the
sample code will work anymore. I have also never used the @@identity
method
and don't know if it is recommended/reliable since several users could be
adding records at the same time... how do you know you got yours? Aside
from
really needing answers to 1) and 2) above, I am wondering if the same
applies to sql server identity columns?

any info is appreciated. Thanks.
Click to expand...
Click to expand...
 
djc said:
Thank you for the reply.

I must say I am shocked at how complex a task this seems. It should be so
simple, and was so simple in Access. I can only assume the difference is
due
to ado.net being 'disconnected'.
Click to expand...

Yes.

What I need to do is so common: insert a
record in one table, store the ID that was just used, use that ID to
create
related records in a different table.
Yes.


I am very frustrated and disappointed. With all the power offered by
asp.net/ado.net why do I have to write pages and pages of code for what
should not even be one page worth?
Click to expand...

Pages? Almost all code can be generated by wizards or some other tools.
Other then that you might create methods that deals with this.
And yes, ado.net offers great power.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
sorry for the rant. I do appreciate the reply. Thank you agian.


Val Mazur said:
Hi,

Check next KB with some examples

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629&Product=adonet

--
Val Mazur
Microsoft MVP


djc said:
2 tables. The ID field in table1 is primary key in table1 and foriegn
key
in
table2. In older MS access apps I used something like the following for
handling the need to have the autonumber field automatically generated by
Access to create the next set of records in a related table.
-----------------------------------------------------------
With rst 'table one
.AddNew
.Fields("Name") = UCase(Trim(Me.txtImageName))
.Fields("Sysprepped") = Trim(Me.cboSysprepped)
.Fields("MachineType") = UCase(Trim(Me.txtMachineType))
.Fields("TechID") = UCase(Trim(Me.cboTechID))
.Fields("OSID") = Trim(Me.cboOSID)
.Fields("SPLevel") = Trim(Me.txtSPLevel)
.Fields("DateCreated") = Trim(Me.txtDateCreated)
.Fields("Notes") = Trim(Me.txtNotes)
.Update
ID = .Fields("ImageID") 'here is where I snag the auto ID
number field
.Close
End With

sql = "SELECT * FROM [tblImage-Software] WHERE
[tblImage-Software.ImageID] = 0"
rst.Open sql, options:=adCmdText

i = 0 'reinitialize
With rst 'table two
For i = 0 To lstInstalledSoftware.ListCount - 1 'clear selections
from source lst box
.AddNew
.Fields("ImageID") = ID 'Here is where I use the previously
snagged ID
.Fields("SoftwareID") = lstInstalledSoftware.ItemData(i)
.Update
Next i
.Close
End With
----------------------------------------------------------------------

This part:
.Update
ID = .Fields("ImageID")
.Close

is where I stored the autonumber id field from that record to use later in
this code to enter several records into a seperate related table.

1) can this be done with ado.net in the same way? is so, is it ok
(recommended) to do it this way?
1a) could someone give me a short example using an ado.net dataset?

2) is using the select @@identity query a better way?

I know ado.net is 'disconnected' so I don't know if the method shown in
the
sample code will work anymore. I have also never used the @@identity
method
and don't know if it is recommended/reliable since several users could be
adding records at the same time... how do you know you got yours? Aside
from
really needing answers to 1) and 2) above, I am wondering if the same
applies to sql server identity columns?

any info is appreciated. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
2 tables. The ID field in table1 is primary key in table1 and foriegn key in
table2. In older MS access apps I used something like the following for
handling the need to have the autonumber field automatically generated by
Access to create the next set of records in a related table.
-----------------------------------------------------------
With rst 'table one
.AddNew
.Fields("Name") = UCase(Trim(Me.txtImageName))
.Fields("Sysprepped") = Trim(Me.cboSysprepped)
.Fields("MachineType") = UCase(Trim(Me.txtMachineType))
.Fields("TechID") = UCase(Trim(Me.cboTechID))
.Fields("OSID") = Trim(Me.cboOSID)
.Fields("SPLevel") = Trim(Me.txtSPLevel)
.Fields("DateCreated") = Trim(Me.txtDateCreated)
.Fields("Notes") = Trim(Me.txtNotes)
.Update
ID = .Fields("ImageID") 'here is where I snag the auto ID
number field
.Close
End With

sql = "SELECT * FROM [tblImage-Software] WHERE
[tblImage-Software.ImageID] = 0"
rst.Open sql, options:=adCmdText

i = 0 'reinitialize
With rst 'table two
For i = 0 To lstInstalledSoftware.ListCount - 1 'clear selections
from source lst box
.AddNew
.Fields("ImageID") = ID 'Here is where I use the previously
snagged ID
.Fields("SoftwareID") = lstInstalledSoftware.ItemData(i)
.Update
Next i
.Close
End With
----------------------------------------------------------------------

This part:
..Update
ID = .Fields("ImageID")
..Close

is where I stored the autonumber id field from that record to use later in
this code to enter several records into a seperate related table.

1) can this be done with ado.net in the same way? is so, is it ok
(recommended) to do it this way?
1a) could someone give me a short example using an ado.net dataset?

2) is using the select @@identity query a better way?

I know ado.net is 'disconnected' so I don't know if the method shown in the
sample code will work anymore. I have also never used the @@identity method
and don't know if it is recommended/reliable since several users could be
adding records at the same time... how do you know you got yours? Aside from
really needing answers to 1) and 2) above, I am wondering if the same
applies to sql server identity columns?

any info is appreciated. Thanks.
Click to expand...

User submitted from AEWNET (http://www.aewnet.com/)
 
1) It's a bit rude to post without a name--but lot's of people seem to do
it.
2) This is a frequently asked question so I wrote a whitepaper on it. See
"Handling an @@Identity Crisis" (www.betav.com/articles.htm)

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Guest said:
2 tables. The ID field in table1 is primary key in table1 and foriegn key
in
table2. In older MS access apps I used something like the following for
handling the need to have the autonumber field automatically generated by
Access to create the next set of records in a related table.
-----------------------------------------------------------
With rst 'table one
.AddNew
.Fields("Name") = UCase(Trim(Me.txtImageName))
.Fields("Sysprepped") = Trim(Me.cboSysprepped)
.Fields("MachineType") = UCase(Trim(Me.txtMachineType))
.Fields("TechID") = UCase(Trim(Me.cboTechID))
.Fields("OSID") = Trim(Me.cboOSID)
.Fields("SPLevel") = Trim(Me.txtSPLevel)
.Fields("DateCreated") = Trim(Me.txtDateCreated)
.Fields("Notes") = Trim(Me.txtNotes)
.Update
ID = .Fields("ImageID") 'here is where I snag the auto ID
number field
.Close
End With

sql = "SELECT * FROM [tblImage-Software] WHERE
[tblImage-Software.ImageID] = 0"
rst.Open sql, options:=adCmdText

i = 0 'reinitialize
With rst 'table two
For i = 0 To lstInstalledSoftware.ListCount - 1 'clear
selections
from source lst box
.AddNew
.Fields("ImageID") = ID 'Here is where I use the
previously
snagged ID
.Fields("SoftwareID") = lstInstalledSoftware.ItemData(i)
.Update
Next i
.Close
End With
----------------------------------------------------------------------

This part:
..Update
ID = .Fields("ImageID")
..Close

is where I stored the autonumber id field from that record to use later
in
this code to enter several records into a seperate related table.

1) can this be done with ado.net in the same way? is so, is it ok
(recommended) to do it this way?
1a) could someone give me a short example using an ado.net dataset?

2) is using the select @@identity query a better way?

I know ado.net is 'disconnected' so I don't know if the method shown in
the
sample code will work anymore. I have also never used the @@identity
method
and don't know if it is recommended/reliable since several users could be
adding records at the same time... how do you know you got yours? Aside
from
really needing answers to 1) and 2) above, I am wondering if the same
applies to sql server identity columns?

any info is appreciated. Thanks.
Click to expand...

User submitted from AEWNET (http://www.aewnet.com/)
Click to expand...
 
Back
Top