Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,907
- Office Version
- 365
- Platform
- Windows
Hi
Having a heck of a time finding a way around this. Hope to get some help here.
Stored Proc in MSSQL:
Loading the BU ID variable:
Issue occurs where varBUID returns "" (as string). So load Null instead. I have also left it off entirely, i.e. leave it Empty.
BUID is one of many parameters. All parameters are loaded into an array called varParameters.
When I run the query:
RunQuery is part of my ADO class:
I get the following Error:
-2147217913
Operand type clash: text is incompatible with int
I get that these are different data types, but as I might not have a BU ID, how can I handle the Null?
Having a heck of a time finding a way around this. Hope to get some help here.
Stored Proc in MSSQL:
Code:
CREATE PROC [Schema].[MyProcName]
(
@BusinessUnitID int
,@SomeVal1 nvarchar(50)
, @SomeVal2 int
, @SomeVal3 bit
, @SomeVal4 bit
, @SomeVal5 nvarchar(25)
)
AS
DECLARE @HoldBusinessUnitID int
IF (@BusinessUnitID IS NULL)
SELECT
@HoldBusinessUnitID=MAX(BusinessUnitID) + 1
FROM
[dbo].[BusinessUnits]
ELSE
SET @HoldBusinessUnitID = @BusinessUnitID
-- rest of SP
Loading the BU ID variable:
Code:
varBUID = Evaluate(ThisWorkbook.Names(g_strSELECTED_BUSINESSUNIT).RefersTo)
Select Case varBUID
Case Is = "": varBUID = Null
Case Is > 0: varBUID = CLng(varBUID)
Case Else: strError = "Invalid Business Unit ID": GoTo end_proc
End Select
Issue occurs where varBUID returns "" (as string). So load Null instead. I have also left it off entirely, i.e. leave it Empty.
BUID is one of many parameters. All parameters are loaded into an array called varParameters.
When I run the query:
Code:
Call .RunQuery(g_strINSERT_BUSINESSUNIT, varParameters)
RunQuery is part of my ADO class:
Code:
Public Function RunQuery(ByVal strQueryName As String, Optional ByRef varParms As Variant) As ADODB.Recordset
Dim objRec As ADODB.Recordset
Dim lngParm As Long
With Me.Command
.CommandText = strQueryName
If .Parameters.Count > 0 Then
On Error Resume Next
For lngParm = .Parameters.Count - 1 To 0 Step -1
Call .Parameters.Delete(lngParm)
Next lngParm
On Error GoTo 0
End If
If IsMissing(varParms) Then
Set objRec = .Execute(Options:=4)
Else
Set objRec = .Execute(Parameters:=varParms, Options:=4)
End If
End With
Set RunQuery = objRec
Set objRec = Nothing
End Function
I get the following Error:
-2147217913
Operand type clash: text is incompatible with int
I get that these are different data types, but as I might not have a BU ID, how can I handle the Null?
Last edited: