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 Shared Function getPartId(ByVal part_supplierserialnumber As String) As String

Dim mySqlCommand As New SqlCommand

Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString())

Dim myPart_id As String

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 ex As Exception

myPart_id = ""

Finally

mySqlConnection.Close()

mySqlConnection.Dispose()

End Try

Return myPart_id

End Function

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

Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String

Dim myPart_id As String

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(GetERATSConnectionString())

Dim mySqlCommand As New SqlCommand(strSql, mySqlConnection)

Try

mySqlConnection.Open()

myPart_id = mySqlCommand.ExecuteScalar()

Catch ex As Exception

myPart_id = ""

Finally

mySqlConnection.Close()

mySqlConnection.Dispose()

End Try

Return myPart_id

End Function