Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need to return scope_identity value as recordset value ?

Status
Not open for further replies.
lorca

lorca

Technical User
Joined
Dec 20, 2005
Messages
64
Location
GB
Hi

I've got the following code but it does not return the value of the scope_identity in the variable llID. How can i get its value in a variable for use in another sql statement.

thanks
ian

Code: 
SQLstmt = "IF EXISTS(SELECT uniqueid FROM members WHERE uniqueID = '" & _
request("tempid") & "')"
SQLstmt = SQLstmt & "BEGIN "
SQLstmt = SQLstmt & "SELECT 'This record already exists!' "
SQLstmt = SQLstmt & "END ELSE BEGIN "
SQLstmt = SQLstmt & "SELECT 'Record Added' "
SQLstmt = SQLstmt & "Set Nocount on "
SQLstmt = SQLstmt & "INSERT INTO members (cat, BusinessName,BuildingNo,ContactTitle,ContactFirstName,ContactSurname,
Address1,Address2,Address3,Address4,City,County,Postcode,Telephone,Fax,
Email,[URL unfurl="true"]WWW,BusinessDescription,Password,ContactPosition,PairentCompany,[/URL]
DateEst,HeadOfficeTelephone,Keywords1,Keywords2,Keywords3,ExportArea,
NumberOfEmployees, UserName, Turnover, uniqueid)" 
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "'" & objRS8("myID") & "', "
SQLstmt = SQLstmt & "'" & cleansql(BusinessName) & "',"
SQLstmt = SQLstmt & "'" & cleansql(BusinessNo) & "',"
SQLstmt = SQLstmt & "'" & cleansql(ContactTitle) & "',"
SQLstmt = SQLstmt & "'" & cleansql(ContactFirstName) & "',"
SQLstmt = SQLstmt & "'" & cleansql(ContactSurname) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Address1) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Address2) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Address3) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Address4) & "',"
SQLstmt = SQLstmt & "'" & cleansql(City) & "',"
SQLstmt = SQLstmt & "'" & cleansql(County) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Postcode) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Telephone) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Fax) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Email) & "',"
SQLstmt = SQLstmt & "'" & cleansql(WWW) & "',"
SQLstmt = SQLstmt & "'" & cleansql(BusinessDescription) & "',"
SQLstmt = SQLstmt & "'" & cleansql(str1) & "',"
SQLstmt = SQLstmt & "'" & cleansql(ContactPosition) & "',"
SQLstmt = SQLstmt & "'" & cleansql(PairentCompany) & "',"
SQLstmt = SQLstmt & "'" & DateEst & "',"
SQLstmt = SQLstmt & "'" & cleansql(HeadOfficeTelephone) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Keywords1) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Keywords2) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Keywords3) & "',"
SQLstmt = SQLstmt & "'" & request.form("ExportArea") & "',"
SQLstmt = SQLstmt & "'" & NumberOfEmployees & "',"
SQLstmt = SQLstmt & "'" & cleansql(str) & "',"
SQLstmt = SQLstmt & "'" & cleansql(Turnover) & "',"
SQLstmt = SQLstmt & "'" & objRS10("uniqueid") & "')"
SQLstmt = SQLstmt & " select llID = scope_identity() "
SQLstmt = SQLstmt & " Set Nocount off "
SQLstmt = SQLstmt & "END"

Set loRs = objcon.execute(SQLstmt)

llID = loRs("llID")

SQLstmtTemp = "UPDATE tblID set " & _ 
"DetailsID = " & llID & " " & _
"WHERE UniqueID = '" & strUnique & "'"

objCon.Execute(SQLstmtTemp)
 
Sort by date Sort by votes
I suggest you 'jiggle' things around a little.

Code: 
Set NOCOUNT ON
Declare @Exists Bit
Declare @NewId Integer
Set @NewId = 0

IF EXISTS(SELECT uniqueid FROM members WHERE uniqueID = 'Blah')
  Begin
    Set @Exists = 1
  End
Else
  Begin
    Set @Exists = 0

    -- Put your insert code here

    Select @NewId = Scope_Identity()
  End

Select @Exists As RecordExists, @NewId As TheNewId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
thanks george

I have now re-written this as a stored procedure and did have it working, but data going in the wrong columns, so i've re-done as a quick test (using the wizard) I am now getting an error.

