troubleshooting Question

Read and Unread Lotus Notes mails in asp.net Gridview

Avatar of kahvedzic
kahvedzicFlag for Bosnia and Herzegovina asked on
Lotus IBMASP.NET
4 Comments1 Solution2905 ViewsLast Modified:
Hi all,
have GridView with inbox mails on asp.net form, what I need is to distingush read and unread mails.
Example:
First mail in inbox is unread. I want to see that mail in red color and bold font style in Gridview. Then when I click select button and display mail body and attachment list in text boxes text should be regular, black color and unbold.
Is it possible to get mail status (read/unread) from Lotus Notes database and present it this way in asp.net?

Thanks
Imports Domino 
Imports System.Data.SqlClient 
 
Partial Class Default6 
    Inherits System.Web.UI.Page 
 
Private Function CreateDataTable() As Data.DataTable 
        Try 
            Dim myDataTable As Data.DataTable = New Data.DataTable() 
            Dim myDataColumn As Data.DataColumn 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.Int32") 
            myDataColumn.ColumnName = "ID" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "Status" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "From" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "To" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.DateTime") 
            myDataColumn.ColumnName = "Timestamp" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "Subject" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "Attachment List" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "Body" 
            myDataTable.Columns.Add(myDataColumn) 
 
            Return myDataTable 
        Catch ex As Exception 
 
        End Try 
 
    End Function 
 
