Refreshing xbrowse when dataset is closed and re-opened

[James Bott]

oRs:Open('SELECT FIRST, LAST FROM CUSTOMER WHERE [STATE] = "NY"', oCn)

Fails also.

I don't think it has anything to do with the STATE fieldname since this also fails:

oRs:Open('SELECT FIRST, LAST FROM CUSTOMER WHERE LAST = "Fuller" ', oCn)

James

[nageswaragunupudi]

So, on your system WHERE clause is giving problem. Surprising but we need to find the reason.
Mean while you can connect to test.mdb through Access using the same connection sting as in testxbr3.prg. There also the customer table has the same field structure. The same sql should work there.

[James Bott]

Rao,

Are you suggesting that I test the Access table just to test the SQL syntax?

James

[nageswaragunupudi]

Rao,

Are you suggesting that I test the Access table just to test the SQL syntax?

James

Yes.
Actually the original sample I posted should work with Access by changing only one line of code .

Code: Select all  Expand view  RUN

/*
   local cStr  := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
                  FWPATH + ;
                  ";Extended Properties=dBASE III;User ID=Admin;Password=;"
*/
   local cStr   := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
                    FWPATH + "xbrtest.mdb;User Id=admin;Password=;"
 

I am wondering why WHERE clause is failing at your end.

[anserkk]

Hi,

This is to confirm that Mr.Rao's sample is working fine for me. I am also curious to know why it is failing in Mr.James PC

Tested on Windows 7 Professional.

BTW Mr.James which windows version do you use ?. May be the MDAC is corrupted in your PC.

MDAC version Pre-installed along with the Windows 7



Regards

Anser

[nageswaragunupudi]

The same logic was working for me even on Windows XP, though I am now using Windows 7.

[James Bott]

Anser,

I am using XP Pro. My registry shows FullInstallVer 2.81. Maybe this is the issue. Is there a way to update this?

James

[nageswaragunupudi]

Mr. James
We need to see why Jet DBase driver is giving problems for you.
I suggest you try Access example. The connection string I have given for Access is also through Jet. Let is see if the version you have works well on Access. You can either use the above sample or testxbr3.prg, by adding a WHERE clause in the sql string there. After this experiment we can decide about upgrading MDAC.

You may google for MDAC. I think MDAC 2.8 SP1 is the latest for XP

[Carles]

James,

Is very impportant the string connection. Some times we think that it's ok but it can be with wrong parameters. I think that u need to try with string that give you Datalink. Can you test this sample ?

Code: Select all  Expand view  RUN

#include 'fivewin.ch'
*--------------
FUNCTION Main()
*--------------
   LOCAL oDataLink := TOleAuto():New("Datalinks") ,;
         oConn     := oDataLink:PromptNew()
   LOCAL cString   := IF( oConn == NIL, '', oConn:connectionString )
   LOCAL oCn, oRs
   LOCAL oError
   LOCAL cSql      := "SELECT * FROM CUSTOMER WHERE STATE ='NY'"

   IF Empty( cString )
      RETU NIL
   ENDIF

   MsgInfo( cString, 'Connection String' )

   oCn   := TOleAuto():New( "ADODB.Connection" )
   oCn:Open( cString )

   WHILE MsgGet( 'SQL', 'Check Sql', @cSql )

      cSql := Alltrim( cSql )

      TRY

        oRs   := TOleAuto():New( "ADODB.RecordSet" )
        oRs:Open( cSql, oCn , 1, 3 )

        xBrowse( oRs )

        oRs:Close()

       CATCH oError

        xBrowse( oError )

      END

      cSql += Space( 200 )

   END

RETU NIL

[James Bott]

Carles,

Your sample code errors out with or without the WHERE clause. I am not sure it is finding the customer.dbf file. It is in the same directory as the EXE, but to I still have to specify the path?

The error message is the same; unknownName "Open."

James

[James Bott]

Rao,

The WHERE clause works fine with the Access database.

James

[nageswaragunupudi]

Rao,

The WHERE clause works fine with the Access database.

James

Rao,

The WHERE clause works fine with the Access database.

James

Good.
That means the Jet driver that is installed on your PC for DBase III is not supporting complex syntax like WHRE or ORDER.

I think we can leave this issue for the time being. Anyway we rarely use ADO for accessing DBFs.

