|
|
Posted by vassone on 09/14/05 11:41
Dear All,
My newly insert GUID is not return from a store procedure. I turned this
around and around and can't understand why. The records are inserted but no
GUID is returned.
I use (newid()) in the table to generate a GUID
**************Store Procedure ***********************
CREATE PROCEDURE heasvase.[usp_insert_address]
@ADDR_NAME_2 [char](70) = NULL,
@ADDR_NO_3 [char](10) = NULL,
@ADDR_ROAD_4 [char](50) = NULL,
@ADDR_DISTRICT_5 [char](50) = NULL,
@ADDR_TOWN_6 [char](50) = NULL,
@ADDR_BOROUGH_7 [char](50) = NULL,
@ADDR_PCODE_8 [char](12) = NULL,
@addr_id [int] OUTPUT
AS
INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
(
[ADDR_NAME],
[ADDR_NO],
[ADDR_ROAD],
[ADDR_DISTRICT],
[ADDR_TOWN],
[ADDR_BOROUGH],
[ADDR_PCODE])
VALUES
(
@ADDR_NAME_2,
@ADDR_NO_3,
@ADDR_ROAD_4,
@ADDR_DISTRICT_5,
@ADDR_TOWN_6,
@ADDR_BOROUGH_7,
@ADDR_PCODE_8)
SELECT @addr_id = scope_identity()
GO
***************** ASP ***********************
'Set connection and command properties
set objConn = Server.CreateObject("ADODB.Connection")
set objComm = Server.CreateObject("ADODB.Command")
objConn.Open "Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist
Security Info=False;User ID=iusr_abc; Initial Catalog=Cat_name; Data
Source=SQLSER7"
objComm.ActiveConnection = objConn
objComm.CommandType = adCmdStoredProc
objComm.CommandText = "heasvase.usp_insert_address"
'Set parameters
set value1 = objComm.CreateParameter("@val1", adChar, adParamInput, 20 ,
val1)
set value2 = objComm.CreateParameter("@val2", adChar, adParamInput, 20 ,
val2)
set value3 = objComm.CreateParameter("@val3", adChar, adParamInput, 20 ,
val3)
set value4 = objComm.CreateParameter("@val4", adChar, adParamInput, 20 ,
val4)
set value5 = objComm.CreateParameter("@val5", adChar, adParamInput, 20 ,
val5)
set value6 = objComm.CreateParameter("@val6", adChar, adParamInput, 20 ,
val6)
set value7 = objComm.CreateParameter("@val7", adChar, adParamInput, 20 ,
val7)
set value8 = objComm.CreateParameter("@addr_id", adInteger, adParamOutput )
objComm.Parameters.Append(value1)
objComm.Parameters.Append(value2)
objComm.Parameters.Append(value3)
objComm.Parameters.Append(value4)
objComm.Parameters.Append(value5)
objComm.Parameters.Append(value6)
objComm.Parameters.Append(value7)
objComm.Parameters.Append(value8)
'Run Command and tell ADO no records only potput params 'adExecuteNoRecords'
objComm.Execute , , adExecuteNoRecords
newId = objComm.Parameters.Item("@addr_id")
response.write("Here ->" & newId)
'Cleanup resources
Set objComm = Nothing
Any help would be greatly appreciated...
[Back to original message]
|