Friday, August 13, 2010

Hiding GridEditCommandColumn and CommandItemDisplay

It is not necessary to disable a gridEditCommandColumn in the prerender or databind event of the radgrid or datagrid.

To hide the GridEditCommandColumn:

wgdDetails.Columns.Item(1).Visible = False


To hide the CommandItem (Add)


wgdDetails.MasterTableView.IsItemInserted = False
wgdDetails.MasterTableView.CommandItemDisplay = Telerik.Web.UI.GridCommandItemDisplay.None

Tuesday, August 3, 2010

Filter DataTable

Method to filter datatable. Returns a dataset with two datatables (0ne for the filtered data and another one to show any error message defined in the caller method or event)

1 Protected Function FilterDataTable(ByVal strFilterString As String, ByVal dtMainTable As Data.DataTable, ByVal strMessage As String) As Data.DataSet
2 Dim ResultData As New
DataSet
3 Dim dtMessage As New
Data.DataTable
4 Dim DataTableClon As New
Data.DataTable
5 Dim drClon As
System.Data.DataRow()
6 Dim dsDataFiltered As New
Data.DataSet
7 Try

8
ResultData.Tables.Add(dtMainTable)
9 DataTableClon = ResultData.Tables(0
).Copy
10
DataTableClon.Rows.Clear()
11 drClon = ResultData.Tables(0
).Select(strFilterString)
12 For Each dr In
drClon
13
DataTableClon.ImportRow(dr)
14 Next

15

16 dtMessage.TableName = "Header"
17 dtMessage.Columns.Add("ErrorMessage", GetType
(System.String))
18
dsDataFiltered.Tables.Add(DataTableClon)
19
dsDataFiltered.Tables.Add(dtMessage)
20 Dim newMessageRow As DataRow = dsDataFiltered.Tables("Header"
).NewRow()
21 newMessageRow("ErrorMessage") =
strMessage
22 dsDataFiltered.Tables("Header"
).Rows.Add(newMessageRow)
23

24 Catch ex As Exception
25 Call ManageError(strGlobalUser, strAppName, strModName, "FilterDataTable"
, intGlobalMain, Err, strGlobalPathError, ApplicationType.Web)
26 Finally

27 FilterDataTable =
dsDataFiltered
28 End Try

29 End Function

Export to a formatted excel file as an attachment (safe mode)

If you want to export a datatable into an excel file keeping each columns place in an excel file and you want also to be asked where you want your excel file to be saved. Then you should use the method I have bellow.

If you are also one of those programmers who had this:
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005., then, you should also use this code instead of the one you are currently trying to make work.

I've tried before and it worked fine locally. However, once I deployed it didn't work at all. I tried to find out how to solve that issue and even though I did everything suggested on the net it never worked. May be the reason is because Microsoft doesn't support server side automation at office. You can read more about that
here

The method:

1 Public Sub ExportToSpreadsheet(ByVal tblTable As Data.DataTable, ByVal strName As String)
2 Try

3 Dim context As HttpContext =
HttpContext.Current
4 Dim grid As
System.Web.UI.WebControls.DataGrid
5 grid = New
System.Web.UI.WebControls.DataGrid
6 grid.HeaderStyle.Font.Bold = True

7 grid.DataSource =
tblTable
8 grid.DataMember =
tblTable.TableName
9
grid.DataBind()
10

11 context.Response.Clear()
12

13 Dim sw As StringWriter
14 sw = New
StringWriter()
15

16 Dim hw As HtmlTextWriter
17 hw = New
HtmlTextWriter(sw)
18

19 grid.RenderControl(hw)
20
context.Response.Write(sw.ToString)
21 context.Response.AppendHeader("content-disposition", "attachment; filename=" & strName & ".xls"
)
22 context.Response.ContentType = "application/excel"

23 context.Response.Charset = String
.Empty
24
context.Response.Flush()
25
context.Response.End()
26 Catch ex1 As
System.Threading.ThreadAbortException
27 Exit Sub

28 Catch ex As
Exception
29 Call ManageError("ExportToSpreadsheet"
, Err)
30 End Try

31

32 End Sub



Important Considerations:
1.ManageError is a method I use to manage errors, so that you can replace for a simple exit sub or another customized method you use to manage errors.

2. Make sure you will hava an exception type System.Threading.ThreadAbortException in your caller method since that exception will be catched there too.
Apparently the context.response.end automatically runs System.Threading.ThreadAbortException and the only way of avoiding it is catching it. I tried what Microsoft suggest. However, it shows my filter screen page instead of the datatable data.

3. If you don't really need to ask the user where to save your excel file you can simply use the code suggested in this page and forget about that exception.