>>>
>>>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