Visual Basic

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        Dim i As Integer = 1


        Dim tmpConn As New SqlConnection(connStrDBHERFI)
        Dim tmpComm As SqlCommand = Nothing
        Dim tmpRdr As SqlDataReader = Nothing
        Try

            xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
            Me.Cursor = Cursors.WaitCursor
            If Not tmpConn.State = ConnectionState.Open Then tmpConn.Open()
            tmpComm = New SqlCommand("Select cardcode, cardname from ocrd where 'y' not in (FrozenFor, ValidFor)", tmpConn)
            tmpRdr = tmpComm.ExecuteReader(CommandBehavior.Default)
            If tmpRdr.HasRows Then
                Do While tmpRdr.Read
                    xlWorkSheet.Cells(i, 1) = tmpRdr.Item("CardCode")
                    xlWorkSheet.Cells(i, 2) = tmpRdr.Item("CardName")
                    i += 1
                Loop
                With xlWorkSheet.Range("A1", "B" & i)
                    '.BorderAround()'
                    .Sort(Key1:=.Columns(2), Order1:=Excel.XlSortOrder.xlAscending, _
    Key2:=.Columns(1), Order2:=Excel.XlSortOrder.xlAscending, _
    Orientation:=Excel.XlSortOrientation.xlSortColumns, _
    Header:=Excel.XlYesNoGuess.xlNo, _
    SortMethod:=Excel.XlSortMethod.xlPinYin, _
    DataOption1:=Excel.XlSortDataOption.xlSortNormal, _
    DataOption2:=Excel.XlSortDataOption.xlSortNormal, _
    DataOption3:=Excel.XlSortDataOption.xlSortNormal)
                End With


            End If
            tmpRdr.Close()
            tmpConn.Close()
            tmpConn.Dispose()

            xlWorkSheet.SaveAs("c:\Temp\test.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()

            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)

        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

Christian Graus wrote:

I mean, it's easy enough for me to tell you the line of code that does this, but do you wnat to learn VB, or do you want to post and hope that someone will give you code for every little thing you hope to do ?

if USER IS MANAGER then
  select all items from case table joined to investigator and user tables (to get the rest of the details)

else if USER IS INVESTIGATOR then
  select items from case table joined to investigator and user tables (to get the rest of the details)
  where USERID = LOGGED IN USERID

else
  put code to handle the default situation here

end if