SQL Stored returning uniqueidentifier

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=;datab ase=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()

re: SQL Stored returning uniqueidentifier

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:
[color=blue]
> 'pUnlockKey.Value = New System.Guid()[/color]

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!
************************************************
"Jacques Wentworth" <JacquesWentworth@Hotmail.com> wrote in message
news:OSI4qT0aEHA.3692@TK2MSFTNGP09.phx.gbl...[color=blue]
> 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[/color]
fine.[color=blue]
>
> Am I doing something wrong?
>
> Thanks
> Jacques Wentworth
>
> See my code snippet below.
>
> Dim cn As SqlConnection = _
> New SqlConnection("server=2000server;uid=sa;pwd=;datab ase=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()
>
>
>[/color]

re: SQL Stored returning uniqueidentifier

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!
************************************************
"Cowboy (Gregory A. Beamer) [MVP]" <NoSpamMgbworld@comcast.netNoSpamM> wrote
in message news:%236orRb0aEHA.2812@tk2msftngp13.phx.gbl...[color=blue]
> 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:
>[color=green]
> > 'pUnlockKey.Value = New System.Guid()[/color]
>
> Also, I do not understand the GUID here, unless you are using a GUID[/color]
field.[color=blue]
> Then, simply input. I assume you later switched to autonumber (IDENTITY).
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ************************************************
> Think Outside the Box!
> ************************************************
> "Jacques Wentworth" <JacquesWentworth@Hotmail.com> wrote in message
> news:OSI4qT0aEHA.3692@TK2MSFTNGP09.phx.gbl...[color=green]
> > Hi
> >
> > I have the following problem. I have a SQL Server (2000) Stored Proc[/color][/color]
that[color=blue][color=green]
> > with 4 parameter (one being @UnlockKey uniqueidentifier OUTPUT). I run[/color][/color]
it[color=blue][color=green]
> > 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[/color]
> fine.[color=green]
> >
> > Am I doing something wrong?
> >
> > Thanks
> > Jacques Wentworth
> >
> > See my code snippet below.
> >
> > Dim cn As SqlConnection = _
> > New SqlConnection("server=2000server;uid=sa;pwd=;datab ase=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()
> >
> >
> >[/color]
>
>[/color]

re: SQL Stored returning uniqueidentifier

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

Jacques Wentworth wrote:[color=blue]
> Hi
>
> I have the following problem. I have a SQL Server (2000) Stored Proc[/color]
that[color=blue]
> with 4 parameter (one being @UnlockKey uniqueidentifier OUTPUT). I[/color]
run it[color=blue]
> from Query Analyzer and the @Key parameter is valued. When I run it[/color]
from[color=blue]
> VB.Net it is empty. I tried it with other other datatype and it works[/color]
fine.[color=blue]
>
> Am I doing something wrong?
>
> Thanks
> Jacques Wentworth
>
> See my code snippet below.
>
> Dim cn As SqlConnection = _
> New SqlConnection("server=2000server;uid=sa;pwd=;datab ase=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,[/color]
0,[color=blue]
> 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()[/color]

re: SQL Stored returning uniqueidentifier

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.






"Clay" wrote:
[color=blue]
> 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
>
> Jacques Wentworth wrote:[color=green]
> > Hi
> >
> > I have the following problem. I have a SQL Server (2000) Stored Proc[/color]
> that[color=green]
> > with 4 parameter (one being @UnlockKey uniqueidentifier OUTPUT). I[/color]
> run it[color=green]
> > from Query Analyzer and the @Key parameter is valued. When I run it[/color]
> from[color=green]
> > VB.Net it is empty. I tried it with other other datatype and it works[/color]
> fine.[color=green]
> >
> > Am I doing something wrong?
> >
> > Thanks
> > Jacques Wentworth
> >
> > See my code snippet below.
> >
> > Dim cn As SqlConnection = _
> > New SqlConnection("server=2000server;uid=sa;pwd=;datab ase=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,[/color]
> 0,[color=green]
> > 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()[/color]
>
>[/color]