Problem updating an integer value in SQL Server table from ASP.NET page

[shanef1981]

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?

[shanef1981]

Don't worry - I finally figured it out.

I converted the value to a varchar to see if it would evaluate it properly then, and hey presto! it did...

SET @UPD = @UPD + 'SET TotalPcs = ' + convert(varchar(5), @ShipQty)
 

This seems to have fixed it.

[jhardman]

Hey Shane, welcome to Bytes. You accidentally posted in the "ASP Classic (VBScript)" forum. I've moved your question to the ASP.NET forum, but I see you already figured it out. Just remember that everything posted over HTTP is considered text even if it only contains numbers, so you will need to deal with that one way or another on the backend. And in the future, please post ASP.NET questions in the ASP.NET forum.

Jared