The task was to provide users with an interface where they can upload the 'csv' files, these files were to be processed and loaded to Database by a Console application. The code in Console application could not handle the 'xls' files so we thought, OK, lets convert 'xls' to 'csv' in the code, Seemed like fun. The idea was to convert it right after uploading within 'csv' file.
As Microsoft does not recommend using the Excel objects in ASP.NET, we decided to use the Jet engine to open xls. (Ace driver is used for xlsx)
The code was pretty straight, can be found on following links:
http://www.c-sharpcorner.com/uploadfile/yuanwang200409/102242008174401pm/1.aspx
http://www.devasp.net/net/articles/display/141.html
FIRST BUMP 'OleDbException (0x80004005): Unspecified error' ( Impersonation ): The ablove code ran fine in my test web site and test console application, but it gave an 'OleDbException (0x80004005): Unspecified error' in main web site, turns out imperonation was set to True and as soon as I changed it to False, it did work. on My XP box, web site was running under user
'ASPNET' with imperosnation set to FALSE
'IUSR_*' i.e IIS guest user with impersonation set to TRUE
The weired part was that both users had same rights on the folders I was saving files to and on Excel app in DCOM Config.
We decided to give it a try on Windows Server 2003 with web site set to windows authentication ( impersonation = true ) and yes it did work.
SECOND BUMP 'External table not in correct format': I got this error with some files and it appeared that the file from client has some metadata issues ( when I opened the file in Excel and try to save it ,excel would give me this error saying File can not be saved in current format ) and the error was caused by that. Some people were able to reslove the error by using "Extended Properties=HTML Import;" in connection string. But it did not work for me.
We decided to detour from here and use Excel object :( as we had no control on client setting the meta deta of Excel files.
Before third bump there were a ouple of small thingies like 'Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005'
Fix can be found at
http://blog.crowe.co.nz/archive/2006/03/02/589.aspx
THIRD BUMP ( Could not get rid of the EXCEL process ): I has all the code in place to 'Quiet' the excel, but, it just did not work. work around was done to Kill the process as we knew no other application on server was using EXCEL. The normal steps to quite the excel application worked just fine in console application though.
FOURTH BUMP: Code worked with one file 1 on my machine and with the other file 2 code will break. and the same code will work perfectly fine with file 2 on some other machine . We moved it to QA ( Windows Server 2003 )and worked with every file just perfect. But , then there was another problem: one user can upload it and second cant, permissions on folder and DCOM Conifg checked.
Another Detour: Uplooad the xls as it is and convert in Console application.
Lesson Learnt: If its 'xlsx' use 'ACE Driver' or read xml within excel as recommneded by MS. If xls and you know its always going to be properly formatted 'jet Engine'
Code:
Imports Microsoft.Office.Interop
Private Function ConvertFile(ByVal SourceFolder As String, ByVal FileName As String, ByVal FileExtension As String)As Boolean
Dim appExcel As New Excel.Application
Dim workBooks As Excel.Workbooks = appExcel.Workbooks
Dim objWorkbook As Excel.Workbook
Try
objWorkbook = workBooks.Open(CompleteFilePath )
objWorkbook.SaveAs(Filename:=CObj(SourceFolder & FileName & ".csv"), FileFormat:=Excel.XlFileFormat.xlCSV)
Catch ex As Exception
GenerateAlert(ex.Message().Replace("'", "") & " Error Converting File to CSV.")
LogError(ex )
Return False
Finally
If Not(objWorkbook is Nothing) then
objWorkbook.Close(SaveChanges:=CObj(False))
End If
ReleaseObj(objWorkbook)
ReleaseObj(workBooks)
appExcel.Quit()
ReleaseObj(appExcel)
Dim proc As System.Diagnostics.Process
For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
proc.Kill()
Next
DeleteSourceFile(SourceFolder & FileName & FileExtension)
End Try
Return True
End Function
Private Sub ReleaseObj(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch ex As Exception
LogError(ex )
Finally
o = Nothing
End Try
End Sub
Protected Sub DeleteSourceFile(Byval CompleteFilePath As string)
Try
Dim MyFile As FileInfo = New FileInfo(CompleteFilePath)
If MyFile.Exists Then
File.Delete(CompleteFilePath)
Else
Throw New FileNotFoundException()
End If
Catch ex As Exception
GenerateAlert( " Source File could not be deleted.")
LogError(ex)
End Try
End Sub
The code to kill the process ( Avoid it if you can ):
Dim proc As System.Diagnostics.Process
For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
proc.Kill()
Next
I was parsing a csv file in order to move it to DB. one of the value in the row was a date value with 'yyyyMMdd' format.
Convert.ToDateTime & Date.Parse did not work,
DateTime.ParseExact came in handy and did the trick :)
DateTime.ParseExact(ValueInTheFile,"yyyyMMdd",Nothing)
I got this error on production for a report which was working fine in Dev and it took me a while to figure out the cause.
Report's Data Source was pointing to a SQL Server View, when I looked at the SQL Query ( Database - > Show SQL Query ), I noticed that Database name was there with view name: "DbName.dbo.ViewName"
I did a lot of googling, trying to figure out a way to change this behaviour and have just "dbo.ViewName" in SQL, but in vain.
For now, I have fixed it by putting the SQL in the view within a Command in Crystal Report and its working.
Its kind of OK for this report as view had a small SQL and report is pretty small.
I would love to find REAL solution though, Crystal Reports does not always put the DBName with View.
Most of the times 'SMTP proxy service on Firewall ' would not allow a line longer than '999' characters in HTML email. The solution would be to put a line feed in the HTML string of the mail 900 characters.
If you put this Line feed into the HTML string, it might break the formating. The right approach will be to put linefeed in the text fields ( e.g. Comments / Description) at appropriate position.
I ended up putting linefeed every 200 characters:
int j = TextColumn.Length/200;
for(int i=1;i<=j;i++)
{
TextColumn = TextColumn.Insert(i * 200,Environment.NewLine);
}
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
http://www.tsqltutorials.com/pivot.php
PS: For more complex Queries, inner SQL's Group By are important and you might also need to use Sum in inner SQL
Quite Straight forward:
- Select the Column , Right Click -> Propoerties OR hit F4
- Visibility -> Hidden -> Expression i.e. Click on Hidden under Visibility and select Expression from Drop Down
- Write the expression e,g.
=IIF(Parameters!MonthlyOrYearly.Value.Equals("Monthly"),false,true)
compared to FormatDateTime Good old 'Format' function makes the job a lot easier :
- Format(Fields!ResultDate.Value,"M/d/yyyy")
- Format(Fields!ResultDate.Value,"yyyy")
=IIF(Parameters!MonthlyOrYearly.Value.Equals(Monthly"),Format(Fields!ResultDate.Value,"M/d/yyyy"),Format(Fields!ResultDate.Value,"yyyy"))
I was trying to add a new field in an Oracle DB table using this script:
alter table TABLENAME add FIELDNAME char(1) not null default '0' ;
it was throwing an error:
'ORA-30649:missing directory keyword'
it drove me nutts for a while and after some googling, I was able to figure it out, its the Order: 'not null' should come after default value. right script is:
alter table TABLENAME add FIELDNAME char(1) default '0' not null;
order does matter :)
A JavaScript object can be of great help when you need to pass multiple values to a Modal Window. E.G.
var objArgs = new Object();
objArgs.Value1 = "Value 1";
objArgs.Value2 = 2;
var modalWindowFeatures = 'dialogHeight:600px;dialogWidth:600px;scroll:no;status:no;resizable:no';
url = 'relative/path/to/page';
var returnValue;
returnValue = window.showModalDialog(url,objArgs,modalWindowFeatures );
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
As the error message suggests , this normally occurs when you are assigning a T-SQL variable a value within sql query and the query is returning more that one rows e.g.
Declare @var1 nvarchar(50)
' select @var1=name from table1'
Putting 'top 1' can resolve the problem in many cases ( do check the business logic aspects):
' select top 1 @var1=name from table1'