autonumber

[mary555]

i am using asp with vbscript and sql server.
i have a field in my database that is an autonumber (increments by one but is numeric). I need my web form to work so that i can insert a record from a form (which i know how to do) but i need to retrieve the ID (autonumber) from the record I just inserted. I've tried using stored procedures and cna't get that to work cuz im not too familiar with them and i've tried other things but can't seem to get it. does anyone have any suggestions?

 

[emozley]

Try this:

http://www.adopenstatic.com/experiments/fastestautonumber.asp

Ed

 

[mary555]

its not in accesss...do u know if it makes a difference

 

[eyeswideclosed]

select @@identity from table

 

[emozley]

Not sure - one approach which should work for all platforms is as follows:

1. Add the record
2. SELECT * FROM Records ORDER BY RecordID DESC
3. LastestRecord=rs("RecordID")
4. URL="nextpage.asp?RecordID=" & LatestRecrd
5. Response.Redirect(URL)

This may be a very inefficient way of doing it though and I'm not sure what would happen if you had lots of people using the database at the same time.

Sorry not to be able to offer anything a bit more concrete!

 

[gmmastros]

If you are using SQL Server, you will not want to use @@Identity because there are problems with it. Your basic stored procedure would look like....

Create Procedure InsertRecord
    @Field1 Integer, 
    @Field2 VarChar(10)
As
SET NOCOUNT ON

Insert Into TableName(Field1, Field2)
Values (@Field1, @Field2)

Select Scope_Identity() As AutoNumberIdField

With SQL Server, Scope_Identity() is better than @@Identity because you could have an insert trigger on the table that automatically inserts a record in to another table that also has an identity field. @@Identity would return the ID number of the secondary table instead of the one you really wanted.

Even if you are not using triggers, you should get in to the habit of using Scope_Identity() instead of @@Identity.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mary555]

george,
i am actually using this to insert a record:

Dim insertRS
Set SQLStmt = Server.CreateObject("ADODB.Command")
SQLStmt.CommandText = "Insert into table...................."
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
Set insertRS = Server.CreateObject ("ADODB.Recordset")
insertRS.Open SQLStmt


so woudl the code for the stored procedure be different?

 

[DotNetGnat]

try something like this:

<%
Set RS = Server.CreateObject("ADODB.RecordSet")
' Open the table
RS.Open "table_name", connection_name, adOpenKeySet, adLockPessimistic, adCmdTable
' Add a new record
RS.AddNew
RS("field_name") = request.form("field_name")

' Update the record
RS.update
' Retrive the ID
unique_ID=RS("ID")
' Close the RecordSet
RS.Close
Set RS = Nothing
%>

-DNG

 

[gmmastros]

Yes. The code would be different.

After creating the stored procedure, you could simply call it like so...

Set insertRS = Server.CreateObject("ADODB.Recordset")
insertRS.Open("InsertRecord " & Field1 & ",'" & Field2 & "'")

Then, check the recordset object. There should be 1 field with 1 record, the value of which is the identity id that was inserted in to the table.

You should not use the method I describe. Instead, you should use the command object because it protects you from SQL Injection attacks.

Truth is... I'm not yet a 'good' ASP developer. My prior experience is VB and SQL Server. I've been lurking around this forum so that I can pick up tips myself. The only reason I spoke up in the first place is because of the ill-advised use of @@Identity. (No offense to hejamana).

If you are having trouble with using a command object, others around here can help more than I. I can tell you this... You should start using stored procedures. Your database will become faster, your app will become faster, and it'll be easier to maintain.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

DNG,

Wouldn't that pull all the records from the table, with potential performance problems (if the table is large)?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[damber]

I would recommend Georges solution. Although what DNG has recommended will work, it is not the best way to approach it.

You should run pretty much all interactions with the database via a stored procedure - as George mentions, much more efficient and easier to maintain, thus a better design.



A smile is worth a thousand kind words. So smile, it's easy!

 

[DotNetGnat]

George,

Yes there would be a performance issue with the method i suggested...i was just trying to make it simple for the OP.

definitely stored procedures are very efficient as damber and you guys pointed out...

and not to mention the fact that GetRows() method is more efficient than Open method...

-DNG

 

[mary555]

george, is there a syntax error in this:
insertRS.Open("InsertRecord " & Field1 & ",'" & Field2 & "'")

are there supposed to be quotes around each field? Also, for this, is field1 and field2 referring to the table columns or the values to be put into the table, from my form.

 

[mary555]

and george if i use your method, i still put this:

Create Procedure InsertRecord
@Field1 Integer,
@Field2 VarChar(10)
As
SET NOCOUNT ON

Insert Into TableName(Field1, Field2)
Values (@Field1, @Field2)

Select Scope_Identity() As AutoNumberIdField



right? and then i put that 2nd 2 lines of code u put?

 

[gmmastros]

The Field1 and Field2 variables represent form variables (from your asp page) that you want to insert into the table.

I put apostrophes around field2 because in my example, field1 was integer and field2 was varchar. In sql server, you don't put apostrophes around numbers (int, tinyint, bigint, numeric, decimal, float, real). You do use apostrophes for other data types like varchar, nvarchar, char, nchar, text, ntext, and dates.

Ultimately, you want your query to be valid. It's best to test queries directly in Query Analyzer (QA). Once you have it working there, then use it in ASP. Without testing in QA, you will have a harder time debugging the whole process.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mary555]

thanks george
ok so in my asp page i got:

OBJdbConnection.Execute "exec InsertRecord"

in my stored procedure i have:

CREATE Procedure InsertRecord
@Produe VarChar(10),
@RivName VarChar(10),
@Plan VarChar(10),
@Gener VarChar(10),
@Lost VarChar(10),
@Reason VarChar(10),
@Description VarChar(10),
@Type VarChar(10),
@StartDate DateTime,
@StartTime VarChar(10),
@EndDate DateTime,
@EndTime VarChar(10),
@OnHours VarChar(10),
@OffHours VarChar(10),
@OnMinutes VarChar(10),
@OffMinutes VarChar(10),
@PC VarChar(10),
@Riv VarChar(10),
@Pl VarChar(10),
@Ge VarChar(10),
@Lo VarChar(10),
@Ca VarChar(10),
@Am VarChar(10),
@Ty VarChar(10),
@SD DateTime,
@ST VarChar(10),
@ED DateTime,
@ET VarChar(10),
@OPH VarChar(10),
@OfPH VarChar(10),
@OPM VarChar(10),
@OfPM VarChar(10)

As
SET NOCOUNT ON

Insert into Out (Produce, RivName, Plan,Gener,Lost,Reason,Descriptio,Type,StartDate, StartTime,EndDate, EndTime,OnHours,OffHours,OnMinutes,OffMinutes ) values ('"&PC&"','"&Riv&"','"&Pl&"','"&Ge&"', '"&Lo&"', '"&Ca&"', '"&Am&"', '"&Ty&"','"&SD&"','"&ST&"','"&ED&"','"&ET&"','"&OPH&"','"&OfPH&"','"&OPM&"','"&OfPM&"')

Select Scope_Identity() As OutReferenceID
GO



In my web form I have:
<%
PC = request.querystring("Produce")
Riv =request.querystring("Riv")
Pl=request.querystring("Plan")
Ge=request.querystring("Gener")
Lo=request.querystring("Lost")
Ty=request.querystring("Type1")
Am=request.querystring("Amp")
ST=request.querystring("STime")
ET=request.querystring("ETime")
SD=request.querystring("SDate")
ED=request.querystring("EDate")
OPH=request.querystring("OHours")
OfPH=request.querystring("OfHours")
OPM=request.querystring("OMin")
OfPM=request.querystring("OMin")
Ca=request.querystring("Cause")
Ev=request.querystring("Event")
%>

Do u kinda ssee what I am trying to do? I am now getting the error that 'InsertRecord' expects parameter '@Produce', which was not supplied.


Im a little confused...

 

[gmmastros]

Yeah, I understand why you are confused. You're dealing with 2 different technologies and therefore 2 different programming languages.

In your ASP Page, you should have...

OBJdbConnection.Execute "exec InsertRecord '" & PC & "','" & Riv & "','" & Pl & "','" & Ge & "','" & Lo & "','" & [!]etc...[/!]

