Parameterized Store Procedures Failing


Author
Message
Jeff Pagley
Jeff Pagley
StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)
Group: StrataFrame Users
Posts: 223, Visits: 893
Hi SF Team,

I have just setup ES on a server and I began to do some testing.  The code (below) has been running for a couple of years on a local production server with a SQLExpress database, but when this same code (below) runs connecting through ES, I am getting an error (see below).  This piece of code is using a BO to run a parameterized stored procedure.  I am getting the same error when running other BOs using parameterized stored procredures.  It seems this error is associated with the BO.FillByStoredProcedure method.   What is going on?

Thanks,

Jeff

Public Sub FillByCallBackTop1ByCampaignIDAndUserIDAndDateRange(ByVal campaignID As Integer, ByVal userID As Integer, _

ByVal startDate As Date, ByVal endDate As Date, ByVal campaignMaximumCallTries As Integer)

Dim parms(4) As SqlParameter

parms(0) = New SqlParameter("@CampaignID", SqlDbType.Int)

parms(0).Value = campaignID

parms(1) = New SqlParameter("@UserID", SqlDbType.Int)

parms(1).Value = userID

parms(2) = New SqlParameter("@StartDate", SqlDbType.SmallDateTime)

parms(2).Value = startDate

parms(3) = New SqlParameter("@EndDate", SqlDbType.SmallDateTime)

parms(3).Value = endDate

parms(4) = New SqlParameter("@CampaignMaximumCallTries", SqlDbType.SmallInt)

parms(4).Value = campaignMaximumCallTries

Me.FillByStoredProcedure("CampaignContactsView_CallBack_Top_ByCampaignIDUserIDStartEndDate_Select", parms)

End Sub

=========================================================================================

Exception (EnterpriseDataSourceException)

Message = "An error occurred while executing the command on the remote data source. See the inner exception for more details."

InnerException (SqlException): Source=".Net SqlClient Data Provider"; Target=null; Tag=null; Class=16; LineNumber=0; Number=201; Server="TESTSERVER\SQLEXPRESS"; State=4; ErrorCode=-2146232060;

Message = "Procedure or Function 'CampaignContactsView_CallBack_Top1_ByCampaignIDUserIDStartEndDate_Select' expects parameter '@CampaignID', which was not supplied."

StackTrace =

" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior ... (truncated) ...xecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

at MicroFour.StrataFrame.Data.DbDataSourceItem.GetDataTable(DbCommand Command, OnChangeEventHandler CallBack)

at MicroFour.StrataFrame.Data.SqlDataSourceItem.GetDataTable(DbCommand Command, OnChangeEventHandler CallBack)

at MicroFour.StrataFrame.Data.Enterprise.RequestProcessor.ExecuteDataTable(ExecuteDataTableParams params)"

Errors (SqlErrorCollection): Count=1;

#0 = {System.Data.SqlClient.SqlError: Procedure or Function 'CampaignContactsView_CallBack_Top1_ByCampaignIDUserIDStartEndDate_Select' expects parameter '@CampaignID', which was not supplied.}

Procedure = "CampaignContactsView_CallBack_Top1_ByCampaignIDUserIDStartEndDate_Select"

Environment: ThreadIdentity="" DateTime=["2010-01-29 07:53:48.537"] ThreadName=null WindowsIdentity="STRUSS\JPagley" ThreadId="10" DomainName="ProspectHelper.vshost.exe" OSVersion=["Microsoft Windows NT 5.1.2600 Service Pack 3"]

MachineName="1LPJPAGLEY" UserName="JPagley"


Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
Hi Jeff,



I have not used the ES or the method FillByStoredProcedure, here is how I fill the BO using store procedure:





Using cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "dbo.sp_FU_CheckDuplicateInvoice_FileName"



'-- Create and set the parameters

cmd.Parameters.AddWithValue("@TransactionType", Enumerations.TransactionType.FirstUseImport).SqlDbType = SqlDbType.SmallInt

cmd.Parameters.AddWithValue("@PK_Transaction", pPK_Transaction).SqlDbType = SqlDbType.Int

cmd.Parameters.AddWithValue("@FU_CarrierInvoiceNo", pCarrierInvoice_FileName).SqlDbType = SqlDbType.VarChar

