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?

 

[mary555]

when i put
Dim insertRS
Set SQLStmt = Server.CreateObject("ADODB.Command")

SQLStmt.CommandText="InsertRecord '" & PC&"','"&Riv&"','"&Pl&"','"&Ge&"', '"&Lo&"', '"&Ca&"', '"&Am&"', '"&Ty&"','"&SD&"','"&ST&"','"&ED&"','"&ET&"','"&OPH&"','"&OfPH&"','"&OPM&"','"&OfPM&"'"
SQLStmt.CommandType = 1
Set insertRS = Server.CreateObject ("ADODB.Recordset")
insertRS.Open SQLStmt

I get the error The connection cannot be used to perform this operation. It is either closed or invalid in this context.


but when i use the insert statement that i used to have, it works perfectly...

 

[DotNetGnat]

did u forget this line:

Set SQLStmt.ActiveConnection = OBJdbConnection

-DNG

 

[mary555]

perfect, i had that before but didn't realize i deleted it.
k now i get this error:
Procedure 'InsertRecord' expects parameter '@PC', which was not supplied.

i need to learn more about stored procedures i think...but do u know what that means?

 

[gmmastros]

The number of parameters in the stored procedure must match the number of parameters that you supply it. Your stored procedure looks like it has more parameters than you are using. Remove the unused parameters.

-George

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

 

[mary555]

hmm..theres the same amount. in my asp page i retrieved a couple extra variables but that doesn't matter. i still get that error...

 

[gmmastros]

Your stored procedure:

CREATE Procedure InsertRecord
[blue]    @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)
[/blue]
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

The parameters for the stored procedure are highlighted in blue. There appears to be 32 of them (unless I miscounted). It appears as though you are only using 16 of the parameters in the insert statement. You are probably sending those 16 parameters to the stored procedure from your ASP code. By removing the parameters you are not using AND sending the parameter you are using, the procedure will probably start working for you.

-George

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

 

[mary555]

oh ok sorry, i thought that i needed to send the variables that i was using as Values as parameters, thanks, my mistake

 

[gmmastros]

Is it working now?

-George

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

 

[CaffeinatedBacon]

I think I have an even easier solution for you... if you have copied your Procedure InsertRecord verbatim and pasted it here, you have misspelled "Produce" as "Produe" in the second line.

Cheers!

 

[gmmastros]

It's actually the first 16 parameters that should be removed.

For example:

The 17th parameter (@PC) is being used in the procedure to insert in to the Out table for field Produce.


-George

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

 

[mary555]

haha thanks, i actually got it to work!!! thanks sooooo much! so now i just need to do what the whole point of this was and that is retrieve that OutReferenceID and put that value on my form.....

 

[gmmastros]

In my post from '25 May 06 14:04' I had...

OutReferenceId = insertRS("OutReferenceId")

Just response.write it, and you should be good to go.

-George

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

 

[mary555]

got it!
<%=insertRS("OutReferenceID")%>'>

george, and everyone, thanks sooooooo much !!!

 

[gmmastros]

You're welcome.

-George

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

 

[CaffeinatedBacon]

Hi George

I hadn't seen where she had resolved this error:

I am now getting the error that 'InsertRecord' expects parameter '@Produce', which was not supplied.

I assumed she had already implemented your stated fix.

Glad you have it sorted out Mary!

-Cheers

 

[Sheco]

What could be better than CaffeinatedBacon ????

 

[gmmastros]

Caffeinated Milk to go with it?

-George

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

 

[CaffeinatedBacon]

...and a nice bowl of Admiral Crunch