SQL Stored returning uniqueidentifier

  • Thread starter Thread starter Jacques Wentworth
  • Start date Start date
J

Jacques Wentworth

Hi

I have the following problem. I have a SQL Server (2000) Stored Proc that
with 4 parameter (one being @UnlockKey uniqueidentifier OUTPUT). I run it
from Query Analyzer and the @Key parameter is valued. When I run it from
VB.Net it is empty. I tried it with other other datatype and it works fine.

Am I doing something wrong?

Thanks
Jacques Wentworth

See my code snippet below.

Dim cn As SqlConnection = _
New SqlConnection("server=2000server;uid=sa;pwd=;database=bar")
cn.Open()
Dim co As SqlCommand = _
New SqlCommand("EditListSelectID", cn)
co.CommandType = CommandType.StoredProcedure
Dim pID As SqlParameter = BuildParam("@id", SqlDbType.BigInt, 0,
ParameterDirection.Input)
Dim pLock As SqlParameter = BuildParam("@Lock", SqlDbType.Bit, 0,
ParameterDirection.Input)
Dim pUserID As SqlParameter = BuildParam("@UserID", SqlDbType.BigInt, 0,
ParameterDirection.Input)
Dim pUnlockKey As SqlParameter = BuildParam("@UnlockKey",
SqlDbType.UniqueIdentifier, 0, ParameterDirection.Output)
pID.Value = ID
pLock.Value = 1
pUserID.Value = 1
'pUnlockKey.Value = New System.Guid()
co.Parameters.Add(pID)
co.Parameters.Add(pLock)
co.Parameters.Add(pUserID)
co.Parameters.Add(pUnlockKey)
co.ExecuteReader()
'co.ExecuteNonQuery()
'Dim myReader As SqlDataReader = co.ExecuteReader()
 
When the Reader is open, you should be able to grab the output parameter,
like so:

Dim x As int= pUnlockKey.Value

Once the Reader is closed or the connection is closed (which closes the
Reader), I believe you are toast.

BTW, this is NEVER a good idea for an Output Parameter:
'pUnlockKey.Value = New System.Guid()
Click to expand...

Also, I do not understand the GUID here, unless you are using a GUID field.
Then, simply input. I assume you later switched to autonumber (IDENTITY).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
 
Never mind. Brain death.

OK, I see the GUID now. Here is how I would handle this, assuming a GUID
field only:

Dim connString As String = "{conn strring here}"
Dim sproc As String = "sp_SprocName"
Dim outputGuid as Guid

Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sproc, conn)
cmd.CommandType = CommandType.StoredProcedure

Dim param As New SqlParameter("@paramName")
param.Direction = ParameterDirection.Output

Try
conn.Open()
Dim r As SqlDataReader = cmd.ExecuteReader()

'Other Reader work here

outputGuid = param.Value
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If

conn.Dispose()
End Try


NOTE: You can also preload the GUID and shove it into the database instead
of generating inside the sproc, if that is an option in your app.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
 
Hi Jacques,

I'm having the exact same problem, and wondering if you found a fix? I
can't return a UNIQUEIDENTIFIER SQL type as an output parm.. It always
comes back empty.

Thanks in advance,

-Clay
 
You must be doing something wrong because it does work. I have written a
small example (in C#) which shows how to do it:

The stored procedure:

CREATE PROCEDURE usp_GuidTest
@@guid AS UNIQUEIDENTIFIER OUTPUT
AS

SET @@guid = NEWID()

The C# code:

SqlConnection cn = new SqlConnection("Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data
Source=KONTOR-JCH");
SqlCommand cmd = new SqlCommand("usp_GuidTest", cn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter("@@guid",
SqlDbType.UniqueIdentifier, 16);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);

try
{
cn.Open();
cmd.ExecuteNonQuery();

Console.WriteLine(param.Value.ToString());
}
finally
{
cn.Close();
}

HTH, Jakob.
 
Back
Top