Stored Procedure Returns Same Value Using ExecuteScalar and Sql Server

  • Thread starter Thread starter Mike H
  • Start date Start date
M

Mike H

When running the sp from Query Analyzer, or even firing it from the
command window in VS.NET, the sp returns the correct value (an
incremented one). However, in code it doesn't update the variable i'm
assigning the result too. I'm guessing it's some sort of caching???

Here is the sp:

ALTER PROCEDURE dbo.GetAndLockNextRecord
(
@JobName As NVARCHAR(30)
)
AS

DECLARE @EDID AS Integer

SET @EDID = (SELECT TOP 1 EDID FROM EDInfo WHERE
ApplicationInformationCaptured = 0)

UPDATE EDInfo SET ApplicationInformationCaptured = 1 WHERE EDID =
@EDID

SELECT @EDID
RETURN

Here is the chunk of code that hates me:


mcmdGetNextRecord = New SqlCommand("GetAndLockNextRecord", New
SqlConnection(clsDatabaseSupport.connString))
mcmdGetNextRecord.CommandType = CommandType.StoredProcedure
mcmdGetNextRecord.Parameters.Add("@JobName",
SqlDbType.NVarChar)


mcmdGetNextRecord.Parameters("@JobName").Value = sJobName
mcmdGetNextRecord.Connection.Open()

While bGoodToGo
mcmdGetNextRecord.Parameters("@JobName").Value = sJobName
iEDID = mcmdGetNextRecord.ExecuteScalar

If iEDID = 0 Then
bGoodToGo = False
End If
CatalogFile(iEDID)
End While
 
Bill- thanks for the quick reply.

I've tried executing this code in a variety of ways, including using
output parameters. I should note that the query returns the correct
value the first time it is executed, but not subsequent times. I.e. If
the next EDID parameter is 11, it will return 11 first, then always
return 11.

Any ideas?

Thanks...
Mike
 
This problem has nothing to do with the code- it works fine. Another
command was resetting the database field, the problem is entirely my
fault.

Thanks for the help.

Mike
 
Back
Top