Strange! Strange ! Strange!

[dickylam]

I am using MS-SQL Server 2000 for database and use ADO as well as SQLVIEW to open, edit and save the tables.

For details, I put my code here:-

main_ado = createobject('adodb.connection')
main_ado.open('maindb','sa')

The above lines declare an object and open maindb database which has been defined via ODBC, I am using Win2000 professional and the SQL-2000 server is installed on this also.

Now, I open anytable, say customer, the code will be

customer_db = createobject('adodb.recordset')
customer_db.open('customer',cm_main_ado,2,3)

the customer table is one of the tables under the database of maindb.

Once I open the customer table, I can use the normal VFP command to take and save any records, any fields into some temp files, like the following code I want to copy all customer name into a temp table and use it into list box:-

store substr(sys(2015),3,10) to temp_custdb

create table &temp_custdb (c_name c(50))

since the database file is open after the "create table.." executed, I can then perform a loop to copy the data from the SQL table into this temp table

With customer_db
if .not. .bof
.movefirst
do while .not. .eof
store .fields('cust_name').value to zcust_name
*
select &temp_custdb
append blank
replace c_name with trim(zcust_name)

.movenext
loop
enddo
endif
Endwith

After that, in a form, I have one listbox object, say clistbox, I than declare the following code into the form.init method (temp_custdb is declared as public variable when the top program runs)

select &temp_custdb

store 'c_name' to zcust_namerow
thisform.clistbox.rowsource = zcust_namerow


NOW, STRANGE HAPPENS....
I HAVE BEEN RUNNING THE ABOVE CODE VERY WELL IN THE PAST 2 MONTHS.... BUT SUDDENDLY, TODAY, NO RECORDS CAN BE SEEN, BUT IN SQL SERVER ENTERPRISES MANAGER I CAN STILL SEE ALL RECORDS....

I REALLY MIS-UNDERSTANDING WHAT HAPPENS WITH VFP.. I FEEL MUCH HEADACHE IN VFP, MANY UNKNOWN BUGS....SOMETIMES...

ANYONE IF READ THIS USAGE CAN HELP ME TO SOLVE OUT ?

MUCH THANKS.

 

[craigsboyd]

When you say "NO RECORDS CAN BE SEEN" are you talking about the recordset being empty OR the temp table you create is empty OR the listbox is empty?

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[dickylam]

After chasing the debugging mode, the select &temp_custdb return an error

""Command is missïng ....."

 

[craigsboyd]

In the future...

select (temp_custdb) &&No need for macro substitution

So does that table exist that temp_custdb is holding the name to?

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[craigsboyd]

When you are generating a name for your dbf use sys(2015) instead of substr(sys(2015),3,10)...the latter of these will produce a name that starts with a numeric value which is not a legal DBF name. From the VFP helpfile:

"...a valid FoxPro cursor or alias name cannot begin with a number."

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[dickylam]

Yes, this is true, I found that, if the string substr(sys(2015),3,10) starts with a numeric value, the error arise. therefore, anybody know which portion or random string is reliable to generate. ?

 

[craigsboyd]

Well you could just use SYS(2015) since it always starts with an underscore that would be a legal name...

...if however you are trying to confor to DOS 8.3 naming conventions then you can use:

"_"+SUBSTR(SYS(2015), 4)

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]