faizan ahmad

Here is a extension method which can be used to perform the  Delete operation on a DataTable just like the select i.e. using a filterExpression:

using

System;

using

System.Collections.Generic;

using

System.Data;

using

System.Data.SqlClient;

public static class MyExtensions

{

   /// <summary>

   /// Delete row based on filterExpression

   /// </summary>

   /// <param name="dt"></param>

   /// <param name="filterExpression"> just like select </param>

   /// <returns> number of deleted rows </returns>

   public static int Delete( this DataTable dt, string filterExpression)

   {

      int nor=0; // number of rows deleted

      DataRow [] toBeDeleted;

      toBeDeleted = dt.Select(filterExpression);

      if (toBeDeleted.Length > 0)

      {

         foreach ( DataRow dr in toBeDeleted)

            {

               dt.Rows.Remove(dr);

               nor++;

            }

      }

      return nor;

   }

 }

 

I ended up using the MS Excel objects to convert files from XLS to CSV . Converted CSV had comma "," as field separator. But, what if the a filed content has comma like "lastName,FirstName". Now if you use default .net split method to get the fileds of a row in csv file, it will treat "lastName,FirstName" as two fields. I ended up writing my own split method which will take care of the comma in fields:

    Private Function CustomSplit(ByVal sText As String,ByVal sSeparator As String) As String()
        Dim RetrunArray As  New ArrayList
        Dim CurrentIndex As Int32
        Dim CommaIndex As Int32
        Dim CurrentField As String
        Dim NextChar As String
       
        CommaIndex = sText.IndexOf(sSeparator)
        While(CommaIndex>0 and CommaIndex<sText.Length)
            CurrentField = sText.Substring(CurrentIndex,CommaIndex-CurrentIndex)
            RetrunArray.Add(CurrentField)
            NextChar = sText.Substring(CommaIndex+1,1)
            If(NextChar.Equals ("""")) Then
                CurrentIndex = CommaIndex +1
                CommaIndex = sText.IndexOf("""",CommaIndex+2)
                CommaIndex = CommaIndex +1
                If(CommaIndex<sText.Length)
                    NextChar = sText.Substring(CommaIndex,1)               
                    If(Not NextChar.Equals (",")) Then
                        CommaIndex = sText.IndexOf(",",CommaIndex+2)
                    End If
                  
                End If
            Else
                CurrentIndex = CommaIndex +1
                CommaIndex = sText.IndexOf(sSeparator,CurrentIndex)
            End If           
        End While
        'Pick up last field
        CurrentField = sText.Substring(CurrentIndex)
        RetrunArray.Add(CurrentField)

        Return RetrunArray.ToArray(Type.GetType("System.String"))
       
    End Function
 

 

 

Presumption/limitation:

1. yearly holidays are stored in a table
2. maximum 2 consecutive holidays

Table structure: HOLIDAY_DATE (DATETIME),HOLIDAY_DESC (VARCHAR)

DB Script :

 

DECLARE @CurrentDay DATETIME
DECLARE @LastWorkingDay DATETIME, @CurrentWeekDay INT

SET @CurrentDay = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)

SET @LastWorkingDay = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)

DECLARE @DaysToLastWorkingDay INT

SET @DaysToLastWorkingDay = 0

SET @LastWorkingDay = DATEADD(day, -1, @LastWorkingDay )

--check if yesterday was holiday

IF EXISTS (SELECT HOLIDAY_DATE FROM tblHOLIDAY WHERE HOLIDAY_DATE =CAST(CONVERT(VARCHAR, @LastWorkingDay, 101) AS DATETIME))                                                           

BEGIN                                                                                                                                                                                                            SET @LastWorkingDay= DATEADD(day,-1, @LastWorkingDay)
END                                                                                                                                                        

-- adjust for weekends, if needed

SET @CurrentWeekDay = DATEPART(weekday, @LastWorkingDay)

IF @CurrentWeekDay IN (1, 7)

SET @DaysToLastWorkingDay = (CASE @CurrentWeekDay

WHEN 1 THEN -2            -- 1 = Sunday

WHEN 7 THEN -1            -- 7 = Saturday

ELSE 0

END)

SET @LastWorkingDay = DATEADD(day,@DaysToLastWorkingDay, @LastWorkingDay)

IF EXISTS (SELECT HOLIDAY_DATE FROM tblHOLIDAY WHERE HOLIDAY_DATE =CAST(CONVERT(VARCHAR, @LastWorkingDay, 101) AS DATETIME))

BEGIN

SET @DaysToLastWorkingDay = -1

IF EXISTS (SELECT HOLIDAY_DATE FROM tblHOLIDAY WHERE HOLIDAY_DATE =CAST(CONVERT(VARCHAR, DATEADD(day, -1, @LastWorkingDay), 101) AS DATETIME))

SET @DaysToLastWorkingDay = @DaysToLastWorkingDay - 1

SET @LastWorkingDay= DATEADD(day,@DaysToLastWorkingDay, @LastWorkingDay)

END
SELECT @LastWorkingDay

 

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.

Quite Straight forward:

1. Select the Column , Right Click -> Propoerties OR hit F4
2. Visibility -> Hidden -> Expression i.e. Click on Hidden under Visibility and select Expression from Drop Down
3. 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 :

 

1.  Format(Fields!ResultDate.Value,"M/d/yyyy")
2. 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'

other day I had to install Visual studio.NET 2003 on my system, the problem was I had Visual Studio 2005 and 2008 with framework 2.0 and 3.5 and I did not have .NET 1.1 on it.

There were two suggetions from ppl at work:

1. Install .NET framework 1.1 and load project in VS 2005
2. You will need to Un install VS 2005 and 2008 and then install VS.NET 2003

After googling a little first idea did not seem workable and I was not ready to  follow the second option of starting all over. Half hour down I found a solution:

The trick is to install .NET framework 1.1 separately first and then run Visual studio 2003 installer.

-- Follow UP

VS 2003 is working fine, VS 2005 and SQL Server 2005 prompt an error on start   "unable to load file, this error could not be resolved, Please reinstall the application". It seems to work fine though.