Kadrin


Hey everyone,

You may have read my post the other day about inserting records into a table, well I did get that sorted out.

However, once I finished the insert statements I put them into a DO WHILE loop and had it working yesterday before leaving the office. Today, I am having difficulties, there was an error when I ran the INSERTs , it said "End of File Encountered". The expression of the while was:

DO WHILE !EOF("curtble1")
<inserts>
SKIP
ENDDO

The skip keyword was highlighted. I removed the skip, ran the program and VFP froze. Well how do I get the corrupted table back and how do I prevent this

Thanks.



Re: Corrupted Table after attempting inserts.

Naomi Nosonovsky


Are you scanning the same table you're inserting records into When you are going to reach the end then

Assuming you're doing inserts into another table, you can re-write your code this way:

Code Snippet

select myTable

scan

insert into MyOtherTable ...

endscan

Now, what exactly you're trying to achieve If you want to insert all records from one table into another (or by some condition) you don't have to scan the original table in VFP8 and above. Look at the INSERT-SQL command in Help, you can use the third form to do it in one shot.

Hope this helps.






Re: Corrupted Table after attempting inserts.

dni

You may try:

Code Snippet

SELECT curtble1

GO TOP

DO WHILE .T.

<inserts>

IF .NOT.EOF()

SKIP

ELSE

EXIT

ENDIF

ENDDO







Re: Corrupted Table after attempting inserts.

Kadrin

I am trying to add records from an SQL table to a table I have in a VFP database. Some of the records I am inserting are of different data types so I added the appropriate code to convert them. However I was advised to add the SKIP, I misunderstood because I simply added the skip, instead I needed it to say:

SKIP IN curtbl1

I haven't been able to test this yet though, my table was corrupted, I deleted from the database environment, because I have the code saved that creates it (the records in the table were merely test data). But, when I go to create the table, I receive an error: "File <name> exists as part of a database." How can that be if I deleted it I have checked to make sure it was in fact deleted, and it was. Anyone




Re: Corrupted Table after attempting inserts.

Naomi Nosonovsky

Ouch, it's a bad one. I had the same problem while back, let me research a solution.

For now make a backup of a database.

You may have to open your database as a table (use myDatabase.DBC exclusive) and search for the name of this table.

Then delete this record and do PACK.

Then close the database as a table and try:

OPEN DATABASE myDatabase EXCLUSIVE

VALIDATE DATABASE RECOVER

See, if this would work. But as I said, make sure to do a backup first.

Also please give us more info about your INSERT problem, it looks to me you're trying to do it the hard way and the code may be simplified.





Re: Corrupted Table after attempting inserts.

Kadrin

I'll be sure to try that but for now here is some information on my inserts:

I'm grabbing a table from an SQL server and connecting it to VFP using SQLConnect() and SQLExec(), which are just fine. The inserts were a bit difficult to figure out, I had to get the data type of the SQL table and convert it to the appropriate data type if they didn't match. I did test each column individually to make sure it worked and it did (meaning I did a lot of deletes and packs later on). Mostly I used STR() and IIF(ISNULL()) since I didn't want NULLs in my table.

Then put them into a while loop like I said on my earlier post.







Re: Corrupted Table after attempting inserts.

Naomi Nosonovsky

Once you get your cursor in VFP and you want to insert records into VFP table which doesn't allow NULLs you may use NVL function. IIF(ISNULL is the same as NVL does.

Also you don't need DO WHILE !EOF() loop, just use SCAN/ENDSCAN or do inserts in one shot using INSERT-SQL command, if the fields transformations are not very complicated.





Re: Corrupted Table after attempting inserts.

Kadrin

Ok, I used the VALIDATE DATABASE RECOVER, and recreated the table with no errors. Then I added the code you suggested. Now my table is in the database with the data from the SQL table. As well as the other two tables I had to do the same thing too.

Thanks alot for the help!

Kadrin.




Re: Corrupted Table after attempting inserts.

Naomi Nosonovsky

You're welcome. Always glad to help.