Solved

To Retrieve value from Database

Posted on 2003-11-30
23
270 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
xyBalance chalenge 58 88
Add content to output file 4 58
not able to insert into temp table 68 150
FizzBuzz challenge 9 73
Here is how to use MFC's automatic Radio Button handling in your dialog boxes and forms.  Beginner programmers usually start with a OnClick handler for each radio button and that's just not the right way to go.  MFC has a very cool system for handli…
This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now