Jaymond Flurrie
Well-known Member
- Joined
- Sep 22, 2008
- Messages
- 921
- Office Version
- 365
- Platform
- Windows
Hi,
I'm creating my first stored procedure ever and it looks relatively good so far. The problem is with passing the parameters, more exactly:
Arguments are of wrong type, are out of acceptable range, or are in confilict with one another
The essential part of the stored procedure looks like:
while the essential code calling it from VBA looks like:
and in that rs.Open comes that error.
What have I done wrong, how do I fix it?
I'm creating my first stored procedure ever and it looks relatively good so far. The problem is with passing the parameters, more exactly:
Arguments are of wrong type, are out of acceptable range, or are in confilict with one another
The essential part of the stored procedure looks like:
Code:
CREATE PROC GetUserAuthForApp
@User varchar(7),
@application int
AS
while the essential code calling it from VBA looks like:
Code:
Dim strConn As String 'Connection string to SQL Server
Dim strSQLtoExecute As String 'SQL query string to execute
Dim oConn As ADODB.Connection 'Object for connecting
Dim rs As ADODB.Recordset 'Object for recordset
Dim cmd As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmApplication As ADODB.Parameter
Dim stProcName As String 'Stored Procedure name
Function LoadAuthorizationInfoFromSQL(iApp As Integer, strUser As String) As Variant
'Define connection string and open the connection
strConn = ConnectToSQL()
'Initialize variables for database connections
Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
oConn.Open strConn
stProcName = "GetUserAuthForApp" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdStoredProc 'Define the ADODB command
cmd.ActiveConnection = oConn 'Set the command connection string
cmd.CommandText = stProcName 'Define Stored Procedure to run
Set prmUser = cmd.CreateParameter("@User", adVarChar, adParamInput, 7)
cmd.Parameters.Append prmUser
Set prmApplication = cmd.CreateParameter("@application", adInteger, adParamInput)
cmd.Parameters.Append prmApplication
prmUser.Value = "'" & strUser & "'" 'Tried also without quotes, no difference
prmApplication.Value = iApp
'Execute stored procedure and return to a recordset
rs.Open cmd.Execute
and in that rs.Open comes that error.
What have I done wrong, how do I fix it?