guid parameter with SQL stored procedures

G

Guest

Hello,

Need some expertise for this one...

I have a SQL stored procedure that returns a value 1 if a match is found in
the table based on 2 parameters.

Here is the SP:
--------------------------------
CREATE PROCEDURE dbo.spCheckDuplicatePOItem_ID
-- example usage: spCheckDuplicatePOItem_ID
'{75C3A07D-67CD-4DBB-95B3-37F73C77C94F}', '20.D'
-- checks if an entry with this POItem_ID and POID already exists. If so, it
returns a value > 0, else returns 0.
--INPUT PARAMETERS

@POvProject_ID Uniqueidentifier,
@POItem_ID nvarchar(50)
AS
declare @RecCount nvarchar(50)
SELECT @RecCount = (SELECT COUNT(fldItem_ID) AS [Count] FROM dbo.tblPOItems
WHERE fldItem_ID IN
(SELECT fldItem_ID FROM dbo.tblPOItems
WHERE (FKPOvProject_ID = @POvProject_ID)
Group By fldItem_ID HAVING (fldItem_ID = @POItem_ID)))

select @RecCount as RecCount
return @RecCount
GO
---------------------------
This SP works well using Anaylzer and a RecCount value of 1 gets returned.
The problems lies in the VBA code and when I input the parameters to get a
return value. Here's the VBA:
----------------------------
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection
conn.CursorLocation = adUseClient

Set cmd.ActiveConnection = conn
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spCheckDuplicatePOItem_ID"
cmd.Parameters.Refresh
cmd.Parameters("@POvProject_ID").Value = Me.FKPOvProject_ID
'same value a SP example
cmd.Parameters("@POItem_ID") = "'" & Me.fldItem_ID & "'"
'same value a SP example
Set rs = cmd.Execute

