SELECT statement not working

[hallm]

This is the other part of the export file that I'm working on. For some reason the text file that is created only has the first record duplicated. It doesn't list all the records for a certain date. The code was posted in answer to one of my posts in the VFP Forms forum and just used a SELECT tablename statement before the SCAN command. I origanlly tried putting the SELECT statement there, but I was getting errors so then I moved it to the top of the code and saved results into a cursor, but I still can't get it to give me all of the results for a certain date.

What am I doing wrong?

Thanks,
Marion

dValue = THISFORM.paydatevar.Value
SELECT employeepay.ssn, employeepay.employeename, employeepay.employeesalary, employeepay.employeecont;
FROM mers!employeepay;
WHERE employeepay.paydate = dValue;
ORDER BY employeepay.employeename;
INTO CURSOR temp

lcFileName = "test.TXT"
l_nHandle = FCREATE(lcFileName, 0)
IF l_nHandle < 0
*l_cOpenerror = LUFE(FERROR())
l_cOpenerror = "Please Try Something Else"
messagebox("Error Creating MEDCERT file."+;
CHR(13)+l_cOpenerror, d_nOkbutton+d_nStopsign, "Export Data")
RETURN
ENDIF && l_nHandle < 0

SELECT temp
lnMyRecCnt = 0
SCAN
lcBuffer = ""
* kills leading '0's for candidate ID's
lcBuffer = lcBuffer + "D" && 1
lcBuffer = lcBuffer + "0" && 2
lcBuffer = lcBuffer + employeepay.ssn && 3-11
lcBuffer = lcBuffer + employeepay.employeename && 12-35
lcBuffer = lcBuffer + " " && 36-39
lcBuffer = lcBuffer + employeepay.employeesalary && 40-48
lcBuffer = lcBuffer + employeepay.employeecont && 49-57
lcBuffer = lcBuffer + " " && 58-66
lcBuffer = lcBuffer + "City of Many" && 67-80

=FPUTS(l_nHandle, lcBuffer) && Write with CRLF
lnMyRecCnt = lnMyRecCnt + 1

ENDSCAN &&*

= FCLOSE(l_nHandle)

 

[Mike Gagnon]

Does your cursor contain the information you are looking for?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[hallm]

It does, I've got two different sets of code that I'm playing with and comparing one I asked a question about in an earlier forum. It uses COPY TO filename.txt TYPE SDF and works fine as far as the data it produces. I'm just needed to append another sql query to the bottom of that file and that's why I'm playing with the current code.

It's the same sql query as the first code. The text file produced justs takes the first record and duplicates it. Do SCAN and END SCAN work well with data stored in cursors or should I do it another way.



Marion

 

[Mike Gagnon]

Is it possible you are not actually skipping line in your text document? That it is always writing on the same line?

 =FPUTS(l_nHandle, lcBuffer)    && Write with CRLF

Try adding a FSEEK(l_nHandle, 0, 2) after your FPUTS()



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[hallm]

I added the FSEEK command after FPUTS but it's still writing the same record two times. If I put a third record it makes a text file with the same record three times. So it knows how many records I have it just repeats the first record for as many record are in there.

Like you said its probably not going to the next record. Any suggestions?

Marion

 

[mspratt]

Hi Hallm,
...
lcBuffer = lcBuffer + "City of Many"
lcBuffer = lcBuffer + CHR(13) + CHR(10)

Then use "STRTOFILE(lcBuffer, lcFileName, 1)" and lose the low level file functions. (Start by erasing test.TXT before the SCAN if it exists)

Regards,

Mike

 

[Mike Gagnon]

Another option might be

SELECT temp
lnMyRecCnt = 0
do while !eof()
scatter memvar
lcBuffer = ""
* kills leading '0's for candidate ID's
lcBuffer = lcBuffer + "D" && 1
lcBuffer = lcBuffer + "0" && 2
lcBuffer = lcBuffer + m.ssn && 3-11
lcBuffer = lcBuffer + m.employeename && 12-35
lcBuffer = lcBuffer + " " && 36-39
lcBuffer = lcBuffer + m.employeesalary && 40-48
lcBuffer = lcBuffer + m.employeecont && 49-57
lcBuffer = lcBuffer + " " && 58-66
lcBuffer = lcBuffer + "City of Many" && 67-80

=FPUTS(l_nHandle, lcBuffer) && Write with CRLF
lnMyRecCnt = lnMyRecCnt + 1

select tmp
skip
enddo
= FCLOSE(l_nHandle)


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[hallm]

It's working the only thing I did was remove the tablenames from the lcbuffer variable. It may have been stupid for me to have had them there in the first place. The original code works fine now.

I posted the working code below in case it helps anyone.

Thanks for your help.

Marion

dValue = THISFORM.paydatevar.Value
SELECT employeepay.ssn, employeepay.employeename, employeepay.employeesalary, employeepay.employeecont;
FROM mers!employeepay;
WHERE employeepay.paydate = dValue;
ORDER BY employeepay.employeename;
INTO CURSOR temp

lcFileName = dValue+"test.TXT"
l_nHandle = FCREATE(lcFileName, 0)
IF l_nHandle < 0
*l_cOpenerror = LUFE(FERROR())
l_cOpenerror = "Please Try Something Else"
messagebox("Error Creating MEDCERT file."+;
CHR(13)+l_cOpenerror, d_nOkbutton+d_nStopsign, "Export Data")
RETURN
ENDIF && l_nHandle < 0

SELECT temp
lnMyRecCnt = 0
SCAN

lcBuffer = ""
* kills leading '0's for candidate ID's
lcBuffer = lcBuffer + "D" && 1
lcBuffer = lcBuffer + "0" && 2
lcBuffer = lcBuffer + ssn && 3-11
lcBuffer = lcBuffer + employeename && 12-35
lcBuffer = lcBuffer + " " && 36-39
lcBuffer = lcBuffer + employeesalary && 40-48
lcBuffer = lcBuffer + employeecont && 49-57
lcBuffer = lcBuffer + " " && 58-66
lcBuffer = lcBuffer + "City of Many" && 67-80

=FPUTS(l_nHandle, lcBuffer) && Write with CRLF

lnMyRecCnt = lnMyRecCnt + 1



ENDSCAN &&*

= FCLOSE(l_nHandle)

 

[dbMark]

Yes, you were referencing the wrong table's fields. You had been scanning through the temp table but rather than reading/writing its fields as you expected, you were reading/writing the employeepay fields which of course stayed on the same record.

Technical cause: Programmer's dyslexia.
Victims: All.