User-1305530094 posted
oned_gk
Try move Using cmd inside
Dim Data = PtRegData(0).PartnerData
For i As Integer = 0 To Data.Count - 1
'Using cmd
'End Using
Next
I moved my cmd inside and it did not work
Public Class PartnerData
Public Property RecID As Integer
Public Property PrtStatus As Integer
Public Property PtFilenum As Integer
Public Property PrtFilenum As Integer
Public Property FullName As String
Public Property DOB As String
End Class
<WebMethod> _
Public Function TblRegJoinUpd3(ByVal Data As List(Of TblRegJoindata))
Dim Prtdata As New List(Of PartnerData)()
Dim constr As String = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString
For i As Integer = 0 To Data.Count - 1
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("TblRegJoinDynHdlr", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Clear()
If (String.IsNullOrEmpty(Data(i).RecID)) Then
cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = DBNull.Value
Else
cmd.Parameters.Add("@RecID", SqlDbType.Int).Value = Convert.ToInt32(Data(i).RecID)
End If
If (String.IsNullOrEmpty(Data(i).ptfilenum)) Then
cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = DBNull.Value
Else
cmd.Parameters.Add("@ptfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).ptfilenum)
End If
If (String.IsNullOrEmpty(Data(i).prfilenum)) Then
cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = DBNull.Value
Else
cmd.Parameters.Add("@prfilenum", SqlDbType.Int).Value = Convert.ToInt32(Data(i).prfilenum)
End If
cmd.Parameters.Add("@PrtStatus", SqlDbType.Int).Value = (Data(i).PrtStatus)
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
Prtdata.Add(New PartnerData() With { _
.RecID = sdr("RecID").ToString(), _
.PrtFilenum = sdr("PrtFilenum").ToString(), _
.PtFilenum = sdr("PtFilenum").ToString(), _
.FullName = sdr("FullName").ToString(), _
.PrtStatus = sdr("PrtStatus").ToString(), _
.DOB = sdr("DOB").ToString() _
})
End While
End Using
con.Close()
End Using
End Using
Next
Return JsonConvert.SerializeObject(Prtdata)
'Return Nothing
End Function
MY SP is
ALTER PROCEDURE [dbo].[TblRegJoinDynHdlr]
-- Add the parameters for the stored procedure here
(
@RecID int,
@PrtStatus int,
@ptfilenum int,
@prfilenum int
)
AS
BEGIN
DECLARE @validRecID int
SET @validRecID = (SELECT COUNT(*) as numRows FROM TblRegjoin WHERE RecID=@RecID)
SET NOCOUNT ON;
IF (@validRecID = 0)
BEGIN
SET NOCOUNT ON;
INSERT INTO TblRegjoin(PtFilenum, PrtFilenum) Values (@ptfilenum, @prfilenum)
SELECT TblRegjoin.RecID, TblRegjoin.PtFilenum, TblRegjoin.PrtFilenum, CAST(TblRegjoin.PrtStatus As int) AS PrtStatus , TblReg.FullName, CONVERT(VARCHAR(24), TblReg.DOB, 113) AS DOB
FROM TblRegjoin INNER JOIN TblReg ON TblRegjoin.PrtFilenum = TblReg.Filenum WHERE (TblRegjoin.PtFilenum = @ptfilenum)
END
ELSE IF (@validRecID>0)
BEGIN
SET NOCOUNT ON;
UPDATE TblRegjoin
SET PrtStatus=@PrtStatus
WHERE RecID= @RecID
SELECT TblRegjoin.RecID, TblRegjoin.PtFilenum, TblRegjoin.PrtFilenum, CAST(TblRegjoin.PrtStatus As int) AS PrtStatus , TblReg.FullName, CONVERT(VARCHAR(24), TblReg.DOB, 113) AS DOB
FROM TblRegjoin INNER JOIN TblReg ON TblRegjoin.PrtFilenum = TblReg.Filenum WHERE (TblRegjoin.PtFilenum = @ptfilenum)
END
END
PS. Without the loop (for single record operation) the code works fine, I update/insert and get the corresponding values back
