SQLDataReader returns nothing but the Query returns 1 Row

Question

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;

    }

you need to show c# side code

Ehsan Sajjad
– Ehsan Sajjad

2014-06-25 19:14:45 +00:00
Commented Jun 25, 2014 at 19:14 — Ehsan Sajjad
– Ehsan Sajjad, Commented Jun 25, 2014 at 19:14
sorry, i edited my code

Eray Geveci
– Eray Geveci

2014-06-25 19:22:23 +00:00
Commented Jun 25, 2014 at 19:22 — Eray Geveci
– Eray Geveci, Commented Jun 25, 2014 at 19:22

Habib · Accepted Answer · 2014-06-25 19:25:01Z

3

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.

answered Jun 25, 2014 at 19:25

Habib

224k30 gold badges420 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLDataReader returns nothing but the Query returns 1 Row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related