looping and calling stored procedure returning 1 result is all
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>
