.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Varchar to Numeric

Posted By:      Posted Date: September 21, 2010    Points: 0   Category :Windows Application


I have a textbox where I can enter a number. If the number exists in my sql database then it would change the label accordingly. It seems to work with any input number except for numbers starting with zero. If I input a number that is 12 characters or more and  starting with zero(089470211000) do not get an error. If it is 11 characters or less  and beginning with zero (I get the error: Arithmetic Overflow error converting varchar to data type numeric.

Imports System.Data.SqlClient
Public Class ValidateForm

  Private Sub MineralCloseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MineralCloseButton.Click
  End Sub
  Private Sub CheckButton_Click(

View Complete Post

More Related Resource Links

Error converting data type varchar to numeric.


I create a form to record data using ASP.NET and SQL database...
when i run the project show error "Error converting data type varchar to numeric"

what's the problem actually.i try to change in datatype in database..
but still showing error...


Error converting data type varchar to numeric



I am trying to execute below query.



CAST('123,456,789.00' AS DECIMAL(11,2))

"IN" operator and error converting data type varchar to numeric



I hope someone can help me figure this out please...

I have this query that returns some student data - specifically the student's names, and some classes they're enroled in. What I'm trying to do is return multiple rows using the LIKE operator.

However when I run it and plug in the parameters I get the errror - "Error converting data type varchar to numeric". This error doesn't occur when there is only ONE studentId. Only when there are multiple studentIds. I know it's to do with the string of studentIds being passed, but I'm not sure how to fix it?

Should I be using varchar as the datatype to pass the studentIds? Or do I need to cast this as something else?

Any ideas, much appreciated.



	@StudentIds varchar(8000),
	@ProfileId numeric,
	@Fname varchar(50) OUTPUT,
	@Lname varchar(50) OUTPUT,
	@ClassName varchar (100) OUTPUT,
	@ClassName2 varchar (100) OUTPUT,
	@ClassName3 varchar (100) OUTPUT,
	@ClassName4 varchar (100) OUTPUT,
	@Class numeric OUTPUT,
	@Class2 numeric OUTPUT,
	@Class3 numeric OUTPUT,
	@Class4 numeric OUTPUT


@Fname = Fname, @Lname = Lname, @ClassName = C.ClassName, @ClassName2 = C2.ClassName, @ClassName3 = C3.ClassName, @ClassName4 = C4.ClassName,

Problem converting varchar values to numeric format for comparison


I've got an SQL-2008 database containing our network inventory (it's from LanSweeper - a low-end and very recommendable network management tool). In a table are all logins with the IP-address of that moment, where the IP-address is defined as VARCHAR(15).

I need to compare IP addresses with those in the table defining the IP segments, where the segment addresses are also defined as VARCHAR(15).

In order to be able to compare reliably, I need to convert the addresses to numeric format. I cooked up the following SELECT statement for that:

SELECT TOP (100) PERCENT Ipaddress, (
CAST(SUBSTRING(Ipaddress, 1, CHARINDEX('.', Ipaddress, 1) - 1) AS DEC) * 1000000000 +
CAST(SUBSTRING(Ipaddress, CHARINDEX('.', Ipaddress, 1) + 1, CHARINDEX('.', Ipaddress, CHARINDEX('.', Ipaddress, 1) + 1) - 1) AS DEC) * 1000000) +
CAST(SUBSTRING(Ipaddress, CHARINDEX('.', Ipaddress, CHARINDEX('.', Ipaddress, 1) + 1) + 1, CHARINDEX('.', Ipaddress, CHARINDEX('.', Ipaddress, CHARINDEX('.', Ipaddress, 1) + 1) + 1) - 1) AS DEC) * 1000 +
CAST(SUBSTRING(Ipaddress, CHARINDEX('.', Ipaddress, CHARINDEX('.', Ipaddress, CHARINDEX('.', Ipaddress, 1) + 1) + 1) + 1, 3) AS DEC) AS IP, Computername, logontime, Username, ADsite,
FROM dbo.tblCPlogoninfo

Error converting data type varchar to numeric in SQL SERVER

Hi frendz..
Demo Example is...

alter FUNCTION [dbo].[Return_Example] 
@a numeric(18,4),
@b numeric(18,4)
RETURNS numeric(18,4)
@division numeric(18,4),
@reminder numeric(18,4),
@Num2 varchar(100),
@c numeric(18,4)

set @division=floor(@a/@b)
set @reminder=@a%@b
----Here output will be a concatenated
set @Num2= cast((@division +'.'+ @reminder) as numeric(10,3)) 
set @c=cast(@Num2 as numeric(18,3))


Execute :

exec Return_Example 50 ,5

Error converting data type varchar to numeric.


I have the trivial problem ... I can not do SQL Insert:

 SqlDataSource1.InsertCommand = "insert into TABLE (ID, number) values('" & ID.Text & "', '0,21')"

Then I get the message: System.Data.SqlClient.SqlExceptio

Combining email and numeric regular expressions

Hi, I am using the following regular expression within an XSD to validate a combination of email ID's and six digit employee ID's separated by a semicolon (;). for example : vedansh.mm@fgfgfg.com;ghgh@ff.com;333333;555555 ^((\s*;?\s*[0-9a-zA-Z_]([-.\w]*[0-9a-zA-Z_])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})+\s*(;\d{6})*)*$ It works for the following vedansh.mm@fgfgfg.com;ghgh@ff.com;333333;555555 but does not work when the six digit number preceeds the email, like below 222333;vedansh.mm@ct.com;ghgh@ff.com;333333;555555;fhfhf@gggg.com or if the input contains only numbers, like 555555 What am I doing wrong. The numbers and email address count may or may not be the same. Thank you for all your help Vedanshu

