Error Converting Data Type Varchar To Numeric Asp.net
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error: error converting data type varchar to numeric up vote -2 down vote favorite I am trying to insert into the database a string of the current time: string tm = DateTime.Now.ToString("HH:mm:ss"); string sql = string.Format("INSERT INTO Kabala1 (Nu_kabala,Ma_num,Date,Time,Total,Status,Name,User_n) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", n, Session["Ma_num"], now, tm, lprice, "sdfsdf", Session["user"], "sdfsdf"); But when I run the code above I get the error: error converting data type varchar to numeric. How do I solve it? c# mysql asp.net sql datetime share|improve this question edited May 18 '14 at 17:25 Soner Gönül 69.6k22110200 asked May 18 '14 at 17:20 user3542927 2117 You need to identify the columns on your table that are of numeric type (i.e. int) and remove corresponding '' characters of the INSERT. –HuorSwords May 18 '14 at 17:23 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote One or more of the column data types that you're inserting into is numeric, however you are wrapping all of the values you insert in single quotes ('). This means that when the value is inserted, it will try to cast an varchar (the value you wrapped in single quotes) as a numeric value, and fail. You need to identify which of the values you are trying to insert is numeric, and remove the single quotes from it within the insert statement. For example: string.Format("INSERT INTO Kabala1 (Nu_kabala,Ma_num,Date,Time,Total,Status,Name,User_n) VALUES('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}')", n, Session["Ma_num"], now, tm, lprice, "sdfsdf", Session["user"], "sdfsdf"); Where {4} is numeric. sh
| Related Tips: More > Data Types Problem We've been importing data into VARCHAR columns to verify valid character types before moving into our final destination table and we ran across some decimal values that wouldn't CAST or CONVERT even though they appeared to be decimal values (other decimal values from the same source converted without errors). We received the message "Error converting data type varchar to numeric" and even when we tried to http://stackoverflow.com/questions/23724748/error-error-converting-data-type-varchar-to-numeric import them as numbers they also failed. In addition, all values failed the ISNUMERIC function even though the values look numeric (like 1.00) and when we copy these values into Google Spreadsheets and run functions on them, we get numerical answers. How can we load these problem values? Solution Here is an example of the issue https://www.mssqltips.com/sqlservertip/4008/handling-error-converting-data-type-varchar-to-numeric-in-sql-server/ I was facing. In the below screenshot the data looks correct, but when I checked to make sure the values were numeric using a CAST function I got the following error message. Here are some of the things I noticed: They appear as numerical characters, yet don't convert.If we copy the values directly and do a direct SELECT CAST('1.00000' AS DECIMAL(22,8)), they convert without error.If they come with extra spaces, no trimming function works to remove the error. We seldom stumble on these types of data, but they can create encumbrances for developers, so it's good to know a work-around when transforming these VARCHARs into numerical data points. What differs about these data, compared to other times when facing issues with converting numerical VARCHARs to numerical data points is that all of them will fail the ISNUMERIC (for verifying), CAST, CONVERT, TRY_CONVERT and TRY_PARSE functions (the latter two returning NULLs). In other cases, when converting VARCHARs to numerical data points, we can use these other functions
Tips/Tricks Top Articles Beginner Articles Technical Blogs Posting/Update Guidelines Article Help Forum Article Competition Submit an article or tip Post your http://www.codeproject.com/Questions/647405/Error-converting-data-type-varchar-to-numeric Blog quick answersQ&A Ask a Question View Unanswered Questions View All Questions... C# questions Linux questions ASP.NET questions SQL questions VB.NET questions discussionsforums All Message Boards... Application http://www.developerfusion.com/thread/53361/error-converting-data-type-varchar-to-numeric/ Lifecycle> Running a Business Sales / Marketing Collaboration / Beta Testing Work Issues Design and Architecture ASP.NET JavaScript C / C++ / MFC> ATL / WTL / error converting STL Managed C++/CLI C# Free Tools Objective-C and Swift Database Hardware & Devices> System Admin Hosting and Servers Java .NET Framework Android iOS Mobile SharePoint Silverlight / WPF Visual Basic Web Development Site Bugs / Suggestions Spam and Abuse Watch features Competitions News The Insider Newsletter The Daily Build Newsletter Newsletter archive Surveys Product Showcase error converting data Research Library CodeProject Stuff communitylounge Who's Who Most Valuable Professionals The Lounge The Insider News The Weird & The Wonderful The Soapbox Press Releases Non-English Language > General Indian Topics General Chinese Topics help What is 'CodeProject'? General FAQ Ask a Question Bugs and Suggestions Article Help Forum Site Map Advertise with us About our Advertising Employment Opportunities About Us Ask a Question All Questions All Unanswered FAQ "Error converting data type varchar to numeric". Rate this: Please Sign up or sign in to vote. See more: C# Visual-Studio Dear All, private void btnSave_Click(object sender, EventArgs e) { string connstr = @"Server=.\SQLEXPRESS ;Initial Catalog=RPSJDB;Integrated Security=True; Max Pool Size=100"; SqlDataReader reader = null; SqlConnection conn = null; try { string query = "insert into CustomerTable values('" + txtCustomerName.Text + "','" + txtAddress.Text + "','" + txtMobileNo.Text + "','" + lblGoldBalance.Text + "','" + lblSilverBalance.Text + "','" + lblCashBalance.Text + "')"; conn = new SqlConnection(connstr); if (txtCustomerName.Text != "" & txtAddress.Text != "")
Groups Books Podcasts Forum Jobs Home Database SQL Server Forum Back to discussion Error converting data type varchar to numeric. Replies (2) Email updates Last post was 18 May 2008 at 06:14 sql server India Reply 8 years ago by yogeshkadvekar yogesh kadvekar , India Joined 9 years ago hi i m writting following code declare @i as numeric(18,0)declare @sql as varchar(8000)set @i =4set @sql ='select top '+@i+' * from dbo.tbl_ls_feed_data'exec(@sql)i mgetting errorto Error converting data type varchar to numeric.how to cast @i in this case Report abuse Reply 8 years ago by TimL Tim Leung Berkshire, United Kingdom Joined 9 years ago Because @sql is varchar, @i must also be varchar when constructing your dynamic sql string.The following should resolve your problem declare @i as numeric(18,0)declare @sql as varchar(8000)set @i =4set @sql ='select top '+ CAST(@i AS VARCHAR) + ' * from dbo.tbl_ls_feed_data'exec(@sql) Report abuse Reply 8 years ago by yogeshkadvekar yogesh kadvekar , India Joined 9 years ago thanks loti did it same way Report abuse Post a reply Enter your message below Sign in or Join us (it's free). SQL Server forum discussion Send mail from stored Procedure. by Mulish Mehdi (3 replies) how to update multiple entries by khari6579 (1 replies) problem with select within select by khari6579 (1 replies) Problem in Passing Values to a StoreProcedure by khari6579 (2 replies) How do I modify this query ? by khari6579 (2 replies) SQL Server tutorials Building Your First Data Cube Using SqlBulkCopy for high performance inserts Spatial data in SQL Server 2008 Using SQL Server for ASP.NET session state SharePoint, Document Library and SQL Server Quick links Most recent Most popular Unanswered My threads Recent activity arif ahmad replied to How to receive data in web ... William Thompson replied to What is the name of the Win... Sameera Piyadigamage replied to Point of Sale Developers: H... Scott Carline replied to 4 x C# Developers for large... Rajendra Dhakal replied to Restore SQL Server text dat... cloud rainda replied to How to convert between TS f... Contribute Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!
Web Development ASP.NET Quickstart Programming news Java programming ASP.NET tutorials C# programming Developer Jobs ASP.NET Jobs Java Jobs Developer Jobs Our tools We've got automatic conversion tools t