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

Asked By spudg

11-Aug-07 08:41 PM

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

11-Aug-07 09:22 PM

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

11-Aug-07 10:44 PM

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")

12-Aug-07 07:34 AM

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]

12-Aug-07 08:25 AM

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