In your VB code you can comment out the time out setting, to use the server default setting:
' objCommand.CommandTimeout = 0
In your sp code, put BEGIN TRANSACTION before BEGIN TRY, also is a good practice name transactions.
BEGIN TRANSACTION trShipmentDetails
BEGIN TRY
INSERT INTO ShipmentDetails (MailLogId, RxNumber, ProductName, Quantity, Charge)
VALUES (@MailLogId, @RxNumber, @ProductName, @Quantity, @Charge) --notice they are the input parameters above
SET @numrecs = @@rowcount
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record added'
-- SET @recn = 0 not necessary because you have previously assign this value
END
ELSE
SET @recn = @numrecs
COMMIT TRANSACTION trShipmentDetails
END TRY
BEGIN CATCH
DECLARE @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@errmessage varchar(1000)
SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@errmessage = ERROR_MESSAGE()
ROLLBACK TRAN trShipmentDetails
SET @errmsg = CONVERT(varchar(200), @errmessage)
SET @spstat = 0 --error occured
SET @recn = 0
END CATCH
RETURN @spstat
You're using and output parameter in your sp, but you're not getting it in your VB code, perhaps this could help you to find where is the failure:
Dim recno As Integer
objCommand.ExecuteNonQuery()
Integer.TryParse(command.Parameters(command.Parameters.Count - 1).Value.ToString(), recno)
If recno = 0 Then...
I hope this will be useful 4 you 🙂
Best regards!