Retrieving an Output Parameter (in classic ASP w/SQL Server 2005 Express)

Hello,

I'm working on migrating a classic ASP application that used an Access
database to SQL Server 2005 Express. I need to insert a record and get
back the value of the primary key. See code below, then I'll describe
what I've tried:

CURRENT CODE:
Error: 'Must declare the scalar variable "@userid".'
'------------------------------------------------------------
Dim adoRecordSet
Dim adoConnection
Dim adoCommand
Dim strConnectionString

strConnectionString = "Provider=SQLOLEDB;Data Source=*****
\*****;UID=*****;PWD=*****;"

set adoConnection = Server.CreateObject("ADODB.Connection")
set adoRecordSet = Server.CreateObject("ADODB.RecordSet")
set adoCommand = Server.CreateObject("ADODB.Command")

adoConnection.ConnectionString = strConnectionString
adoConnection.Open adoConnection.ConnectionString

adoCommand.CommandText = "INSERT INTO dbo.tblMarginNotes (userid,
date, " & _
groupcode) " & _
@comment, " & _
adoCommand.CommandType = adCommandText

adoCommand.ActiveConnection = adoConnection
adoCommand.Parameters.Append
adoCommand.CreateParameter("userid",adVarChar,adParamInput,Len(strUserID),strUserID)
adoCommand.Parameters.Append
adoCommand.CreateParameter("date",adDBTimeStamp,adParamInput,,dtDate)
adoCommand.Parameters.Append
adoCommand.CreateParameter("startverseid",adInteger,adParamInput,,intStartVerseID)
adoCommand.Parameters.Append
adoCommand.CreateParameter("endverseid",adInteger,adParamInput,,intEndVerseID)
adoCommand.Parameters.Append
adoCommand.CreateParameter("subject",adVarChar,adParamInput,Len(strSubject),strSubject)
adoCommand.Parameters.Append
adoCommand.CreateParameter("comment",adVarChar,adParamInput,Len(strText),strText)
adoCommand.Parameters.Append
adoCommand.CreateParameter("notetype",adVarChar,adParamInput,Len(strType),strType)
adoCommand.Parameters.Append
adoCommand.CreateParameter("tags",adVarChar,adParamInput,Len(strTags),strTags)
adoCommand.Parameters.Append
adoCommand.CreateParameter("groupcode",adVarChar,adParamInput,Len(strGroupID),strGroupID)
adoCommand.Parameters.Append
adoCommand.CreateParameter("id",adInteger,adParamOutput,,intNoteID)
adoCommand.Execute()

'------------------------------------------------------------

I've spent hours tweaking the code above to try and get it to work,
but I'm not getting any love. I've tried adding the "@" to the front
of the Names passed in the CreateParameter method (e.g. "@userid"
instead of "userid"). I've also tried using "?" instead of
were added. This got the record inserted but I couldn't figure out how
to get the value of the primary key back.

Any help is much appreciated.

Thanks!

- Nick

In addition to using '?

In addition to using '?' for parameter markers, add SET NOCOUNT ON before
the INSERT.  This will suppress DONE_IN_PROC messages that can interfere
with ADO returning result sets, messages and output values.  You can also
return the SCOPE_IDENTITY() value using a SELECT and recordset in your code.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Thanks!

Thanks! Here's the code that ended up working:

adoCommand.CommandText = "SET NOCOUNT ON; INSERT INTO
dbo.tblMarginNotes (userid, date, " & _
groupcode) " & _
adoCommand.CommandType = adCmdText

adoCommand.ActiveConnection = adoConnection
adoCommand.Parameters.Append
adoCommand.CreateParameter("userid",adVarChar,adParamInput,Len(strUserID),strUserID)
adoCommand.Parameters.Append
adoCommand.CreateParameter("date",adDBTimeStamp,adParamInput,,dtDate)
adoCommand.Parameters.Append
adoCommand.CreateParameter("startverseid",adInteger,adParamInput,,intStartVerseID)
adoCommand.Parameters.Append
adoCommand.CreateParameter("endverseid",adInteger,adParamInput,,intEndVerseID)
adoCommand.Parameters.Append
adoCommand.CreateParameter("subject",adVarChar,adParamInput,Len(strSubject),strSubject)
adoCommand.Parameters.Append
adoCommand.CreateParameter("comment",adVarChar,adParamInput,Len(strText),strText)
adoCommand.Parameters.Append
adoCommand.CreateParameter("notetype",adVarChar,adParamInput,Len(strType),strType)
adoCommand.Parameters.Append
adoCommand.CreateParameter("tags",adVarChar,adParamInput,Len(strTags),strTags)
adoCommand.Parameters.Append
adoCommand.CreateParameter("groupcode",adVarChar,adParamInput,Len(strGroupID),strGroupID)
adoCommand.Parameters.Append
adoCommand.CreateParameter("id",adInteger,adParamOutput,,intNoteID)
adoCommand.Execute()

intNoteID = adoCommand.Parameters("id")

Hi,Always try and use stored procedures to do stuff rather than embedding SQL

Hi,

Always try and use stored procedures to do stuff rather than embedding SQL
into your application.

It makes it a lot easier to improve and take advantage of new stuff going
forward.

Also makes permissioning easier and more controlled....

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

I'm glad I was able to help.

I'm glad I was able to help.  I agree with Tony's suggestion to consider
stored procedures instead of embedded SQL.  We use stored procedures
exclusively for the reasons Tony mentioned.

--
Dan Guzman
SQL Server MVP