Monday, March 12, 2012

How do I get a procedure OUTPUT-parameter...

In my ASP.NET page I use a stored procedure that have a parameter declared as OUTPUT...
however...I do not know how to get this OUTPUT to be stored in a ASP.NET-variable...

this is the sp:

CREATE PROCEDURE spInsertNews
@.uidArticleId uniqueidentifier OUTPUT,
@.strHeading nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400)
AS
SET @.uidArticleId = newid()
INSERT INTO tblArticles
VALUES(@.uidArticleId ,@.strHeading,@.strAbstract,@.strText,@.dtDate,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAuthorEmail,@.strKeywords)

my asp code is something like this:

...
SqlCommand sqlcmdInsertNewsArticle = new SqlCommand(insertCmd, sqlconCon);

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strHeading", SqlDbType.NVarChar, 300));
sqlcmdInsertNewsArticle.Parameters["@.strHeading"].Value = strHeading.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strAbstract", SqlDbType.NVarChar, 600));
sqlcmdInsertNewsArticle.Parameters["@.strAbstract"].Value = strAbstract.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strText", SqlDbType.NVarChar, 4000));
sqlcmdInsertNewsArticle.Parameters["@.strText"].Value = strText.Text;

...

sqlcmdInsertNewsArticle.Connection.Open();
sqlcmdInsertNewsArticle.ExecuteNonQuery();
sqlcmdInsertNewsArticle.Connection.Close();

How do I do if I want to catch the OUTPUT-parameter (@.uidArticleId)?

anyone?Start by defining another parameter in the Parameters collection:

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.uidArticleId", SqlDbType.UniqueIdentifier));
sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Direction = ParameterDirection.Output;

Then, after you call ExecuteNonQuery, grab the value of that parameter:

Dim id as GUID = sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Value;

I didn't test this code so you may need to tweak it, but that's the idea.

Don|||thanks... that would probably work if I just could get the %#&¤ connection to work... :)

(see: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=553656 )

No comments:

Post a Comment