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() 4 10120
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()
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" <Ja**************@Hotmail.com> wrote in message
news:OS**************@TK2MSFTNGP09.phx.gbl... 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()
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]" <No************@comcast.netNoSpamM> wrote
in message news:%2****************@tk2msftngp13.phx.gbl... 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() 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" <Ja**************@Hotmail.com> wrote in message news:OS**************@TK2MSFTNGP09.phx.gbl... 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()
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: 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()
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: 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: 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()
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: vassone |
last post by:
Dear All,
My newly insert GUID is not return from a store procedure. I turned this
around and around and can't understand why. The records are inserted but no
GUID is returned.
I use...
|
by: Johnny |
last post by:
I have a stored procedure that takes three input parameters and returns a
single record result set. The stored procedure is declare as follows:
p_getTaxAmount (@ZipCodeDtlId uniqueidentifier,...
|
by: bidllc |
last post by:
I have a funtion that works fine and dandy when called from anywhere in my
app. It will NOT work when called from inside the class in which it resides.
This is the function I'm calling:...
|
by: ASP .NET Newbie |
last post by:
I am trying to insert a new record into my database, and have it return a
uniqueidentifier as the "newcatid". I don't use integers as my "id"'s, but
rather uniqueidentifiers. Here's my Stored...
|
by: Jacques Wentworth |
last post by:
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...
|
by: dew |
last post by:
I have a stored procedure that retrieves the id of a client table, which is
a guid
To get that id, I have
dim clientid as guid = GetClientID("OrgName")
The GetClientID returns a string,...
|
by: SandySears |
last post by:
I am trying to use a stored procedure to insert a record using VS 2005, VB
and SQL Server Express. The code runs without errors or exceptions, and
returns the new identifer in the output...
|
by: mvmashraf |
last post by:
Hi..
I have a select statement inside(WITH statement), In the where clause of the where statement we are checking withe passed argument. The belows are the passed argument
...
|
by: mvmashraf |
last post by:
Hi..
I have a select statement inside(WITH statement), In the where clause of the where statement we are checking withe passed argument. The belows are the passed argument
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |