Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

To Retrieve value from Database

Posted on 2003-11-30
23
274 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pressing cancel button with C# UI Automation 3 81
matchUp  challenge 6 69
Problem to open Excel file 15 174
how to use laptop or pad camera in vb.net windows application 2 85
Introduction: Dialogs (1) modal - maintaining the database. Continuing from the ninth article about sudoku.   You might have heard of modal and modeless dialogs.  Here with this Sudoku application will we use one of each type: a modal dialog …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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