Varchar string to int - how to convert empty cells to nulls

Hi, I have a large table from which I want to convert certain columns from varchar to int while loading the table to another table. However, I run into problems when the input column contains empty cells. The SSIS data conversion data flow item can't handle these so I tried the script component. With the script component I can convert these empty cells to zeros but after that I'd need to use SQL update to update the zeros to nulls which in the case of a large table with over 3 million rows takes unnecessarily long. So, is there another way?

add two numeric values

Hi, I'm trying to add two numeric values, where the one is a parameter, like this: numvalue1=numvalue1+@numvalue2 I get the error: operator does not exist:  numeric + @ numeric Then how can I add this values? I use vb.net, where I define parameters with the @ mark.

The data types varchar and date are incompatible in the add operator

On the following query I keep getting the error 'The data types varchar and date are incompatible in the add operator'.  As you can see I have tried to convert the date but doesn't seem to work.  The variable @Date1 will start off as a date.  Any suggestions? ' declare @Date1 date declare @SQL1 varchar(2000) set @Date1 = convert(varchar, cast(getdate() as date), 102) set @SQL1 = 'select * from tbl_1 where convert(varchar,cast(tbl_1.Current_dt as date),102) = '+@Date1+'' print @SQL1    LISA86

Replace string in xml without converting to varchar using SQL Server

We have a xml datatype in SQL server which gets a xml file loaded into it. We want to replace some of the content in the xml datatype, but are running into problems converting from xml to varchar(for manipulation purposes), back to xml to output. Does anyone know of a way to replace xml without converting to varchar?Thanks in advance

Validating Textbox for Numeric Values

I want to validate a textbox that only a numeric currency value can be put into it, for example 0.00. I thought the following code was correct but it doesnt seem to wrk, its causing an error on my page and when I remove it the page is fine, it doesn't actually give exact details of the error on the page just says "syntax error" in the browser that is all.<asp:CustomValidator runat="server" ErrorMessage="Please enter a numeric value for Night Rate in the format of 0.00" ID="NightRateNum" ControlToValidate="txtNightRate" Text="*" ClientValidationFunction="^\d+(\.\d\d)?$"></asp:CustomValidator>Would appreciate if any can tell me where I went wrong Thanks

SSRS 2008 R2 Dataset Field Value Property Data Type not numeric

We're building SSRS 2008 R2 reports using SSAS as data sources.  There are several occaissions where the data types of certain dataset field values that represent cube measures are not numeric.  As such, when these fields are used in an aggregate function they throw an error.  Is there a way to remedy this in the dataset? Thanks for your help.


Hi Folks, I downloaded SQL 2008 and installed recently.Everything perfectly works except one thing that I can't use VARCHAR AND CHAR data types anymore.I was using SQL 2005 and I had the same problem there as well. Please helpe to solve this problem

Error converting data type nvarchar to numeric.

i create a form using form view tools in asp.net..when i click insert command it shows error..this is code which i use for InsertCommand:<asp:SqlDataSource ID="SqlDataSourceFormView" runat="server"         ConnectionString="<%$ ConnectionStrings:AED_PanasonicConnectionString2 %>"         SelectCommand="SELECT * FROM [Debtor]"        InsertCommand="INSERT INTO Debtor (CompanyName, Desc2, RegisterNo, Address1, Address2, Address3, Address4, PostCode)VALUES (@CompanyName, @Desc2, @RegisterNo, @Address1, @Address2, @Address3, @Address4, @PostCode)">        </asp:SqlDataSource>Error:-Error converting data type nvarchar to numeric.where i have to make changes?

Problem with Conversion of numeric data from sql table to csv

I have a numeric value which is of data type Numeric(38,12) in sql server 2000. Now loading to csv what happens is 2000--data looks like 115,834,000.00 when loaded to csv ---115834000--this is how it looks. but I want it to look like as it is in the database. It would be of great help to me, if anybody can tell me the work around. Thanks, PreenSheen

valicate textbox to accept only numeric value

Hi all,  I have a asp.net webform created using c#. my text box is of type: <asp:textbox id="txtqanum" runat="server">private void txtqanum_TextChanged(object sender, System.EventArgs e) { }   it has only textchanged property. I want user to entered values between  0,1.....9. and if they entered anything else I want to show a message about invalid input. How can I accomplish this? Please help me. Thank You,
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend