Processing each records in recordset.

[wvdba]

Hi.
i have developed an app using vb.net and it seems that my first try at this is going ok. the app retrieves some records from an oracle database and the "repeater" control puts them in a table with tr and td tags. however, i want to be able to examine each records in the recordset and pick and choose fields that will be emailed to a recipient using System.Net.Mail
any suggestions would be greatly appreciated.
thanks.

 

[jmeckley]

you will need a way to mark which records you want to export. a checkbox would work. you will also need a button to send the email.

the user will check each record they want to export and then click the button. in the button click handler loop through the repeater and find all the rows that were checked. from the checked rows extract the record's ID. query the database based on these IDs. create a mail message and send the email.

<asp:repeater id="therepeater">
<asp:repeateritemtemplate runat="server">
  <asp:checkbox id="includeinemail" runat="server" value='<%#Eval("ID")%>' />
   ...text to display to user...
</asp:repeateritemtemplate>
</asp:repeater>
<asp:Button id="emailbutton" runat="server" text="send email" OnClick="SendEmail"/>

protected void SendEmail(object sender, EventArgs e)
{
   var ids = ParseSelectedIdsFromRepeater();
   var records = GetRecordsFor(ids);
   var body = BuildMessageWith(records);

   using(var message = new MailMessage{
                                        Body = body,
                                        Subject = ...
                                      })
   {
      message.To.Add("me@domain.com");
      new SmtpClient().Send(message);
   }
}

private IEnumerable<string> ParseSelectedIdsFromRepeater()
{
   foreach(RepeaterItem item in therepeater.Items)
   {
       if (item.ItemType != RepeaterItemType.Item) continue;

       var checkbox = item.FindControl("includeinemail") as CheckBox;
       if(checkbox == null) continue;
       if(checkbox.Checked == false) continue;

       yield return checkbox.Value; //the record id
   }
}

you would also implement GetRecordsFor and BuildMessageWith.

Something to consider is paging. if you are returning 100's of rows from the database the performance and user experience will suffer. If you do need to page the records you will need to temporarily store the selected ids until the user is ready to send the email. using the asp.net session is a common way to store data between requests.

this should be enough to get you started.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[wvdba]

thanks for reply.
the email is not the problem. i have that under control and it works fine.
this app will retrieve at most 5-10 records.
my question is navigating through the records in the recordset and how to reference the field names in the recordset.
thanks.

 

[jmeckley]

there are a number of examples all over the web. just search for "bind datatable to repeater" or something like that. i'm sure the msdn help files also have a number of examples.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[jbenson001]

Do you want to do this at the time the repeater is bound?
If so, then you can use the ItemDataBound event of the Repeater.

 

[wvdba]

thanks.
i already have the repeater part working.
i just want to process EACH record in the recordset and choose what i want.

 

[jmeckley]

i just want to process EACH record in the recordset and choose what i want.

what do you mean specifically? it will also help if you post the relevant code.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[wvdba]

The rows that are retrieved contain the fields:
first_name, last_name, DOB
i want to retrtieve each record and examine these fields.
thanks.

 

[jmeckley]

and the code?

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[wvdba]

the code is what i'm after.
i have the connection string, open db, and get data using repeater control.

sql = "SELECT * FROM attendees WHERE last_name LIKE ('" & entered_name & "%')"
.
.
.
dbcommand = New OleDbCommand(sql, objConn)
        
        dbread = dbcommand.ExecuteReader()
        rep_info.DataSource = dbread
        rep_info.DataBind()
        dbread.Close()
        objConn.Close()
.
.
.
<asp:Repeater id="rep_info" runat="server">
<HeaderTemplate>
<table border="1" width="100%">
<tr>
<th>first name</th>
<th>middle name</th>
<th>last name</th>
<th>birth date</th> 
</tr>
</HeaderTemplate>

<ItemTemplate>
<tr>
<td><%#Container.DataItem("first_name")%></td>
<td><%#Container.DataItem("middle_name")%></td>
<td><%#Container.DataItem("last_name")%></td>
<td><%#Container.DataItem("birth_date")%></td> 
</tr>
</ItemTemplate>

<FooterTemplate>
</table>
</FooterTemplate>

</asp:Repeater>

as you can see, the repeater takes care of all data gethering.
i want to be able to examine each record and build my own logic based on each record in the recordset.
thanks.

 

[jmeckley]

var results = new DataTable();
using(var command = connection.CreateCommand())
{
   command.CommandText = "sql statement";
   results.Load(dbcommand.ExecuteReader());
}
examine results;
foreach(DataRow row in results)
{
  ...
}
myrepeater.DataSource = results;
DataBind();

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[wvdba]

what language is this code on?
i'm after VB
this seems not to do what i want to do, which is examine each and every record on the recordset.
thanks.

 

[jmeckley]

it's c#. the syntax is different but the intent is the same. if you cannot convert c# to vb use an online translator.

if you want to examine each record than this is exactly what you want.
get data from database
load data into datatable
disconnect from database
loop through each row in the datatable and "process"
bind datatable to repeater.

Jason Meckley
Programmer

faq855-7190
faq732-7259