cmd.Parameters.AddWithValue("@FU_ImportFileName", pCarrierInvoice_FileName).SqlDbType = SqlDbType.VarChar

cmd.Parameters.AddWithValue("@SearchByInvoice", pSearchByInvoice).SqlDbType = SqlDbType.Bit



'-- Execute the query

Me.FillDataTable(cmd)

End Using





Probable there is a problem with the FillByStoredProcedure but with above code I have never got any problem. Hope it can help you.

Edhy Rijo

Jeff Pagley
Jeff Pagley
StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)
Group: StrataFrame Users
Posts: 223, Visits: 893
Hi Edhy,

That is a very easy code change to make.  I going to make the change and then test it.

Thanks,

Jeff

Jeff Pagley
Jeff Pagley
StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)
Group: StrataFrame Users
Posts: 223, Visits: 893
Hi Edhy,

Yeh..that worked and it was easy   I guess I was doing it the hard way.

I going to have to go back and refactor my code.

Thank You!

Jeff

Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
Hi Jeff,



Glad it worked for you. For now I don't have the need for ES, but it is something that I may start using real soon.

Edhy Rijo

Trent Taylor
Trent Taylor
StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)
Group: StrataFrame Developers
Posts: 6.6K, Visits: 6.9K
Thanks, Edhy! Glad you got it going, Jeff! Smile
Keith Chisarik
Keith Chisarik
StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)
Group: StrataFrame Users
Posts: 939, Visits: 40K
I get a very similar error, code works fine when not using ES. I am using SF method ExecuteStoredProcedure to call the Sproc. The SPROC returns a single value that I capture by setting the parameter.direction = ParameterDirection.ReturnValue. How do I make this work using ES?

Error:

BusinessLayerException
  An error occurred while saving an the data to the server.
EnterpriseDataSourceException
  An error occurred while executing the command on the remote data source.  See the inner exception for more details.
SqlException
  Procedure or function 'GetNextWingForAcademy' expects parameter '@acadpk', which was not supplied.

Source     : MicroFour StrataFrame Business

Calling Code:

Dim retval As New SqlClient.SqlParameter()

retval.ParameterName = "@nextwing"

retval.Direction = ParameterDirection.ReturnValue

MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteStoredProcedure("GetNextWingForAcademy", _

DbCommandExecutionType.ExecuteScalar, New SqlClient.SqlParameter("@acadpk", p_acadpk), retval)

If Not retval.Value Is Nothing Then

r_nextwing = retval.Value

End If



Keith Chisarik
Greg McGuffey
Greg McGuffey
Strategic Support Team Member (2.2K reputation)
Group: Forum Members
Posts: 2K, Visits: 6.6K
Likely, this isn't it, but your sproc defines just one parameter right? @acadpk? I'm asking because it is more usual to use an OUTPUT parameter rather than the return value.
Jeff Pagley
Jeff Pagley
StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)StrataFrame User (241 reputation)
Group: StrataFrame Users
Posts: 223, Visits: 893
Hi Keith,

I have never used the ExecuteStoredProcedure command.   The one thing I see in common with both of our issues is the use of the SqlParameter object.  When I refactored my code to use the SQLCommand object and eliminated the use of the SqlParameter object, it fixed the problem for me. 

I hope this helps.

Jeff

Trent Taylor
Trent Taylor
StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)
Group: StrataFrame Developers
Posts: 6.6K, Visits: 6.9K
Keith,



Do you get the same error when using the ExecuteScalar instead of the ExecuteStoredProcecdure?



Dim cmd As New SqlCommand("dbo.GetNextWingForAcademy")

Dim retval As New SqlClient.SqlParameter()



'-- Set the command type

cmd.CommandType = SqlCommandType.StoredProcedure



retval.ParameterName = "@nextwing"

retval.Direction = ParameterDirection.ReturnValue



cmd.Parameters.Add(New SqlClient.SqlParameter("@acadpk", p_acadpk))

cmd.Parameters.Add(retval)



MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteScalar(cmd)



If Not retval.Value Is Nothing Then



r_nextwing = retval.Value



End If




I didn't run this code, but it should be close. Try running ExecuteScalar to see if you have any different results. This should work. We can discuss philosophy on this later, but let's start from here and we can see where we are.
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search