| Author |
Topic |
|
jp2code
Posting Yak Master
175 Posts |
 Posted - 2008-08-15 : 14:25:40
|
I've got a query that runs great in SQL Server 2000's Query Analyzer. It pulls up (7016 row(s) affected) actually!However, in Visual Studio 2005 Professional, I try to fill a DataTable using the exact same string, and it returns '0' rows.Could someone enlighten me as to what I'm doing wrong? I've got several apps that use the same SQL Server, and everything else works fine.Here is the SQL Query:SELECT replace(TR.[OP_ID], ' ', ' ') as OP_ID, EI.[NUM], TR.[Date_Time]FROM Test_Results TR LEFT JOIN EmployeeInfo EI ON OP_ID=EI.[FIRSTNAME]+' '+EI.[LASTNAME]WHERE (TR.[System_ID] Like '%Decay%') AND (EI.[JOBTITLE]='BRZ OPRTR') AND (EI.[NUM] IS NOT NULL) AND ('7/1/2008' < TR.[Date_Time]) AND (TR.[Date_Time] < '7/31/2008') ORDER BY TR.OP_ID, TR.[Date_Time] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-15 : 14:28:43
|
| Are you hardcoding date values in vs or sending it through parameter? |
 |
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-08-15 : 14:31:11
|
| Forgot to add:This query worked fine until I added the replace() function.We need the replace function because some of our employee records include trailing spaces in the operator names.(FYI: We have told Human Resources to not do this, but they are idiots, and we haven't had a chance to rewrite their software.) |
|
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-08-15 : 14:35:29
|
quote:
Originally posted by visakh16
Are you hardcoding date values in vs or sending it through parameter?
The dates are hard coded by Visual Studio (through the dates selected on the form).I copied the string after it was created in the program to display here and test in Query Analyzer (QA).QA ran it and gave several records.Visual Studio: I call SqlDataAdapter1.Fill(DataTable1), but I always get DataTable1.Rows.Count = 0. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-15 : 14:38:57
|
quote:
Originally posted by jp2code
quote:
Originally posted by visakh16
Are you hardcoding date values in vs or sending it through parameter?
The dates are hard coded by Visual Studio (through the dates selected on the form).I copied the string after it was created in the program to display here and test in Query Analyzer (QA).QA ran it and gave several records.Visual Studio: I call SqlDataAdapter1.Fill(DataTable1), but I always get DataTable1.Rows.Count = 0.
are you able to see the parameter values passed from form and check if its same as you use in QA? |
|
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-08-15 : 14:45:23
|
quote:
Originally posted by jp2code
This query worked fine until I added the replace() function.We need the replace function because some of our employee records include trailing spaces in the operator names.
quote:
Originally posted by visakh16
are you able to see the parameter values passed from form and check if its same as you use in QA?
Yes, it is the same.Is the replace() function causing the problem? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-15 : 14:53:58
|
quote:
Originally posted by jp2code
quote:
Originally posted by jp2code
This query worked fine until I added the replace() function.We need the replace function because some of our employee records include trailing spaces in the operator names.
quote:
Originally posted by visakh16
are you able to see the parameter values passed from form and check if its same as you use in QA?
Yes, it is the same.Is the replace() function causing the problem?
i dont think so. since its incorrect number of rows returned i think it may be something related to parameters used for filteration. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-16 : 04:42:38
|
Trailing spaces can be removed with RTRIMselect rtrim(replace(TR.[OP_ID], ' ', ' ')) as OP_ID,
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
Query Runs In QA but not VS