Output variables to ASP.NET page problem

Question

Output variables to ASP.NET page problem

Dylan Davis

Ten Centuries

Points: 1283
More actions
June 21, 2005 at 4:20 pm

#186278

I've got a sproc that has to use dynamic sql to set the values of several output parameters. Using the EXEC statement and global temp tables, I was able to set the parameters to the values I needed from each query. Everything runs fine in Query Analyzer, even when logged in as the user that the webpage uses. Once I try to use the sproc from the webpage, the parameters returned (that should have values) are all set to 0, unless the parameter was supposed to return null, in which case it does. If I hard code a value for the output parameter in the sproc, then it will output the value. Anyone have any ideas why it won't return the results of my EXEC statement?
Thanks in advanced for any help!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 1

Will you be able to let me know how you have called the SP from the webpage? I may be able to give you a few suggestions in that case...

Dylan Davis Ten Centuries Points: 1283 More actions · Answer 2

Dim conn1 As New SqlConnection

Dim command1 As New SqlCommand

Connection(conn1) 'sets connection string

Command(command1, conn1, "sp_summary") 'sets command1's connection, text, and commandType properties

With command1.Parameters

.Add("@zone", "")

.Add("@region", "")

.Add("@broker", "")

.Add("@date", "")

.Add("@menrolled", SqlDbType.VarChar, 20) 'parameter index of 4 (for use in loop below)

.Add("@yenrolled", SqlDbType.VarChar, 20)

.Add("@moperators", SqlDbType.VarChar, 20)

.Add("@yoperators", SqlDbType.VarChar, 20)

.Add("@mredemptions", SqlDbType.VarChar, 20)

.Add("@yredemptions", SqlDbType.VarChar, 20)

.Add("@mcases", SqlDbType.VarChar, 20)

.Add("@ycases", SqlDbType.VarChar, 20)

.Add("@mtotal", SqlDbType.VarChar, 20)

.Add("@ytotal", SqlDbType.VarChar, 20)

.Add("@mnonrefund", SqlDbType.VarChar, 20)

.Add("@ynonrefund", SqlDbType.VarChar, 20)

End With

'use the index of 4 to start setting the parameter directions

For i As Integer = 4 To command1.Parameters.Count - 1

command1.Parameters(i).Direction = ParameterDirection.Output

conn1.Open()

command1.ExecuteNonQuery()

'If I were to set the value of the @menrolled param to "test" here,

' then litCurEnrolled would show up as "test"

litCurEnrolled.Text = .Item("@menrolled").Value

litYTDEnrolled.Text = .Item("@yenrolled").Value

litCurOperators.Text = .Item("@moperators").Value

'****** and on through all the output params

conn1.close()

I cut out some non pertinent code for brevity. Hopefully this helps give you enough info on how I'm going about this.

Thanks!

dmbaker SSCertifiable Points: 5139 More actions · Answer 3

Don't know if it's related to the problem or even if it's a problem, but you appear to be missing the parameter for the proc's return value (which is parameter zero). The first parameter you add should probably be for the proc return value, ala:

.Add("ReturnValue", SqlDbType.Int)

(and then be sure to set the direction of this parameter to ParameterDirection.ReturnValue).

Again, not sure if it has anything to do with your problem, just seems odd to not see the return value parameter there and I'm wondering if .NET is getting things confoozed because if it. Anyway, IMHOP, you should always be sure to declare the parm for the return value.