469,610 Members | 1,746 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

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

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:

Expand|Select|Wrap|Line Numbers
  1. smallPcs = Int(Pcs.Text)
  2. cmd.Parameters.Add("@ShipQty", SqlDbType.SmallInt).Value = smallPcs
  3.  
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):

Expand|Select|Wrap|Line Numbers
  1.     DECLARE @UPD nvarchar(1000)
  2.     SET @UPD = 'UPDATE NewInvoice '
  3.     SET @UPD = @UPD + 'SET ShipmentRef = ' + @ImportFile
  4.     SET @UPD = @UPD + ' WHERE InvoiceNbr = ' + @Invoice
  5.     EXEC sp_executesql @UPD
  6.     SET @UPD = 'UPDATE NewInvoice '
  7.     SET @UPD = @UPD + 'SET TotalPcs = ' + @ShipQty
  8.     SET @UPD = @UPD + ' WHERE InvoiceNbr = ' + @Invoice
  9.     EXEC sp_executesql @UPD
  10.  
I have tried everything I can think of now - anyone else any ideas?
Aug 24 '09 #1

✓ answered by 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...

Expand|Select|Wrap|Line Numbers
  1. SET @UPD = @UPD + 'SET TotalPcs = ' + convert(varchar(5), @ShipQty)
  2.  
This seems to have fixed it.

2 3399
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...

Expand|Select|Wrap|Line Numbers
  1. SET @UPD = @UPD + 'SET TotalPcs = ' + convert(varchar(5), @ShipQty)
  2.  
This seems to have fixed it.
Aug 24 '09 #2
jhardman
3,406 Expert 2GB
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
Aug 25 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Thanks | last post: by
10 posts views Thread by Brian Henry | last post: by
5 posts views Thread by junglist | last post: by
4 posts views Thread by Bass Pro | last post: by
1 post views Thread by daniel_xi | last post: by
11 posts views Thread by Ed Dror | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.