CODEWITHSUNDEEP
c#.netsqlsql-server-2008select
Alex Gordon
Alex Gordon

Reputation: 60691

c# query is not returning anything

here is my query:

select reporttime, datapath, finalconc, instrument from batchinfo 
    join qvalues on batchinfo.rowid=qvalues.rowid where qvalues.rowid 
    in (select rowid from batchinfo where instrument LIKE '%TF1%' and reporttime
    like '10/%/2010%') and compound='ETG' and  name='QC1'

i am running it like this:

    // Create a database connection object using the connection string    
    OleDbConnection myConnection = new OleDbConnection(myConnectionString);

    // Create a database command on the connection using query    
    OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);

it does not return any results.

when i try this same query in sql server GUI it returns lots of rows

is there a problem specifically with the syntax of the query for c#?

please note that if i simplify the query like select * from table, there is no issue

Upvotes: 1

Views: 529

Answers (5)

Jeff Ogata
Jeff Ogata

Reputation: 57783

Edit - Never mind, just read the comment that your date is in a varchar field. Will leave this here since it may still be useful information.

Try this:

reporttime like 'Oct%2010%'

I have a test table here and when I query it using 

where LastModified like '11/%/2010%'

no rows are returned, although all of the rows in the table have dates in November 2010. When I run the same query using like 'Nov%2010%', it returns all rows.

Details can be found here:

The LIKE clause can also be used to search for particular dates, as well. You need to remember that the LIKE clause is used to search character strings. Because of this the value which you are searching for will need to be represented in the format of an alphabetic date. The correct format to use is: MON DD YYYY HH:MM:SS.MMMAM, where MON is the month abbreviation, DD is the day, YYYY is the year, HH is hours, MM is minutes, SS is seconds, and MMM is milliseconds, and AM designates either AM or PM.

Upvotes: 1

PostMan
PostMan

Reputation: 6967

Personally I would run query profiler to see what query (if any) is being run, and then go from there.

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85046

Can you try using SqlCommand instead of OleDbCommand?

According to MSDN it: Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.

So if you really are using SQL Server 2008 you should probably use this. Any reason you are not?

Upvotes: 4

Michael
Michael

Reputation: 36

Are you sure you are connecting to the same database in your code?

On a side note do you need the inner select? Couldn't you write this query as follows.

  select reporttime,
         datapath,
         finalconc, 
         instrument 
    from batchinfo  
           join qvalues on batchinfo.rowid = qvalues.rowid
   where compound = 'ETG' 
     and name = 'QC1'
     and batchinfo.instrument like '%TF1%'
     and batchinfo.reporttime like '10/%/2010%'

Upvotes: 1

NeilD
NeilD

Reputation: 2298

How are you setting up the mySelectQuery string? Could this be a problem with an escape character?

If you're not doing it already, 

string mySelectQuery = @"query text here";

Upvotes: 2

Related Questions