Sql-Select

[alisaif]

Hi,

Can I add a record with a word [ALL] in location field while fetching record from a table using the following syntax in Bold/Italic?

Local lcSQL
mpcode = UPPER(This.parent.mCustCode.ssTextBox1.Value)
TEXT TO lcSQL NOSHOW PRETEXT 12
   [i][b]SELECT DISTINCT location FROM location WHERE pcode = mpcode INTO CURSOR tLocation ORDER BY location READWRITE[/b][/i]
Endtext

With This As ComboBox	
    .RowSourceType = 3	
    .RowSource = Strtran(m.lcSQL,";","")
	.ColumnCount = 1	
	.ColumnWidths = '110'	
	.BoundColumn = 3	
	.BoundTo = .T.
	.Value = 0	
	.Style = 2	
	.ListIndex = 1
ENDWITH

 

[Mike Lewis]

Hi Saif,

Yes, you can do this, but you'll need to create the cursor first, then assign it to the combo box in a separate step:

Local lcSQL
mpcode = UPPER(This.parent.mCustCode.ssTextBox1.Value)
SELECT DISTINCT location FROM location WHERE pcode = mpcode INTO CURSOR tLocation ;
    ORDER BY location READWRITE
[b]INSERT INTO Location (Location) VALUES "(All")[/b]

With This As ComboBox	
[b]    .RowSourceType = 2
    .RowSource = "Location"[/b]
    .ColumnCount = 1	
    .ColumnWidths = '110'	
    .BoundColumn = 3	
    .BoundTo = .T.
    .Value = 0	
    .Style = 2	
    .ListIndex = 1
ENDWITH

I've highlighted the bits that are different from your version.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[bborissov]

TEXT TO lcSQL NOSHOW PRETEXT 15
   SELECT location 
          FROM location 
   WHERE pcode = mpcode 
   UNION
   SELECT '   All  ' FROM location
   ORDER BY 1 
   INTO CURSOR tLocation READWRITE
Endtext
With This As ComboBox	
    .RowSourceType = 3	
    .RowSource = m.lcSQL
    .ColumnCount = 1	
    .ColumnWidths = '110'	
    .BoundColumn = 3	
    .BoundTo = .T.
    .Value = 0	
    .Style = 2	
    .ListIndex = 1
ENDWITH

Borislav Borissov
VFP9 SP2, SQL Server

 

[alisaif]

Thanks Mike and Boris,

Both are working for me.

Saif

 

[Mike Lewis]

Saif,

Just to add that Boris's solution has the advantage that it will show the combo in alphabetical order, with " ALL " at the top. That would probably be easier for the user.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[alisaif]

Exactly Mike, actually I want the same with 'All' at the top.

Thanks

Saif

 

[Olaf Doschke]

What I typically do with such entries as "All" is enclose them in brackets, as "(All)", which also sorts them up before other entries.

I typically have an ID and if records of a table mix with such extra entries, I use specific IDs for them, eg 0 or 9999 or something like that, so I can react specifically to these choices in Valid or any other later code, processing the choice of the user.

Bye, Olaf.