If rs.state = adStateClosed Then
'no connection made
GoTo fldItem_ID_temp_AfterUpdate_Error
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
Else
If rs("RecCount") = 1 Then
If MsgBox("A duplicate PO Item # exists. Please
enter a new PO Item #.", vbCritical, "Duplicate ID") = vbOK Then
fldItem_ID = Null
fldItem_ID_temp = Null
fldItem_ID_temp.SetFocus
Exit Sub
End If
Else
fldItem_ID =
Forms![frm_PurchaseOrders]![PurchaseOrderNumber] & "." & fldItem_ID_temp
End If
End If
 
S

Sylvain Lafontaine

I'm not sure but I think that the problem here is that @POvProject_ID is of
type Uniqueidentifier but that you are using a string value. Query Analyser
is able to make the conversion on the fly but not the VBA code. In VBA,
you should use a binary value, if my memory is good.

An easier solution would be to set the type of the parameter to nchar and
make the conversion directly inside the stored procedure. Probably that you
can even use directly the string value in the select query but I'm not sure.

Maybe the following references will be of some value to you:

http://www.trigeminal.com/usenet/usenet011.asp?1033

http://www.trigeminal.com/lang/1033/codes.asp?ItemID=9#9

http://www.trigeminal.com/code/guids.bas

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


steel said:
Hello,

Need some expertise for this one...

I have a SQL stored procedure that returns a value 1 if a match is found
in
the table based on 2 parameters.

Here is the SP:
--------------------------------
CREATE PROCEDURE dbo.spCheckDuplicatePOItem_ID
-- example usage: spCheckDuplicatePOItem_ID
'{75C3A07D-67CD-4DBB-95B3-37F73C77C94F}', '20.D'
-- checks if an entry with this POItem_ID and POID already exists. If so,
it
returns a value > 0, else returns 0.
--INPUT PARAMETERS

@POvProject_ID Uniqueidentifier,
@POItem_ID nvarchar(50)
AS
declare @RecCount nvarchar(50)
SELECT @RecCount = (SELECT COUNT(fldItem_ID) AS [Count] FROM
dbo.tblPOItems
WHERE fldItem_ID IN
(SELECT fldItem_ID FROM dbo.tblPOItems
WHERE (FKPOvProject_ID = @POvProject_ID)
Group By fldItem_ID HAVING (fldItem_ID = @POItem_ID)))

select @RecCount as RecCount
return @RecCount
GO
---------------------------
This SP works well using Anaylzer and a RecCount value of 1 gets returned.
The problems lies in the VBA code and when I input the parameters to get a
return value. Here's the VBA:
----------------------------
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection
conn.CursorLocation = adUseClient

Set cmd.ActiveConnection = conn
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spCheckDuplicatePOItem_ID"
cmd.Parameters.Refresh
cmd.Parameters("@POvProject_ID").Value = Me.FKPOvProject_ID
'same value a SP example
cmd.Parameters("@POItem_ID") = "'" & Me.fldItem_ID & "'"
'same value a SP example
Set rs = cmd.Execute

If rs.state = adStateClosed Then
'no connection made
GoTo fldItem_ID_temp_AfterUpdate_Error
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
Else
If rs("RecCount") = 1 Then
If MsgBox("A duplicate PO Item # exists. Please
enter a new PO Item #.", vbCritical, "Duplicate ID") = vbOK Then
fldItem_ID = Null
fldItem_ID_temp = Null
fldItem_ID_temp.SetFocus
Exit Sub
End If
Else
fldItem_ID =
Forms![frm_PurchaseOrders]![PurchaseOrderNumber] & "." & fldItem_ID_temp
End If
End If
---------------------------------
When the line 'Set rs = cmd.Execute' is executed an error is displayed:
"ERROR: Invalid Character value for cast specification"

Any ideas?? Thanks.
Click to expand...
 
G

Guest

Hi Sylvain,

I tried as well but failed.... The SP works OK if I replace the GUID
parameter in the SP to Binary(16) and the syntex is checked OK. When it runs
during the VBA the right result isn't returned.

I'll have to investigate later as I'm on a time limit at the moment. I
instead used a recordset to achieve the same result, but would have preferred
to use a SP.

Thanks for your help.

--
Regards,

Alan


Sylvain Lafontaine said:
I'm not sure but I think that the problem here is that @POvProject_ID is of
type Uniqueidentifier but that you are using a string value. Query Analyser
is able to make the conversion on the fly but not the VBA code. In VBA,
you should use a binary value, if my memory is good.

An easier solution would be to set the type of the parameter to nchar and
make the conversion directly inside the stored procedure. Probably that you
can even use directly the string value in the select query but I'm not sure.

Maybe the following references will be of some value to you:

http://www.trigeminal.com/usenet/usenet011.asp?1033

http://www.trigeminal.com/lang/1033/codes.asp?ItemID=9#9

http://www.trigeminal.com/code/guids.bas

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


steel said:
Hello,

Need some expertise for this one...

I have a SQL stored procedure that returns a value 1 if a match is found
in
the table based on 2 parameters.

Here is the SP:
--------------------------------
CREATE PROCEDURE dbo.spCheckDuplicatePOItem_ID
-- example usage: spCheckDuplicatePOItem_ID
'{75C3A07D-67CD-4DBB-95B3-37F73C77C94F}', '20.D'
-- checks if an entry with this POItem_ID and POID already exists. If so,
it
returns a value > 0, else returns 0.
--INPUT PARAMETERS

@POvProject_ID Uniqueidentifier,
@POItem_ID nvarchar(50)
AS
declare @RecCount nvarchar(50)
SELECT @RecCount = (SELECT COUNT(fldItem_ID) AS [Count] FROM
dbo.tblPOItems
WHERE fldItem_ID IN
(SELECT fldItem_ID FROM dbo.tblPOItems
WHERE (FKPOvProject_ID = @POvProject_ID)
Group By fldItem_ID HAVING (fldItem_ID = @POItem_ID)))

select @RecCount as RecCount
return @RecCount
GO
---------------------------
This SP works well using Anaylzer and a RecCount value of 1 gets returned.
The problems lies in the VBA code and when I input the parameters to get a
return value. Here's the VBA:
----------------------------
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection
conn.CursorLocation = adUseClient

Set cmd.ActiveConnection = conn
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spCheckDuplicatePOItem_ID"
cmd.Parameters.Refresh
cmd.Parameters("@POvProject_ID").Value = Me.FKPOvProject_ID
'same value a SP example
cmd.Parameters("@POItem_ID") = "'" & Me.fldItem_ID & "'"
'same value a SP example
Set rs = cmd.Execute

If rs.state = adStateClosed Then
'no connection made
GoTo fldItem_ID_temp_AfterUpdate_Error
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
Else
If rs("RecCount") = 1 Then
If MsgBox("A duplicate PO Item # exists. Please
enter a new PO Item #.", vbCritical, "Duplicate ID") = vbOK Then
fldItem_ID = Null
fldItem_ID_temp = Null
fldItem_ID_temp.SetFocus
Exit Sub
End If
Else
fldItem_ID =
Forms![frm_PurchaseOrders]![PurchaseOrderNumber] & "." & fldItem_ID_temp
End If
End If
---------------------------------
When the line 'Set rs = cmd.Execute' is executed an error is displayed:
"ERROR: Invalid Character value for cast specification"

Any ideas?? Thanks.
Click to expand...
Click to expand...
 
N

Norman Yuan

I am not sure if the parameter "@POvProject_ID" value is OK or not. However,
for the value of the parameter "@POItem_ID", you do not need to place the
text value between single quote mark, that is,

cmd.Parameters("@POItem_ID") = Me.fldItem_ID

or

cmd.Parameters("@POItem_ID") = CStr(Me.fldItem_ID)

Not

cmd.Parameters("@POItem_ID") = "'" & Me.fldItem_ID & "'"

steel said:
Hello,

Need some expertise for this one...

I have a SQL stored procedure that returns a value 1 if a match is found
in
the table based on 2 parameters.

Here is the SP:
--------------------------------
CREATE PROCEDURE dbo.spCheckDuplicatePOItem_ID
-- example usage: spCheckDuplicatePOItem_ID
'{75C3A07D-67CD-4DBB-95B3-37F73C77C94F}', '20.D'
-- checks if an entry with this POItem_ID and POID already exists. If so,
it
returns a value > 0, else returns 0.
--INPUT PARAMETERS

@POvProject_ID Uniqueidentifier,
@POItem_ID nvarchar(50)
AS
declare @RecCount nvarchar(50)
SELECT @RecCount = (SELECT COUNT(fldItem_ID) AS [Count] FROM
dbo.tblPOItems
WHERE fldItem_ID IN
(SELECT fldItem_ID FROM dbo.tblPOItems
WHERE (FKPOvProject_ID = @POvProject_ID)
Group By fldItem_ID HAVING (fldItem_ID = @POItem_ID)))

select @RecCount as RecCount
return @RecCount
GO
---------------------------
This SP works well using Anaylzer and a RecCount value of 1 gets returned.
The problems lies in the VBA code and when I input the parameters to get a
return value. Here's the VBA:
----------------------------
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection
conn.CursorLocation = adUseClient

Set cmd.ActiveConnection = conn
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spCheckDuplicatePOItem_ID"
cmd.Parameters.Refresh
cmd.Parameters("@POvProject_ID").Value = Me.FKPOvProject_ID
'same value a SP example
cmd.Parameters("@POItem_ID") = "'" & Me.fldItem_ID & "'"
'same value a SP example
Set rs = cmd.Execute

If rs.state = adStateClosed Then
'no connection made
GoTo fldItem_ID_temp_AfterUpdate_Error
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
Else
If rs("RecCount") = 1 Then
If MsgBox("A duplicate PO Item # exists. Please
enter a new PO Item #.", vbCritical, "Duplicate ID") = vbOK Then
fldItem_ID = Null
fldItem_ID_temp = Null
fldItem_ID_temp.SetFocus
Exit Sub
End If
Else
fldItem_ID =
Forms![frm_PurchaseOrders]![PurchaseOrderNumber] & "." & fldItem_ID_temp
End If
End If
---------------------------------
When the line 'Set rs = cmd.Execute' is executed an error is displayed:
"ERROR: Invalid Character value for cast specification"

Any ideas?? Thanks.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Object inproperly defined 5
Help with converting code - import > 65536 from Access 1
Access Query from Excel VBA 5
>> sql output parameters 3
ADODB Recordset Not Loading From Parameter Query 6
Access 2000 ADO Recordset from Stored Procedure 3
Link between ADP and SQL Server 3
Diference between system and user defined functions 1

Top