In your procedure, you should have....

Insert 
into   Out 
       (Produce, RivName, Plan, Gener, Lost, 
       Reason, Descriptio, Type, StartDate, 
       StartTime,  EndDate, EndTime, OnHours, 
       OffHours, OnMinutes, OffMinutes ) 
values (@PC, @Riv, @Pl,@Ge, @Lo, @Ca, @Am, 
       @Ty, @SD, @ST, @ED, @ET, @OPH, @OfPH,
       @OPM,@OfPM)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh, my mistake.

In the ASP Page, you don't want to execute the stored procedure because you are returning a value from it. You want to open it in the same way that you open sql staments for selecting data from the database.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mary555]

so in my asp page i want to have
SQLStmt.CommandText="Insert Into......"
Set insertRS = Server.CreateObject ("ADODB.Recordset")
insertRS.Open SQLStmt

and in my stored procedure JUST

Insert
into Out
(Produce, RivName, Plan, Gener, Lost,
Reason, Descriptio, Type, StartDate,
StartTime, EndDate, EndTime, OnHours,
OffHours, OnMinutes, OffMinutes )
values (@PC, @Riv, @Pl,@Ge, @Lo, @Ca, @Am,
@Ty, @SD, @ST, @ED, @ET, @OPH, @OfPH,
@OPM,@OfPM)



do i need any variables at the beginning of the stored procedure?

 

[gmmastros]

No. I left some things out so I could show the important parts.

Your ASP page should look something like... [small]remember that I as still learning asp[/small].

<%
PC = request.querystring("Produce")
Riv =request.querystring("Riv")
Pl=request.querystring("Plan")
Ge=request.querystring("Gener")
Lo=request.querystring("Lost")
Ty=request.querystring("Type1")
Am=request.querystring("Amp")
ST=request.querystring("STime")
ET=request.querystring("ETime")
SD=request.querystring("SDate")
ED=request.querystring("EDate")
OPH=request.querystring("OHours")
OfPH=request.querystring("OfHours")
OPM=request.querystring("OMin")
OfPM=request.querystring("OMin")
Ca=request.querystring("Cause")
Ev=request.querystring("Event")

SQLStmt.CommandText="InsertRecord '" & PC & "','" & Riv & "','" & Pl & "','" & Ge & "','" & Lo & "','" & [!]etc...[/!]

 Set insertRS = Server.CreateObject ("ADODB.Recordset")
 insertRS.Open SQLStmt

 OutReferenceId = insertRS("OutReferenceId")

%>

And the Stored Procedure....

CREATE Procedure InsertRecord
    @Produe VarChar(10),
    @RivName VarChar(10),
    @Plan VarChar(10),
    @Gener VarChar(10), 
    @Lost VarChar(10),
@Reason VarChar(10),
@Description VarChar(10),
@Type VarChar(10),
@StartDate DateTime,
@StartTime VarChar(10),
@EndDate DateTime,
@EndTime VarChar(10),
@OnHours VarChar(10),
@OffHours VarChar(10),
@OnMinutes VarChar(10),
@OffMinutes VarChar(10),
@PC VarChar(10),
@Riv VarChar(10),
@Pl VarChar(10),
@Ge VarChar(10),
@Lo VarChar(10),
@Ca VarChar(10),
@Am VarChar(10),
@Ty VarChar(10),
@SD DateTime,
@ST VarChar(10),
@ED DateTime,
@ET VarChar(10),
@OPH VarChar(10),
@OfPH VarChar(10),
@OPM VarChar(10),
@OfPM VarChar(10)

As
SET NOCOUNT ON

Insert 
into   Out 
       (Produce, RivName, Plan, Gener, Lost, 
       Reason, Descriptio, Type, StartDate, 
       StartTime,  EndDate, EndTime, OnHours, 
       OffHours, OnMinutes, OffMinutes ) 
values (@PC, @Riv, @Pl,@Ge, @Lo, @Ca, @Am, 
       @Ty, @SD, @ST, @ED, @ET, @OPH, @OfPH,
       @OPM,@OfPM)

Select Scope_Identity() As OutReferenceID

Of course, you'll need to modify the stored procedure before you can call it from your ASP page.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom