Visual Basic :: Identity Column In Sqlserver

Get @@Identity
Hi,

This is my first post here, and I know my question has been asked before, however after reading all of the suggestions, I'm still stuck!

I have written an Add(...) method for inserting a record into SQLServer 2000. The class is in an ActiveX DLL and will be used from an ASP page. All I want to do is to get the @@Identity from the record added, but for some reason, I get the following error: "ADODB.Errors (0x800A0CC1) Item cannot be found in the collection corresponding to the requested name or ordinal."

I know this can be done using a recordset, however I'd rather not if possible. Could anyone correct my VB code below? Thanks!

The stored procedure will be something like this:

CREATE PROCEDURE [dbo].[spProductsInsert]
(
@ProductName nvarchar (50),
@ProductID int OUTPUT
)
AS
INSERT INTO Products
(
ProductName
)
VALUES
(
@ProductName
)
SELECT
@ProductID = @@Identity
GO

...And the VB I have at the moment is as follows:

Code:
Public Function Add(ByVal objProduct As Product) As Integer

' error handling
On Error GoTo ErrorHandler

' for new primary key value
Dim newID As Integer

' connection and command objects (early binding)
Dim objCmd As ADODB.Command
Set objCmd = New ADODB.Command
Dim objCon As ADODB.Connection
Set objCon = New ADODB.Connection

' connect to the database
objCon.ConnectionString = m_ConnectionString
objCon.Open

' set command properties
With objCmd
.CommandText = "spProductsInsert"
.CommandType = adCmdStoredProc
.ActiveConnection = objCon
' RETURN_VALUE for any error output
.Parameters.Append .CreateParameter("RETURN_VALUE", _
adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("@ProductName", _
adVarChar, adParamInput, 50, objProduct.ProductName)
.Parameters.Append .CreateParameter("@ProductID", _
adInteger, adParamOutput, 4)
.Execute , , adExecuteNoRecords

' get the primary key value
newID = CInt(.Parameters("ProductID").Value)
End With

' return the primary key value
Add = newID

' tidy up
objCon.Close
Set objCon = Nothing
Set objCmd = Nothing

Exit Function

ErrorHandler:
Err.Raise objCon.Errors.Item(0).Number, _
objCon.Errors.Item(0).Source, _
objCon.Errors.Item(0).Description
Set objCon = Nothing
Set objCmd = Nothing

End Function

Identity
Hi people! I have a Table (in MSSQL 8.0) wich primary key is an IDENTITY. I want to insert a record in that table and get the identity´s value cause it is a foreign key for several tables i have to insert records to..
Wich is the best wayto do it? I do a SQL query using MAX but i want to know if Is it possible to overlap inserts and get the wrong identity!
Thanks in advanced and sorry my english!
Bye!

Identity
Hi people! i have this:

Code:
Dim Rst as new adodb.recordset, i as Integer
Rst.Open "select top 1 * from Table1", Cndb

For i=0 to Rst.Fields.Count -1
Select Case Rst.Fields(i).Type
Case adNumeric, adCurrency, adDecimal, adDouble, _
adSingle, adBigInt, adBinary, adGUID, adInteger, adSmallInt, _
adUnsignedBigInt, adUnsignedInt, adUnsignedSmallInt, _
adUnsignedTinyInt, adVarBinary

msgbox "this is a number!"

Case adDate, adDBDate, adDBTime, adDBTimeStamp
msgbox "this is date!"

Case adBoolean
msgbox "this is boolean!"

Case Else
msgbox "this is text!"
End Select
next i
This function is to loop through a recordset's fields and print wich type is the field.
I just want to know how to know that a field is identity!
Thanks in advance and sorry my english!

Identity
hi people!
I want to know wich is the best way to retrieve the recently value from a identity field!

Code:
cndb.Execute "INSERT INTO TABLE1 (fName,fSurname) VALUES ('Carol','Vilmar');"
I want it to return me the value contained in an identity field in that table!

Thanks in advance and sorry my english!
(Further information: vb6 & MS SQL SERVER)

Exe Identity
Hey, i need a way to get the unique identity of an exe file. I can't use exe name, cause it can be changed. Is there a way to do this?

Thanks

BCP And Identity
GentleMen/Women: (Hope everyone was able to get some rest
in spite of these haunting images from TV! May this day find u all well!)

Q. When one BCPs a file into a table (SQL SERVER) is the
data entered into the table in the same sequence as it is
in the file

if not, is there a way to do that and possibly set an identity
field on that table so that (after I do the stuff that I need
to do with the data) I can put it back to the file in the
original sequence?

The original file is not sorted in a any specific way...

Thanks in advance...

ADO And @@identity (SQL 7)
Hi all long time no see. I'm using ADO to insert a new record (SQL server 7) using .AddNew().

The question is: How do I get the auto-inserted identity value of that inserted row? I can't use a stored proc because of the nature of the binary data I'm storing - I need to use GetChunk and all that.

I would be very grateful for any help any of you gurus can offer (I'm told flattery will get a man anywhere)

Thanks all,

Toot

Need Identity Help
Hi,

I have one question.i have to tables in sql server in one table i have setup the identity with primary key and in other table i want to use identity values to relashionship i know how to do.in sql server but i dont know how to do in vb6 please can anybody help me.

Quote: Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Hotel Management"
cn.Open

Set rs = New ADODB.Recordset

SqlQry = "CustomerDetails "
With rs
.Open SqlQry, cn, adOpenKeyset, adLockPessimistic, adCmdTable

.AddNew
rs!Name = txtName.Text
rs!LastName = txtLastName.Text
rs!Gender = CmbGender.Text
rs!Address1 = txtAddress1.Text
rs!Address2 = txtAddress2.Text
rs!City = txtCity.Text
rs!State = txtState.Text
rs!Country = txtCountry
rs!Email = txtEmail.Text
rs!phone = txtPhone.Text
rs!IdentityProof = txtIdentity.Text
rs!ReasonVisit = txtReason.Text
rs.Update
txtName.Text = ""
txtLastName.Text = ""
CmbGender.Text = ""
txtAddress1.Text = ""
txtAddress2.Text = ""
txtCity.Text = ""
txtState.Text = ""
txtCountry.Text = ""
txtReason.Text = ""
txtEmail.Text = ""
txtPhone.Text = "'"
txtIdentity.Text = ""

End With

Set rs = New ADODB.Recordset
SqlQry = "RoomDetail "
With rs
.Open SqlQry, cn, adOpenKeyset, adLockPessimistic, adCmdTable

.AddNew

rs!RoomNumber = txtRoomNumber
rs!RoomType = CmbRoomType.Text
rs!BedType = CmbBedType.Text
rs!Advance = txtAdvance.Text
rs!RentPerDay = txtRent.Text
rs!CheckinDate = txtCheckinDate.Text
rs.Update
End With

SQL Identity
Hey guys! I have a very urgent project but I have a hard time solving it. I need your help. Hope you can help me. This is my SQL query...

TRUNCATE Table Delivery
INSERT INTO Delivery(Reference, Notes, RevisionID, Address, Instructions, CustomerID)
SELECT [SalesOrder].[Customer Reference], [SalesOrder].Notes, [SalesOrder].RevisionID, [SalesOrder].[Delivery Address],
[SalesOrder].[Special Instructions], [SalesOrder].[Customer ID]
FROM [SalesOrder] INNER JOIN ([SOLine] INNER JOIN [DN Lines] ON [SOLine].[SO Line ID] = [DN Lines].[SO Line ID])
ON [SalesOrder].[SO ID] = [SOLine].[SO ID] ORDER BY [SOLine].[SO Line ID], [DN Lines].[DN Seq], [SOLine].[SO ID]

I need to get the values of the Identity Column in the Delivery Table called "DeliveryID" and put it in the "DeliveryID" field of [DN Lines] Table. How do I do this without going thru the values of the Delivery table row by row and save it to [DN Lines]? Note: There are 20,000 records in the Delivery table that's why I used the above statements inorder to speed up the process.
Anyone here who's kind enough to help me? Your help will be pretty much appreciated. Thanks.

Identity
How to set the table to allow identiy insert on
eg "set identity_insert table on" using
ADO before calling the addnew method.

thanks.

Help!! {Identity Insert}
I keep getting this error: "Cannot insert explicit value into table when IDENTITY_INSERT set to off. I think I'm forgetting to do something here but I can't put my finger on it. I get the error on Submit, here's my code:

Code:
Private Sub cmdSubmit_Click()

'check drop down box

If DropDownBox.ItemData _
(DropDownBox.ListIndex) = 0 Then

MsgBox _
"Select ", _
vbOKOnly

GoTo Subexit:
End If

' Insert statement

strSQLUpdate = _
"up_Update " & _
intSelectionID & "," & _
DropDownBox.ItemData _
(DropDownBox.ListIndex) & "," & _
intUsedID

' Insert the Transaction

Set rsSubmit = global_Conn.Execute(strSQLUpdate) **<it highlights this.

' Clear and form1 grid

PopulateGrid _
strSQLGrid, _
"column 1:", _
"intMainID", _
"strMainName", _
"No Records", _
form1.Grid

cmdSubmit.Enabled = False

DropDownBox.ListIndex = 0
DropDownBox.Enabled = False

' Close the Form

Unload Me

Subexit:
End Sub

Identity From Name From Class
i have a class with some methods, and form that call this method.

how can i know from the class, which form call this method ?

thanks,

How To Get The Identity Primary Key Value?
I have a table (tblArticleGroup) with a automatic increasing primary key (ArticleGroupID). I'm using a datatable to insert a new row in the table. Then I want to know the generated ArticleGroupID, but I haven't found a solution.

I'm using SQL Server 7.0

I checked on google and found a good example in C##. I translated it to vb.net (see below), but it doesn't work, because ArticleGroupID = 0 all the time. Can anyone see what's wrong with the code:

Dim conn As New SqlConnection(myConnectionString)
Dim da As New SqlClient.SqlDataAdapter("Select * from tblArticleGroup", conn)

da.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim builder As New SqlClient.SqlCommandBuilder(da)
Dim Command As SqlCommand
Command = builder.GetInsertCommand()
Command.CommandText += " ; select @@IDENTITY as ArticleGroupID"
Dim dt As New DataTable()
da.FillSchema(dt, SchemaType.Source)

conn.Open()
dt.Clear()

Dim row As DataRow
row = dt.NewRow()
row("ChargeCodeSearchStr") = ""
row("Name") = "Testgroup"
row("IsTemporary") = True
dt.Rows.Add(row)

da.Update(dt)
dt.AcceptChanges()
MsgBox(row("ArticleGroupID")) 'This returns "0" all the time!!!
conn.Close()

Insert Identity
I have the following code, which when pasted into SQL Server Enterprise manager works fine.

SET IDENTITY_INSERT [Category] ON
INSERT INTO Category
(CategoryRef, Category)
VALUES (13, 'Chalet')

It forces in the autonumber identity of 13 into my table. great stuff. However, in my VB Code, some of the tables that I am pointing the code to, works fine too via a .EXECUTE command, but there are a couple that just won't let me do it and they come back with

identity insert is already ON, cannot set blah blah blah.

Any ideas of getting around this? (I have checked my spelling of table/field names etc.. and they're ok.)

How To Retrieve Identity?
Hi,

I have a identity column,

how i can get the value of this column after insert?

using visual basic and sql server
Thanks

Next Identity On A SQL TABLE
I can get the identity on a table with @@identity but i have to save the register first. But if i want to get that number before i save the information?

The Select @@identity
I had never used this before.
I want to get the next identity from a Table. Is this correct?
How do i have the result?

Function GetIdentityFromTable(sTable) As Integer
sqlstring = "Select @@Identity From " & sTable
Dim RsIdentity As ADODB.Recordset
Set RsIdentity = New ADODB.Recordset
RsIdentity.Open sqlstring, cn
End Function

Identity Field
When You use identity field (sql server):
a) If the Number is generate when you say: ADDNEW to the Table, how can i work with that value if i need before?

I mean, i need that value because has to be saved on another table. This mean that i have to save the HEADER first and then work with the detail?

Getting Identity Value In Access
I have a table in a MS Access database. The table has a primary column which is AutoNumber type. When I insert a row into this table the primary value will be added automatically to this row. I know in SQL Server, I can use 'Select @@identity' to get this primary value. Does anyone know how to do it in Access?

Thanks for you help,

ADO And Identity Columns
Hi all,

I'm working on a database in SQL Server, and I want to set the identity field in code for certain columns. I can't seem to find how to do this using an ADOX object. Is there a way to do this using ADOX (and if so, how?), or do I need to use a different data access method to do this?

Thanks!

@@IDENTITY Equivalent In VBA
Hello,

Does anyone know of a VBA function that returns the id of a NEW record added in a recordset as soon as it has been added ?

It can be done using @@IDENTITY in a 'Stored Procedure' but i need to use it in VBA.

Thanks

Identity On MS Access
My sql script look like this
create table ENVIO
(
    ID           Integer                 not null,
    CLAVE_REM    Integer                 not null,
    CLAVE_DES    Integer                 not null,
    FECHA        DateTime                not null    ,
    GUIA         Integer                 null    ,
    AUTORIZO     Text(30)                not null,
    DEPTO        Text(30)                not null    ,
    PAGA         Text(30)                not null    ,
    DESCRIPCION  Memo                    null
);

I need the field ID be autonumber type, I know that SQL has IDENTITY but in MS Access the only thing that I get is an error.

create table ENVIO
(
    ID           Integer     IDENTITY    not null,
    CLAVE_REM    Integer                 not null,
    CLAVE_DES    Integer                 not null,
    FECHA        DateTime                not null    ,
    GUIA         Integer                 null    ,
    AUTORIZO     Text(30)                not null,
    DEPTO        Text(30)                not null    ,
    PAGA         Text(30)                not null    ,
    DESCRIPCION  Memo                    null
);

Does any one know how to create a Auntonumber field with a SQL script???

Can Anyone Explain How To Use @@IDENTITY?????
I've spent days trying to find the answer to a simple question and after reading a gazillion different answers, I'm totally confused.

I'm using a form to add records to an Access 2002 database using an ADODB connection. I was able to add records to the database until I added a second table to my database (linked to main which uses an autonumber as the primary key). I realize that I need to get the primary key from table1 and (somehow) insert it into table2. That's where I get lost. :-( I've tried using the strSQL to grab the autonumber of the just added record from table1 and in the process I think I've seen just about every error message conceivable.

Relevant code:
Dim Conn

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath ("/mydatabase connection.mdb")

Conn.Open

Set RS = Server.CreateObject("ADODB.RecordSet")

Conn.BeginTrans

RS.LockType = adLockOptimistic
RS.CursorType = adOpenDynamic
RS.CursorType = adOpenKeyset

'Open Client Table.

RS.Open "SELECT * FROM Client", Conn

RS.AddNew

RS("Title") = Request.Form("title")
RS("FirstName") = Request.Form("fname")
RS("Initial") = Request.Form("initial")
RS("LastName") = Request.Form("lname")
RS("Address1") = Request.Form("address1")
RS("Address2") = Request.Form("address2")
RS("City") = Request.Form("city")
RS("Prov") = Request.Form("prov")
RS("PCode") = Request.Form("pcode")
RS("CountryName") = Request.Form("country") 'This is populated from a separate CountryTable in the same database -- this one I did figure out
RS("E-mail") = Request.Form("email")
RS("Phone") = Request.Form("phone")
RS("Format") = Request.Form("format")
RS("EnrollDate") = Date()
RS("ExpiryDate") = DateAdd("m", 6, Date)

RS.Update
RS.MoveLast

RS.Close 'This worked to here as a single table

'Open Business Table.

RS.Open "SELECT * FROM Business", Conn

RS.AddNew

RS("BusName") = Request.Form("busname")
RS("BusPhone") = Request.Form("busphone")
RS("Ext") = Request.Form("ext")
RS("Industry") = Request.Form("industry")
RS("EmpNum") = Request.Form("empnum")
RS("Email") = Request.Form("email")
RS("Agency") = Request.Form("agency")
RS("WebPage") = Request.Form("webpage")
RS("Comments") = Request.Form("notes")

RS.Update 'This is where I get the error: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

RS.MoveLast

RS.Close

Conn.CommitTrans

I'm hoping someone can tell me (in plain English) just how I go about getting the autonumber from my justadded record and then how I use it to add records to my second table.

I'm about ready to give up and stick everything into a single table (not a great idea I know -- but it would work)

Thanks in advance for your patience. This 'grannie' appreciates all the help she can get (and they say they can't teach an old dog new tricks lol -- I'm betting you can point me in the right direction)

Kasperkatz

The Identity Value Is Not Being Returned!! PLZ HELP!
Hi All,

I have an SQL database with a table that has an integer field set as an identity (increment one).

I do with following:

Dim rsTable as ADODB.Recordset
dim iNewID as Integer

Set rsTable = new ADODB.Recordset
rsTable.open "TABLE NAME",cnData,Dynamic,Pessamistic

rsTable.Addnew
'Fill in fields
rsTable.Update

iNewID = rsTable("EQ_ID") 'This is the Int set as an identity

rsTable.close

The value in the iNewID should the be the automatic new number but it is blank.

Any ideas plz.

Thanx again for your help with my learning curve from ACCESS to SQL.

Jiggy!

Uniqueidentifier Or IDENTITY
Hi , as we know both uniqueidentifirt and identity is used for unique records .But I dont know the advantage of thoose ones.On which stuations must we use Uniqueidentifier .FOr example I will design a table for members , I have used identity until now but that question has stuck on me .Must I use Uniqueidentifier or continue with identity ...

tHanks ...

Select @@IDENTITY
Hi.
Why this code doesn't work. It stops here Code: Set adoRS = madoCMD.Execute
I've found an example for getting ID of inserting record here: http://www.kamath.com/tutorials/tut007_identity.asp
ERROR: "Character found after end of SQL statement"
Code:
Set adoCon = New ADODB.Connection
adoCon.Provider = "Microsoft.Jet.OLEDB.4.0"
adoCon.ConnectionString = "Data Source=" & App.Path & "objednavky.mdb"
adoCon.CursorLocation = adUseClient
adoCon.Open

strSQL = " INSERT INTO t_Order (OrderNumber,CustomerID) VALUES ('NewOrder'," & CStr(CustomerID) & " ) ; " _
      & " SELECT @@IDENTITY AS NewID"
  Set madoCMD.ActiveConnection = adoCon
  madoCMD.CommandType = adCmdText
  madoCMD.CommandText = strSQL
  Set adoRS = madoCMD.Execute
  mlOrderID = adoRS.NextRecordset.Fields("NewID").Value

Doesn't this provider support multiple SQL commands?
thanks for help.

Edited by - plsanek on 9/5/2006 8:36:23 AM

Use EVB To Get Device Identity
I have an eVB app that must run on a number of Pocket PCs 2003 which will all have the same user ID.
I need to bea able to programmatically identify each PDA when synchronising data with it.
If I go to Start-Settings-System Tab-Asset Viewer it lists (among other things) Identity details such as Asset Tag, etc.

Can anyone tell me how to programmatically access that information. I have tried the registry entry on the device 'HKEY_LOCAL_MACHINE, Ident' with the RegOpenKeyEx API but cannot get it to work.

Any help would be appreciated.

ADO Identity/Autonumber
After inserting a new record in a recordset using

rst.Addnew
rst.....
rst.....
rst.Update

I'd like to get the current identity(sql) or autonumber(access) or autoincrement(anywhere).

After the update, i put
variable = rst!identity_column
i assumed that the pointer of the recordset is still on the record that was added, but this does not work and I'm getting a null value in the variable.

How am i suppose to do this....?

ps.. I want to get the identity because I will insert it to another table...

Thanks

Winsock Socket Identity For PM
I have created a chat server. It has the basic features like LOGIN, LEAVE, JOIN, MESSAGE, etc, but not private messages. This is becuase I do not know how to identifiy a specifc socket for a user to forward the PM to. Can anyone explain how I would implement this, or even further, check out my source code and figure it out there? Thanks.

ADOX And Identity Fields...
Yuck,

I have figured out how to create an Autonumber (Identiy) field for an Access database. You have to set the

colNew.Properties("Autoincrement") = True

However, comma, there doesn't seem to be a similar property when one creates a new SQL Server column. Can anyone help?

Later

Roger Bogh
Yak & Quack Software Incorporated...

Reset Identity Counter
In SQL Server I have a column setup as Identity. Through testing this value has grown to a huge number. I would like to set this back to 1 when I move the database to production. Any ideas on how to do this?

Getting Identity From Insert Statement
VB Code:
mysql = "INSERT INTO workouts (Muscle_Group, Workout_Name, Workout_Desc, Category) VALUES ('" & workoutData(1) & "', '" & workoutData(0) & "', '" & workoutData(10) & "', '" & workoutData(9) & "');select @@identity as 'WorkoutID';"gSet.Open mysqlWorkoutID = gSet!WorkoutIDgSet.Close

VB gives me an error about there being characters at the end of the query - is there another way to get the identity?

I can't use record count because its possible that some of the records may be deleted.

Thanks in advance,

Can I Set Morethan One Identity Within The Same SQL Table
Hi

Is it possible to set more than one identity within the same tbl

Thanks

Caller Line Identity
Here is an interesting question. Is it possible to detect the caller line identity (number) of an incoming call in Visual Basic? If so how can this be accomplished.

Valiadte My Identity For Dialer
hello there,
This is the (validate my identity for dialer) security option for a dialer. this one i want to set to my dialer using vb.

Insert @@Identity Problem
Hi All,

I have a basic insert statement and I wish to get the @@Identity value returned... how

I think it is called the @@identity it's the value of the autonumber in the database of the record you just added?

Thanks

Return Identity Col Value Thru Code
I am using VB6 ADO to add a record to a MSSQL 2000 table that has an Identity field. Once the record is added I need to know the value of that Identity to add a record to another table. This is a multi-user db and I have found instances where the time stamp is the same on two records.
I have read the threads on @@IDENTITY but they all involve using a stored procedure to add the new record. I prefer not to do that.
Is there no way to get the IDENTITY value of a newly added record except through @@IDENTITY or am I missing something.

Returning @@IDENTITY Through Code
Hi:

I have a Stored Procedure that I'm trying to be fully functional by inserting a record and then returning the Seed of that record.

This is what I have:

CODE --> >Stored ProcedureCREATE PROC sp_InsertRecord
(@Test nvarchar (50))
AS
INSERT INTO Test (Test)
VALUES (@Test)

SELECT @@IDENTITY

CommandBuilder And @@IDENTITY Columns
Hi,
I have a table with an identity column. The OleDbCommandBuilder builds an Insert statement, but does not repopulate my ID column with the new value.
I have tried updating the Insert command to have a Select after the Insert and adding a Parameter, but the DataAdapter.Update seems to reset the Insert statement.

Is there any way to get the @@IDENTITY column from the table into a DataSet short of reloading everything (as that will lose my current position)? Selecting @@IDENTITY from mytable in a separate statement returns nothing.

I have a stored procedure to return the new ID of an inserted row, but then DataSet isn't updated.

Any help appreciated.
Thanks!