how to retrive data from database and store into textbox in Foxpro 9

Question

how do i store the data i get from select statement into textbox.
i don't have any idea how to do this because this is my first time using foxpro i need this for my job application pls help me.
table name: emp4win
idnum
firstname
lastname
middlename
address
contact

cm=thisform.cmb1.Text
SELECT * FROM emp4win WHERE idnum=cm

data should be store in the following:

txtfirstname.text
txtlastname.text
txtmiddlename.text
txtaddress.text
txtaddress.text
txtcontact.text<br/>

Reynan, I see several questions from you and you say you're just starting with VFP. You'll find some VFP videos listed at http://fox.wikis.com/wc.dll?Wiki~VFPVideos~VFP. You might also consider getting a book. This one is good for beginners (though it's VFP 6, not VFP 9): http://www.hentzenwerke.com/catalog/fund6.htm — Tamar E. Granor, Feb 25 '14 at 21:51

score 1 · Answer 1 · answered Feb 22 '14 at 21:47

I'm going to answer supposing that the table is a native VFP .dbf file. There are many ways to do this, one would be to use normal VFP table processing, something like this:

USE emp4win IN 0                  &&Open table
LOCAL cm
cm = thisform.cmb1.Text
*Validate the value of the input textbox here
SELECT emp4win                    &&Make the table active
LOCATE FOR idnum=cm               &&Search for the row you want
IF NOT FOUND("emp4win")
    *Handle error when the record doesn't exists
ENDIF
*From now on, just populate textboxes with the found data
thisform.txtfirstname.text = emp4win.firstname
thisform.txtlastname.text = emp4win.middlename
thisform.txtmiddlename.text = emp4win.lastname
thisform.txtaddress.text = emp4win.address
thisform.txtcontact.text = emp4win.contact

Another option would be to use the built-in VFP SQL engine to do the search, pretty much as you've done, just with a catch, remember you must indicate where to save the result in order to use it, almost always using an INTO CURSOR clause (and then reading from that cursor), otherwise, like in your sample, it will be shown in a BROWSE window, which is totally useless in an application.

Doesn't the code `USE emp4win IN 0` run the risk of having multiple copies of the table open with different aliases and then SELECTing the wrong one? It's been many years since I used foxpro but I believe you can use `USE emp4win IN SELECT("emp4win")` instead. This either opens the table in a new work area, or SELECTs the table if it is already open. — stuartd, Feb 23 '14 at 23:46
@stuartd Good point, yes I overlooked that possibility. However, if memory serves, it won't open a second copy (you need `AGAIN` and an explicit different alias for that), but instead throw a "table in use" error. What I usually do in more serious code is to ask if the table is `USED` before opening, and use it of so, or open otherwise, restoring the previous state when exiting. I've never saw the `IN SELECT("emp4win")` trick, but sound interesting. — Alejandro, Feb 24 '14 at 01:16

DRapp · Answer 2 · 2014-02-24T12:27:43.690

You were very close on your query and getting the result... If you use a SQL query without an "INTO" clause, it will just pull the results up in a browse display grid. By adding an INTO clause, it will put into some memory space, such as an array, or another cursor (but could also be another permanent table, but not suggested). Then, you can pull those values into the text properties of your textboxes...

I typically would do into a cursor (of which I prefix the result set with "C_" to know it is a temp cursor vs an actual table, and always pre-close the table in case I needed it left open for future use after the initial setting. This way, no remnant after the newest query.

use in select( "C_MyTmpResult" )
SELECT * FROM emp4win 
   WHERE idnum=cm
   into cursor C_MyTmpResult

data should be store in the following:

txtfirstname.Value = C_MyTmpResult.FirstName
txtlastname.Value = C_MyTmpResult.LastName
txtmiddlename.Value  = C_MyTmpResult.MiddleName
txtaddress.Value = C_MyTmpResult.Address
txtcontact.Value = C_MyTmpResult.Contact

You had 2 address lines, but no differentiation between an Address1 vs Address2 and might have just been a posting oversight, but context still holds true if you had two address lines.

how to retrive data from database and store into textbox in Foxpro 9

2 Answers2