Private Function AddDataToTable(ByVal bdy As String, ByVal fom As String, ByVal sbj As String, ByVal time As String, ByVal attachmentNames As String, ByVal myTable As Data.DataTable) 
        Try 
            Dim row As Data.DataRow = myTable.NewRow() 
 
            row("From") = fom 
            row("Subject") = sbj 
            row("Timestamp") = time 
            row("Attachment List") = attachmentNames 
            row("Body") = bdy 
 
            myTable.Rows.Add(row) 
            myTable.AcceptChanges() 
        Catch ex As Exception 
 
        End Try 
    End Function 
 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 
 
 
        Dim appNotes As NotesSession 
        appNotes = New NotesSession 
        Dim dbNotes As NotesDatabase 
        appNotes.Initialize("baahdb") 
        dbNotes = appNotes.GetDatabase("abc", "def")
        Dim vwInbox As NotesView 
        vwInbox = dbNotes.GetView("$Inbox") 
        Dim docMessage As NotesDocument 
        Dim fom As Object 
        Dim sbj As Object 
        Dim bdy As Object 
        Dim time As Object 
        Dim attachmentNames As Object 
        Dim prezime As String 
        Dim vrijeme As String 
        On Error Resume Next 
 
        Dim myDt As Data.DataTable 
        myDt = New Data.DataTable() 
        myDt = CreateDataTable() 
 
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString 
        Dim mySqlConnection As New SqlConnection(connectionString) 
        Dim cmd As New SqlCommand 
        Dim dreader As SqlDataReader 
        If mySqlConnection.State = Data.ConnectionState.Open Then mySqlConnection.Close() 
        mySqlConnection.Open() 
 
        With vwInbox 
            docMessage = .GetLastDocument 
            Do 
                fom = appNotes.Evaluate("@Name([CN]; From)", docMessage) 
                'fom = appNotes.Evaluate("@Right(@Left(From; ""/""); ""CN="")", docMessage) 
 
                sbj = appNotes.Evaluate(("Subject"), docMessage) 
 
                bdy = appNotes.Evaluate("@Text(Body)", docMessage) 
                'List3.Items.Add(docMessage.GetItemValue("Body")) 
                'List3.Items.Add(Join(docMessage.GetItemValue("Body"))) 
 
                time = appNotes.Evaluate("@Text(DeliveredDate)", docMessage) 
                'Convert.ToDateTime(time) 
 
                attachmentNames = appNotes.Evaluate("@Implode(@AttachmentNames; "", "")", docMessage) 
                If attachmentNames(0) = "" Then attachmentNames(0) = " " 
                docMessage = .GetPrevDocument(docMessage) 
                AddDataToTable(CStr(bdy(0)), CStr(fom(0)), CStr(sbj(0)), CStr(time(0)), CStr(attachmentNames(0)), myDt) 
 
            Loop Until docMessage Is Nothing 
        End With 
        On Error Resume Next 
 
        docMessage = Nothing 
        vwInbox = Nothing 
        dbNotes = Nothing 
        appNotes = Nothing 
 
        For i As Integer = 0 To myDt.Rows.Count - 1 
            cmd.Parameters.Clear() 
            prezime = myDt.Rows(i).Item("From") 
            vrijeme = Convert.ToDateTime(myDt.Rows(i).Item("Timestamp")) 
            Convert.ToDateTime(vrijeme) 
            cmd.Connection = mySqlConnection 
            cmd.CommandText = "SELECT ID,Status,Uradio from Zahtjev_za_intervenciju with (nolock) where Prezime_ime=@prezime and [Timestamp]=@vrijeme;" 
 
            cmd.Parameters.AddWithValue("@prezime", prezime.ToString) 
            cmd.Parameters.AddWithValue("@vrijeme", vrijeme.ToString) 
            dreader = cmd.ExecuteReader 
            If dreader.HasRows Then 
                dreader.Read() 
                myDt.Rows(i).Item("ID") = dreader.Item("ID") 
                myDt.Rows(i).Item("To") = dreader.Item("Uradio") 
                myDt.Rows(i).Item("Status") = dreader.Item("Status") 
 
            End If 
            dreader.Close() 
        Next 
 
        mySqlConnection.Close() 
 
        Session("myDataTable") = myDt 
        Me.GridView1.DataSource = myDt 
        Me.GridView1.Attributes.Add("style", "table-layout:fixed") 
        Me.GridView1.AutoGenerateColumns = True 
        Me.GridView1.DataBind() 
 
    End Sub 
 
 
    Public Sub GridView1_OnRowCreated(ByVal sender As Object, ByVal e As Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowCreated 
 
        e.Row.Cells(7).Visible = False 
        e.Row.Cells(8).Visible = False 
 
    End Sub 
 
    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged 
        txtBody.Text = GridView1.SelectedRow.Cells(8).Text 
        txtAttach.Text = GridView1.SelectedRow.Cells(7).Text 
 
    End Sub 
 
    Protected Sub Upisi_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles Upisi.Click 
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString 
        Dim mySqlConnection As New SqlConnection(connectionString) 
        Dim vrijeme2 As String 
        vrijeme2 = Convert.ToDateTime(GridView1.SelectedRow.Cells(5).Text) 
 
        If mySqlConnection.State = Data.ConnectionState.Open Then mySqlConnection.Close() 
        mySqlConnection.Open() 
 
        Dim strSqlInsert As String = "INSERT INTO Zahtjev_za_intervenciju (Prezime_ime, Opis_Lotus, Timestamp, Subject_Lotus, Atach_list) VALUES (@Prezime_ime, @Opis_Lotus, @Timestamp, @Subject_Lotus, @Atach_List);" 
        Dim mySqlCommand As SqlCommand = New SqlCommand(strSqlInsert, mySqlConnection) 
 
        mySqlCommand.Parameters.AddWithValue("@Prezime_ime", GridView1.SelectedRow.Cells(3).Text) 
        mySqlCommand.Parameters.AddWithValue("@Subject_Lotus", GridView1.SelectedRow.Cells(6).Text) 
        mySqlCommand.Parameters.AddWithValue("@Opis_Lotus", txtBody.Text) 
        mySqlCommand.Parameters.AddWithValue("@Timestamp", vrijeme2.ToString) 
        mySqlCommand.Parameters.AddWithValue("@Atach_List", txtAttach.Text) 
 
        mySqlCommand.ExecuteNonQuery() 
        mySqlConnection.Close() 
    End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros