tricky sql & data set question
-
is it possible to loop through each row of a dataset table, and populate the new column based on another column in the same row? i have a Contacts class, and a Message Center class. Each get their information from completely different databases, and reference only by the ContactID. ( the messages table in its database only has a contactid field .. not a contact name field ) The constructor of the Contact class takes the ContactID as the argument. I want to create a dataset of the last 50 messages and have the Contact Name in the same data set. for example:
. . . . . . . . string sql = "SELECT ContactID,Message from Messages ORDER BY MessageID DESC LIMIT 50"; SqlDataAdapter da = new SqlDataAdapter(sql,con); ds = new DataSet(); da.Fill(ds,"Messages"); ds.Tables["Messages"].Columns.Add("ContactName");Now how would I iterate through each row of the Messages.ContactID table, and populate the Messages.ContactName field of the same row ? I hope I made sense =/ .. Thanks for reading this. -
is it possible to loop through each row of a dataset table, and populate the new column based on another column in the same row? i have a Contacts class, and a Message Center class. Each get their information from completely different databases, and reference only by the ContactID. ( the messages table in its database only has a contactid field .. not a contact name field ) The constructor of the Contact class takes the ContactID as the argument. I want to create a dataset of the last 50 messages and have the Contact Name in the same data set. for example:
. . . . . . . . string sql = "SELECT ContactID,Message from Messages ORDER BY MessageID DESC LIMIT 50"; SqlDataAdapter da = new SqlDataAdapter(sql,con); ds = new DataSet(); da.Fill(ds,"Messages"); ds.Tables["Messages"].Columns.Add("ContactName");Now how would I iterate through each row of the Messages.ContactID table, and populate the Messages.ContactName field of the same row ? I hope I made sense =/ .. Thanks for reading this.after messing around with the beautiful intellisense of vs, i think this might work:
// this forum doesn't like my for loop so i had to leave out the less than sign. for(int i=0; i ds.Tables["Issues"].Rows.Count ; i++) { Contacts.Contact Contact = new Contacts.Contact(ds.Tables["Messages"].Rows[i]["ContactID"]); ds.Tables["Messages"].Rows[i]["ContactName"] = Contact.FirstName + " " + Contact.LastName; } -
is it possible to loop through each row of a dataset table, and populate the new column based on another column in the same row? i have a Contacts class, and a Message Center class. Each get their information from completely different databases, and reference only by the ContactID. ( the messages table in its database only has a contactid field .. not a contact name field ) The constructor of the Contact class takes the ContactID as the argument. I want to create a dataset of the last 50 messages and have the Contact Name in the same data set. for example:
. . . . . . . . string sql = "SELECT ContactID,Message from Messages ORDER BY MessageID DESC LIMIT 50"; SqlDataAdapter da = new SqlDataAdapter(sql,con); ds = new DataSet(); da.Fill(ds,"Messages"); ds.Tables["Messages"].Columns.Add("ContactName");Now how would I iterate through each row of the Messages.ContactID table, and populate the Messages.ContactName field of the same row ? I hope I made sense =/ .. Thanks for reading this.In sql I would say SELECT MES.ContactID, CON.COntactName, MES.Message FROM MessageDatabase..Messages AS MES INNER JOIN ContactDatabase..Contacts AS CON ON MES.ContactID = CON.ID ORDER BY MES.MessageID DESC LIMIT 50 that would combine them or in code dsTables["Messages"].Columns.Add("ContactName"); foreach( DataRow row in dsTables["Messages"] ) row["ContactName"] = Contact.FullName; I'm not an expert yet, but I play one at work. Yeah and here too.
-
In sql I would say SELECT MES.ContactID, CON.COntactName, MES.Message FROM MessageDatabase..Messages AS MES INNER JOIN ContactDatabase..Contacts AS CON ON MES.ContactID = CON.ID ORDER BY MES.MessageID DESC LIMIT 50 that would combine them or in code dsTables["Messages"].Columns.Add("ContactName"); foreach( DataRow row in dsTables["Messages"] ) row["ContactName"] = Contact.FullName; I'm not an expert yet, but I play one at work. Yeah and here too.
Foreach loop will be work in this case, but in some cases as adding/deleting rows or modifing primary keys or some computations (resetting expressions to null - it's .net error) will not. Hi, AW
-
Foreach loop will be work in this case, but in some cases as adding/deleting rows or modifing primary keys or some computations (resetting expressions to null - it's .net error) will not. Hi, AW
-
explain further on that. Dont see how foreach won't work. Its the same as using a counter just cleaner code. You can always test for null or the famous DBNull value I'm not an expert yet, but I play one at work. Yeah and here too.
It's not a joke; see help (.Net Framework, foreach, syntax): The foreach statement is used to iterate through the collection to get the desired information, but should not be used to change the contents of the collection to avoid unpredictable side effects :omg: I spent some nights with fighting with error at computed fields/expressions ... Hi, AW
-
It's not a joke; see help (.Net Framework, foreach, syntax): The foreach statement is used to iterate through the collection to get the desired information, but should not be used to change the contents of the collection to avoid unpredictable side effects :omg: I spent some nights with fighting with error at computed fields/expressions ... Hi, AW
-
It's not a joke; see help (.Net Framework, foreach, syntax): The foreach statement is used to iterate through the collection to get the desired information, but should not be used to change the contents of the collection to avoid unpredictable side effects :omg: I spent some nights with fighting with error at computed fields/expressions ... Hi, AW
Yes and no actually The foreach statement repeats a group of embedded statements for each element in an array or an object collection. The foreach statement is used to iterate through the collection to get the desired information, but should not be used to change the contents of the collection to avoid unpredictable side effects. The statement takes the following form: This doesnt mean you cant change the data in the elements of the collections just the contents of a collection. Like so, if you add, delete, or sort the collection while inside an enumerator it will have un expected results for the simple fact that is an Enumerator and just grabs one time data. Much like modifying a pointer while iterating. You can change the data inside the elements without it affecting them. I have more than 20 classes linked to datagrids and use foreach to iterate through them and have not witnessed any unpredicted results yet. And besides all doc code shows ms using that especially in implementing collections. Could use post a code example or detailed situation so I could try it and determine the root problem. nick I'm not an expert yet, but I play one at work. Yeah and here too.
-
Yes and no actually The foreach statement repeats a group of embedded statements for each element in an array or an object collection. The foreach statement is used to iterate through the collection to get the desired information, but should not be used to change the contents of the collection to avoid unpredictable side effects. The statement takes the following form: This doesnt mean you cant change the data in the elements of the collections just the contents of a collection. Like so, if you add, delete, or sort the collection while inside an enumerator it will have un expected results for the simple fact that is an Enumerator and just grabs one time data. Much like modifying a pointer while iterating. You can change the data inside the elements without it affecting them. I have more than 20 classes linked to datagrids and use foreach to iterate through them and have not witnessed any unpredicted results yet. And besides all doc code shows ms using that especially in implementing collections. Could use post a code example or detailed situation so I could try it and determine the root problem. nick I'm not an expert yet, but I play one at work. Yeah and here too.
It's clear. The error is only with computed fields, I send it some time ago to C#corner forum (it's now unusable). It's solved now. The reason is: 1. I created a table with about 400 fields dynamically. 2. I assigned Expression based on another fields to one of them: dtBufor.Columns["aggr"].Expression="..."; 3. I assigned null (or zer-length string) to this expression 4. After it getting from any table field was possible, but setting - sometimes impossible. Using dtBufor.Rows[rowno]["fieldname"] generated an immediate NullObjectReference error and dtBufor.Rows[rowno].ItemArray[fieldno] updated nothing without error 5. After setting Expression="0" it works (workaround). 6. See http://www.codeproject.com/script/comments/forums.asp?forumid=1649&mode=all&userid=56906&select=407524&df=100&app=50&fr=8305#xx407524xx Hi, AW