Decimal byte array constructor requires an array of length four containing valid decimal bytes.

This error was very surprising for me. I spent couple of hours with my friends and even search a lot on internet to quikly fix this problem but I could not solved.
Finally I asked one of my senior and he fixed it in a second because he has faced this issue before.
Scenario: I wrote a query which consists of a column returning value like

select avg(tat) from employee.

It was returning values like

12.397339

2.00303

12.9800122

Note: This query was running perfectly in TOAD, SQL Developer while executed at Oracle Server.

When I executed the query in .Net and bind the result to the dataset, i started getting the above exception. I was so much frustrated why it is not executed in .Net when in Oracle the same query is executed.

Reason: Dataset does not support the binding of a value having more than 2 decimal places. So I have to round the values to two decimal places.

Solution: select round(avg(tat),2) from employee

Note: You might also get the message as Accessor is not a parameter accessor.

The reason if the same what I discussed here.

Thread: ORA-01795, limit on in clause

I would like to share that Oracle 10g has a limit on in clause, therefore you can only have maximum of 1000 expressions inside a in clause.
Example.
You might come accross such situtaion sometime when passing employee Ids as string inside the in clause.

Select name from temployee where Id in ('1','2','3',.....'10000')

Cause: The performance of this query will be very slow and if the number of employee ids limit reached, the oracle server will throw an error message.

Suggestions:

1) Avoid using in clause, use exists clause which is better in performance.

2) Use sub query instead of using in clause and passing id's as string

Example;

Select name from temployee where Id in (select Id from tUsers)

This will work perfectly and performance of the query will be better than the IN and EXISTS clause.

Any suggestions, please comment.

Export GridView to Excel cause System.OutOfMemoryException - ASP.NET VB

Recently I was working on a report where I need to display data in Excel sheet instead of PDF etc. So I designed the query which was returning more than 80,000 records for a given date criteria.
I used the technique of exporting data from gridview to Excel. Since the process is slow but most effcient where you are not required to create worksheet object. Things are managed by ASP.NET because Gridview has inbuilt functionality of exporting data to Excel.
Note: I was having MS office 2003 installed on my PC. there Excel 2003
I used the below code

Public Sub ExportDataToExcel()
' A function that bind data to GridView
bindDataToGridViewControl()
Response.Clear()
Response.Buffer = True
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Response.Write("")
Response.Write(vbCr & vbLf)
Response.Write("")
Dim tw As New StringWriter
Dim hw As New HtmlTextWriter(tw)
' Gridview that have data
GridView1.RenderControl(hw)
Response.AppendHeader("content-disposition","attachment;filename=Excel.xls")
Response.Write(tw.ToString())
Response.End()

End Sub


Issues:I started getting Exception "System.OutOfMemoryException"
After doing lot is searching on Internet, I came to know that Excel 2003's sheet has a limitation where a sheet can stored maximum of 65,536 records in one sheet.

If client has office 2010 installed or later version, one single sheet can contain more than 10,00,0000 records.

So the solution would be better to breakdown your DataTable records into multiple sets, and bind each set of records with each sheet. For that you need to created Worksheet object and each and ever column will be defnied by you.

Tips on Exception Handling

1. Never do a 'catch exception and do nothing'. If you hide an exception, you will never know if the exception happened or not. Lot of developers uses this handy method to ignore non-significant errors. You should always try to avoid exceptions by checking all the error conditions programmatically. In any case, catching an exception and doing nothing is not allowed. In the worst case, you should log the exception and proceed.

2. In case of exceptions, give a friendly message to the user, but log the actual error with all possible details about the error, including the time it occurred, method and class name etc.

3. Always catch only the specific exception, not generic exception.
Good:
Private Sub ReadFromFile (fileName As String)
Try
// read from file.
catch (FileIOException ex)
// log error.
// re-throw exception depending on your case.
Throw
End Try
End Sub

Not Good:
Private Function ReadFromFile (fileName As String) As String
Try
// read from file.
Catch (Exception ex)
// Catching general exception is bad... we will never know whether
// it was a file error or some other error.
// Here you are hiding an exception.
// In this case no one will ever know that an exception happened.
Return String.Empty
End Try
End Function

4. No need to catch the general exception in all your methods. Leave it open and let the application crash. This will help you find most of the errors during development cycle. You can have an application level (thread level) error handler where you can handle all general exceptions. In case of an 'unexpected general error', this error handler should catch the exception and should log the error in addition to giving a friendly message to the user before closing the application, or allowing the user to 'ignore and proceed'.

5. When you re throw an exception, use the throw statement without specifying the original exception. This way, the original call stack is preserved.
Good:
Catch
// do whatever you want to handle the exception
throw
Not Good:
catch (Exception ex)
// do whatever you want to handle the exception
throw ex

6. Do not write try-catch in all your methods. Use it only if there is a possibility that a specific exception may occur and it cannot be prevented by any other means. For example, if you want to insert a record if it does not already exists in database, you should try to select record using the key. Some developers try to insert a record without checking if it already exists. If an exception occurs, they will assume that the record already exists. This is strictly not allowed. You should always explicitly check for errors rather than waiting for exceptions to occur. On the other hand, you should always use exception handlers while you communicate with external systems like network, hardware devices etc. Such systems are subject to failure anytime and error checking is not usually reliable. In those cases, you should use exception handlers and try to recover from error.

7.Do not write very large try-catch blocks. If required, write separate try-catch for each task you perform and enclose only the specific piece of code inside the try-catch. This will help you find which piece of code generated the exception and you can give specific error message to the user.

8. Write your own custom exception classes if required in your application. Do not derive your custom exceptions from the ApplicationException class. Instead, inherit from base class System.Exception.

Good bye Exceptional Handling