Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Stored Procedure Help

Reply
Thread Tools

Stored Procedure Help

 
 
MDB
Guest
Posts: n/a
 
      06-21-2006
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


 
Reply With Quote
 
 
 
 
MDB
Guest
Posts: n/a
 
      06-21-2006
Figured it out, thanks for anyone who looked.

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


"MDB" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
help! jdbtable call stored procedure rob Java 0 09-21-2005 03:37 AM
Re: need help with a complicated stored procedure please iixv Microsoft Certification 0 07-30-2005 04:34 PM
Help with Code to Execute a Stored Procedure Jeff Thur ASP .Net 2 02-07-2005 08:58 PM
error msg help needed on stored procedure TJS ASP .Net 4 11-12-2003 11:15 PM



Advertisments