SQL Error converting data type varchar to numeric

ortaparmak

Hello, I am a hobby programmer, writing a small office automation program for my office (translation agency). It's a small database application with several tables on MS SQL Server 2005 (Express). There is a Jobs table which includes individual Project items (i.e. when a translation project includes multiple language pairs, for instance XXXX_EN-TR and XXXX-DE-TR (where XXXX is ProjectID) these individual items (jobs) are written in Jobs table with a foreign key to Projects table's ProjectID). Upon a button click application should write to Jobs table with following: string addNewJobQuery = "INSERT INTO Jobs (ProjectID, FileName, SourceLanguage, TargetLanguage, Type, CharCount, PageCount, ApplicableRate, QuotedOn)" + " VALUES (\'" + projectID + "\', \'" + fileName + "\', \'" + srcLangID + "\', \'" + trgLangID + "\', \'" + typeID + "\', \'" + charCount + "\', \'" + pageCount + "\', \'" + applicableRate + "\', \'" + quotedOn + "\'); SELECT SCOPE_IDENTITY()"; Above string includes all mandatory (i.e. where null not allowed) fields of Jobs table and data submitted with this query is appropriate (i.e. decimal is sent to SQL for all decimal fields, Int64 sent for all long fields etc.). But it just don't work. It shoots me an exception "e.message = Error converting data type varchar to numeric." Following is a list of variables I sent in my last attempt: ProjectID = 34051 (valid, ProjectID should be long at SQL and Int64 in C# code) fileName = "D:\\UI\\...\\FileName.doc" (this is string in c# code and text at SQL; shouldn't be this one) srcLangID = 0 (bigint at SQL, Int64 in c#; this is foreignkey to LanguageID in Languages table; correct) trgLangID = 1 (biginy at SQL, Int64 in c#; this is foreignkey to LanguageID in Languages table; correct) typeID = 1 (SQL: bigint, C# Int64, correct - foreignkey to Services tables ServiceID, which is also long) charCount = 71325 (bigint at SQL, Int64 in C# code. this shouldn't be problem either) pageCount = 71.325 (decimal at SQL and decimal in C# code) applicableRate = 25 (alo decimal) quotedOn = 05.11.2007 17:40:15 (this I obtain with DateTime.Now; should not be problem UNLESS...) Here is what I seriously suspect; my OS is Turkish language, SQL Server Express is English, Visual Studio is also English. Could it be: 1) DateTime returning different format date/time (. instead of /); OR 2) Different decimal separators (. instead of ,) OH, wait, when I type above string (removing quotemarks and paranthesis ofcourse) in a query, it all works f

Rob Philpott

Hey Haluk, Some 'hints'. If you are inserting a value into an integer column, you shouldn't put in in quotes:

insert into MyTable(Col1) values ('1')

should be just:

insert into MyTable(Col1) values (1)

So make sure that you don't parenthesize integers. Try to break that huge insert statement into something more readable - ideally a parameterised stored procedure where you can add the parameters one by one. If you don't want to do that then try StringBuilder to String.Format to make it more readable. One last thing - it look like you're escaping single quotes (\'). You don't need to do this. You only need to escape double quotes (\") Hope that helps.

Regards, Rob Philpott.

snorkie

For your DateTime issue, you could use the sql getdate() funtion inside your SQL. That would eliminate translation issues as SQL would enter dates that it likes. Finally for debugging reasons, can you output your addNewJobQuery after it is set into the message so we can see the direct values. That might help troubleshoot your issue. Hogan

ortaparmak

Hello There, Here it is: INSERT INTO Jobs (ProjectID, FileName, SourceLanguage, TargetLanguage, Type, CharCount, PageCount, ApplicableRate, QuotedOn) VALUES (34058, 'D:\UI\Projects\2007\34058 (Company)\Sample Document.doc', 0, 1, 1, 71325, 71,325, 25, '05.11.2007 19:56:19'); SELECT SCOPE_IDENTITY(); It is right there, 71,325 is actually 71.325 in English (71325 / 1000). I suspect that is the cause of all this.

snorkie

Yep, it looks like you found the issue. Try overriding ToString() method and convert it to the correct format for SQL Server. Hogan

ortaparmak

Hello Rob, Thanks for helping me. You're right about all your comments. I have removed single quotes but this time I get another exception telling me there are more fields in VALUES than there is in INSERT. I checked what is sent to SQL, it is as follows: INSERT INTO Jobs (ProjectID, FileName, SourceLanguage, TargetLanguage, Type, CharCount, PageCount, ApplicableRate, QuotedOn) VALUES (34058, 'D:\UI\Projects\2007\34058 (Company)\Some Document.doc', 0, 1, 1, 71325, 71,325, 25, '05.11.2007 19:56:19'); SELECT SCOPE_IDENTITY(); As you can see 71.325 is written as 71,325 and I suspect SQL is translating this as 71 for one field and 325 for another field; thus there is more VALUES than INSERTs... PS: once I get this running, I'll optimize it and I took note of your comments about huge string and readibility

ortaparmak

Hogan Hi, Overriding ToString is not problem but... All calculations are made in the program; storing what is inappropriate for OS regional settings would complicate my calculations. In fact, changing the field type to string and storing Turkish notation decimal (i.e. 71,325) would be a workaround; but ideally there should be a real solution for this. Isn't there any localization settings in MS SQL server?!?

Pete OHanlon

The most apparent thing here is that you are using direct SQL to achieve this. You should use a stored procedure instead because it is better for you with regards to SQL Injection Attacks and also it takes care of the mappings for you.

CREATE PROCEDURE dbo.SaveJobs
@ID BIGINT OUT,
@ProjectID BIGINT,
@FileName NVARCHAR(255),
@SourceLanguage BIGINT, 
@TargetLanguage BIGINT, 
@Type BIGINT, 
@CharCount BIGINT, 
@PageCount DECIMAL(10,4), 
@ApplicableRate DECIMAL(10,4), 
@QuotedOn DATETIME
AS
INSERT INTO JOBS(ProjectID, FileName, SourceLanguage, TargetLanguage, Type, CharCount, PageCount, ApplicableRate, QuotedOn)
VALUES (
@ProjectID, 
@FileName, 
@SourceLanguage, 
@TargetLanguage, 
@Type, 
@CharCount, 
@PageCount, 
@ApplicableRate, 
@QuotedOn)

SET @ID = SCOPE_IDENTITY()

Then you create a SqlCommand in your C# and fill in your parameters with the relevant values.

Deja View - the feeling that you've seen this post before.

ortaparmak

Thank you. Much appreciated.

snorkie

Two thoughts for you... First, I know that you can localize strings in SQL server by setting their "collation sequence". That is useful for the order by for a specific language. But you want to be dealing with numbers here. Second, from what I understand, the numbers you see are all stored in memory the same way. When we output them, they are filtered by region. With that being said, you should be able to put numbers in and pull them out without issue. I would try the following. When you are creating your SQL try this. (pseudo code below) decimal calculatedNumber = 71,325 In your SQL statement use the following statement to convert your number to an acceptable format calculatedNumber.ToString().Replace(",",".") When you are getting the value back out of the database, you should be able to just do a straight assignment and get the value as you need it. calculatedNumber = (decimal) dataReader["CalculatedNumberField"]; Hope this helps. Hogan