SQL Server - Auto increment primary key column

[giandeo]

Hello Dr. B
Your solution is amazing. It works.......... .Wowh. Thank you so much.

I still have one problem.
I am using the autonumber of the access database as primary key. When I imported the data in MS SQL Server, the autonumber got converted into integer.

I do not know how to go to the bottom of the database and increment that field while adding data.

Could you please give your valuable suggestions

Thanks
Giandeo


EDIT: Thread split from this one. Dr B

[DrBunchman]

For an auto-incrementing primary key you need to set the field as an IDENTITY column but unfortunately it is not possible to do this with SQL script once your column has already been built.

However, I think I'm right in saying that you can set an IDENTITY column in SQL Server Management Studio (if you have it?). If you right click on your column and select properties then you should see the option to change it.

Let me know how you get on.

Dr B

[giandeo]

For an auto-incrementing primary key you need to set the field as an IDENTITY column but unfortunately it is not possible to do this with SQL script once your column has already been built.

However, I think I'm right in saying that you can set an IDENTITY column in SQL Server Management Studio (if you have it?). If you right click on your column and select properties then you should see the option to change it.

Let me know how you get on.

Dr B

Hello Dr B

I have tried the IDENTITY setting and it works fine

Thank you so much.

Now, the problem I am facing is in retrieving the IDENTITY field once it has been saved in the table.

I am adding a record in one table and at the same time I am trying to keep a backup copy of all additions, deletions and modifications in another table. The record is successfully added in one table but it not being recorded in the backup table solely because SQL Server could not retrieve the IDENTITY field from the table the record has just been saved.

Here is my codes

'declarare variables to access the database
dim adocon, adorst, strSQL

set adocon=server.createobject("ADODB.connection")
adocon.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=yeshti; Integrated Security=SSPI;"

set adorst=server.createobject("ADODB.recordset")
strSQL="SELECT * FROM [" & strchoice & "]"

adorst.CursorType=1
adorst.LockType=3

adorst.open strSQL, adocon

adorst.AddNew
adorst.Fields("make")=(UCase(Request.Form("make")))
adorst.Fields("model")=UCase(strmodel)
adorst.Fields("eng_cap")=strengcap
adorst.Fields("eng_model")=UCase(strengmodel)
adorst.Fields("trans")=UCase(strtrans)
adorst.Fields("chassis_type")=UCase(strchassistype)
adorst.Fields("grade")=strgrade
adorst.Fields("drive")=UCase(strdrive)
adorst.Fields("yr")=UCase(stryr)
adorst.Fields("fuel_type")=UCase(strfueltype)
adorst.Fields("origin")=UCase(strorigin)
adorst.Fields("base_value")=UCase(strbase)
adorst.Fields("fcy")=UCase(strfcy)
adorst.Fields("import")=UCase(strimport)
adorst.Fields("source")=UCase(strsource)
adorst.Fields("alloy_wheel")=UCase(stralloywheel)
adorst.Fields("spoiler")=UCase(strspoiler)
adorst.Fields("active")=UCase(stractive) 
adorst.Fields("username")=strusername
adorst.Fields("action_date")=strdate 
adorst.Fields("action_time")=strtime 
adorst.Fields("status")=UCase(strstatus)
adorst.Fields("IP")=strIp 
adorst.Update


dim theserial
theserial = adorst("serial")

adocon.close
set adocon=nothing

'declarare variables to access the database
dim conn, rst, SQL

set conn=server.createobject("ADODB.connection")
conn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=yeshti; Integrated Security=SSPI;"

set rst=server.createobject("ADODB.recordset")
SQL="SELECT * FROM backupcardata"

rst.CursorType=1
rst.LockType=3

rst.open SQL,conn

rst.AddNew
rst.Fields("serial")=theserial 'retrieve the serial from the saved table'
rst.Fields("make")=UCase(strmake)
rst.Fields("model")=UCase(strmodel)
rst.Fields("eng_cap")=strengcap
rst.Fields("eng_model")=UCase(strengmodel)
rst.Fields("trans")=UCase(strtrans)
rst.Fields("chassis_type")=UCase(strchassistype)
rst.Fields("grade")=strgrade
rst.Fields("drive")=UCase(strdrive)
rst.Fields("yr")=UCase(stryr)
rst.Fields("fuel_type")=UCase(strfueltype)
rst.Fields("origin")=UCase(strorigin)
rst.Fields("base_value")=UCase(strbase)
rst.Fields("fcy")=UCase(strfcy)
rst.Fields("import")=UCase(strimport)
rst.Fields("source")=UCase(strsource)
rst.Fields("alloy_wheel")=UCase(stralloywheel)
rst.Fields("spoiler")=UCase(strspoiler)
rst.Fields("active")=UCase(stractive) 
rst.Fields("username")=strusername 
rst.Fields("action_date")=strdate 
rst.Fields("action_time")=strtime 
rst.Fields("status")=UCase(strstatus)
rst.Fields("IP")=strIp 
rst.Update

conn.close
set conn=nothing

if err<>0 then
 	Response.Write("<h1 align='center'>No update permissions! </h1>")
else 
	Response.Write("<h1 align='center'>Record Sucessfully Created!</h1>")
end if


%>

Please give your suggestions
Thanks


Giandeo

[liz0001]

You could write another quick query to look up the entry that you just made. Use recordset.movel ast to get to the last entry.

Another possibility, If you're doing it in order, is to create a session variable that keeps track of where you are. Just increment it by one each time you add a record to that table.

[DrBunchman]

Giandeo,

Further to Liz' suggestions you should google "@@IDENTITY " for some information about picking up the last inserted record.

Dr B

[giandeo]

You could write another quick query to look up the entry that you just made. Use recordset.movel ast to get to the last entry.

Another possibility, If you're doing it in order, is to create a session variable that keeps track of where you are. Just increment it by one each time you add a record to that table.

Thank you so much for your valuable suggestions. It works!!!!!