Using ParamByName with ADO.CreateAdoStoredProc()
I have a TC function that uses an SQL server stored procedure; see below. It bombs on the ParamByName line. I've tried the name with and without the @. When I omit the line, it complains (correctly) that a value was not provided for InstanceNumber. Any ideas?
The error message is: An exception occurred in the "GetMachineConfiguration" unit at line
12: Parameter '@InstanceNumber' not found
The TC function:
function GetMachineConfiguration(instanceNumber) {
var qry = ADO.CreateADOStoredProc();
qry.ConnectionString = ConnectionString.ConnectionString();
qry.ProcedureName = "dbo.STUV_GetMachineConfiguration"
qry.Parameters.ParamByName("@InstanceNumber").Value = instanceNumber;
qry.Open();
qry.First();
return qry;
}
The SQL server stored procedure:
ALTER PROCEDURE [dbo].[STUV_GetMachineConfiguration]
(
@InstanceNumber AS INT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT *
FROM MachineData
INNER JOIN MachineTypes ON MachineData.MachineTypeCode = MachineTypes.MachineTypeCode
INNER JOIN MachineConfigs ON
MachineData.MachineTypeCode = MachineConfigs.MachineTypeCode AND
MachineData.MachineLampingCode = MachineConfigs.MachineLampingCode
CROSS JOIN SoftwareData
WHERE MachineData.InstanceNumber = @InstanceNumber
END
The error message is: An exception occurred in the "GetMachineConfiguration" unit at line
12: Parameter '@InstanceNumber' not found
The TC function:
function GetMachineConfiguration(instanceNumber) {
var qry = ADO.CreateADOStoredProc();
qry.ConnectionString = ConnectionString.ConnectionString();
qry.ProcedureName = "dbo.STUV_GetMachineConfiguration"
qry.Parameters.ParamByName("@InstanceNumber").Value = instanceNumber;
qry.Open();
qry.First();
return qry;
}
The SQL server stored procedure:
ALTER PROCEDURE [dbo].[STUV_GetMachineConfiguration]
(
@InstanceNumber AS INT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT *
FROM MachineData
INNER JOIN MachineTypes ON MachineData.MachineTypeCode = MachineTypes.MachineTypeCode
INNER JOIN MachineConfigs ON
MachineData.MachineTypeCode = MachineConfigs.MachineTypeCode AND
MachineData.MachineLampingCode = MachineConfigs.MachineLampingCode
CROSS JOIN SoftwareData
WHERE MachineData.InstanceNumber = @InstanceNumber
END
