473,839 Members | 1,358 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

2 New Member
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
2 3702
shanef1981
2 New Member
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 Recognized Expert Specialist
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

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

Similar topics

1
3555
by: Thanks | last post by:
I have a routine that is called on Page_Init. It retrieves folder records from a database which I display as Link Buttons in a table cell. I set the table cell's bgcolor to a default color (say black for example). I am dynamically creating the LinkButton controls and adding them into the table cell and I've also hooked up an event handler for each LinkButton's Click event. This all works fine. Now in the Link Button's Click event...
10
3093
by: Brian Henry | last post by:
Hi, I am having a problem with an attachment system I made... it works with files up to ~3MB in size then after that if you try to upload a file it just goes to a "Page can not be displayed" page like it tried to do it but errored... I thought changeing the max size for the file input box to about 100mb would fix it but nope here is my page code and my code behind code... ===============
5
2045
by: junglist | last post by:
Hi guys, I've been trying to implement an editable datagrid and i have been succesful up to the point where i can update my datagrid row by row. However what used to happen was that once i updated one row, all of them were updated so i immediatelly figured out that i have to include the id of every entry in the update statement. This is where the problem is raised. My database is an Access database. The table i am updating contains a Date...
4
2096
by: Bass Pro | last post by:
Hi, I am creating textbox, radiobuttonlist and checkboxlist dynamically depending on data from a table. It is a questionnaire. I add the control on a Panel control during the 1st load_page event. Each question is displayed and answered, then result written to a SQL table. Then the next question is read from a table and displayed using the load_page event again. The questions display and function perfectly. The user anwers the question...
1
2042
by: r2destini | last post by:
Hi Friends, I am new to .Net. So I don't know much. I am facing a problem in updating database through ADO.Net I am creating the dataset and there is no problem in the updation and deletion or insertion in the dataset but when I am updating the
1
4121
by: daniel_xi | last post by:
Hi all, I am running a VS 2003 .NET project on my client machine (Win 2000 SP4, ..NET framework 1.1), running an ASP.NET application on a remote web server (Win 2000 Server, IIS 6.0, .NET framework 1.1). The application implements a Progress Bar webcontrol, that pops up in a window, using the HttpHandler interface, on the event of a button click. The handler's process request routine reads the status of the progress by querying the...
2
4377
by: toddw607 | last post by:
Hi Everyone! I have a ASP.NET webform that brings data in from SQL Server 2000 and displays it on a page. I want to update the webpage while in IE using the following code: <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data.OleDb" %> <html>
11
4087
by: Ed Dror | last post by:
Hi there, I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro. I have a Price page (my website require login) with GridView with the following columns PriceID, Amount, Approved, CrtdUser and Date And Edit and Delete buttons
2
2644
by: sirdavethebrave | last post by:
Hi guys - I have written a form, and a stored procedure to update the said form. It really is as simple as that. A user can go into the form, update some fields and hit the update button to update the information which is stored in a SQL database. In testing we noticed that the form was updating correctly but the update mechanism was also updating the first record of the table in the sql database every time. No error messages are on...
0
9855
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10587
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10649
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10295
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9426
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7829
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7018
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5682
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.