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);

sarath83Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Roshan DavisCommented:
Ooops only need one equal (=)

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

Rosh :)

0
AndyAinscowFreelance programmer / ConsultantCommented:
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
AndyAinscowFreelance programmer / ConsultantCommented:
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
AndyAinscowFreelance programmer / ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sarath83Author Commented:
its not working .PlaylistID is of type Number(Auto).
0
AndyAinscowFreelance programmer / ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.