CRecordSet and CDatabase.ExecuteSQL
-
Hello all.. i need your help again. I'm use CDatabase and CRecordSet to access to my access database. forexample myTable have two rows, row1 and row2. row1 contains texts, row2 contain numbers as shown below. r1 | r2 ___________ a | 1 a | 1 b | 2 c | 1 a | 1 c | 5 d | 5 d | 9 a | 3 I want only a,b,c,d from row1 not entire column, so I wrote the following code to get select the data that I want. but it still giving me entire column instead of only a,b,c,d could you please point me out that what am i doing wrong?
CDatabase db; MyCRecordSet rs(&db); db.open..... rs.open... //some more code here then CString qstr = "SELECT DISTINCTROW r1 FROM myTable"; rs.m_pDatabase->ExecuteSQL(qstr); if(rs.ReQuery()) { while(!rs.isEOF()) { MessageBox(rs.rOneData); } }thank you -
Hello all.. i need your help again. I'm use CDatabase and CRecordSet to access to my access database. forexample myTable have two rows, row1 and row2. row1 contains texts, row2 contain numbers as shown below. r1 | r2 ___________ a | 1 a | 1 b | 2 c | 1 a | 1 c | 5 d | 5 d | 9 a | 3 I want only a,b,c,d from row1 not entire column, so I wrote the following code to get select the data that I want. but it still giving me entire column instead of only a,b,c,d could you please point me out that what am i doing wrong?
CDatabase db; MyCRecordSet rs(&db); db.open..... rs.open... //some more code here then CString qstr = "SELECT DISTINCTROW r1 FROM myTable"; rs.m_pDatabase->ExecuteSQL(qstr); if(rs.ReQuery()) { while(!rs.isEOF()) { MessageBox(rs.rOneData); } }thank youpnpfriend wrote: CString qstr = "SELECT DISTINCTROW r1 FROM myTable"; should be "SELECT DISTINCT r1 FROM myTable"; pnpfriend wrote: myTable have two rows, row1 and row2 I think you meant myTable have two Columns, r1 and r2 and you want one row for each unique value in column r1 "select distinct" will do that. select distinctrow retuns all the rows that are not duplicates (all column vaues equal). Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
-
pnpfriend wrote: CString qstr = "SELECT DISTINCTROW r1 FROM myTable"; should be "SELECT DISTINCT r1 FROM myTable"; pnpfriend wrote: myTable have two rows, row1 and row2 I think you meant myTable have two Columns, r1 and r2 and you want one row for each unique value in column r1 "select distinct" will do that. select distinctrow retuns all the rows that are not duplicates (all column vaues equal). Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
yes.. i meant myTable have two columns, r1 and r2 i think i have to use distinctrow, since you said that distinctrow returns all the rows that are not duplicates meaning if i use distinctrow i will get a,b,c,d from r1 as I wanted. anyway i tried both and it is not giving me the answer i wanted.
CString qstr = "select distinctrow r1 from myTable"; rs.m_pDatabase->ExecuteSQL(qstr); if(rs.ReQuery()) { while(!rs.IsEOF()) { MessageBox(rs.r1Data); rs.MoveNext(); } }keeping giving me entire r1 column data. is it because of rs.IsEOF() while loop?? is is CRecordSet will have the record of whatever record set of sql query statement executed and return?? -
yes.. i meant myTable have two columns, r1 and r2 i think i have to use distinctrow, since you said that distinctrow returns all the rows that are not duplicates meaning if i use distinctrow i will get a,b,c,d from r1 as I wanted. anyway i tried both and it is not giving me the answer i wanted.
CString qstr = "select distinctrow r1 from myTable"; rs.m_pDatabase->ExecuteSQL(qstr); if(rs.ReQuery()) { while(!rs.IsEOF()) { MessageBox(rs.r1Data); rs.MoveNext(); } }keeping giving me entire r1 column data. is it because of rs.IsEOF() while loop?? is is CRecordSet will have the record of whatever record set of sql query statement executed and return??The DISTINCTROW only eliminates duplicate records from the query result; it does nothing to qualify the selection. You need to include a WHERE clause in the query to define what records you want returned. Adding the clause WHERE r1 >= 'a' AND r1 <= 'd' (or whatever is syntactically correct for Access) will filter the resulting recordset so that it contains only records that meet the specified criteria. Also, specifying r1 as the only value returned is probably not what you intend - try SELECTing * to return entire records. Will Build Nuclear Missile For Food - No Target Too Small
-
Hello all.. i need your help again. I'm use CDatabase and CRecordSet to access to my access database. forexample myTable have two rows, row1 and row2. row1 contains texts, row2 contain numbers as shown below. r1 | r2 ___________ a | 1 a | 1 b | 2 c | 1 a | 1 c | 5 d | 5 d | 9 a | 3 I want only a,b,c,d from row1 not entire column, so I wrote the following code to get select the data that I want. but it still giving me entire column instead of only a,b,c,d could you please point me out that what am i doing wrong?
CDatabase db; MyCRecordSet rs(&db); db.open..... rs.open... //some more code here then CString qstr = "SELECT DISTINCTROW r1 FROM myTable"; rs.m_pDatabase->ExecuteSQL(qstr); if(rs.ReQuery()) { while(!rs.isEOF()) { MessageBox(rs.rOneData); } }thank youExecuteSQL lets you send SQL-Commands to the Database directly, but it doesn't change the recordset (you can prove by examining the m_strSQL member) to use aggregate functions and the like (sum, count, distinct, ...) you have to modify the DopFieldExchange-function: void CDPDCount::DoFieldExchange(CFieldExchange* pFX) { pFX->SetFieldType(CFieldExchange::outputColumn); RFX_Long(pFX, _T("COUNT(*)"), m_lCount); } hope it helps :) dirk