|
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()";<br />
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 fine... But in C# it just won't.
Could anyone come up with a hint? Thanks in advance & Kind Regards
Haluk
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
Yep, it looks like you found the issue. Try overriding ToString() method and convert it to the correct format for SQL Server.
Hogan
|
|
|
|
|
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?!?
|
|
|
|
|
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
<br />
calculatedNumber.ToString().Replace(",",".")<br />
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.
<br />
calculatedNumber = (decimal) dataReader["CalculatedNumberField"];<br />
Hope this helps.
Hogan
|
|
|
|
|
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.
|
|
|
|
|
Thank you. Much appreciated.
|
|
|
|
|
Hi,
if you had to search 50000 lines of code for the word example as fast as possible, with every kind of whitespace in the line, which method would you prefer.
REGEX
or
myString.Trim() == "example"
|
|
|
|
|
May be Regex, which compiled into assembly, but I'm not sure. It's better to test.
|
|
|
|
|
error1408 wrote: if you had to search 50000 lines of code for the word example as fast as possible, with every kind of whitespace in the line, which method would you prefer.
REGEX
or
myString.Trim() == "example"
In this case RegEx would be the only one that works.
The second example would only ever return false.
|
|
|
|
|
string.IndexOf
[ My Blog] "Visual studio desperately needs some performance improvements. It is sometimes almost as slow as eclipse." - Rüdiger Klaehn "Real men use mspaint for writing code and notepad for designing graphics." - Anna-Jayne Metcalfe
|
|
|
|
|
Why not try it out for yourself and find out. Create a document containing the phrase "The quick brown fox jumps over the lazy fox" by cutting and pasting repeatedly; it won't take you long to knock up a 50000 line document with this in. Now, put a seed word in the middle somewhere - call it seed for instance.
Then, run a program that uses both versions against this document and see which method is faster.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Ok i wrote a little app that compares the two methods.
I found out, that testing a file with 60000 lines 30 times took my pc about a second with this method:
<br />
using (StreamReader rIn = new StreamReader("test.txt"))<br />
{<br />
string line = "";<br />
while ((line = rIn.ReadLine()) != null)<br />
{<br />
if (line.Trim() == "{{{tcl")<br />
{<br />
return true;<br />
}<br />
}<br />
rIn.Close();<br />
<br />
}<br />
return false;<br />
and about 9 seconds with the regex:
<br />
using (StreamReader rIn = new StreamReader("test.txt"))<br />
{<br />
string line = "";<br />
Regex reg = new Regex(@"\s*\{\{\{tcl\s*");<br />
while ((line = rIn.ReadLine()) != null)<br />
{<br />
if (reg.IsMatch(line))<br />
{<br />
return true;<br />
}<br />
}<br />
rIn.Close();<br />
}<br />
return false;<br />
Thats a BIG difference.
|
|
|
|
|
A well deserved 5 for answering your own question.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I am writing some code which calls a web service that i wrote. Works just fine when i pass in all of the parameters via the method call (names have been changed, duh):
public void SetOption(string A, string B, int C)<br />
{<br />
this.Invoke("SetOption", new object[] { A, B, C });<br />
}
However, when i try to set "A" from within the method:
public void SetOption(string B, int C)<br />
{<br />
string A = (from global source which has been verified to be active);<br />
this.Invoke("SetOption", new object[] { A, B, C });<br />
}
This sends a null object to the web service, which gives it a headache. 
Has anyone run in to this problem before? (when i simply switch it back to the passed in parameter, it works fine again)
-Jeremy A. Cunningham
|
|
|
|
|
Why your using invoke to call another function in your web service is beyond me.. . But, you could try just:
public void SetOption(string B, int C)<br />
{<br />
this.Invoke("SetOption", new object[] { ([A] from global source which has been verified to be active), B, C });<br />
}
Just specifically put the A value in with the arguments instead of making a new string instance.
|
|
|
|
|
Let me clarify, the invoke is being called from the web service client. (entirely seperate visual studio project)
I receive the same results using your suggestion. (a null object is passed to the web service instead of the string)
-Jeremy A. Cunningham
|
|
|
|
|
Try to seperate the web method into two different methods. One with as an internal helper with all of the logic.
public void SetOption(string a, string b, string c);
private void HelpSetOption(string a, string b, string c);
Although, a soap extension may work better for parameter replacement.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
I tried this as well while testing. The system then complains about accessibility, and won't function until i make it public again. I tried with "protected" as well.
Thank you all for taking a look at this.
-Jeremy A. Cunningham
|
|
|
|
|
The web method must be public however :
[WebMethod]
public void Foo{string a, string b, string c}{
HelpFoo("test", b, c);
}
private void HelpFoo(string a, string b, string c){
...
}
Should work just fine.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
A private helper function isn't useful for objects outside of the class. And as you have pointed out, the web method has to be pubilc, or:
System.ArgumentException: SetOption Web Service method name is not valid.
So i have two public functions, and it seems i'm just stuck at that.
-Jeremy A. Cunningham
|
|
|
|
|
Then make it public or internal and don't decorate it with the WebMethod attribute.
Realistically all web method calls should be immediate proxy calls to another class anyway.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
