sarath83
asked on
To Retrieve value from Database
I am using MS Access database.I need to Retrive value of the field "SongID " from table PlaylistItems where PlaylistID is 3.
i am using the SQL statement i am getting an error.. Assertion failure and syntax error in SqlItem string.
CDatabase database;
CString SqlString,SqlItem;
CRecordset recItem( &database );
SqlItem = "SELECT SongID " "FROM PlaylistItems " "WHERE PlaylistID == sPlaylistID ";
recItem.Open (CRecordset::forwardOnly,S qlItem,CRe cordset::r eadO
nly);
recItem.GetFieldValue(" SongID ",sPlaylistID);
i am using the SQL statement i am getting an error.. Assertion failure and syntax error in SqlItem string.
CDatabase database;
CString SqlString,SqlItem;
CRecordset recItem( &database );
SqlItem = "SELECT SongID " "FROM PlaylistItems " "WHERE PlaylistID == sPlaylistID ";
recItem.Open (CRecordset::forwardOnly,S
nly);
recItem.GetFieldValue(" SongID ",sPlaylistID);
ASKER
I am getting an error "Syntax error (missing operator) in querey experssion in PlaylistID==3"
ASKER
I am getting an error "Syntax error (missing operator) in querey experssion in PlaylistID==3"
Ooops only need one equal (=)
SqlItem.Format("SELECT SongID FROM PlaylistItems WHERE PlaylistID = %d", nPlayID);
Rosh :)
SqlItem.Format("SELECT SongID FROM PlaylistItems WHERE PlaylistID = %d", nPlayID);
Rosh :)
For MS Access use can the CDaoDatabase and CDAORecordset (and other CDAO.. classes). They are specific for the Jet engine (maybe better performance).
ASKER
Now error is Too few parameters expected in below to the sql stirng line in the code
Try
SqlItem.Format("SELECT * FROM PlaylistItems WHERE PlaylistID == %d", nPlayID);
Is that working...?
Rosh :)
SqlItem.Format("SELECT * FROM PlaylistItems WHERE PlaylistID == %d", nPlayID);
Is that working...?
Rosh :)
ASKER
recItem.Open (CRecordset::forwardOnly,S qlItem,CRe cordset::r eadOnly);
After this line execution i am getting dialog box Select souce data . and after selecting the source Assertion failure is occuring.
After this line execution i am getting dialog box Select souce data . and after selecting the source Assertion failure is occuring.
ASKER
no that is not workng.
Did you changed that "==" to "=". If not, please change that (SqlItem.Format("SELECT * FROM PlaylistItems WHERE PlaylistID = %d", nPlayID);)
I think you are not opening the database correctly....
try this
CDatabase database;
CString SqlString,SqlItem,csSongID ;
CString strDSN;
strDSN.Format("ODBC;DRIVER ={%s};DSN= '';DBQ=%s" ,
"MICROSOFT ACCESS DRIVER (*.mdb)",
"C:\\YourMDB.mdb");
// Open the database
database.Open(NULL, false, false, strDSN);
int nPlayID = 3;
int nSongID;
CRecordset recItem( &database );
SqlItem.Format("SELECT SongID FROM PlaylistItems WHERE PlaylistID = %d", nPlayID);
recItem.Open (CRecordset::forwardOnly,S qlItem,CRe cordset::r eadOnly);
recItem.GetFieldValue((sho rt)0,csSon gID);
nSongID = atoi(csSongID);
Good Luck
I think you are not opening the database correctly....
try this
CDatabase database;
CString SqlString,SqlItem,csSongID
CString strDSN;
strDSN.Format("ODBC;DRIVER
"MICROSOFT ACCESS DRIVER (*.mdb)",
"C:\\YourMDB.mdb");
// Open the database
database.Open(NULL, false, false, strDSN);
int nPlayID = 3;
int nSongID;
CRecordset recItem( &database );
SqlItem.Format("SELECT SongID FROM PlaylistItems WHERE PlaylistID = %d", nPlayID);
recItem.Open (CRecordset::forwardOnly,S
recItem.GetFieldValue((sho
nSongID = atoi(csSongID);
Good Luck
Would brackets (and no spaces) make any difference in the where clause?
SqlItem.Format("SELECT * FROM PlaylistItems WHERE (PlaylistID=%ld)", nPlayID);
SqlItem.Format("SELECT * FROM PlaylistItems WHERE (PlaylistID=%ld)", nPlayID);
ASKER
Here is the code which i am using ,
hItem = pTree->GetSelectedItem();
if(hItem != m_playlist_root)
{
CString str;
str = pTree->GetItemText(hItem);
m_ListCtrl.DeleteAllItems( );
CDatabase database;
CString SqlString,SqlItem;
CString sPlaylistID, sCategory,sSongID;
CString sDriver = "MICROSOFT ACCESS DRIVER (*.mdb)";
CString sDsn;
CString sFile = "C:\\Documents and Settings\\abhiram\\PPSimul ator\\PPSi mulator.md b";
int iRec = 0;
sDsn.Format("ODBC;DRIVER={ %s};DSN='' ;DBQ=%s",s Driver,sFi le);
TRY
{
database.Open(NULL,false,f alse,sDsn) ;
CRecordset recset( &database );
CRecordset recItem( &database );
SqlString = "SELECT PlaylistID " "FROM Playlists ";
recset.Open(CRecordset::fo rwardOnly, SqlString, CRecordset ::readOnly );
while( !recset.IsEOF() )
{
recset.GetFieldValue("Play listID",sP laylistID) ;
SqlItem = "SELECT SongID " "FROM PlaylistItems " "WHERE PlaylistID=sPlaylistID";
SqlItem.Format("SELECT * FROM PlaylistItems WHERE PlaylistID = %s",sPlaylistID);
recItem.Open(CRecordset::f orwardOnly ,SqlItem,C Recordset: :readOnly) ;
recItem.GetFieldValue("Son gID",sSong ID);
iRec = m_ListCtrl.InsertItem(i,sS ongID,0);
i++;
m_ListCtrl.SetItemText(i,1 ,sCategory );
i++;
recset.MoveNext();
}
database.Close();
}
CATCH(CDBException, e)
{
AfxMessageBox("Database error: "+e->m_strError);
}
END_CATCH;
}
hItem = pTree->GetSelectedItem();
if(hItem != m_playlist_root)
{
CString str;
str = pTree->GetItemText(hItem);
m_ListCtrl.DeleteAllItems(
CDatabase database;
CString SqlString,SqlItem;
CString sPlaylistID, sCategory,sSongID;
CString sDriver = "MICROSOFT ACCESS DRIVER (*.mdb)";
CString sDsn;
CString sFile = "C:\\Documents and Settings\\abhiram\\PPSimul
int iRec = 0;
sDsn.Format("ODBC;DRIVER={
TRY
{
database.Open(NULL,false,f
CRecordset recset( &database );
CRecordset recItem( &database );
SqlString = "SELECT PlaylistID " "FROM Playlists ";
recset.Open(CRecordset::fo
while( !recset.IsEOF() )
{
recset.GetFieldValue("Play
SqlItem = "SELECT SongID " "FROM PlaylistItems " "WHERE PlaylistID=sPlaylistID";
SqlItem.Format("SELECT * FROM PlaylistItems WHERE PlaylistID = %s",sPlaylistID);
recItem.Open(CRecordset::f
recItem.GetFieldValue("Son
iRec = m_ListCtrl.InsertItem(i,sS
i++;
m_ListCtrl.SetItemText(i,1
i++;
recset.MoveNext();
}
database.Close();
}
CATCH(CDBException, e)
{
AfxMessageBox("Database error: "+e->m_strError);
}
END_CATCH;
}
Please check the return values of *database.Open* and *recset.Open*
Rosh :)
Rosh :)
ASKER
I am getting the values of the sPlaylistID from Playlists Table. then with that value i need to get the SongID from Songs Table where sPlaylistID.
Please check the return values of *database.Open* and *recset.Open*
*** Also check the TABLE NAME and FIELD NAMES are correct ***. Sometimes I also experienced similar situation on a simple spelling mistake...
*** Also check the TABLE NAME and FIELD NAMES are correct ***. Sometimes I also experienced similar situation on a simple spelling mistake...
ASKER
The problem is with using WHERE .Coz when i remove it i am getting the values.
WHERE PlaylistID = %s",sPlaylistID
is PlaylistID a text field? If it is you need (NOTE single quotes in the WHERE clause).
WHERE (PlaylistID='%s')",sPlayli stID
is PlaylistID a text field? If it is you need (NOTE single quotes in the WHERE clause).
WHERE (PlaylistID='%s')",sPlayli
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
its not working .PlaylistID is of type Number(Auto).
Put a breakpoint at
recItem.Open(CRecordset::f orwardOnly ,SqlItem,C Recordset: :readOnly) ;
now see what the SqlItem variable contains.
recItem.Open(CRecordset::f
now see what the SqlItem variable contains.
Here I checked my MDB file, and it is working fine.... did you checked the spelling of that field?
ASKER
s
ASKER
I got thru another method
CString SqlString,SqlItem,csSongID
int nPlayID = 3;
int nSongID;
CRecordset recItem( &database );
SqlItem.Format("SELECT SongID FROM PlaylistItems WHERE PlaylistID == %d", nPlayID);
recItem.Open (CRecordset::forwardOnly,S
recItem.GetFieldValue((sho
nSongID = atoi(csSongID);
Good Luck