urgent: cant get uniqueidentifier returned from sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

urgent: cant get uniqueidentifier returned from sp

Author

Topic

robertnzana
Starting Member

42 Posts

Posted - 2008-05-10 : 20:03:36

I am a total newb, using VB.NET/ASP.NET 2.0 (visual studio 2008).

Here's my stored proce (hope it's right)

ALTER PROCEDURE dbo.GetPostingAuthorIdClassified
	(
	@Id int
	)
AS
	SELECT UserId FROM classifieds WHERE (Id = @Id)
RETURN

Here's my code I'm using to retrieve the value UserId (which is a guid/uniqueidentifier)...

            MyCommand = New SqlCommand(spname, MyConnection)
            MyCommand.CommandType = Data.CommandType.StoredProcedure
            MyCommand.Parameters.AddWithValue("@id", CType(Request.QueryString("Id"), Integer))
            Dim MyId = MyCommand.ExecuteScalar

The problem is that the guid is ALWAYS empty, though I should have records. What am I doing wrong? Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-11 : 00:26:59

The way that your stored procedure is coded is it'll return a record set. Your VB.NET code looks like it's expecting a value back rather than a record set.

Try using an OUTPUT variable for UserId in the stored procedure instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-11 : 02:47:49

quote:

Originally posted by robertnzana

            MyCommand = New SqlCommand(spname, MyConnection)
            MyCommand.CommandType = Data.CommandType.StoredProcedure
            MyCommand.Parameters.AddWithValue("@id", CType(Request.QueryString("Id"), Integer))
            Dim MyId = MyCommand.ExecuteScalar

The problem is that the guid is ALWAYS empty, though I should have records. What am I doing wrong? Thanks!

1)You said you expect "records" while you are using executescalar for your command object which will just return one record.Are you sure that your records are unique to return one record?

2)always make a practice to declare you variable before you use it. Use dim myid as integer=MyCommand.ExecuteScalar & remove the return statement from your stored proc because it is no returning anything.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-11 : 09:00:09

quote:

The problem is that the guid is ALWAYS empty, though I should have records. What am I doing wrong? Thanks!

GUID? Your parameter and your variables seem to be declared as INTEGERS. Which is it?

- Jeff
http://weblogs.sqlteam.com/JeffS

robertnzana
Starting Member

42 Posts

Posted - 2008-05-24 : 17:09:51

thanks so much everyone!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources