Problem reading LN inbox with COM objects and framework 4

Imports Domino
Imports System.Data.SqlClient

Partial Class LNForm
    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.String")
            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") = Convert.ToDateTime(time).ToString("yyyy-MM-dd HH:mm") 'time
            row("Attachment List") = attachmentNames
            row("Body") = bdy.Trim()

            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
        'If Page.IsPostBack = False Then
        Dim appNotes As NotesSession
        appNotes = New NotesSession
        Dim dbNotes As NotesDatabase
        appNotes.Initialize("pass")
        dbNotes = appNotes.GetDatabase("mail server", "mail database")
        'mail\baaltp.nsf
        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)
                'bdy = appNotes.Evaluate("@Abstract([TextOnly]; @Body)", docMessage)
                'bdy = docMessage.Body.GetUnformattedText()
                'bdy = Join(docMessage.GetItemValue("Body"))
                'List3.Items.Add(docMessage.GetItemValue("Body"))
                'List3.Items.Add(Join(docMessage.GetItemValue("Body")))

                time = appNotes.Evaluate("@Text(DeliveredDate)", docMessage)
                Convert.ToDateTime(time).ToString("yyyy-MM-dd HH:mm")

                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")).ToString("yyyy-MM-dd HH:mm")
            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", Convert.ToDateTime(vrijeme).ToString("yyyy-MM-dd HH:mm"))
            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()

        If Not IsPostBack Then
            Session("myDataTable") = myDt
            Me.GridView1.DataSource = myDt
            Me.GridView1.Attributes.Add("style", "table-layout:dynamic")
            Me.GridView1.AutoGenerateColumns = True
            Me.GridView1.AllowSorting = True
            Me.GridView1.DataBind()
        End If

        InboxLabel.Text = GridView1.Rows.Count
    End Sub

    Protected Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowCreated
        'Kolone koje ne treba da se ne vide u GridView idu ovdje
        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

        If txtAttach.Text = " " Then txtAttach.Text = ""
        txtBody.Text = txtBody.Text.Replace(""", """")
        txtAttach.Text = txtAttach.Text.Replace(""", """")
        txtNaslov.Text = txtNaslov.Text.Replace(""", """")
        txtBody.Text = txtBody.Text.Replace(">", ">")
        txtAttach.Text = txtAttach.Text.Replace(">", ">")
        txtNaslov.Text = txtNaslov.Text.Replace(">", ">")

        If txtAttach.Text.Length > 100 Then
            txtAttach.Text = txtAttach.Text.Substring(0, 100)
        End If

        txtNaslov.Text = GridView1.SelectedRow.Cells(6).Text
        If txtNaslov.Text.Length > 100 Then
            txtNaslov.Text = txtNaslov.Text.Substring(0, 100)
        End If

        If txtNaslov.Text = " " Then
            txtNaslov.Text = ""
        End If

        Dim selektovan As String
        selektovan = GridView1.SelectedRow.Cells(1).Text
        If selektovan = " " Then
            Upisi.Enabled = True
        Else
            Upisi.Enabled = False
        End If

        GridView1.SelectedRow.Focus()
    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).ToString("yyyy-MM-dd HH:mm")

        If mySqlConnection.State = Data.ConnectionState.Open Then mySqlConnection.Close()
        mySqlConnection.Open()

        Dim strSqlInsert As String = "INSERT INTO Zahtjev_za_intervenciju (Prezime_ime, Timestamp, Subject_Lotus, Atach_list, Lotus_Intranet, Opis_Lotus) VALUES (@Prezime_ime, @Timestamp, @Subject_Lotus, @Atach_List, @Lotus_Intranet, @Opis_Lotus);"
        'CAST( as varchar(8000))
        Dim mySqlCommand As SqlCommand = New SqlCommand(strSqlInsert, mySqlConnection)

        mySqlCommand.Parameters.AddWithValue("@Prezime_ime", GridView1.SelectedRow.Cells(3).Text)
        mySqlCommand.Parameters.AddWithValue("@Subject_Lotus", txtNaslov.Text)
        'mySqlCommand.Parameters.AddWithValue("@Subject_Lotus", GridView1.SelectedRow.Cells(6).Text)
        mySqlCommand.Parameters.AddWithValue("@Opis_Lotus", txtBody.Text.Trim())
        mySqlCommand.Parameters.AddWithValue("@Timestamp", Convert.ToDateTime(vrijeme2).ToString("yyyy-MM-dd HH:mm"))
        mySqlCommand.Parameters.AddWithValue("@Atach_List", txtAttach.Text)
        mySqlCommand.Parameters.AddWithValue("@Lotus_Intranet", "L")

        mySqlCommand.ExecuteNonQuery()
        mySqlConnection.Close()

        'Response.Redirect(Request.RawUrl)
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        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()

        cmd.Connection = mySqlConnection
        cmd.CommandText = "SELECT Opis_Lotus from Zahtjev_za_intervenciju with (nolock) where ID=@ID;"

        cmd.Parameters.AddWithValue("@ID", GridView1.SelectedRow.Cells(1).Text)
        dreader = cmd.ExecuteReader
        If dreader.HasRows Then
            dreader.Read()
            TextBox1.Text = dreader.Item("Opis_Lotus")

        End If
        dreader.Close()

        mySqlConnection.Close()
    End Sub

    Protected Sub Refresh_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles Refresh.Click
        Response.Redirect(Request.RawUrl)
    End Sub

    Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting
        'Retrieve the table from the session object.
        Dim dt = TryCast(Session("myDataTable"), Data.DataTable)

        If dt IsNot Nothing Then

            'Sort the data.
            dt.DefaultView.Sort = e.SortExpression & " " & GetSortDirection(e.SortExpression)
            GridView1.DataSource = Session("myDataTable")
            GridView1.DataBind()

        End If
    End Sub

    Private Function GetSortDirection(ByVal column As String) As String

        ' By default, set the sort direction to ascending.
        Dim sortDirection = "DESC"

        ' Retrieve the last column that was sorted.
        Dim sortExpression = TryCast(ViewState("SortExpression"), String)

        If sortExpression IsNot Nothing Then
            ' Check if the same column is being sorted.
            ' Otherwise, the default value can be returned.
            If sortExpression = column Then
                Dim lastDirection = TryCast(ViewState("SortDirection"), String)
                If lastDirection IsNot Nothing _
                  AndAlso lastDirection = "DESC" Then

                    sortDirection = "ASC"

                End If
            End If
        End If

        ' Save new values in ViewState.
        ViewState("SortDirection") = sortDirection
        ViewState("SortExpression") = column

        Return sortDirection

    End Function

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Attributes.Add("onmouseover", "this.style.cursor='hand';")
            e.Row.Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(sender, "Select$" + e.Row.RowIndex.ToString))
        End If
    End Sub
End Class

Select allOpen in new window