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!