• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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,SqlItem,CRecordset::readO
nly);

recItem.GetFieldValue(" SongID ",sPlaylistID);

0
sarath83
Asked:
sarath83
  • 11
  • 8
  • 4
1 Solution
 
Roshan DavisCommented:
     CDatabase database;
      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,SqlItem,CRecordset::readOnly);

      recItem.GetFieldValue((short)0,csSongID);

      nSongID = atoi(csSongID);

Good Luck
0
 
sarath83Author Commented:
I am getting an  error  "Syntax error (missing operator) in querey experssion in PlaylistID==3"
0
 
sarath83Author Commented:
I am getting an  error  "Syntax error (missing operator) in querey experssion in PlaylistID==3"
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Roshan DavisCommented:
Ooops only need one equal (=)

SqlItem.Format("SELECT SongID FROM PlaylistItems WHERE PlaylistID = %d", nPlayID);

Rosh :)

0
 
AndyAinscowCommented:
For MS Access use can the CDaoDatabase and CDAORecordset (and other CDAO.. classes).  They are specific for the Jet engine (maybe better performance).
0
 
sarath83Author Commented:
Now error is Too few parameters expected in  below to the sql stirng line in the code
0
 
Roshan DavisCommented:
Try

SqlItem.Format("SELECT * FROM PlaylistItems WHERE PlaylistID == %d", nPlayID);

Is that working...?

Rosh :)
0
 
sarath83Author Commented:
 recItem.Open (CRecordset::forwardOnly,SqlItem,CRecordset::readOnly);

After this line execution i am getting dialog box Select souce data . and after selecting the source Assertion failure is occuring.
0
 
sarath83Author Commented:
no that is not workng.
0
 
Roshan DavisCommented:
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,SqlItem,CRecordset::readOnly);

      recItem.GetFieldValue((short)0,csSongID);

      nSongID = atoi(csSongID);

Good Luck
0
 
AndyAinscowCommented:
Would brackets (and no spaces) make any difference in the where clause?
SqlItem.Format("SELECT * FROM PlaylistItems WHERE (PlaylistID=%ld)", nPlayID);
0
 
sarath83Author Commented:
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\\PPSimulator\\PPSimulator.mdb";
            int iRec = 0;
            sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,sFile);
            TRY
      {
                      database.Open(NULL,false,false,sDsn);
            CRecordset recset( &database );
            CRecordset recItem( &database );
                        
            SqlString =  "SELECT PlaylistID " "FROM Playlists ";
            
            recset.Open(CRecordset::forwardOnly,SqlString,CRecordset::readOnly);
                  
      while( !recset.IsEOF() )
            {
                           recset.GetFieldValue("PlaylistID",sPlaylistID);
            
               SqlItem = "SELECT SongID " "FROM PlaylistItems " "WHERE PlaylistID=sPlaylistID";
               SqlItem.Format("SELECT * FROM PlaylistItems WHERE PlaylistID = %s",sPlaylistID);
            
                                    recItem.Open(CRecordset::forwardOnly,SqlItem,CRecordset::readOnly);
                recItem.GetFieldValue("SongID",sSongID);
                iRec = m_ListCtrl.InsertItem(i,sSongID,0);
                i++;

                                     m_ListCtrl.SetItemText(i,1,sCategory);
                  i++;
            recset.MoveNext();
            }
            database.Close();
      }
      CATCH(CDBException, e)
      {
            AfxMessageBox("Database error: "+e->m_strError);
      }
      END_CATCH;      
      
      }
0
 
Roshan DavisCommented:
Please check the return values of *database.Open* and *recset.Open*

Rosh :)
0
 
sarath83Author Commented:
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.
0
 
Roshan DavisCommented:
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...
0
 
sarath83Author Commented:
The problem is with using WHERE .Coz when i remove it i am getting the values.
0
 
AndyAinscowCommented:
WHERE PlaylistID = %s",sPlaylistID

is PlaylistID a text field?  If it is you need  (NOTE single quotes in the WHERE clause).
WHERE (PlaylistID='%s')",sPlaylistID
0
 
Roshan DavisCommented:
Check the spelling of "PlaylistID" in Access database...
0
 
sarath83Author Commented:
its not working .PlaylistID is of type Number(Auto).
0
 
AndyAinscowCommented:
Put a breakpoint at
                                    recItem.Open(CRecordset::forwardOnly,SqlItem,CRecordset::readOnly);
now see what the SqlItem variable contains.
0
 
Roshan DavisCommented:
Here I checked my MDB file, and it is working fine.... did you checked the spelling of that field?
0
 
sarath83Author Commented:
s
0
 
sarath83Author Commented:
I got thru another method
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 11
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now