Refreshing xbrowse when dataset is closed and re-opened

[James Bott]

Rao,

I tried your sample code (2 messages above) and it errors out on this line.

oRs:Open( StrTran( cSql, '?', cState ), oCn )

I note that:

oRs := TOleAuto():New( "ADODB.RecordSet" )

Is returning an object, but it doesn't seem to have an Open() method.

I don't know much about ADO, so I don't see the problem. Perhaps I need to like in another LIB? I am using the standard BUILDX.BAT file and xHarbour with FWH 10.3.

Regards,
James

Code: Select all  Expand view

Application
===========
   Path and name: C:\Temp\TestADO.exe (32 bits)
   Size: 1,801,728 bytes
   Time from start: 0 hours 0 mins 0 secs
   Error occurred at: 07/15/10, 07:07:22
   Error description: Error ADODB.RecordSet/6  DISP_E_UNKNOWNNAME: OPEN
   Args:
     [   1] = C   SELECT FIRST,CITY,STATE  FROM CUSTOMER WHERE STATE = "NY" ORDER BY FIRST
     [   2] = O   ADODB.Connection

Stack Calls
===========
   Called from: source\rtl\win32ole.prg => TOLEAUTO:OPEN(0)
   Called from: TestADO.prg => MAIN(22)

[Rick Lipkin]

James

What SQL back end are you using ?? Sql Server, Access ??. Each database has it's own connection string .. how did you set up your oCn ( connection ) .. also .. do not use double quotes in the sql string .. I put single quotes around your 'N' .. or you could use a like statement if you are looking for NY

cSQL := "SELECT FIRST,CITY,STATE FROM CUSTOMER WHERE STATE = 'N' ORDER BY FIRST"

cSQL := "SELECT FIRST,CITY,STATE FROM CUSTOMER WHERE STATE like 'N%' ORDER BY FIRST"

Hope this helps

Rick Lipkin

[James Bott]

Rick,

The simple answer is that I am just trying to run the sample code that Rao published a few messages back in this thread. I have copied it here.

It errors out on the oRs:Open() method with an unknown name "open" error. Maybe I have to install some ADO driver?

James

Code: Select all  Expand view

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

#define FWPATH "c:\fwh\samples\"

static oCn

function Main()

   local cStr  := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
                  FWPATH + ;
                  ";Extended Properties=dBASE III;User ID=Admin;Password=;"
   local cSql  := 'SELECT FIRST,CITY,STATE  FROM CUSTOMER WHERE STATE = "?" ORDER BY FIRST'
   local oDlg, oBrw, oRs
   local cState   := 'NY'

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

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

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

   oBrw:CreateFromCode()

   @ 10,240 COMBOBOX cState ITEMS { "AK", "AL", "AR", "AZ", "CA", "DA", "GE", "HI", "NY" } ;
      SIZE 50,100 PIXEL OF oDlg ;
      ON CHANGE ( ;
         oBrw:oRs:Close(), ;
         oBrw:oRs:Open( StrTran( cSql, "?", cState ), oCn ), ;
         oBrw:GoTop(), oBrw:Refresh(), oBrw:SetFocus() )

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

return nil

[nageswaragunupudi]

Mr. James

You do not have to install anything. This is Jet driver ( installed on all PCs by default ) and is for working on DBase III tables.

It works perfectly for me.
Only possibility is that FWPATH may not be correctly pointing to your \fwh\samples folder.
When you copy and paste from forums, the backslash is not properly pasted in some cases.
Please manually ensure that FWPATH exactly points to fivewin samples folder. It should start with drive letter and finally end with backslash. And the entire path should be enclosed in quotes.

Also please ensure that the CUSTOMER.DBF with the fields FIRST, CITY and STATE does exist in that folder.

[James Bott]

Rao,

Please manually ensure that FWPATH exactly points to fivewin samples folder. It should start with drive letter and finally end with backslash.

As I mentioned in one of my previous messages, it is correct for my system.

#define FWPATH "c:\fwh\samples\"

I have just double checked it, and it is correct. Still just calling oRs:Open() without any parameters also generates the same error. This eliminates the path issue. It would seem that the oRS object that is created does not have an Open method; unless the error description is incorrect.

Curiously, I can call oRs:refresh() without any error.

Regards,
James

[James Bott]

Oh, and the customer.dbf file does exist in the path. I had already checked that too.

James

[nageswaragunupudi]

Rao,

Please manually ensure that FWPATH exactly points to fivewin samples folder. It should start with drive letter and finally end with backslash.

As I mentioned in one of my previous messages, it is correct for my system.

#define FWPATH "c:\fwh\samples\"

I have just double checked it, and it is correct. Still just calling oRs:Open() without any parameters also generates the same error. This eliminates the path issue. It would seem that the oRS object that is created does not have an Open method; unless the error description is incorrect.

Curiously, I can call oRs:refresh() without any error.

Regards,
James

Open method does exist, but it fails with proper parameters are not provided, The message is misleading.
I am surprised why should it fail on your system. I posted it only after testing it. I shall post a small test in my next post.

Yes, curiously oRs:Refresh() does not give any error, but this is no where officially documented and I am not sure what it does or does not do. I can be sure of what is documented by Microsoft and many other books on the subject.

[nageswaragunupudi]

Mr James

Can you please test this and see if the connection is opened successfully or not.

Code: Select all  Expand view

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

#define FWPATH "c:\fwh\samples\"

static oCn

function Main()

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

   oCn   := TOleAuto():New( "ADODB.Connection" )
   oCn:Open( cStr )
   if oCn:State > 0
      MsgInfo( 'Connection Opened', 'Success' )
      MsgInfo( oCn:ConnectionString, 'DBASE III' )
      oCn:Close()

   else
      MsgAlert( 'Connection Failed' )
   endif

return nil

[James Bott]

Rao,

Yes, that code works fine. I also had no trouble with the connection object in your previous sample code.

James

[James Bott]

Rao,

OK, I just tried this:

oRs:Open("SELECT * FROM CUSTOMER", oCn)

And it works fine!

So, the problem seems to be with the cSQL statement in your sample code. I don't know why it is working for you and not for me.

James

[James Bott]

These both cause it to crash:

Code: Select all  Expand view

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

It seems that the WHERE clause is causing the problem.

James

[James Bott]

This also causes a crash:

oRs:Open('SELECT * FROM CUSTOMER ORDER BY FIRST')

Now it looks like anything more compilcated that just a simple SELECT, causes a crash (no WHERE or ORDER clauses supported).

James

[nageswaragunupudi]

ok
the reason mus be that STATE is also a reserved word
And does your copy of customer.dbf contain STATE field?

Can you try this change?

Code: Select all  Expand view

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

And please don't forget replacing ? with CA or something

[James Bott]

Rao,

This crashes too:

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

This works:

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

James

[nageswaragunupudi]

'SELECT FIRST, LAST FROM CUSTOMER WHERE [STATE] = "NY"'
NY should be in double quotes