Solved

Searching a DataGridView

Posted on 2012-03-16
13
464 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

733 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