Code: 
ADODB.Command error '800a0d5d'

Application uses a value of the wrong type for the current operation.

the table has more columns than this but these are the columns i am interested in now (the other's get used at a later date). all the data types are correct, i am passing in the correct order, no length is being truncated. i cannot fathom what the problem is. any ideas ?

thanks

Code: 
CREATE PROCEDURE [AddNewMember]
	(@Cat_1 	[text],
	 @BusinessName_2 	[varchar](256),
	 @BuildingNo_3 	[varchar](52),
	 @ContactTitle_4 	[varchar](52),
	 @ContactFirstName_5 	[varchar](52),
	 @ContactSurname_6 	[varchar](52),
	 @Address1_7 	[varchar](256),
	 @Address2_8 	[varchar](256),
	 @Address3_9 	[varchar](256),
	 @Address4_10 	[varchar](256),
	 @City_11 	[varchar](256),
	 @County_12 	[varchar](52),
	 @PostCode_13 	[varchar](256),
	 @Telephone_14 	[varchar](256),
	 @Email_15 	[varchar](256),
	 @[URL unfurl="true"]WWW_16[/URL] 	[varchar](256),
	 @Fax_17 	[varchar](256),
	 @BusinessDescription_18 	[text],
	 @Password_19 	[varchar](52),
	 @ContactPosition_20 	[varchar](256),
	 @PairentCompany_21 	[varchar](256),
	 @HeadOfficeTelephone_22 	[varchar](256),
	 @DateEst_23 	[varchar](256),
	 @Keywords1_24 	[varchar](52),
	 @Keywords2_25 	[varchar](52),
	 @Keywords3_26 	[varchar](52),
	 @ExportArea_27 	[text],
	 @NumberOfEmployees_28 	[varchar](52),
	 @UserName_29 	[varchar](50),
	 @Turnover_30 	[varchar](52),
	 @uniqueid_31 	[varchar](50))

AS

INSERT INTO [BusinessDir].[dbo].[members] 
	 ([Cat],
	 [BusinessName],
	 [BuildingNo],
	 [ContactTitle],
	 [ContactFirstName],
	 [ContactSurname],
	 [Address1],
	 [Address2],
	 [Address3],
	 [Address4],
	 [City],
	 [County],
	 [PostCode],
	 [Telephone],
	 [Email],
	 [[URL unfurl="true"]WWW],[/URL]
	 [Fax],
	 [BusinessDescription],
	 [Password],
	 [ContactPosition],
	 [PairentCompany],
	 [HeadOfficeTelephone],
	 [DateEst],
	 [Keywords1],
	 [Keywords2],
	 [Keywords3],
	 [ExportArea],
	 [NumberOfEmployees],
	 [UserName],
	 [Turnover],
	 [uniqueid]) 
 
VALUES 
	(@Cat_1,
	 @BusinessName_2,
	 @BuildingNo_3,
	 @ContactTitle_4,
	 @ContactFirstName_5,
	 @ContactSurname_6,
	 @Address1_7,
	 @Address2_8,
	 @Address3_9,
	 @Address4_10,
	 @City_11,
	 @County_12,
	 @PostCode_13,
	 @Telephone_14,
	 @Email_15,
	 @[URL unfurl="true"]WWW_16,[/URL]
	 @Fax_17,
	 @BusinessDescription_18,
	 @Password_19,
	 @ContactPosition_20,
	 @PairentCompany_21,
	 @HeadOfficeTelephone_22,
	 @DateEst_23,
	 @Keywords1_24,
	 @Keywords2_25,
	 @Keywords3_26,
	 @ExportArea_27,
	 @NumberOfEmployees_28,
	 @UserName_29,
	 @Turnover_30,
	 @uniqueid_31)
GO

here is the code from the asp, all the other recordset values are being passed correctly, the only thing i can think is that the text fields are causing problems ?

Code: 
with cmd

   cmd.CommandText = "AddNewMember"
   cmd.CommandType = adCmdStoredProc
   cmd.ActiveConnection = objCon

   cmd.Parameters(0) = objRS8("MyID")
   cmd.Parameters(1) = cleansql(BusinessName)
   cmd.Parameters(2) = cleansql(BuildingNo)
   cmd.Parameters(3) = cleansql(ContactTitle) 
   cmd.Parameters(4) = cleansql(ContactFirstName)
   cmd.Parameters(5) = cleansql(ContactSurname) 
   cmd.Parameters(6) = cleansql(Address1)
   cmd.Parameters(7) = cleansql(Address2) 
   cmd.Parameters(8) = cleansql(Address3) 
   cmd.Parameters(9) = cleansql(Address4) 
   cmd.Parameters(10) = cleansql(City) 
   cmd.Parameters(11) = cleansql(County) 
   cmd.Parameters(12) = cleansql(Postcode) 
   cmd.Parameters(13) = cleansql(Telephone) 
   cmd.Parameters(14) = cleansql(Email) 
   cmd.Parameters(15) = cleansql(WWW) 
   cmd.Parameters(16) = cleansql(Fax) 
   cmd.Parameters(17) = cleansql(BusinessDescription) 
   cmd.Parameters(18) = cleansql(str1) 
   cmd.Parameters(19) = cleansql(ContactPosition) 
   cmd.Parameters(20) = cleansql(PairentCompany) 
   cmd.Parameters(21) = cleansql(HeadOfficeTelephone) 
   cmd.Parameters(22) = cleansql(DateEst)
   cmd.Parameters(23) = cleansql(Keywords1) 
   cmd.Parameters(24) = cleansql(Keywords2) 
   cmd.Parameters(25) = cleansql(Keywords3) 
   cmd.Parameters(26) = request.form("ExportArea")
   cmd.Parameters(27) = NumberOfEmployees 
   cmd.Parameters(28) = cleansql(str) 
   cmd.Parameters(29) = cleansql(Turnover) 
   cmd.Parameters(30) = objRS10("uniqueid") 
 set objRS = cmd.Execute
end with
 
Upvote 0 Downvote
What is the line number on which you are getting the error??

i think this is the line causing the problem

cmd.CommandType = adCmdStoredProc

do you have ADOVBS.INC file included on the page?

or you can say...

cmd.CommandType = 4

-DNG
 
Upvote 0 Downvote
thanks

I have tried that but no joy.
what i've done now is create a temp table with 2 columns ID (identity, 1,1 ) as primary key also and another column temp12 as varchar(50)

my sp is

Code: 
CREATE PROCEDURE [insert_temp12_1]
	( @temp12_2 	[varchar](50))

AS
SET NOCOUNT ON 
INSERT INTO [BusinessDir].[dbo].[temp12] 
	 ( [temp12]) 
 
VALUES 
	( @temp12_2)
GO

my asp code is (I have tried so many variations)

Code: 
set buscmd = Server.CreateObject("ADODB.Command")

buscmd.CommandType = 4
buscmd.CommandText = "insert_temp12_1"
buscmd.ActiveConnection = objCon.ConnectionObject

buscmd.Parameters.Refresh

Set param = buscmd.CreateParameter("@temp12_2", adVarChar, adParamInput, 50, cleansql(BusinessName))
buscmd.Parameters.append param
buscmd.Execute

i now get this error ?

thanks for any help.

Code: 
ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

 
Upvote 0 Downvote
found the problem - it was the text columns - i needed to append the parameters and specify the text columns as adlongvarchar

Code: 
buscmd.Parameters.append buscmd.createparameter("@Cat_2", adlongvarchar, adParamInput, len(objRS8("MyID")))
buscmd.Parameters.Item("@Cat_2").AppendChunk objRS8("MyID")
buscmd.Parameters("@Cat_2") = objRS8("MyID")

regards
richard
 
Upvote 0 Downvote
thanks - this is now working ok

one last thing how can i read the scope-identity value from my SP ? i need to use it to update another table something like this.....

thanks

Code: 
SQLstmtTemp = "UPDATE tblID set " & _ 
"DetailsID = " & cmd.Parameters("@NewId") & " " & _
"WHERE UniqueID = '" & strUnique & "'"


Stored proc
Code: 
CREATE PROCEDURE [spAddNewMember]
	(@tempid    [varchar](50),
	 @Cat_2 	[text],
	 @BusinessName_3 	[varchar](256),
	 @BuildingNo_4 	[varchar](52),
	 @ContactTitle_5 	[varchar](52),
	 @ContactFirstName_6 	[varchar](52),
	 @ContactSurname_7 	[varchar](52),
	 @Address1_8 	[varchar](256),
	 @Address2_9 	[varchar](256),
	 @Address3_10 	[varchar](256),
	 @Address4_11 	[varchar](256),
	 @City_12 	[varchar](256),
	 @County_13 	[varchar](52),
	 @PostCode_14 	[varchar](256),
	 @Telephone_15 	[varchar](256),
	 @Email_16 	[varchar](256),
	 @[URL unfurl="true"]WWW_17[/URL] 	[varchar](256),
	 @Fax_18 	[varchar](256),
	 @BusinessDescription_19 	[text],
	 @Password_20 	[varchar](52),
	 @ContactPosition_21 	[varchar](256),
	 @PairentCompany_22 	[varchar](256),
	 @HeadOfficeTelephone_23 	[varchar](256),
	 @DateEst_24 	[varchar](256),
	 @Keywords1_25 	[varchar](52),
	 @Keywords2_26 	[varchar](52),
	 @Keywords3_27 	[varchar](52),
	 @ExportArea_29 	[text],
	 @NumberOfEmployees_30 	[varchar](52),
	 @UserName_34 	[varchar](50),
	 @Turnover_36 	[varchar](52),
	 @uniqueid_37 	[varchar](50))

AS

Set NOCOUNT ON
Declare @Exists Bit
Declare @NewId Integer
Set @NewId = 0

IF EXISTS(SELECT uniqueid FROM members WHERE uniqueID = @tempid)
  Begin
    Set @Exists = 1
  End
Else
  Begin
    Set @Exists = 0

 INSERT INTO [BusinessDir].[dbo].[members] 
	 ([Cat],
	 [BusinessName],
	 [BuildingNo],
	 [ContactTitle],
	 [ContactFirstName],
	 [ContactSurname],
	 [Address1],
	 [Address2],
	 [Address3],
	 [Address4],
	 [City],
	 [County],
	 [PostCode],
	 [Telephone],
	 [Email],
	 [[URL unfurl="true"]WWW],[/URL]
	 [Fax],
	 [BusinessDescription],
	 [Password],
	 [ContactPosition],
	 [PairentCompany],
	 [HeadOfficeTelephone],
	 [DateEst],
	 [Keywords1],
	 [Keywords2],
	 [Keywords3],
	 [ExportArea],
	 [NumberOfEmployees],
	 [UserName],
	 [Turnover],
	 [uniqueid]) 
 
VALUES 
	(@Cat_2,
	 @BusinessName_3,
	 @BuildingNo_4,
	 @ContactTitle_5,
	 @ContactFirstName_6,
	 @ContactSurname_7,
	 @Address1_8,
	 @Address2_9,
	 @Address3_10,
	 @Address4_11,
	 @City_12,
	 @County_13,
	 @PostCode_14,
	 @Telephone_15,
	 @Email_16,
	 @[URL unfurl="true"]WWW_17,[/URL]
	 @Fax_18,
	 @BusinessDescription_19,
	 @Password_20,
	 @ContactPosition_21,
	 @PairentCompany_22,
	 @HeadOfficeTelephone_23,
	 @DateEst_24,
	 @Keywords1_25,
	 @Keywords2_26,
	 @Keywords3_27,
	 @ExportArea_29,
	 @NumberOfEmployees_30,
	 @UserName_34,
	 @Turnover_36,
	 @uniqueid_37)

   Select @NewId = Scope_Identity()
  End

Select @Exists As RecordExists, @NewId As TheNewId
GO
 
Upvote 0 Downvote
i've set the command object as a recordset, which now works ok - is this the recommended method ?

Code: 
set objRS = buscmd.Execute

response.write objRS("TheNewID")
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question Question
Return to current location on page after asp processes.
Replies
2
Views
1K
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question Question
How to send HTML emails with ASP classic?
Replies
0
Views
1K
leifoet
leifoet
penguinspeaks
  • Locked
  • Question Question
write HTML code within ASP line
Replies
1
Views
1K
xwb
xwb
leifoet
  • Locked
  • Question Question
How to check if a datatable is empty ?
Replies
0
Views
793
leifoet
leifoet
GriffMG
  • Locked
  • Question Question
Classic ASP - Comm object sometimes doesn't return data and I have to requery
Replies
1
Views
473
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top