Solved

Searching a DataGridView

Posted on 2012-03-16
13
473 Views
Last Modified: 2012-06-20
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?
0
Comment
Question by:InfoTechEE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
13 Comments
 
LVL 5

Expert Comment

by:Aruiz04
ID: 37731695
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
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37732122
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
 

Author Comment

by:InfoTechEE
ID: 37798510
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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37853190
When you send the email to John Smith  , what do you want to be as Resource in the email - both Bob and Kate?
0
 

Author Comment

by:InfoTechEE
ID: 37882859
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37883198
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
 

Author Comment

by:InfoTechEE
ID: 37883214
Is @resource a string array?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37883226
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
 
LVL 20

Accepted Solution

by:
BuggyCoder earned 500 total points
ID: 37883254
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
 

Author Comment

by:InfoTechEE
ID: 37931625
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

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question