variable not updateing from executescalar command

  • Thread starter Thread starter Eric Cathell
  • Start date Start date
E

Eric Cathell

I have a database of pallets, each pallet has 40 boxes on it. Assigned to
each box is an area. and each pallet consists of boxes from one area. ie
area 6. there are several areas available and I am doing a check to make
sure the pallet the person is working with is in the area he says he wants
to be working in.

in order to accomplish this I have a sp that takes the palletid, the area
that was input and then checks the database to verify the palletid entered
is in that area. if it is it returns a value of 1 if not it returns a value
of 0. The initial value of the variable in the code is 0. I run the sp (both
through query analyzer and debug mode in code) and it updates the
returnvalue correctly...but my variable that is supposed to be filled does
not update...ie:

check=cmd.executescalar

check does not get updated to 1...it stays at zero, even though I saw the
return value update and return a 1.

help......
 
I have an output parameter set. This is only one of about 35 functions that
act similarly in my program. this is the only one giving me fits...here is
my function code.

its not that optimized anymore because I have been trying anything I can
think of to get this to work the way its suppose to...

Public Shared Function rec_CheckArea(ByVal palletid As Integer, ByVal area
As Integer) As Boolean

Dim con As New SqlConnection()

Dim cmd As New SqlCommand()

Dim mycon As New EricDLL.DatabaseConnection(sqlServer, sqlDatabase)

Dim checkme As Integer = Nothing

con.ConnectionString = mycon.ConnectionString

With cmd

..Connection = con

..CommandTimeout = 60

..CommandText = "REC_areaCheck"

..CommandType = CommandType.StoredProcedure

..Parameters.Add("@palletid", SqlDbType.Int)

..Parameters.Add("@area", SqlDbType.Int)

..Parameters.Add("@returnval", SqlDbType.Int)

..Parameters("@palletid").Value = palletid

..Parameters("@area").Value = area

..Parameters("@returnval").Direction = ParameterDirection.Output

End With

con.Open()

checkme = cmd.ExecuteScalar

Debug.WriteLine("value of areacheck=" & checkme)

con.Close()

If checkme = 1 Then

Return True

Else

Return False

End If

End Function
 
if you read the documentation you would see that ExecuteScalar returns the
value of the first column of the first row, not the return value of the sp,
which is in cmd.Parameters("@returnval").Value.

-- bruce (sqlwork.com)
 
ahh..well..i made a mistake..no harm done

in that case should i be using executenonquery? I feel like a dolt because I
cant get this simple process to work the way I have gotten all my other ones
going...of course its been 4 months since i worked on this program..hehe..

Eric
 
Back
Top