In short, ExecuteScalar is a direct shot to the data execution of the data layer with no other interpretation.  The SQL command is handed off and handled as is by the data layer whereas there is some interpretted logic in the ExecuteStoredProcedure (which is generally intended for more simple SPROCS). Generally speaking, I always use the ExecuteScalar, ExecuteNonQuery, etc. anytime that I want to execute a SPROC anyway as it is actually slightly better on the performance side of things.  At any rate, I am glad that you got it going.

Works fine when using ExecuteScalar as you posted Trent. I am not too concerned with the philosophy, but if you feel like giving a brief explanation of why one works and the other doesnt.... or why ExecuteScalar should be used over the other I'll take it. If not, I'm just happy to have something that works because I had this issue in quite a few places with after moving this app to ES.

Thanks as always.

Greg, your right it didnt end up being the problem but OUTPUT is propably technically the right return type to use, I knew it was always going to be a single integer return value so I felt ok using a return value. I may indeed refactor, thanks for the input.

Keith Chisarik

Keith,



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







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.

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

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.

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

Thanks, Edhy! Glad you got it going, Jeff!

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

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

Hi Edhy,

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

Thanks,

Jeff