|
 |
activex_data_objects thread: Output Parameters and stored procedure problem
Message #1 by "Rosalind Philips" <rosalind115@a...> on Wed, 19 Dec 2001 00:12:19
|
|
Hi there. I am having the dreaded "Multiple-step OLE DB operation
generated errors." problem.
My problem is with output parameters.
The relevant code is below. I am using Access 2000, MSSQL 2000, MDAC
2.6. The stored procedure works fine if I remove the output parameter.
The stored procedure works fine with output parameter if I run it from SQL
Analyzer. Does Anybody have any ideas?
Rosalind Philips
rosalind115@a...
With cmdSQL
.ActiveConnection = cnSQL
.CommandText = strStoredProcedureName
.CommandType = adCmdStoredProc
If blnParameterFlag = False Then
'Add Input Parameters
.Parameters.Append .CreateParameter("@TableName",
adVarChar, adParamInput, 100, strSqlTableName)
.Parameters.Append .CreateParameter("@ColumnName",
adVarChar, adParamInput, 100, strSqlColumnName)
.Parameters.Append .CreateParameter("@Value", adDecimal,
adParamInput, 19, DblValue)
.Parameters("@Value").Size = 19
.Parameters("@Value").Precision = 2
.Parameters.Append .CreateParameter("@SchoolYear", adChar,
adParamInput, 9, strSchoolYear)
.Parameters.Append .CreateParameter("@SchoolMonth",
adChar, adParamInput, 2, strSchoolMonth)
.Parameters.Append .CreateParameter("@CoDist", adChar,
adParamInput, 5, strCountyDist)
.Parameters.Append .CreateParameter("@RevCode", adVarChar,
adParamInput, 6, strRevCode)
.Parameters.Append .CreateParameter("@AppMaField", adChar,
adParamInput, 2, strAppMaField)
.Parameters.Append .CreateParameter("@ReasonDesc",
adVarChar, adParamInput, 50, strRemarks)
.Parameters.Append .CreateParameter("@PersonName",
adVarChar, adParamInput, 40, strPersonName)
.Parameters.Append .CreateParameter("@AdjDate",
adDBTimeStamp, adParamInput, 40, dtExtractDate)
'Add Output Parameters
.Parameters.Append .CreateParameter("@ReturnCode",
adInteger, adParamOutput, , 0)
blnParameterFlag = True
Else
.Parameters.Refresh
.Parameters("@TableName").Value = "dbo." + strSqlTableName
.Parameters("@ColumnName").Value = "dbo." +
strSqlColumnName
.Parameters("@Value").Value = DblValue
.Parameters("@SchoolYear").Value = strSchoolYear
.Parameters("@SchoolMonth").Value = strSchoolMonth
.Parameters("@CoDist").Value = strCountyDist
.Parameters("@RevCode").Value = strRevCode
.Parameters("@AppMaField").Value = strAppMaField
.Parameters("@ReasonDesc").Value = strRemarks
.Parameters("@PersonName").Value = strPersonName
.Parameters("@AdjDate").Value = dtExtractDate
.Parameters("@ReturnCode").Value = 0
End If
.Execute , , adExecuteNoRecords
intRetCode = .Parameters("@ReturnCode")
End With
Message #2 by "Zadoyen, Eva" <EZadoyen@s...> on Wed, 19 Dec 2001 09:14:43 -0500
|
|
May be your problem is in "adExecuteNoRecords"
-----Original Message-----
From: Rosalind Philips [mailto:rosalind115@a...]
Sent: Tuesday, December 18, 2001 7:12 PM
To: ActiveX_Data_Objects
Subject: [activex_data_objects] Output Parameters and stored procedure
problem
Hi there. I am having the dreaded "Multiple-step OLE DB operation
generated errors." problem.
My problem is with output parameters.
The relevant code is below. I am using Access 2000, MSSQL 2000, MDAC
2.6. The stored procedure works fine if I remove the output parameter.
The stored procedure works fine with output parameter if I run it from SQL
Analyzer. Does Anybody have any ideas?
Rosalind Philips
rosalind115@a...
With cmdSQL
.ActiveConnection = cnSQL
.CommandText = strStoredProcedureName
.CommandType = adCmdStoredProc
If blnParameterFlag = False Then
'Add Input Parameters
.Parameters.Append .CreateParameter("@TableName",
adVarChar, adParamInput, 100, strSqlTableName)
.Parameters.Append .CreateParameter("@ColumnName",
adVarChar, adParamInput, 100, strSqlColumnName)
.Parameters.Append .CreateParameter("@Value", adDecimal,
adParamInput, 19, DblValue)
.Parameters("@Value").Size = 19
.Parameters("@Value").Precision = 2
.Parameters.Append .CreateParameter("@SchoolYear", adChar,
adParamInput, 9, strSchoolYear)
.Parameters.Append .CreateParameter("@SchoolMonth",
adChar, adParamInput, 2, strSchoolMonth)
.Parameters.Append .CreateParameter("@CoDist", adChar,
adParamInput, 5, strCountyDist)
.Parameters.Append .CreateParameter("@RevCode", adVarChar,
adParamInput, 6, strRevCode)
.Parameters.Append .CreateParameter("@AppMaField", adChar,
adParamInput, 2, strAppMaField)
.Parameters.Append .CreateParameter("@ReasonDesc",
adVarChar, adParamInput, 50, strRemarks)
.Parameters.Append .CreateParameter("@PersonName",
adVarChar, adParamInput, 40, strPersonName)
.Parameters.Append .CreateParameter("@AdjDate",
adDBTimeStamp, adParamInput, 40, dtExtractDate)
'Add Output Parameters
.Parameters.Append .CreateParameter("@ReturnCode",
adInteger, adParamOutput, , 0)
blnParameterFlag = True
Else
.Parameters.Refresh
.Parameters("@TableName").Value = "dbo." + strSqlTableName
.Parameters("@ColumnName").Value = "dbo." +
strSqlColumnName
.Parameters("@Value").Value = DblValue
.Parameters("@SchoolYear").Value = strSchoolYear
.Parameters("@SchoolMonth").Value = strSchoolMonth
.Parameters("@CoDist").Value = strCountyDist
.Parameters("@RevCode").Value = strRevCode
.Parameters("@AppMaField").Value = strAppMaField
.Parameters("@ReasonDesc").Value = strRemarks
.Parameters("@PersonName").Value = strPersonName
.Parameters("@AdjDate").Value = dtExtractDate
.Parameters("@ReturnCode").Value = 0
End If
.Execute , , adExecuteNoRecords
intRetCode = .Parameters("@ReturnCode")
End With
$subst('Email.Unsub').
Message #3 by "David Billingham" <db@p...> on Wed, 19 Dec 2001 16:02:24 -0000
|
|
Hi Rosalind
The line below suggests that you are trying to add a value to the output
parameter
.Parameters.Append .CreateParameter("@ReturnCode",
adInteger, adParamOutput, , 0)
Try removing this, so you get:
.Parameters.Append .CreateParameter("@ReturnCode",
adInteger, adParamOutput)
HTH
Yours sincerely
David Billingham
Senior Software Developer - Poise Associates
This e-mail is subject to copyright and the information in it is confidential. Use of this e-mail or of any information in it other
than by the addressee is unauthorised and unlawful. Please delete this e-mail if you have received it in error.
Message #4 by "Rosalind Philips" <rosalind115@a...> on Wed, 19 Dec 2001 17:22:47
|
|
> May be your problem is in "adExecuteNoRecords"
Thanks. This solved the problem. I guess having an output parameter
means that records are returned even if the stored procedure is just doing
an update/insert.
|
|
|
View Cart
