Hi. I want to use 2 free tables in one button. For example I have a textbox named Text1. I want to replace in 'tabel1' the 'tabel1.value1' with Text1 value and in 'tabel2.value1' with same value. I tried to use simple those 2 table, but I receive: File is in use. If I try to close tables after using them (in button), but the tables can't be opened again for replacing.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
2 table in one button
- Thread starter DPaul1994
- Start date
- Thread starter
- #2
)Ensuring table is open before trying to reopening it.
if not used('YourFirstTable')
use YourFirstTable in 0
endif
if not used('YourSecondTable')
use YourSecondTable in 0
endif
to close a table:
use in select('YourTableName')
Ez Logic
Michigan
if not used('YourFirstTable')
use YourFirstTable in 0
endif
if not used('YourSecondTable')
use YourSecondTable in 0
endif
to close a table:
use in select('YourTableName')
Ez Logic
Michigan
Olaf Doschke
Programmer
If you need to store the same data in two tables that points to a bad database design, because of the redundancy.
Controlsource updates one field or variable, UPDATE-SQL updates in one table, REPLACE updates in One Workarea. But you can do two commands, can't you.
Once tables are open, they are open, there is no need to use tables in your button click. You either SELECT them or use the IN clause of REPLACE or do two UPDATEs with the two workarea names.
UPDATE-SQL like SELECT-SQL differs from the SELECT and REPLACE commands in opening the needed table itself.
Bye, Olaf.
Controlsource updates one field or variable, UPDATE-SQL updates in one table, REPLACE updates in One Workarea. But you can do two commands, can't you.
Once tables are open, they are open, there is no need to use tables in your button click. You either SELECT them or use the IN clause of REPLACE or do two UPDATEs with the two workarea names.
UPDATE-SQL like SELECT-SQL differs from the SELECT and REPLACE commands in opening the needed table itself.
Bye, Olaf.
vgulielmus
Programmer
In addition to the good suggestions you've already received, if you opened two tables, to switch between them, use the xBase command "SELECT"
You can make those two replacement without switching between tables, by using the 'IN' clause :
If your tables have an ID field, then you can use SQL UPDATE :
Respectfully,
Vilhelm-Ion Praisach
Resita, Romania
Code:
lValue = Text1.value
SELECT tabel1
REPLACE value1 WITH m.lValue
SELECT tabel2
REPLACE value1 WITH m.lValueYou can make those two replacement without switching between tables, by using the 'IN' clause :
Code:
lValue = Text1.value
REPLACE value1 WITH m.lValue IN table1
REPLACE value1 WITH m.lValue IN table2If your tables have an ID field, then you can use SQL UPDATE :
Code:
lValue = Text1.value
UPDATE tabel1 SET value1 = m.lValue WHERE ID = currentIdForTable1
UPDATE tabel2 SET value1 = m.lValue WHERE ID = currentIdForTable2Respectfully,
Vilhelm-Ion Praisach
Resita, Romania
- Thread starter
- #6
Olaf Doschke
Programmer
Paul,
before you go in any direction with your coding ideas, I suggest you learn data binding for leaner and simpler code.
1. The datasession concept
==========================
VFP supports several data sessions, each having a set of tables (or views, cursors, anything a workarea can hold, doesn't matter for now).
You can let each form either have it's own data session, starting out empty, or let it work in the current data session.
The prpoerty to set is datasession to either 1 (public=current) or 2 (private). That doesn't mean there are datasessions 1 and 2 only, 2 private means the forms starts a new datasession and gets a new datasessonid. That is another form property, too, and readonly. If you have several forms open, you could switch between the datasessions via SET DATASESSION, if knowing the IDs. That's not recommended, as your form then will lose it's data bindings, but just telling for how to peek into other forms data from anywhere. You don't need to worry about switching data sessions, if two forms have private datasessions, then activating a form (and deactivating the previous active form) activates its datasession. The only thing to keep in mind: If you want to start a form multiple times, eg displaying details of a single record, you give it a private datasession. If started twice these two forms each open the same table and don't cause an overlap. One datasession is active at any time. Even jsut starting VFP without any form open, you already have datsessionid 1 available. See Data Session in the Window menu.
2. The workarea concept
=======================
You open tables in workareas, these have both a number and a name. Often people confuse these workarea (alias) names with table names, as they normally are the same. Side note: File names - also DBF file names - can contain spaces and other characters. Ideally you refrain from using such characters in file names, also in paths. An alias name can't be given to two workareas, even if they would still be distinguishable by their workarea number, VFP prevents that, so alias names must be uniquely used. There always is a currently selected workarea in each datasession, unless no table is open.
If you open a table, which already is open, you would reuse the same alias name, which is impossible. That's not the error you got, you got File is in use. Before Foxpro checks about alias names already used, it also checks for files already being opened and errors, if you reopen a file, you already have the file - the table - open, simply continue working with it. The best practice therefore is to open your tables at the initialisation stage of your form, not interactively with a button click, then you don't open a file twice and therefore avoid that error.
You can do that to save file handles (it won't save much resources though), as you have been shown, if you first look out, if the table already is used via USED() function and only open it, if not.
Caution: Now the two concepts come into play in more detail, especially the workarea alias name concept: As said the alias name typically is the same as the main file name (without the path), so you also have a problem, if two tables in different paths have the same file name and therefore also the same alias name given automatically. And what you also can't do, even though the files differ is USE C:\data\mandant1\customers.dbf IN 0 and then also USE C:\data\mandant2\customers.dbf IN 0, that would error 'Alias name is already in use'. If you would want to open these two tables, you'd need to give them two alias names. One things is important about USED: If you'd check with USED("customer") after opening USE C:\data\mandant1\customers.dbf IN 0 you'd obviously get a .T. as return value, but not because the file C:\data\mandant1\customers.dbf is open, but because the alias name "customers" is used. If you now wanted data from C:\data\mandant2\customers.dbf you'd still need to change the situation. Take that as a side note, as it's not important, even if you'd have systems with data for several clients or mandants, you can solve that differently. You typically therefore don't name two different tables equally, if theay are part of the data you need to work with in one session. You rather switch between data of two different clients or customers by having two databases (DBCs) in two different paths with their own folders and then CLOSE TABLES ALL and CLOSE DATABASE of the current mandant and OPEN the other database. Once you have a database open, you also can USE database!tablename and the tablename may even differ from the DBF file name.
So overall: You can't use a file twice, you can't use an alias name twice.
Then you may have noticed me and also Ez Logic using a dbf with the addition IN 0. That is the work area number 0, it has a special meaning. If you USE some.dbf in 0 it's workarea is not 0 finally, it's simply the first free workarea. using a table without adding IN 0, you'd rather open a table in the currently active and selected workarea and that means you also close whatever is open currently. That's why USE is the command to open and to close tables. USE without any further additions simply closes what's open in the current workarea and doesn't specify anything to open.
So what does that mean for working on multiple tables: You USE them IN 0 once at form start and have them available throughout the form being present. You can activate a table by SELECT tablename, actually meaning aliasname of the workarea, it's just typically identical to avoid confusion. But you can also use the IN keyword in several commands like REPLACE, also GOTO 5 IN alias let's VFP go to record 5 in the workarea with that alias. Sepcifying IN, you don't need to SELECT some alias first. Several commands don't offer IN, eg LOCATE always works in the current workarea.
But mentioning record positions leads to the last concept:
3. The record concept
=====================
You always are in a) a currently set Database b) a currently set datasession c) a currently selected workarea and d) a current record.
You can therefore always refer to the value of a field of certain record in a certain table just with the field name. It's almost working like a variable, despite you can't assign values to it with = or the STORE command. By the way: If a variable with the same name as a field exists, ? name will give you the value of the field, not the variable. To always address things uniquely you'd therefore prefix variable names with m., eg ? m.name will always give you the value of the variable named "name".
As you can skip forward and backward in a workarea with GOTO, SKIP and LOCATE, you have a more general concept of a recordset you can move in, not just a set of records, even though we also talk about cursors (current set of records) when referring to workareas holding data in local or remote view or cursoradapter or slql passthrough cursors, all technologies not necessarily needed when working on DBFs.
When you open a table, you will be positioned at the top of it, record 1.
And now the best part of it all of that:
If you USE yourtable.dbf IN 0 in form init and set a textbox.controlsource to yourtable.yourfield, the textbox does not only display the value of yourtable.yourfield of record 1, you also change the value inside the dbf simply by changing the textbox, either interactive or programmatically. Controlsource is a two way connection, it's not only the data source of the textbox (or other) control, it's also the target of changes.
To show other records you can GOTO them or SKIP +1 or SKIP -1, just watch out if you try to SKIP +1, while you're already at EOF() - that is end of file - or SKIP -1 while you're already at BOF() - begin of file. Of course you refresh the form, after you goto or skip and change record position, to see the data of the now current record.
And if you look back to the other concepts as well, a form with a private datasession may open a certain table without getting a alias or file is already in use error, you can go to a certain record (eg the one chosen in a main form listing all data in a listbox or grid), and this form may run multiple times and each of them showing a different record without influencing each other, of course unless two forms show the same record, all the forms still work on just one DBF file.
You won't see the overall picture in the first run, just reading it also won't help, you need to experiment with all of this, which you already do, but you should read this as a primer to go about your form and table design with a better plan in mind, fitting these concepts. You don't need much of what you are doing, because there are automatisms you didn't used so far. Controlsource is really a big conecept to understand, navigation in the set database, set datasession and more important current workarea and current record are important to know.
Bye, Olaf.
before you go in any direction with your coding ideas, I suggest you learn data binding for leaner and simpler code.
1. The datasession concept
==========================
VFP supports several data sessions, each having a set of tables (or views, cursors, anything a workarea can hold, doesn't matter for now).
You can let each form either have it's own data session, starting out empty, or let it work in the current data session.
The prpoerty to set is datasession to either 1 (public=current) or 2 (private). That doesn't mean there are datasessions 1 and 2 only, 2 private means the forms starts a new datasession and gets a new datasessonid. That is another form property, too, and readonly. If you have several forms open, you could switch between the datasessions via SET DATASESSION, if knowing the IDs. That's not recommended, as your form then will lose it's data bindings, but just telling for how to peek into other forms data from anywhere. You don't need to worry about switching data sessions, if two forms have private datasessions, then activating a form (and deactivating the previous active form) activates its datasession. The only thing to keep in mind: If you want to start a form multiple times, eg displaying details of a single record, you give it a private datasession. If started twice these two forms each open the same table and don't cause an overlap. One datasession is active at any time. Even jsut starting VFP without any form open, you already have datsessionid 1 available. See Data Session in the Window menu.
2. The workarea concept
=======================
You open tables in workareas, these have both a number and a name. Often people confuse these workarea (alias) names with table names, as they normally are the same. Side note: File names - also DBF file names - can contain spaces and other characters. Ideally you refrain from using such characters in file names, also in paths. An alias name can't be given to two workareas, even if they would still be distinguishable by their workarea number, VFP prevents that, so alias names must be uniquely used. There always is a currently selected workarea in each datasession, unless no table is open.
If you open a table, which already is open, you would reuse the same alias name, which is impossible. That's not the error you got, you got File is in use. Before Foxpro checks about alias names already used, it also checks for files already being opened and errors, if you reopen a file, you already have the file - the table - open, simply continue working with it. The best practice therefore is to open your tables at the initialisation stage of your form, not interactively with a button click, then you don't open a file twice and therefore avoid that error.
You can do that to save file handles (it won't save much resources though), as you have been shown, if you first look out, if the table already is used via USED() function and only open it, if not.
Caution: Now the two concepts come into play in more detail, especially the workarea alias name concept: As said the alias name typically is the same as the main file name (without the path), so you also have a problem, if two tables in different paths have the same file name and therefore also the same alias name given automatically. And what you also can't do, even though the files differ is USE C:\data\mandant1\customers.dbf IN 0 and then also USE C:\data\mandant2\customers.dbf IN 0, that would error 'Alias name is already in use'. If you would want to open these two tables, you'd need to give them two alias names. One things is important about USED: If you'd check with USED("customer") after opening USE C:\data\mandant1\customers.dbf IN 0 you'd obviously get a .T. as return value, but not because the file C:\data\mandant1\customers.dbf is open, but because the alias name "customers" is used. If you now wanted data from C:\data\mandant2\customers.dbf you'd still need to change the situation. Take that as a side note, as it's not important, even if you'd have systems with data for several clients or mandants, you can solve that differently. You typically therefore don't name two different tables equally, if theay are part of the data you need to work with in one session. You rather switch between data of two different clients or customers by having two databases (DBCs) in two different paths with their own folders and then CLOSE TABLES ALL and CLOSE DATABASE of the current mandant and OPEN the other database. Once you have a database open, you also can USE database!tablename and the tablename may even differ from the DBF file name.
So overall: You can't use a file twice, you can't use an alias name twice.
Then you may have noticed me and also Ez Logic using a dbf with the addition IN 0. That is the work area number 0, it has a special meaning. If you USE some.dbf in 0 it's workarea is not 0 finally, it's simply the first free workarea. using a table without adding IN 0, you'd rather open a table in the currently active and selected workarea and that means you also close whatever is open currently. That's why USE is the command to open and to close tables. USE without any further additions simply closes what's open in the current workarea and doesn't specify anything to open.
So what does that mean for working on multiple tables: You USE them IN 0 once at form start and have them available throughout the form being present. You can activate a table by SELECT tablename, actually meaning aliasname of the workarea, it's just typically identical to avoid confusion. But you can also use the IN keyword in several commands like REPLACE, also GOTO 5 IN alias let's VFP go to record 5 in the workarea with that alias. Sepcifying IN, you don't need to SELECT some alias first. Several commands don't offer IN, eg LOCATE always works in the current workarea.
But mentioning record positions leads to the last concept:
3. The record concept
=====================
You always are in a) a currently set Database b) a currently set datasession c) a currently selected workarea and d) a current record.
You can therefore always refer to the value of a field of certain record in a certain table just with the field name. It's almost working like a variable, despite you can't assign values to it with = or the STORE command. By the way: If a variable with the same name as a field exists, ? name will give you the value of the field, not the variable. To always address things uniquely you'd therefore prefix variable names with m., eg ? m.name will always give you the value of the variable named "name".
As you can skip forward and backward in a workarea with GOTO, SKIP and LOCATE, you have a more general concept of a recordset you can move in, not just a set of records, even though we also talk about cursors (current set of records) when referring to workareas holding data in local or remote view or cursoradapter or slql passthrough cursors, all technologies not necessarily needed when working on DBFs.
When you open a table, you will be positioned at the top of it, record 1.
And now the best part of it all of that:
If you USE yourtable.dbf IN 0 in form init and set a textbox.controlsource to yourtable.yourfield, the textbox does not only display the value of yourtable.yourfield of record 1, you also change the value inside the dbf simply by changing the textbox, either interactive or programmatically. Controlsource is a two way connection, it's not only the data source of the textbox (or other) control, it's also the target of changes.
To show other records you can GOTO them or SKIP +1 or SKIP -1, just watch out if you try to SKIP +1, while you're already at EOF() - that is end of file - or SKIP -1 while you're already at BOF() - begin of file. Of course you refresh the form, after you goto or skip and change record position, to see the data of the now current record.
And if you look back to the other concepts as well, a form with a private datasession may open a certain table without getting a alias or file is already in use error, you can go to a certain record (eg the one chosen in a main form listing all data in a listbox or grid), and this form may run multiple times and each of them showing a different record without influencing each other, of course unless two forms show the same record, all the forms still work on just one DBF file.
You won't see the overall picture in the first run, just reading it also won't help, you need to experiment with all of this, which you already do, but you should read this as a primer to go about your form and table design with a better plan in mind, fitting these concepts. You don't need much of what you are doing, because there are automatisms you didn't used so far. Controlsource is really a big conecept to understand, navigation in the set database, set datasession and more important current workarea and current record are important to know.
Bye, Olaf.
- Thread starter
- #8
I managed to fix it, but now, when I insert records via form, I reset all textbox fields with initial value (0) and return, but if I try to insert another records in same session, I receive: File is in use. I know this concept from C#, I guess that the table is not closed after first attempt
- Thread starter
- #9
This is source code:
Code:
SELECT bilete
LOCATE FOR thisform.Text13.Value = serie
IF FOUND()
thisform.Text13.Value = INT(RAND() * 1000)
endif
GO BOTTOM
APPEND BLANK
replace bilete.cod1 WITH thisform.Text1.value, bilete.cod2 WITH thisform.Text2.value,bilete.cod3 WITH thisform.Text3.Value,bilete.cod4 WITH thisform.Text4.Value,bilete.cod5 WITH thisform.Text5.Value, bilete.pariu1 WITH thisform.Text6.Value, bilete.pariu2 WITH thisform.Text7.Value, bilete.pariu3 WITH thisform.Text8.Value, bilete.pariu4 WITH thisform.Text9.Value, bilete.pariu5 WITH thisform.Text10.value, bilete.cotatot WITH thisform.Text11.Value,bilete.pariu WITH thisform.Text14.value, bilete.castig WITH thisform.Text12.Value,bilete.serie WITH thisform.Text13.value, bilete.datab WITH thisform.Text20.value
SELECT incasari
GO bott
APPEND BLANK
replace incasari.serie WITH thisform.Text13.Value, incasari.pariu WITH thisform.Text14.Value,incasari.datab WITH thisform.Text20.value
use in select('bilete')
use in select('incasari')
MESSAGEBOX("Bilet adaugat cu succes")
thisform.Text1.Value = 0
thisform.Text2.Value = 0
thisform.Text3.Value = 0
thisform.Text4.Value = 0
thisform.Text5.Value = 0
thisform.Text11.Value = 0
thisform.Text12.Value = 0
thisform.Text13.Value = 0
thisform.Text14.Value = 0
thisform.Text15.Value = 0
thisform.Text16.Value = 0
thisform.Text17.Value = 0
thisform.Text18.Value = 0
thisform.Text19.Value = 0
thisform.Text20.Value = 0
thisform.Text6.Value = SPACE(2)
thisform.Text7.Value = SPACE(2)
thisform.Text8.Value = SPACE(2)
thisform.Text9.Value = SPACE(2)
thisform.Text10.Value = SPACE(2)
RETURNOlaf Doschke
Programmer
All of this code would be unnecessary, if you'd made use of the controlsource. You also don't need to close and reopen tables.
The code you show is therefore not at all an ideal solution, but it's closing the tables. You have to look at the code (re)opening them, if you need to find out why you now again get "File is already in use" errors.
Bye, Olaf.
The code you show is therefore not at all an ideal solution, but it's closing the tables. You have to look at the code (re)opening them, if you need to find out why you now again get "File is already in use" errors.
Bye, Olaf.
- Thread starter
- #11
vgulielmus
Programmer
Some additional suggestions.
1) Instead of
you can use INSERT SQL
2) instead of
you can write
or
Respectfully,
Vilhelm-Ion Praisach
Resita, Romania
1) Instead of
Code:
SELECT incasari
GO bott
APPEND BLANK
replace incasari.serie WITH thisform.Text13.Value, incasari.pariu WITH thisform.Text14.Value,incasari.datab WITH thisform.Text20.value
Code:
INSERT INTO incasari (serie,pariu,datab) VALUES (thisform.Text13.Value,thisform.Text14.Value,thisform.Text20.value)
Code:
thisform.Text6.Value = SPACE(2)
thisform.Text7.Value = SPACE(2)
thisform.Text8.Value = SPACE(2)
thisform.Text9.Value = SPACE(2)
thisform.Text10.Value = SPACE(2)
Code:
STORE SPACE(2) TO thisform.Text6.Value,thisform.Text7.Value,thisform.Text8.Value,thisform.Text9.Value,thisform.Text10.Value
Code:
WITH thisform
STORE SPACE(2) TO .Text6.Value,.Text7.Value,.Text8.Value,.Text9.Value,.Text10.Value
ENDWITHRespectfully,
Vilhelm-Ion Praisach
Resita, Romania
Olaf Doschke
Programmer
Read my previous long post.
If you have a detail question, ask it.
I gave you the example on how to use the controlsource already even earlier.
Bye, Olaf.
If you have a detail question, ask it.
I gave you the example on how to use the controlsource already even earlier.
Bye, Olaf.
- Thread starter
- #14
Koen Piller
Programmer
Hi Paul,
sorry I did not read the full content of this thread. I stopped with your first message which strikes me.
To store in two different tables the exact same value? This does not sound as being a rational database construction.
Please study and you will learn it is not done to store in two tables the same data.
Again, sorry if this is already mentioned by someone else in this thread, it is way too long.
Regards,
Jockey2
sorry I did not read the full content of this thread. I stopped with your first message which strikes me.
To store in two different tables the exact same value? This does not sound as being a rational database construction.
Please study and you will learn it is not done to store in two tables the same data.
Again, sorry if this is already mentioned by someone else in this thread, it is way too long.
Regards,
Jockey2
- Status
Similar threads
