Solved

Searching a DataGridView

Posted on 2012-03-16
13
442 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
  • 4
  • 3
  • 2
  • +1
13 Comments
 
LVL 5

Expert Comment

by:Aruiz04
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
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
Comment Utility
Is @resource a string array?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now