guid parameter with SQL stored procedures

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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...
 
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...
 
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...
 
Back
Top