CODEWITHSUNDEEP
c#sqlvisual-studio-2008
Eray Geveci
Eray Geveci

Reputation: 1129

SQLDataReader returns nothing but the Query returns 1 Row

i have a SQLQuery and the SQLDataReader doesn't return the result reliably.

here is the query:

SELECT b.ID,b.VERANSTALTER, b.SERIENBOOKUID, (select oa.ORIGINALDATE from OUTLOOKADDIN oa where oa.BOOKINGID = b.ID and oa.GUID is null) as ORIGINALDATE , rr.VON ,rr.BIS, b.THEMA, b.STORNO, ra.BEZEICHNUNG, et.BEZEICHNUNG as ETAGE,geb.ADRESSE, st.NAME as STADT 
from BUCHUNG b 
JOIN RESERVIERUNGRAUM rr on rr.BUCHUNG_ID = b.ID 
JOIN RAUM ra on ra.ID = rr.RAUM_ID 
JOIN ETAGE et on et.ID = ra.ETAGE_ID 
JOIN GEBAEUDE geb on geb.ID = ra.GEBAEUDE_ID 
JOIN STADT st on st.ID = ra.STADT_ID 
WHERE b.UPDATE_DATE BETWEEN cast('20140625 20:58:16' as DATETIME) AND getdate() 
AND b.BOOKVERNR = 0 
AND b.ID = (select distinct oa.BOOKINGID from OUTLOOKADDIN oa where b.ID = oa.BOOKINGID and oa.GUID is null)

When i enter the Query to the SQLManagement Field, it returns a result.

This is the Main function

  public void start()
            {
                String SQLQuery = "SELECT b.ID,b.VERANSTALTER, b.SERIENBOOKUID, (select oa.ORIGINALDATE from OUTLOOKADDIN oa where oa.BOOKINGID = b.ID and oa.GUID is null) as ORIGINALDATE , rr.VON ,rr.BIS, b.THEMA, b.STORNO, ra.BEZEICHNUNG, et.BEZEICHNUNG as ETAGE,geb.ADRESSE, st.NAME as STADT"
                                + " from BUCHUNG b"
                                + " JOIN RESERVIERUNGRAUM rr on rr.BUCHUNG_ID = b.ID"
                                + " JOIN RAUM ra on ra.ID = rr.RAUM_ID"
                                + " JOIN ETAGE et on et.ID = ra.ETAGE_ID"
                                + " JOIN GEBAEUDE geb on geb.ID = ra.GEBAEUDE_ID"
                                + " JOIN STADT st on st.ID = ra.STADT_ID"
                                + " WHERE b.UPDATE_DATE BETWEEN " + oh.DatToDBISO(oldDate) + " AND getdate() AND b.BOOKVERNR = 0"
                                + " AND b.ID = (select distinct oa.BOOKINGID from OUTLOOKADDIN oa where b.ID = oa.BOOKINGID and oa.GUID is null)";
                SqlDataReader reader = oh.getSQLDR(SQLQuery);

                List<BookingUpdate> bookingUpdateList = new List<BookingUpdate>();

                if (reader.HasRows)
                {
                log.Debug("not empty");
                }
                else
                {
                log.Debug("empty");
                }
                while (reader.Read())
                {
                    BookingUpdate buObject = new BookingUpdate();
                    Int32 SerienBookUID = 0; ;

                    if (reader["SERIENBOOKUID"] != DBNull.Value)
                    {
                    log.Debug("b2");
                        SerienBookUID = Convert.ToInt32(reader["SERIENBOOKUID"]);
                    }

...
}

This is the Helper function which connects to SQL

 public SqlDataReader getSQLDR(string query)
    {
        SqlConnection con = new SqlConnection(GetConnectionString());
        SqlCommand cmd = new SqlCommand(query, con);
        try
        {
            cmd.Connection = con;
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        catch (Exception ex)
        {
            log.Debug("Fehler beim übergeben des SQLDATAReaders :" + ex.Message);
        }
        return null;

    }

Upvotes: 0

Views: 259

Answers (1)

Habib
Habib

Reputation: 223207

Your issue is concatenating your query with the Date. If you are going to use concatenation then date value should be enclosed in single quotes, so this:

b.UPDATE_DATE BETWEEN " + oh.DatToDBISO(oldDate) + " AND getdate() ....

should be 

b.UPDATE_DATE BETWEEN '" + oh.DatToDBISO(oldDate) + "' AND getdate()

But a better option would be to use SqlParameter and pass that parameter with your query, this will not only save you from SQL Injection but also let you pass the date object without worrying about its presentation format.

Upvotes: 3

Related Questions