topic-31532

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

OT Recordset question
Posted: Tue Nov 03, 2015 08:58 AM

Hi,

I have a question about a recordset.

I have a database records.
Each record contain also a a field 'Slot'.

If I want to show it, I want to show it from slot 1 until slot 10
If a slot does not exist, I want to show a emty row , with only the slot

Until now, I did it in DBF, and created a temporary DBF file and added a empty record with that slot.

I was wondering that I can do it in one recordset, without creating a tempory database-file

So if I have a database like

Code (fw): Select all Collapse

SLOT    NAME
1   TEST1
2   TEST9
6   TEST60
10  TEST66

I want to show

Code (fw): Select all Collapse

SLOT    NAME
1   TEST1
2   TEST9
3
4
5
6   TEST60
7
8
9
10  TEST66

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: OT Recordset question
Posted: Tue Nov 03, 2015 12:50 PM

Marc

Off the top of my head .. if you have a recordset with only 4 records I don't see a way to add a blank record with a Select * statement. However, instead of creating a .dbf you might consider creating an array much like you did with your ..dbf... Basically loop thru the recordset with your logic and add to your array either with " " or the oRs:Value

Rick Lipkin

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: OT Recordset question
Posted: Tue Nov 03, 2015 01:23 PM

Rick,

That is what I do now, but I thought that I could maybe do it in one query using the UNION clause.

Marcelo Via Giglio

Posts: 1088

Joined: Fri Oct 07, 2005 03:33 PM

Re: OT Recordset question
Posted: Tue Nov 03, 2015 02:25 PM

Hello,

you can create a list of number ( search in internet ), then with your table and INNER JOIN you can get what you need, idea only

regards

Marcelo

Marcelo Via Giglio

Posts: 1088

Joined: Fri Oct 07, 2005 03:33 PM

Re: OT Recordset question
Posted: Tue Nov 03, 2015 02:45 PM

Marcelo Via Giglio wrote:Hello,

you can create a list of number ( search in internet ), then with your table and INNER JOIN you can get what you need, idea only

regards

Marcelo

P.D.

this SQL generate list of number 1-10

Code (fw): Select all Collapse

select * from
(select 0 x union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A;

reference http://dba.stackexchange.com/questions/75785/how-to-generate-a-sequence-in-mysql

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: OT Recordset question
Posted: Wed Nov 04, 2015 06:32 AM

Thank you Marcelo,

I allready have tried it with a temporary table with 10 records and joined it with my table, and it is working.
Now will try it with the generated list as you have showed.

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: OT Recordset question
Posted: Wed Nov 04, 2015 07:31 AM

Marcelo,

Thank you, it's working fine now!

FiveTech Support Forums

Continue the discussion