looping and calling stored procedure returning 1 result is all

**lleemon** · May 20th, 2004, 10:12 AM

I have created vcd_isnew stored procedure that looks in the database for a certain id passed into it and returns a value indicating if found or not.

So I loop through a list of id's that need to be checked and I can display to screen all the ids (without calling SP). However when I add my SP code I just get the first id results back and that is all.

Anyone seen this issue before? If so what is the best way to handle this?

Thanks in advance.

The partial code I am using is:

Code:

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" Debug="true" Trace="True" %>
<%@ import Namespace="System.XML" %>
<%@ import Namespace="system.xml.xmlnodetype" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">

    Sub Page_Load(S As Object, E As EventArgs)
         trace.tracemode=TraceMode.SortByTime
		 
		 Dim conData as SqlConnection
		 Dim cmdSelect as SqlCommand
		 Dim paramReturnValue as SqlParameter
		 Dim iResult as Integer
    
	
		 conData = New SqlConnection("Server=xx.xx.xx.xx;uid=name;pwd=1111111;database=dbname")
		cmdSelect = New SqlCommand("vcd_isnew", conData)
		cmdSelect.CommandType = CommandType.StoredProcedure
		paramReturnValue = cmdSelect.Parameters.Add("RETURN_VALUE", SqlDBType.Int)
	
	'............
			 
			 'Open connection
    		 conData.Open()
			 
             DO WHILE Xmltr1.Read()
			   
				'.......................
				
				paramReturnValue.Direction = ParameterDirection.ReturnValue
				cmdSelect.Parameters.Add("@PID", strPrdId)
				cmdSelect.Parameters.Add("@TIMESTAMP", sTimeStamp)
				cmdSelect.ExecuteNonQuery()
				iResult = cmdSelect.Parameters("RETURN_VALUE").value
				
				trace.write("Result=",iResult)
				 If iResult = -0 Then
					'Output that result if -0 is returned
					trace.write("Price=",strPRICE)
				 End if

             LOOP
			 
			'Close the connecton
			conData.Close()
			 
         CATCH ex1 as exception
    
         FINALLY
             Xmltr1.Close()
    
         END TRY
    End Sub

</script>
<html>
<head>
    <title>Loop Test</title>
</head>
<body bgcolor="#ffffff">
    <form runat="server">
        <ASP:placeholder id="Err" runat="server"></ASP:placeholder>
        See The Trace For More Details 
    </form>
</body>
</html>

**dj4uk** · May 21st, 2004, 04:00 AM

Since when has -0 been a number? Surely it should be 0?

DJ

**lleemon** · May 21st, 2004, 12:30 PM

Well, I can try and change this. In my stored procedure I have the following:

Code:

IF @RCNT > 0
  RETURN -1
ELSE
  RETURN -0
GO

Thread: looping and calling stored procedure returning 1 result is all

Thread Tools

Display

looping and calling stored procedure returning 1 result is all

Posting Permissions