Stored Procedure Help

Discussion in 'ASP .Net' started by MDB, Jun 21, 2006.

  1. MDB

    MDB Guest

    Hello all, I know this may not be the correct group but was hoping someone
    could help anyway. I have a stored procedure that is not returning the
    correct information. For some reason it is not returning the correct lane or
    reference number and was wondering if someone can take a look and help
    figure out why.

    Here is the asp.net code:

    OleDbCommand cmd = new OleDbCommand ( "Get_CCAuth_Lane", conn );
    cmd.CommandType = CommandType.StoredProcedure;
    OleDbParameter dbLane = cmd.Parameters.Add("@Lane_Nbr",OleDbType.Integer);
    dbLane.Direction = ParameterDirection.ReturnValue;
    OleDbParameter dbRefNbr =
    cmd.Parameters.Add("@Last_Reference_Nbr",OleDbType.Integer);
    dbRefNbr.Direction = ParameterDirection.ReturnValue;

    dr = cmd.ExecuteReader ( );
    dr.Close();

    string sLane = dbLane.Value.ToString();
    string sRefNbr = dbRefNbr.Value.ToString();

    This is returning 0 as lane number when it should be 1 and 1 as sRefNbr
    where it should be 555.


    Here is the stored procedure:

    ALTER Procedure pmmobile.Get_CCAuth_Lane (out @lane_nbr int, out
    @Last_Reference_Nbr int)
    begin

    DECLARE @CurrentTime DateTime;
    set @CurrentTime = now(*);

    getloop:
    WHILE datediff(Second ,@CurrentTime, now(*)) < 30 LOOP
    Select FIRST(Lane_Nbr), Last_Reference_Nbr
    INTO @lane_nbr, @Last_Reference_Nbr
    FROM ccauth_lane WHERE status = 'I' or datediff(Minute, last_accessed,
    now(*)) >= 2;

    IF @lane_nbr IS NOT NULL THEN
    UPDATE ccauth_lane SET status = 'A', last_accessed = now(*) where lane_nbr
    = @lane_nbr AND (status = 'I' or datediff(Minute, last_accessed, now(*)) >=
    2);
    IF @@ROWCOUNT = 1 THEN
    return;
    END IF;
    END IF;
    END LOOP;
    SET @lane_nbr = -99;
    SET @Last_Reference_Nbr = -99;
    END
     
    MDB, Jun 21, 2006
    #1
    1. Advertising

  2. MDB

    MDB Guest

    Figured it out, thanks for anyone who looked.

    Had the Direction set wrong.......


    "MDB" <> wrote in message
    news:...
    > Hello all, I know this may not be the correct group but was hoping someone
    > could help anyway. I have a stored procedure that is not returning the
    > correct information. For some reason it is not returning the correct lane
    > or reference number and was wondering if someone can take a look and help
    > figure out why.
    >
    > Here is the asp.net code:
    >
    > OleDbCommand cmd = new OleDbCommand ( "Get_CCAuth_Lane", conn );
    > cmd.CommandType = CommandType.StoredProcedure;
    > OleDbParameter dbLane = cmd.Parameters.Add("@Lane_Nbr",OleDbType.Integer);
    > dbLane.Direction = ParameterDirection.ReturnValue;
    > OleDbParameter dbRefNbr =
    > cmd.Parameters.Add("@Last_Reference_Nbr",OleDbType.Integer);
    > dbRefNbr.Direction = ParameterDirection.ReturnValue;
    >
    > dr = cmd.ExecuteReader ( );
    > dr.Close();
    >
    > string sLane = dbLane.Value.ToString();
    > string sRefNbr = dbRefNbr.Value.ToString();
    >
    > This is returning 0 as lane number when it should be 1 and 1 as sRefNbr
    > where it should be 555.
    >
    >
    > Here is the stored procedure:
    >
    > ALTER Procedure pmmobile.Get_CCAuth_Lane (out @lane_nbr int, out
    > @Last_Reference_Nbr int)
    > begin
    >
    > DECLARE @CurrentTime DateTime;
    > set @CurrentTime = now(*);
    >
    > getloop:
    > WHILE datediff(Second ,@CurrentTime, now(*)) < 30 LOOP
    > Select FIRST(Lane_Nbr), Last_Reference_Nbr
    > INTO @lane_nbr, @Last_Reference_Nbr
    > FROM ccauth_lane WHERE status = 'I' or datediff(Minute, last_accessed,
    > now(*)) >= 2;
    >
    > IF @lane_nbr IS NOT NULL THEN
    > UPDATE ccauth_lane SET status = 'A', last_accessed = now(*) where
    > lane_nbr = @lane_nbr AND (status = 'I' or datediff(Minute, last_accessed,
    > now(*)) >= 2);
    > IF @@ROWCOUNT = 1 THEN
    > return;
    > END IF;
    > END IF;
    > END LOOP;
    > SET @lane_nbr = -99;
    > SET @Last_Reference_Nbr = -99;
    > END
    >
     
    MDB, Jun 21, 2006
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. TJS
    Replies:
    4
    Views:
    2,138
  2. Jeff Thur
    Replies:
    2
    Views:
    1,076
    Guest
    Feb 7, 2005
  3. rob
    Replies:
    0
    Views:
    449
  4. rob
    Replies:
    0
    Views:
    349
  5. Mike P
    Replies:
    0
    Views:
    3,372
    Mike P
    Jun 19, 2006
Loading...

Share This Page