>>>>
>>>>create cursor stylelist (style c(10),synonyms m)
>>>>select styles
>>>>scan
>>>> select synonyms
>>>> lcSynonym = ""
>>>> scan while fk = styles.pk
>>>> lcSynonyms = m.lcSynonyms + iif(empty(m.lcSynonyms),"",",") + synonyms.synonym
>>>> endscan
>>>> if _Tally > 0
>>>> lcSynonyms = "("+m.lcSynonyms+")"
>>>> endif
>>>> select styles
>>>> insert into stylelist values (styles.style,m.lcSynonyms)
>>>>endscan
>>>>select stylelist
>>>>browse
>>>>
>>>
>>>I forgot to mention that the result as to be also with the OleDb provider. Thus, one big SQL to achieve all that.
>>
>>Michel,
>>Saying OleDb Provider and I know you're doing Vb.Net this question is really meant how to do from VB.Net, right? Otherwise doing that with a single SQL (though might be possible with VFP's-I don't know) if ever could be done I think would be slow.
>>Hopefully I'm right it's for VB.Net, and then you can convert this one easily:
>>
>>using System;
>>using System.Data;
>>using System.Data.OleDb;
>>using System.Text;
>>
>>namespace RelatedDataset
>>{
>> class Class1
>> {
>> [STAThread]
>> static void Main()
>> {
>> string strcon,strsql1,strsql2;
>> strcon = @"Provider=VFPOLEDB;Data Source=C:\temp\";
>>
>> // Just plain select
>> strsql1 = "select * from Styles";
>> strsql2 = "select * from Synonyms";
>>
>> DataSet ds = new DataSet();
>>
>> OleDbConnection conn = new OleDbConnection(strcon);
>> conn.Open();
>>
>> OleDbDataAdapter daStyles = new OleDbDataAdapter(strsql1, conn);
>> OleDbDataAdapter daSynonyms = new OleDbDataAdapter(strsql2, conn);
>>
>> daStyles.Fill(ds,"styles");
>> daSynonyms.Fill(ds,"synonyms");
>> conn.Close();
>>
>> // Add a calculated column to Styles
>> ds.Tables["Styles"].Columns.Add("mySynonyms", typeof(string));
>>
>> // relate tables: Styles.pk -- synonyms.fk
>> DataRelation myRel = new DataRelation("StyleSynonyms",
>> ds.Tables["styles"].Columns["pk"],
>> ds.Tables["synonyms"].Columns["fk"],
>> false);
>> ds.Relations.Add(myRel);
>>
>> // StringBuilder is optimized for string operations
>> StringBuilder sb = new StringBuilder();
>> foreach (DataRow row in ds.Tables["Styles"].Rows)
>> {
>> sb.Length = 0;
>> DataRow[] aRows = row.GetChildRows(myRel);
>> // Collect synonyms for current row
>> if (aRows.Length > 0) {
>> sb.Append("("); }
>> for (int childs=0;childs<aRows.Length;childs++)
>> {
>> if (childs > 0) {
>> sb.Append(","); }
>> sb.Append( ((string)aRows[childs]["synonym"]).Trim() );
>> }
>> if (aRows.Length > 0) {
>> sb.Append(")"); }
>> // add collected to calculated column
>> row["mySynonyms"] = sb.ToString();
>> }
>> /* List from current styles table with added calculated column */
>> foreach(DataRow row in ds.Tables["styles"].Rows)
>> {
>> Console.WriteLine("Style:{0}\tSynonyms:{1}", row["style"],row["mySynonyms"]);
>> }
>> }
>> }
>>}
>>
>>
>>PS: BTW this is VFP code to create test tables:
>>
>>local ix,jx
>>Create Table ("c:\temp\styles") free (Style c(10), pk i)
>>Create Table ("c:\temp\synonyms") (synonym c(10), pk i)
>>For ix =1 To 20
>> Insert Into ("c:\temp\styles") Values ('style'+Padl(m.ix,2,'0'),m.ix)
>> For jx=1 To Int(Rand()*5)
>> Insert Into ("c:\temp\synonyms") Values ('synonym'+Padl(m.jx,2,'0'),m.ix)
>> Endfor
>>Endfor
>>
>>Synonyms might be 0-N with no practical limit I think.
>>Cetin
>
>Hi,
>Did you see my suggestion that Michel just used your previous code in a SP?
>Regards,
>Viv
Now I did:) I thought of that too but creating stored procedures for each similar task sounded to be an overkill. He could decide which path to take:)
Cetin