calling the same store procedure repeatly, but only work in the first time

I had try calling a function, that call a store procedure, repeatly using a for loop, but I notice it will only get the expected part_id in the first time, and return an empty string sub-sequentially without throwing an exception. So I had try using a sql query instead, but the same thing happen. Below is my function, can you point out to me what's wrong?

My original version that calls a store procedure

Public

SharedFunction getPartId(ByVal part_supplierserialnumberAsString)AsStringDim mySqlCommandAsNew SqlCommandDim mySqlConnectionAs SqlConnection =New SqlConnection(GetERATSConnectionString())Dim myPart_idAsString

mySqlCommand.CommandType = CommandType.StoredProcedure

mySqlCommand.CommandText = "getPartId"

mySqlCommand.Connection = mySqlConnection

mySqlCommand.Parameters.Add(

New SqlParameter("@.part_supplierserialnumber", part_supplierserialnumber))Try

mySqlConnection.Open()

myPart_id = mySqlCommand.ExecuteScalar()

Catch exAs Exception

myPart_id = ""

Finally

mySqlConnection.Close()

mySqlConnection.Dispose()

EndTryReturn myPart_idEndFunction

My Store procedure

create procedure getPartId
@.part_supplierserialnumber as nvarchar(50)
as

select top 1 part_id from tblPtSingapore where part_supplierserialnumber = @.part_supplierserialnumber order by part_datecreated desc

GO

The new version I tried which happen the same thing

PublicSharedFunction getPartId(ByVal part_supplierserialnumberAsString)AsStringDim myPart_idAsStringDim strSqlAsString = "select top 1 part_id from tblPtSingapore where part_supplierserialnumber = '" & part_supplierserialnumber & "' order by part_datecreated desc"Dim mySqlConnectionAs SqlConnection =New SqlConnection(GetERATSConnectionString())Dim mySqlCommandAsNew SqlCommand(strSql, mySqlConnection)Try

mySqlConnection.Open()

myPart_id = mySqlCommand.ExecuteScalar()

Catch exAs Exception

myPart_id = ""

Finally

mySqlConnection.Close()

mySqlConnection.Dispose()

EndTryReturn myPart_idEndFunction

I tested your stored procedure. It works.

Here is my test code on the page load event. It retrieves partid for the for loop.

--code

Dim mystring1, mystring2 As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim a(3) As String
a(0) = "abc"
a(1) = "xyz"
a(2) = "hij"
Dim i As Integer = 0
For i = 0 To 2
mystring1 = Convert.ToString(getPartId(a(i)))
mystring2 = mystring2 + mystring1 + "+"
Label1.Text = mystring2

Next
End Sub

--code

But your new version should work with the parameterized query. Not this one with misplaced single quotas. It didn't run.

This one works:

.....

Dim strSql As String = "SELECT top 1 part_id FROM tblPtSingapore where part_supplierserialnumber=@.part_supplierserialnumber order by part_datecreated desc"
Dim mySqlConnection As SqlConnection = New SqlConnection(connString)
Dim mySqlCommand As New SqlCommand(strSql, mySqlConnection)
mySqlCommand.Parameters.Add(New SqlParameter("@.part_supplierserialnumber", part_supplierserialnumber))

......

Check your test data and the for loop too.

|||

I have check the for loop and test data, it seems all rite.. I tried your query too..

I have try calling that store procedure getPartId from another store procedure, but it just doesn't assign the variable with the part id I got from the getPartId, though it does return a part id if I call getPartId in the sql query analyzer.

CREATE PROCEDURE PRVtoRIP
@.part_supplierserialnumber NVARCHAR(30)
AS

declare @.newpart_id as nvarchar(35)
/* get the unique PK for the record, cos at application lvl doesn't work */
Execute @.newpart_id = getPartId @.part_supplierserialnumber

UPDATE tblPtSingapore
SET part_status = 'RIP',
part_datemodified = getDate()
WHERE part_id = @.newpart_id
and part_status = 'PRV'

select @.newpart_id as new_part_id
GO