Hey Guys,
Hoping someone can help me here - I am at my wits end trying to get this to work.
I have written a stored procedure in SQL Server to move records from one table to another once verified, and then update two fields.
The first field is a varchar field which updates without a hitch.
However, the second is an integer field (type smallint) and despite having this declared in my ASP VB.NET code and in SQL server it refuses to update.
If I put a fixed value in the update statement it works, so I am assuming there is a type conversion issue somewhere.
Here is my value assignment in VB.NET:
-
smallPcs = Int(Pcs.Text)
-
cmd.Parameters.Add("@ShipQty", SqlDbType.SmallInt).Value = smallPcs
-
This is done as the value is typed into a text field (verification is done to make sure only numbers are entered).
The stored procedure when launched executes these two update queries.
The first one works, but the second one fails (no error is returned):
-
DECLARE @UPD nvarchar(1000)
-
SET @UPD = 'UPDATE NewInvoice '
-
SET @UPD = @UPD + 'SET ShipmentRef = ' + @ImportFile
-
SET @UPD = @UPD + ' WHERE InvoiceNbr = ' + @Invoice
-
EXEC sp_executesql @UPD
-
SET @UPD = 'UPDATE NewInvoice '
-
SET @UPD = @UPD + 'SET TotalPcs = ' + @ShipQty
-
SET @UPD = @UPD + ' WHERE InvoiceNbr = ' + @Invoice
-
EXEC sp_executesql @UPD
-
I have tried everything I can think of now - anyone else any ideas?
