Dear all,
I have something very weird, never experienced it before with c# express edition

- i have a piece of selection query that selects all the records within a specific month.

- this query works very fine in msaccess, but doesnt work in the run time in c#. so frankly when i run it in msaccess, i get rows.count = 1, but in c# it gives me rows.count =0.

anyhow i thought perhaps i made something wrong in the c# script. so i double checked it and the weirdest thing occurs!! when i set a break point after retrieveing the dataset, i get rows.count= 0. while, when i manually retry to run the same piece of query surprising i get rows.count =1.

how is that possible, any suggestions?

The sql query:

string sqlWhere = select * from RESERVATIONS where check_in >= FORMAT(#1/11/2010#,'MM/DD/YYYY') and check_in <= FORMAT(#30/11/2010#,'MM/DD/YYYY') and record_status = 'n' or check_out >= FORMAT(#1/11/2010#,'MM/DD/YYYY') and check_out <= FORMAT(#30/11/2010#,'MM/DD/YYYY')and record_status = 'n' or check_in <= FORMAT(#1/11/2010#,'MM/DD/YYYY') and check_out >= FORMAT(#30/11/2010#,'MM/DD/YYYY')and record_status = 'n';

The dataset function, which worked always very fine with me:

private dataset ds(string sqlQuery)
            string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbFile;


            // create an open the connection          
            OleDbConnection conn = new OleDbConnection(conString);
            OleDbCommand command = conn.CreateCommand();

            // create the DataSet
            DataSet ds = new DataSet();

            // create a new DataSet
            ds = new DataSet();

            // open the connection
            conn.Open();

            // run the query
            command.CommandText = sqlQuery;
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(ds);

            // close the connection
            conn.Close();

            return ds;
        }

Dear all,

string sqlWhere = select * from RESERVATIONS where check_in >= FORMAT(#1/11/2010#,'MM/DD/YYYY') and check_in <= FORMAT(#30/11/2010#,'MM/DD/YYYY') and record_status = 'n' or check_out >= FORMAT(#1/11/2010#,'MM/DD/YYYY') and check_out <= FORMAT(#30/11/2010#,'MM/DD/YYYY')and record_status = 'n' or check_in <= FORMAT(#1/11/2010#,'MM/DD/YYYY') and check_out >= FORMAT(#30/11/2010#,'MM/DD/YYYY')and record_status = 'n';

Are you escaping your query string or using @

string SqlWhere = "select * from RESERVATIONS where check_in >= FORMAT(#1//11//2010#,'MM//DD//YYYY')...

-or-


string SqlWhere = @"select * from RESERVATIONS where check_in >= FORMAT(#1/11/2010#,'MM/DD/YYYY')...

This article has been dead for over six months. Start a new discussion instead.