Searching a DataGridView

We have a SQL query that returns the sample data below.
 
JobNo   Attorney         Email                            Resource
12345   John Smith     JSmith@gmail.com      Bob
12345   John Smith     JSmith@gmail.com      Kate
34832   Tom Parks     TParks@gmail.com       Jake
83747   Gary Day        GDay@yahoo.com       Shane

We are building a program to send out emails to attorneys notifying them who the resource is going to be at a particular job.

So far the program can successfully send an email to each row the SQL query returns. However this is causing a problem because as you can see from the sample query, John Smith will receive two emails. Our goal is to only send a single email to each attorney.

How do we select 2 rows from C# DataGridView by searching the DataGrid?
InfoTechEEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aruiz04Commented:
the following will return a table with the email column with distinct emails
DataView dv = (DataView)DataGridView.DataSource;
DataTable dvalues = dv.ToTable(true, "email");

Open in new window

from there just loop thru the DataRowView(s)  and send email to the list
0
BuggyCoderCommented:
can the same job no be assigned to resources, if yes then you may need to send the resource names as well, so adding to the above solution by @Aruiz04:-

//step 1. lets retrieve the distinct email
DataView dv = (DataView)DataGridView.DataSource;
DataTable tableView= dv.ToTable(true, "email");
//step 2. lets now find the resource names for this email id:-
var resources = new List<string>();

                foreach (var email in from DataRow dr in tableView.Rows select dr.Field<string>(0))
                {
                    dv.ToTable()
                        .Rows
                        .Cast<DataRow>()
                        .Where(rw => rw.Field<string>("email") == email)
                        .ToList()
                        .ForEach(rw => resources.Add(rw.Field<string>("Resource")));
                }

Open in new window


Now resources list contains resources assigned to each attorney...
May be this will make your job easier....
0
InfoTechEEAuthor Commented:
DataView dv = (DataView)DataGridView.DataSource; does not work saying:
An object reference is required for the non-static field, method, or property 'System.Windows.Forms.DataGridView.DataSource.get'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vadim RappCommented:
When you send the email to John Smith  , what do you want to be as Resource in the email - both Bob and Kate?
0
InfoTechEEAuthor Commented:
Sorry for the delay. When I send an email to John Smith, I dont' want to send him 2 different emails with separate info.

I want one email to go out to him telling him he will have the following resources: Bob and Kate.
0
Vadim RappCommented:
Here's very schematic algorithm of what you can code:

select distinct attorney, jobno from mytable
loop while not end-of-file
  fetch next row into @attorney, @jobno
  select distinct resource from mytable where jobno=@jobno
  @resources = ''
  loop while not end-of-file
     fetch next row into @resource
     @resources = @resources + ', ' + @resource
  end loop
  @body = 'Dear ' + @attorney + ", your resources are: ' + @resources
  send email with body=@body
end loop
0
InfoTechEEAuthor Commented:
Is @resource a string array?
0
Vadim RappCommented:
No, it's a variable. On 1st pass it gets "bob", on 2nd it gets "kate". This is not any particular language, just showing the idea of the algorithm. I'm sure any competent programmer should understand and code this in no more than 10 minutes.
0
BuggyCoderCommented:
i suppose you are replacing DataGridView with your gridview name in your program:-

DataView dv = (DataView)DataGridView.DataSource;

all we are trying to do here is access its data source and retrieve distinct email ids from the data source.....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
InfoTechEEAuthor Commented:
Sorry for the delay again. The full time developer that wrote this program is no longer with the company. I'm just trying to finish up his application, and this seems to be last piece.

BuggyCoder: When I try your suggestion:

"DataView dv = (DataView)DataGridView.DataSource;"
I get an error message about object reference, and it makes sense.

So instead, since my datagrid is named "dataGrid", I'm trying:

DataView dv = (DataView) dataGrid.DataSource;

It seems to pass the initial parse -- meaning that Visual Studio is not throwing any errors, but it crashes at runtime saying:

Unable to cast object of type 'System.Data.DataTable' to type 'System.Data.DataView'.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.