Now we can try the original sample code I posted with the connection string I provided for Access instead of the one for DBase. Rest of the code works the same way whether it is DBase of Access.

In this process we lost sight of the main thread. My intention in posting the sample code was to demonstrate one of the ways to change the recordset at runtime with *ease*.

In Mr. Rick's case it was only an issue of vairable management. I shall try to post another sample explaining this issue in greater detail.

[nageswaragunupudi]

This sample does the same thing as my original sample. But explains the usage of variables in a clearer way.

Code: Select all  Expand view  RUN

#include 'fivewin.ch'
#include 'xbrowse.ch'

#define BS Chr(92)    // BackSlash
#define FWPATH "c:" + BS + "fwh" + BS + "samples" + BS

static oCn

function Main()

   local oDlg, oBrw, oRsCust
   local cState   := 'NY'

   oRsCust  := GetRecordSet( cState )
   // now the local variable oRsCust points to the recordset ( state = NY )

   DEFINE DIALOG oDlg SIZE 600,400 PIXEL
   @ 30,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;
      AUTOCOLS AUTOSORT RECORDSET oRsCust ;
      CELL LINES NOBORDER

   // At this state, both the local variable oRsCust and
   // oBrw:oRs point to the same record set.
   // Any operations done on oRsCust or oBrw:oRs affect the same recordset object.
   // If we write oRsCust:Close(), it closes the recordset that is referred to by oBrw:oRs also.

   oBrw:CreateFromCode()

   // On Change code here can be writting in many ways as long as we keep in mind
   // what variables are pointing to what recrodset

   @ 10,240 COMBOBOX cState ITEMS { "AK", "AL", "AR", "AZ", "CA", "DA", "GE", "HI", "NY" } ;
      SIZE 50,100 PIXEL OF oDlg ;
      ON CHANGE ( ; // Both oRsCust and oBrw:oRs refer to same old recordset.
         oRsCust  := nil, ; // now oBrw:oRs still refers to the old recordset and oRsCust is dilinked
         oRsCust  := GetRecordSet( cState ), ; // oRsCust refers to new and oBrw:oRs refers to the old recset
         oBrw:oRs:Close(), oBrw:oRs := oRsCust, ; // now again both refer to the same new recordset
         oBrw:GoTop(), oBrw:Refresh(), oBrw:SetFocus() )

   ACTIVATE DIALOG oDlg CENTERED ON INIT ( oBrw:SetFocus(), .f. )

return nil

static function GetRecordSet( cState )

/*
   local cStr  := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
                  FWPATH + ;
                  ";Extended Properties=dBASE III;User ID=Admin;Password=;"
*/
   local cStr   := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
                    FWPATH + "xbrtest.mdb;User Id=admin;Password=;"

   local cSql  := 'SELECT FIRST,CITY,STATE FROM CUSTOMER WHERE STATE = "?" ORDER BY FIRST'
   local oRs

   if oCn == nil
      oCn   := TOleAuto():New( "ADODB.Connection" )
      oCn:Open( cStr )
      oCn:CursorLocation   := 3 // adUseClient
   endif

   oRs   := TOleAuto():New( "ADODB.RecordSet" )
   oRs:Open( StrTran( cSql, '?', cState ), oCn )

return oRs
 

As I said above we can write the ON CHANGE code in many ways as long as we keep in mind what recordset the oBrw is actually browsing.
Note: Please give your fwh samples path in the define

[James Bott]

Rao,

Yes, you are right, the sample does work fine to show how to switch recordsets when I am using the Access file.

I got sidetracked when I saw that you could execute SQL statements agains DBFs. This would be very useful for me, especially for some summary reports.

I did find a Microsoft program to check the MDAC components on a PC, and it seems that I have two copies of the MDAC installed (with slightly different names, but they have the same version numbers). I guess this is what is causing the problem. I have no idea how to fix this however.

Thanks for the example.

James

[nageswaragunupudi]

Mr James

Code: Select all  Expand view  RUN

I got sidetracked when I saw that you could execute SQL statements agains DBFs. This would be very useful for me, especially for some summary reports.
 

Yes. I am sure using SQL with DBFs make summary reports extremely easy, avoids lengthy coding and therefore bug-free. It is much easier to maintain the code too. May or may not be faster in execution but difference in speeds may not be perceptible.

Except in a very rare case like your installation, this works well on all installations of XP and above.