passing a string to function!

[Coolstep]

I face a very strange problem, I have a function that executes a SQL statement, this SQL statement has a string variable exists 3 times in the statment that passed by the function's arguments. So simple till now but what actually happens seems to be a bug. consider I pass one of two values 'IATA' or 'ICAO' so when i pass the 'IATA' to the statement it works, but when I pass the 'ICAO' it displays 'NULLs' in the first two places and work in the 3rd place!!!.
e.g.
In case of IATA:
ID - CODE X - CODE Y - CODE Z
-----------------------------
1 - IATA1 - IATA2 - IATA3

In case of ICAO:
-----------------------------
1 - NULL - NULL - ICAO3

!! Knowing that CODE Y and code Z are from same table
and if i place static 'ICAO' in the statement, it works fine!!

I wish it's not a bug.

 

[Coolstep]

Other Clue, I show the SQL statement before execution and the it displays correct and complete statement.

 

[bajdev]

It's hard for me to troubleshoot this problem without seeing your actual C# code or the SQL it's executing.

If I had to guess though I'd bet it's a problem with the SQL statement and not your C# code. It sounds like the C# code runs fine but SQL doesn't return the data that you are expecting it too. Is that correct?

You said you've shown the SQL statement and it displays correctly? Have you tried coping the SQL code it outputs and executing it directly through Query Analyzer to see what it returns?

Once again this doesn't sound like a bug in C# it's probably an issue with your SQL statement.

 

[Coolstep]

Dear Bajdev,
thank u for ur concern, well about the sql statement it's right and tested because when i replace the variable part with a static string, it returns data!
Actually i discovered the problem after i posted this thread by 1 hr.

The problem was that the variable part in the sql statement was a column name, I query 18 columns and define them afterwads as datacolumns and filter what to display on grid. so the first time the query runs it runs successfully and return 18 columns with valid data. after changing the column name, the statement recognizes it as a new column so it returns 19 column and the 19th column is not visible.
e.g.
select id,name,"+var1+" from person

1. first time i call it with var1="salary"
the statement returns "ID, Name, Salary"

2. second call u set var1="job"
so the statement returns "ID, Name, null,job"

the sql statement is correct but it's a bug as i c in the datareader. this problem happens although i remove my datatables before each query.

I could overcome this problem by using a user defined SQL-Server function works as ORACLE's NVL()

so the sql statement became: select id,name,nvl("+var1+",'') from person

in this way in all cases it always returns 3 columns.

hope i could explain it carefully

 

[bajdev]

Interesting. Glad you figured it out! Thanks for the explanation too.