Solved

To Retrieve value from Database

Posted on 2003-11-30
23
272 Views
Last Modified: 2013-11-20
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
Comment
Question by:sarath83
  • 11
  • 8
  • 4
23 Comments
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 9848520
     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
 

Author Comment

by:sarath83
ID: 9848548
I am getting an  error  "Syntax error (missing operator) in querey experssion in PlaylistID==3"
0
 

Author Comment

by:sarath83
ID: 9848549
I am getting an  error  "Syntax error (missing operator) in querey experssion in PlaylistID==3"
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 23

Expert Comment

by:Roshan Davis
ID: 9848580
Ooops only need one equal (=)

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

Rosh :)

0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9848622
For MS Access use can the CDaoDatabase and CDAORecordset (and other CDAO.. classes).  They are specific for the Jet engine (maybe better performance).
0
 

Author Comment

by:sarath83
ID: 9848623
Now error is Too few parameters expected in  below to the sql stirng line in the code
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 9848722
Try

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

Is that working...?

Rosh :)
0
 

Author Comment

by:sarath83
ID: 9848750
 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
 

Author Comment

by:sarath83
ID: 9848770
no that is not workng.
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 9848776
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9848781
Would brackets (and no spaces) make any difference in the where clause?
SqlItem.Format("SELECT * FROM PlaylistItems WHERE (PlaylistID=%ld)", nPlayID);
0
 

Author Comment

by:sarath83
ID: 9848885
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
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 9848923
Please check the return values of *database.Open* and *recset.Open*

Rosh :)
0
 

Author Comment

by:sarath83
ID: 9848961
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
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 9849001
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
 

Author Comment

by:sarath83
ID: 9849109
The problem is with using WHERE .Coz when i remove it i am getting the values.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9849153
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
 
LVL 23

Accepted Solution

by:
Roshan Davis earned 30 total points
ID: 9849155
Check the spelling of "PlaylistID" in Access database...
0
 

Author Comment

by:sarath83
ID: 9849187
its not working .PlaylistID is of type Number(Auto).
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9849196
Put a breakpoint at
                                    recItem.Open(CRecordset::forwardOnly,SqlItem,CRecordset::readOnly);
now see what the SqlItem variable contains.
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 9849201
Here I checked my MDB file, and it is working fine.... did you checked the spelling of that field?
0
 

Author Comment

by:sarath83
ID: 9849238
s
0
 

Author Comment

by:sarath83
ID: 9849240
I got thru another method
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
xyBalance chalenge 58 93
iSeries DB2 Query 2 95
Shell script to login to remote m/c and execute commands 10 78
Problem to event 3 92
Introduction: Dynamic window placements and drawing on a form, simple usage of windows registry as a storage place for information. Continuing from the first article about sudoku.  There we have designed the application and put a lot of user int…
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question