Sharing my Tech knowledge and experiences - Microsoft MVP ASP.Net

Step by Step Guide to Migrate from CE10 Crystal Reports CMS to BOXI R2 CMC (Admin)

noreply@blogger.com (Deepika) — Mon, 09 Apr 2018 08:52:00 +0000

Below given is the step-by-step guide to migrate from CE 10 CMS & Crystal Reports to BO XI Release 2 CMC Environment (along with Business Intelligence Administration Launch Pad guide)

Ø Remotely connect to BO XI R2 Server.

Ø Open the Import Wizard Tool of Business Object XI Release 2 to start migrating Report/Repository Objects/Instances (including Reports, Instances, Users/Groups, Schedules, Events etc.) from CE 10 CMS to BO XI R2 CMC as follows –

Ø Import Wizard window will popup, click on Next button

Ø In next screen provide Source Environment Detail (Crystal Enterprise 10 CMS Detail) and click on the Next Button.

Ø It will take you to the next screen - Destination Environment Detail screen. The Destination Environment is BO XI R2 Environment. Please provide the Destination Environment Details and click on Next Button-

Ø On the next screen, please select all the Objects/Repository Objects to be imported (as shown below in print screen) and click on Next Button.

Ø Please select the Import scenario as shown below and click on Next button.

Ø Click on Next button on this informational screen.

Ø At this point, the Import wizard will start retrieving all the CMS Objects from our Source CE 10 Environment (such as Report, Its instances, User Groups/Users etc.)

Ø The Import Wizard will list all the available User List and User Groups (from Source CE 10 Environment) to be imported into Destination (BO XI R2 Environment CMC). Select all the required User Groups/Users and click on Next Button.

Ø Now on the Folders and Objects screen of Import Wizard, please select all the Required Reports Folders and corresponding Reports (as shown below in screen shot) and click on Next button

Ø On the Import Repository Object Options screen, please select the first option – Import all the repository objects (as shown below in screen shot) and click on Next button -

Ø The Import Wizard will list all Ready to Import Objects (as per selections made in previous screens), please review the list and click on Finish button to start Import-

Ø It will start actual Importing of CMS Objects into BO XI R2 CMC Environment. It will take time based on parameters selection in previous screens (No of Report Folders, Reports, User Groups/Users, Report Instances, and Schedules etc.). Once finished, it will provide the Import Summary. Please review the Import Summary by clicking on View Details Log button (as shown below in screen shot), it will provide detail import logs and summary.

Ø The Detail Log provides the detailed import summary for all the Objects. Now, we have to open the Business Intelligence Platform Administration Launchpad (to manage all aspects of Business Objects) as shown in screen shot below. On the Business Intelligence Admin Launchpad, click on the Launch the Central Management Console link to open CMC.

Ø This is the screen shot of the BO XI R2 Central Management Console. Click on the Folder link to verify all the Selected CE 10 CMS Objects (including Report Folders, Reports, corresponding user groups/users for the imported reports, instances, schedules etc.) have been imported successfully.

Ø To verify the detail of any of the given imported reports such as Report History, Database connection (under Process), Schedules, Authentication (user rights) etc., please click on the required report. It will open the Report detail. Click on the Process tab and under that select Database link (as shown in the screen shot below). Update to required credentials to test.

Ø Now, click on the Schedule tab to set up the Report generation recurring/on-demand schedule (as shown below). To verify the Report generation, select On Demand option and click Schedule button.
Ø It will take users to History tab for the given report. This tab lists out all the Instances of the given Report generated/scheduled so far along with Status, DateTime, and Parameters Details. User can verify the here the status of their Report generation (scheduled on-demand), once it completes the status will say Success. Click on the Report instance and it will open the generated Instance in PDF Format. Users can modify the Report parameters under Parameter section under Process tab. Users can verify user groups/permissions setup on given Report under Rights tab.

Promotion Management | LCMBIAR - Transport/ Migrate contents to a file

noreply@blogger.com (Deepika) — Thu, 05 Apr 2018 07:44:00 +0000

LCMBIAR- For moving content between same version of the product (in this case all Bi4.x versions are considered same) - a new tool is used, its called promotion management.
With this tool you can generate LCMBIAR files for backup. It has command line interface as well.

So, XI3.1 BIAR equal to BI4.x LCMBIAR now.

Steps for using LCMBIAR:

The other case can be we are transporting the objects in LCMBIAR format and using it to import the objects in destination BO Server.

Select Output to LCMBIAR File in the destination while creating the job.

Object selection window will come as earlier, select the objects you want to transport along with Manage Dependencies checked and click Promote.

All the setting will be same as per the earlier case except here you will be able to save the LCMBIAR file either on your File System or using FTP. In my case I have selected File System.

Save File window will get open.

Once this is done, you can go the Promotion Management again and now select Import. A new window will get open browse the desired file from the File System or FTP

After that a new window will come which will ask you for the Destination server details where you want to import the LCMBIAR file. Provide the credentials of the destination and click create.

You will be able to see your job in the promotions job list Promote it and check the status for Success.

Populate Current date ABAP code through customer exit variable BW

noreply@blogger.com (Deepika) — Thu, 22 Mar 2018 07:08:00 +0000

1) Logon to SAP

2) Go to /ncmod T-code

3) Enter Project details and select "Components". Now click on Display button.

4) Click on Function Exit. Click text next to include. Double click on text next to include once you are in the function exit module.

 //Declaring variables..  
 DATA: l_s_range TYPE rsr_s_rangesid.  
 CASE i  
  when [your customer exit variable name].  
  if i_step = 1.  
   data : cal_day type sy-datum.  
   cal_day = sy-datum.  
   l_s_range-low = cal_day.  
     l_s_range-sign = 'I'.  
     l_s_range-opt = 'EQ'.  
     APPEND l_s_range TO e_t_range.  
 ENDIF.   
 ENDCASE

Create header in cross tab - Webi

noreply@blogger.com (Deepika) — Tue, 20 Mar 2018 06:16:00 +0000

Create header in cross tab - WEBI

Usually for cross tabs if we put required text above the crosstab data part it would replicate (i.e. it wont merge) the same header in all columns.

So here's solution to get single header across cross tab data

1) Create a variable by right clicking on Variables in left pane and New with required text that needs to go in the header and set its qualification to Dimension.

2) Right-click on the headings row and choose Insert -> Insert Row above. Now drag and drop the variable to the header portion of cross tab

3) Right-click on the object in the cross table heading and choose Break -> Add Break

4) Right-click on this object again and choose Break -> Manage Breaks. Within the Manage Breaks dialog box, unselect "Break Footer" and change the Duplicate Values behavior to "Merge".

Transport or Migrate BEx Query changes from one environment to another (Admin)

noreply@blogger.com (Deepika) — Mon, 26 Feb 2018 07:43:00 +0000

Transport or Migrate BEx Query changes from one environment to another

In order to transport/migrate BEx query from lower environments to higher environment, follow below steps:

1) Login to SAP Logon Dev system

2) Go to T-code /nse01. Below screen will appear. Click on Display button at the bottom

3) You should be able to see your latest request number in the queue which needs to be transported/migrated to next system (in my case Quality environment)

4) Expand the request to root level and then starting downwards release each of the root requests first then the main one by clicking on Truck symbol (i.e. Release Directly). You'll be able to see all your changes in another system.

Automation of BW scheduled Jobs linked to BO Webi reprts

noreply@blogger.com (Deepika) — Wed, 14 Feb 2018 11:12:00 +0000

AUTOMATION OF SCHEDULED JOBS LINKING TO BO REPORTS

CURRENT SCENARIO:

If BOBJ reports are scheduled to run on daily recurrence and for some reason if BW loads get delayed or failed, BOBJ report will not show the latest data.

PROPOSED SOLUTION:

To overcome the current scenario we are going to have BO reports scheduled based on file events. The file will be created based on the successful completion of BW data loads/process chains. This would make sure the report is generated and sent to users only if the process chain completes successfully.

In BW, we have created an ABAP program to create a test file on application server. This directory, where the file is created, is mounted on both BW server as well as BOBJ server. BOBJ reports will be scheduled prior to this file creation in BW. In BOBJ once the schedules starts, Event will continuously search for the file. In case the file is available prior to BOBJ report schedule time, Event doesn’t process. It will internally check with timestamp of BOBJ report schedule and file creation. So, we are deleting the old file and replacing it with a new file after BW data loads.

Below is high level summary of the steps involved:

Create a share drive in BW and mount it to BOBJ server so that it can be accessed by both BW and BOBJ server. This is required so that file can be created in BW side and read in BOBJ side.

Create an ABAP Program to delete the previously created file

Create an ABAP program to create a sample test file on Application server

Create the File based Event on the BOBJ side and assign this event to the desired Report for scheduling

BENEFITS:

Accuracy in data since the report generates only after BW data load finishes

Incorrect data Reports aren’t sent to users if BW loads fail (especially useful on weekends wherein there is no monitoring)

DETAILED STEPS:

Creating a shared file path at OS level:

Create a share drive in BW and mount it to BOBJ server so that can be accessed by BW system and BOBJ system with full rights with an admin user. For example, \\AZSXX2120\data\BOBJ\ is directory that we created in BW where test file will be written. This file can be read from the BOBJ side. This step is done by our BASIS team.

BW Steps

We have created two ABAP program to delete and create the test file. We included these two program in a process chain and attached the process chain with our transactional data load process chain. This helped to create the file only at the end of all transactional data load.

File deletion ABAP code:
*&---------------------------------------------------------------------*
*& Report ZBO_ORDERS_DELETE_TEST
*&
*&---------------------------------------------------------------------*
*&
*& Sample Testing. This program will delete a file from Application *& Server
*&---------------------------------------------------------------------*

REPORT ZBO_ORDERS_DELETE_TEST.

data: p_s_path type pathextern. " Complete filepath
data: p_s_file type pathextern." File name
data: p_s_dir type pathextern. " Directory

p_s_file ='Orders.csv'. "file name to be deleted

p_s_dir = '\AZSXX2120\data\BOBJ\'. " Path from where file will be deleted

concatenate p_s_dir p_s_file into p_s_path.
condense p_s_path.

open dataset p_s_path for input in text mode encoding default.

if sy-subrc = 0.
close dataset p_s_path.
delete dataset p_s_path.

if sy-subrc eq 0.
write: 'File Deleted Succesfully', p_s_path.
else.
write: 'No Authorization to delete or no file exists', p_s_path.
endif.
else.

write: p_s_path, 'File does not exist'.
endif.

File Creation ABAP code:
*&---------------------------------------------------------------------*
*& Report ZBO_ORDERS_CREATE_TEST
*& Program will create a file and place it at application server
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZBO_ORDERS_CREATE_TEST.
tables: rlgrap.
data: lv_file_str type string . "file Path For Application server file
data: lv_msg_text(50),
lv_filename type localfile.

data l_lines type i.
data ls_split type string.
data lt_split like table of ls_split.
data lp_file type localfile.
data lp_fil2 like rlgrap.
lp_file = 'Orders.csv'. "file name to be created
lp_fil2 = '\AZSXX2120\data\BOBJ'. " Path where file will be created

at line-selection.

call transaction 'AL11' .

*———————————————————————-*
* Start of selection
*———————————————————————-

start-of-selection.
split lp_file at '.' into lv_file_str lv_filename .

translate lv_filename to upper case .

if lv_filename = 'CSV' .

clear : lv_filename .

split lv_file_str at '\' into table lt_split.
describe table lt_split lines l_lines.
* file name

loop at lt_split into ls_split.
if sy-tabix eq l_lines.
lv_filename = ls_split.
endif.
endloop.

lv_file_str = lp_file .
if lv_file_str is not initial.
perform write_file .
else.
message 'File Path Needed .' type 'W'.
endif.
else.
message '.CSV File Needed .' type 'W'.
endif.

free :lv_filename ,lt_split ,l_lines ,lv_file_str.
*&———————————————————————*
*& Form write_file – This will create a file at Application Server
*&———————————————————————

form write_file .

concatenate lp_fil2 lv_filename '.csv' into lv_filename .
condense lv_filename no-gaps .
open dataset lv_filename for output in text mode
encoding default message lv_msg_text.

if sy-subrc eq 0.
write: 'File' ,lv_filename ,'created Successfully at Application server.'.
exit.
else.
write: 'File' ,lv_filename ,' cannot get created at Application server.'.
exit.
endif.
endform.

The above two program in the process chain are included in our transactional data load chain. This ensures that file would be created only after BW transactional load is completed. After all sequences, BW system will write file on /data/BOBJ mount or share drive.

BOBJ Steps:

From the Business Objects side, we will be creating file-based event and attaching the event to the report. Once the recurrence schedule starts, the event will continuously search for the files specified in the path. If the file occurs prior to schedule time, event will not trigger, internally it will check with time stamp of file creation and schedule time. In case if the file occurs then need to delete it and write the file. Once the file placed, event will get triggered and schedule process starts to run the report.

Transport / Migrate BO Webi Reports to Production (Admin)

noreply@blogger.com (Deepika) — Wed, 07 Feb 2018 06:18:00 +0000

The below article provides information on how to use Promotion Management tool in SAP Business Objects BI Platform 4.0 or above to transport various objects (Webi, universe, dashboards etc.) from one BO environment to other.

Prerequisites –

Access to CMC (Central Management Console)
Administrative Rights to access Promotion Management Tool from CMC.
Server Details and Credentials of both the BO environment between whom transfer needs to be done.
If you are transferring directly between the BO Servers than both the server should be on same network or can talk to each other.

Steps-

1) Login to CMC

2) Select Promotion Management from the Manage column

3) Promotion Management window will get open , you will be able to see the jobs created for Objects Transport. Click on New Job

Provide the Job details like Job Name, Description, Source (CMS) and Destination(CMS/LCMBIAR)

4)

Source will be the CMS from where you want to transport the Objects, select Login to New CMS and provide the credentials. Destination can be either a CMS system where you directly want to transport the objects or LCMBIAR file which can be imported later.

In my case I have selected CMS(Destination- CMS)

A new window will get open which will provide you option to select all the objects you need to transport. Select the objects from the list and click Add & Close.

If you are selecting objects from different folders than select object one by one and click Add. After adding all the objects click Add & Close.

You will be able to see the objects selected by you in the list that will be transported.

6) Now, here an important point to be noted-always check Manage Dependencies before transporting the objects as this option will check the important dependent objects that needs to be transferred along with the objects selected by user. E.g. Reports along with connection and universe.

As you click Manage Dependencies a new window will get open and will display all the dependent objects. In my case I don’t have any so it’s not displayed below. If you get any dependent objects than select all of them and click Apply & Close.

Now, select all the objects that need to be transported and Click Promote.

Promotion window will get open .You will be able to see the summary, security settings and Test Promote.

7) In Security Settings you can transfer the security attached to the objects along with it. Select appropriate option as per your requirements.

Always use the Test Promote before actually transporting the objects as it will give you a rough idea and errors that may come. Click Save.

Below you can see the results of Test Promote. You can check the status and errors if any.

Now, you will be able to see your Job created for objects transport, wait for some time and check the status. It can be Failure, Partial Success and Success. If it’s successfully done you will be able to check your objects in the destination BO Server now.

Locking a BEx query in SAP BW

noreply@blogger.com (Deepika) — Mon, 05 Feb 2018 07:43:00 +0000

In order to modify any existing BEx query in lower environments first step is to lock the query for which follow below steps:

1) Login to SAP Logon Dev system

2) Go to Transport Connection on left pane

3) Expand SAP Transport -> Click on Object Types -> Expand Query Elements -> Expand Query -> Double Click Select Objects and find for the query by providing technical name and once found click on transfer.

4) New pane will open on right similar to the one shown below. Click on the Display button next to Find and select List

5) For All except Query Element, right click each of them and select Do not Transport Any Below. For Query Element select Transport All Below. Then Click on Truck symbol (i.e. Transport Objects).

6) It will ask for Request no#. If there is no request already present create a new request, save and transfer.

For transporting or migrating the changes to higher environment refer below link
Transport or Migrate BEx query changes to another environment

Random posts

noreply@blogger.com (Deepika) — Thu, 15 Jun 2017 04:48:00 +0000

Travel:

Olive Mountain HillView - Best Resorts in Wayanad

Olive Mountain is a joint venture of Babu Varghese and Thomas K. Paul. It is situated at the lower slopes of elegant chembra peak, the top most point of Wayanad which turns out to be the most beautiful for enjoying the at most of your trip.

Shanghumugham Beach in Trivandrum, Kerala

Shanghumugham Beach is one of the main beaches in Trivandrum city, Kerala. Shankumugham Beach or Shangumukham Beach is situated close to Veli Tourist Village and Thiruvananthapuram International Airport.

Cherai Beach - The Princess of the Arabian Sea, Kochi

Cherai is one of the finest and beautiful beaches in Kochi. It is located on the northern end of Vypeen Island. The 15 kilometers (9.3 miles) long beach is great for walking and enjoying the bounty of nature.

Cook Book:

Aviyal Recipe - Kerala Vegetarian Curry

This recipe describes on how you can easily make aviyal. Find out more for the ingredients and method of preparation. Aviyal, Kerala vegetarian curry is one of the Dishes present at any occasions in Kerala.

Tomato Fry Recipe - Kerala Vegetarian Curry for Chapati

This article describes step by step process to make Tomato fry Recipe. This is one of the common vegetarian curries you can make at home as well found at all restaurants in Kerala. This recipe is one of the favorites in Kerala

How to sort columns in Infragistics WinGrid

noreply@blogger.com (Deepika) — Tue, 02 Nov 2010 14:02:00 +0000

This code demonstrates how WinGrid allows the user to sort on both single and multiple columns by clicking on the column headers. The UltraWinGrid allows user to sort by setting the .HeaderClickAction property to either .SortSingle or .SortMulti.

Below are the steps to achieve the same

1. Adding using/imports directives
VB:


Imports Infragistics.Win.UltraWinGrid

C#:
using Infragistics.Win.UltraWinGrid;

2. The InitializeLayout event of UltraGrid sets the default HeaderClickAction for all bands and all columns

VB:

Private Sub UGSort_InitializeLayout(ByVal sender As Object, _
  ByVal e As Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs) _
  Handles UGSort.InitializeLayout
   e.Layout.Override.HeaderClickAction = HeaderClickAction.SortSingle
End Sub

C#:

private void UGSort_InitializeLayout(object sender, 
  Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs e)
{
 e.Layout.Override.HeaderClickAction = HeaderClickAction.SortSingle;
}

3. The code in the Button Click event instructs the UltraWinGrid to sort on band 1 column 2 in Descending order

Visual Basic:

Private Sub btnSortBand1Column2_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles btnSortBand1Column3.Click
 Me.UltraGrid1.DisplayLayout.Bands(1).Columns(2).SortIndicator = _
   SortIndicator.Descending
End Sub

C#:

private void btnSortBand1Column2_Click(object sender, EventArgs e)
{
 this.ultraGrid1.DisplayLayout.Bands[1].Columns[2].SortIndicator = 
   SortIndicator.Descending;
}

How to choose column chooser feature in Infragistics Wingrid

noreply@blogger.com (Deepika) — Tue, 02 Nov 2010 13:55:00 +0000

This code demonstrates using the Column Chooser functionality to display a dialog similar to Microsoft Outlook's Field Chooser where the user can choose columns to display in the WinGrid.

Now, lets start with the step by step process of how we can achieve the same

1. Add using/imports directives
VB:


Imports Infragistics.Win
Imports Infragistics.Win.UltraWinGrid

C#:

using Infragistics.Win;
using Infragistics.Win.UltraWinGrid;

2. The WinGrid has in-built RowSelectorheaderstyle property to choose the column button which will display a button in the row selector area. You can change the appearance of it by RowSelectorHeaderAppearance property.

VB:

' Set the RowSelectorHeaderStyle property to ColumnChooserButton.
Me.UltraGrid1.DisplayLayout.Override.RowSelectorHeaderStyle = _
  RowSelectorHeaderStyle.ColumnChooserButton

' Enable the RowSelectors. 
Me.UltraGrid1.DisplayLayout.Override.RowSelectors = DefaultableBoolean.True

' If you want to change the BackColor of the button then you need to
' set the ThemedElementAlpha property to Transparent otherwise BackColor setting won't be ' honored. 
Me.UltraGrid1.DisplayLayout.Override.RowSelectorHeaderAppearance.ThemedElementAlpha = Alpha.Transparent
Me.UltraGrid1.DisplayLayout.Override.RowSelectorHeaderAppearance.BackColor = Color.Blue

C#:

// Set the RowSelectorHeaderStyle to ColumnChooserButton.
this.ultraGrid1.DisplayLayout.Override.RowSelectorHeaderStyle = 
  RowSelectorHeaderStyle.ColumnChooserButton;

// Enable the RowSelectors. 
this.ultraGrid1.DisplayLayout.Override.RowSelectors = DefaultableBoolean.True;

// If you want to change the BackColor of the button then you need to
//set the ThemedElementAlpha property to Transparent otherwise BackColor setting won't be //honored. 
this.ultraGrid1.DisplayLayout.Override.RowSelectorHeaderAppearance.ThemedElementAlpha = Alpha.Transparent;
this.ultraGrid1.DisplayLayout.Override.RowSelectorHeaderAppearance.BackColor = Color.Blue;

3. You can also have your own user interface for displaying the column chooser. UltraGridColumnChooser is derived from .NET Control class. It displays list of columns & implements drag-n-drop functionality. ColumnChooserDialog derives from .NET Form class. It simply embeds UltragridColumnChooser inset it.

The following code demonstrates how to display column chooser dialog.

Visual Basic:

' Create an instance of the ColumnChooserDialog.
Dim dlg As ColumnChooserDialog = New ColumnChooserDialog()

dlg.Owner = Me

Dim cc As UltraGridColumnChooser = dlg.ColumnChooserControl

' Associate a WinGrid to the column chooser. 
cc.SourceGrid = Me.UltraGrid1

cc.CurrentBand = Me.UltraGrid1.DisplayLayout.Bands(0)

cc.Style = ColumnChooserStyle.AllColumnsWithCheckBoxes
cc.MultipleBandSupport = MultipleBandSupport.SingleBandOnly

' Set location, size etc...
dlg.Size = New Size(150, 300)

dlg.Show()

C#:

// Create an instance of the ColumnChooserDialog.
ColumnChooserDialog dlg = new ColumnChooserDialog( );

dlg.Owner = this;

UltraGridColumnChooser cc = dlg.ColumnChooserControl;

// Associate a WinGrid to the column chooser. 
cc.SourceGrid = this.ultraGrid1;

cc.CurrentBand = this.ultraGrid1.DisplayLayout.Bands[0];

cc.Style = ColumnChooserStyle.AllColumnsAndChildBandsWithCheckBoxes;
cc.MultipleBandSupport = MultipleBandSupport.SingleBandOnly;

// Set location, size etc...
dlg.Size = new Size( 150, 300 );

dlg.Show( );

How to bind WinGrid to obtain a card view layout - Infragistics

noreply@blogger.com (Deepika) — Tue, 02 Nov 2010 13:48:00 +0000

This code shows how to obtain a card view layout with the help of Infragistics WinGrid. WinGrid helps you to easily print and export your data in the form of excel or PDF document.

1. Bind grid to a DataSource (Same as we do in usual Gridview)

2. Now we need to obtain a card view layout which can be achieved by properties of Displaylayout object

VB:


'You must set this property in order to print in card view.
Me.UltraGrid1.DisplayLayout.AllowCardPrinting = _
   Infragistics.Win.UltraWinGrid.AllowCardPrinting.RootBandOnly

'Display WinGrid in card view.
Me.UltraGrid1.DisplayLayout.Bands(0).CardView = True

'Set a caption for each card using the CompanyName field.
Me.UltraGrid1.DisplayLayout.Bands(0).CardSettings.CaptionField = "CompanyName"

C#:

//You must set this property in order to print in card view.
this.ultraGrid1.DisplayLayout.AllowCardPrinting = Infragistics.Win.UltraWinGrid.AllowCardPrinting.RootBandOnly;

//Display WinGrid in card view.
this.ultraGrid1.DisplayLayout.Bands[0].CardView = true;

//Set a caption for each card using the CompanyName field.
this.ultraGrid1.DisplayLayout.Bands[0].CardSettings.CaptionField = CompanyName";

3. To print cards (print layout) you need to write code in InitializePrintPreview event

VB:
'Standard labels makes it easier for each card to stand on its own, allowing you 'to possibly cut out the cards for distribution.
e.PrintLayout.Bands(0).CardSettings.Style = _ Infragistics.Win.UltraWinGrid.CardStyle.StandardLabels

'Setting the MaxCardAreaCols and MaxCardAreaRows properties allow you to limit 'the amount of cards per page. Setting these properties to 3 will give you nine 'cards per page.
e.PrintLayout.Bands(0).CardSettings.MaxCardAreaCols = 3
e.PrintLayout.Bands(0).CardSettings.MaxCardAreaRows = 3

'Each card will automatically increase width in order to fit in the available space.
e.PrintLayout.Bands(0).CardSettings.AutoFit = True

C#:
//Standard labels makes it easier for each card to
//stand on its own, allowing you to possibly cut out the cards for distribution.
e.PrintLayout.Bands[0].CardSettings.Style = Infragistics.Win.UltraWinGrid.CardStyle.StandardLabels;

//Setting the MaxCardAreaCols and MaxCardAreaRows properties allow you to limit //the amount of cards per page. Setting these properties to 3 will give you nine //cards per page.
e.PrintLayout.Bands[0].CardSettings.MaxCardAreaCols = 3;
e.PrintLayout.Bands[0].CardSettings.MaxCardAreaRows = 3;

//Each card will automatically increase width in order to fit in the available space.
e.PrintLayout.Bands[0].CardSettings.AutoFit = true;

4. For print preview write this code on the button click event of Print Preview
VB:
'Calling the PrintPreview method displays WinGrid's print preview dialog box.
Me.UltraGrid1.PrintPreview()

C#:
//Calling the PrintPreview method displays WinGrid's print preview dialog box.
this.ultraGrid1.PrintPreview();

5. Now Run the application and you'll see the output in the form of card view.

6. Output on Print Preview

Draw line on Windows Form

noreply@blogger.com (Deepika) — Thu, 07 Oct 2010 08:39:00 +0000

Drawing simple lines on a Windows form is not easier as there is no Line control. Still you can make a line by using a Panel control and setting its width or height to a few pixels. But if you want a real line, the secret is in understanding the correct set of library methods you need.

To draw a line with code you need to use GDI +(i.e. the .NET graphics library)

In VB:

Private Sub btnOk_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles MyBase.Paint 
    Dim g As Graphics = e.Graphics 
    Dim penLine As Pen = New Pen(Color.CornflowerBlue, 12) 
    g.DrawLine(penLine, 15, 0, 15, Me.Height) 
    penLine.Dispose() 
End Sub

In C#:

private void btnOk_Paint(object sender,System.Windows.Forms.PaintEventArgs e) 
{ 
  Graphics g = e.Graphics; 
  Pen penLine = new Pen(Color.CornflowerBlue, 12); 
  g.DrawLine(penLine, 15, 0, 15, this.Height); 
  penLine.Dispose(); 
}

Using SHA1 algorithm

noreply@blogger.com (Deepika) — Wed, 06 Oct 2010 09:00:00 +0000

The following code demonstrates using SHA1 algorithm to hash a password:

In VB:

Imports System.Security.Cryptography 
Public Function ComputeSHA1(ByVal textToHash As String) As String 
    Dim SHA1 As SHA1CryptoServiceProvider = New SHA1CryptoServiceProvider 
    Dim byteV As Byte() = System.Text.Encoding.UTF8.GetBytes(textToHash) 
    Dim byteH As Byte() = SHA1.ComputeHash(byteV) 
    SHA1.Clear() 
    Return Convert.ToBase64String(byteH) 
End Function

In C#:

using System.Security.Cryptography; 
public static String ComputeSHA1(string textToHash) 
{ 
    SHA1CryptoServiceProvider SHA1 = new SHA1CryptoServiceProvider(); 
    byte[] byteV = System.Text.Encoding.UTF8.GetBytes(textToHash); 
    byte[] byteH = SHA1.ComputeHash(byteV); 
    SHA1.Clear(); 
    return Convert.ToBase64String(byteH); 
}

DirectCast better than CType

noreply@blogger.com (Deepika) — Tue, 05 Oct 2010 08:37:00 +0000

DirectCast

performs better than using CType when casting from an object to a more specific type because it does not use runtime helper functions.

The below example shows way to cast from type Object to another type with VB:

Private Sub txt_Submit(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtUId.Submit 
    DirectCast(sender, TextBox).BackColor = Color.Yellow
End Sub

The DirectCast statement in this example casts the sender variable that is of type Object to a TextBox control in order to access the TextBox properties.

You can use DirectCast only when casting from a type to a related but more specific type, called a derived type.

For example, you can use DirectCast to cast from type Control to type TextBox because TextBox is derived from Control. You cannot use DirectCast to convert from an Integer to a String type because String is not derived from Integer. DirectCast can always be used to cast from type Object to any other type because all other types derive from type Object.

Create a Log File in ASP.Net

noreply@blogger.com (Deepika) — Thu, 23 Sep 2010 05:32:00 +0000

This code snippet demonstrates the use to create a log file to store either your error or informational thing like storing executed queries etc.

Here you need to pass the message when you call this function. You can also specify drive in which you want your log files to be stored or else by default it will store in your project's bin folder.

public void writeToLogFile(String s)
{
    String Filename;
    FileStream file;
    file = new FileStream("BackupLog" +DateTime.Today.ToString("yyyyMMdd") + ".txt", FileMode.Append );
    TextWriter tw = new StreamWriter(file);
    tw.WriteLine("Log:" + DateTime.Now + ":- " + s);
    tw.Close();
}

SQL Functions - Oracle

noreply@blogger.com (Deepika) — Thu, 05 Aug 2010 12:42:00 +0000

SQL Functions

Types of Functions

Single Row Functions -

1. Single row functions:
•2. Manipulate data items
•3. Accept arguments and return one value
•4. Act on each row returned
•5. Return one result per row
•6. May modify the data type
•7. Can be nested
•8. Accept arguments which can be a column or an expression

Case manipulation functions -

1. LOWER
(e.g. LOWER('SQL') -> sql)
2. UPPER
(e.g. UPPER('sql Course') -> SQL COURSE)
3. INITCAP
(e.g. INITCAP('sql') -> Sql)

Character manipulation functions -

1. CONCAT
(e.g.
CONCAT('Hello', 'World') -> HelloWorld)

2. SUBSTR
(e.g.
SUBSTR('HelloWorld',1,5) -> Hello)
3. LENGTH
(e.g.
LENGTH('HelloWorld') -> 10)
4. INSTR
(e.g.
INSTR('HelloWorld', 'W') -> 6)

5. LPAD | RPAD
(e.g.
LPAD(salary,10,'*') -> *****24000)
(e.g
RPAD(salary, 10, '*') -> 24000*****)
6. TRIM
(e.g.
TRIM('H' FROM 'HelloWorld') -> elloWorld)
7. REPLACE

Number Functions -

1. ROUND
(e.g
ROUND(45.926, 2) -> 45.93)
2. TRUNC
(e.g.
TRUNC(45.926, 2) -> 45.92)
3. MOD
(e.g.
MOD(1600, 300) -> 100)

Dates functions -

Conversion functions -

1. Implicit data type conversion
- From varchar2/char to number
-

From varchar2/char to date

- From date to varchar2

- From number to varchar2

2. explicit data type conversion

- From varchar2/char to number using TO_NUMBER
-

From varchar2/char to date using TO_DATE

- From date to varchar2 using TO_CHAR

- From number to varchar2 using TO_CHAR

Basics - Table in Oracle

noreply@blogger.com (Deepika) — Wed, 04 Aug 2010 11:35:00 +0000

Table - Basic unit of storage; composed of rows and columns

Naming Rules -

1. Table names and column names:
•2. Must begin with a letter
3•3. Must be 1–30 characters long
•4. Must contain only A–Z, a–z, 0–9, _, $, and #
•5. Must not duplicate the name of another object owned by the same user
•6. Must not be an Oracle server reserved word

CREATE TABLE statement -

•1. You must have:
–CREATE TABLE privilege
–A storage area

•2. You specify:
–Table name
–
Column name, column data type, and column size

Refer other user's table -

•Tables belonging to other users are not in the user’s schema.
•

You should use the owner’s name as a prefix to those tables.

For example :

CREATE TABLE dept (deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
Table created.

DESCRIBE dept

Name Null? type
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

Validations in Devexpress controls

noreply@blogger.com (Deepika) — Sun, 30 May 2010 14:49:00 +0000

This code shows usage of validations in Devexpress controls.

ASPX Page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ValidationsDev.aspx.cs" Inherits="ValidationsDev" %>

<%@ Register Assembly="DevExpress.Web.ASPxEditors.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.Web.ASPxEditors" TagPrefix="dx" %>
<%@ Register Assembly="DevExpress.Web.ASPxGridView.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.Web.ASPxGridView" TagPrefix="dxg" %>
   <%@ Register Assembly="DevExpress.Web.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" Namespace="DevExpress.Web.ASPxTabControl" TagPrefix="dxtc" %>

   <%@ Register Assembly="DevExpress.Web.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" Namespace="DevExpress.Web.ASPxPanel" TagPrefix="dxp" %>
<%@ Register Assembly="DevExpress.Web.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" Namespace="DevExpress.Web.ASPxRoundPanel" TagPrefix="dxrp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title>Validations</title>

   <script src="Common.js" type="text/javascript"></script>

</head>
<body>
   <form id="form1" runat="server">
   <div>
   <%--Textbox validation example--%>
   <dx:ASPxLabel ID="lblSample" Text="Sample" runat="server" />
   <dx:ASPxTextBox ID="txtSample" ClientInstanceName="txtValidate" Width="170px" EnableClientSideAPI="true"
   runat="server" OnValidation="txtSample_Validation">
   <ValidationSettings SetFocusOnError="True" ErrorText="Name must be at least two characters long">
   <ErrorImage Height="16px" Width="16px" AlternateText="Error" />
   <RequiredField IsRequired="True" ErrorText="Name is required" />
   <ErrorFrameStyle ForeColor="Red">
   <Paddings Padding="3px" PaddingLeft="4px" />
   <Border BorderColor="#FD4D3E" BorderStyle="Solid" BorderWidth="1px" />
   <ErrorTextPaddings PaddingRight="3px" />
   </ErrorFrameStyle>
   </ValidationSettings>
   <ClientSideEvents Validation="OnNameValidation" />
   </dx:ASPxTextBox>
   <dx:ASPxLabel ID="lblAge" Text="Age" runat="server" />
   <dx:ASPxTextBox ID="txtAge" ClientInstanceName="txtAge" EnableClientSideAPI="true"
   runat="server" Width="170px" OnValidation="txtAge_Validation">
   <ValidationSettings SetFocusOnError="True" ErrorText="Age must be greater than or equal to 18">
   <ErrorImage Height="16px" Width="16px" AlternateText="Error" />
   <RequiredField IsRequired="True" ErrorText="Age is required" />
   <ErrorFrameStyle ForeColor="Red">
   <Paddings Padding="3px" PaddingLeft="4px" />
   <Border BorderColor="#FD4D3E" BorderStyle="Solid" BorderWidth="1px" />
   <ErrorTextPaddings PaddingRight="3px" />
   </ErrorFrameStyle>
   </ValidationSettings>
   <ClientSideEvents Validation="OnAgeValidation" />
   </dx:ASPxTextBox>
   <%--Combo box example--%>
   <dx:ASPxLabel ID="lblCity" Text="City" runat="server" />
   <dx:ASPxComboBox ID="cmbxCity" runat="server" ValueType="System.String">
   <Items>
   <dx:ListEditItem Text="1" Value="1" />
   <dx:ListEditItem Text="2" Value="2" />
   </Items>
   <ValidationSettings SetFocusOnError="true">
   <RequiredField ErrorText="City reuired" IsRequired="true" />
   <ErrorFrameStyle ForeColor="Red">
   <Paddings Padding="3px" PaddingLeft="4px" />
   <Border BorderColor="#FD4D3E" BorderStyle="Solid" BorderWidth="1px" />
   <ErrorTextPaddings PaddingRight="3px" />
   </ErrorFrameStyle>
   </ValidationSettings>
   </dx:ASPxComboBox>
   <br />
   <%--Get multiple values in combobox--%>
   <dx:ASPxComboBox ID="cmbxMultivalue" runat="server" EnableCallbackMode="True" EnableIncrementalFiltering="True"
   CallbackPageSize="10" ValueField="intGlCode" ValueType="System.String" DataSourceID="SqlDataSource1">
   <Columns>
   <dx:ListBoxColumn FieldName="varAreaName" />
   <dx:ListBoxColumn FieldName="varAreaCode" />
   <dx:ListBoxColumn FieldName="varAreaHead" />
   <dx:ListBoxColumn FieldName="varEmail" />
   </Columns>
   </dx:ASPxComboBox>
   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BASF_1111ConnectionString %>"
   SelectCommand="SELECT * FROM [Area_Mst]"></asp:SqlDataSource>
   <%--Get selected values in listbox --%>
   <dx:ASPxListBox ID="lstbxItems" runat="server" SelectionMode="CheckColumn" DataSourceID="SqlDataSource1">
   <Columns>
   <dx:ListBoxColumn FieldName="varAreaName" />
   <dx:ListBoxColumn FieldName="varAreaHead" />
   <dx:ListBoxColumn FieldName="varAreaCode" />
   </Columns>
   </dx:ASPxListBox>
   <%--Gridview on dropdown click--%>
   <dx:ASPxDropDownEdit ID="drpEditTest" runat="server">
   <DropDownWindowTemplate>
   <dxg:ASPxGridView ID="ZoneGrid" DataSourceID="SqlDataSource1" runat="server"
   KeyFieldName="ID" Width="100%">
   <columns>
   <dxg:GridViewDataColumn FieldName="intGlCode" VisibleIndex="0">
   </dxg:GridViewDataColumn>
   <dxg:GridViewDataColumn FieldName="varAreaCode" VisibleIndex="1">
   </dxg:GridViewDataColumn>
   <dxg:GridViewDataColumn FieldName="varAreaName" VisibleIndex="2">
   </dxg:GridViewDataColumn>
   <dxg:GridViewDataTextColumn FieldName="varAreaHead" VisibleIndex="3">
   </dxg:GridViewDataTextColumn>
   <dxg:GridViewDataColumn FieldName="varEmail" VisibleIndex="4">
   </dxg:GridViewDataColumn>
   </columns>
   </dxg:ASPxGridView>
   </DropDownWindowTemplate>
   </dx:ASPxDropDownEdit>
   <dxg:ASPxGridView ID="ZoneGrid" DataSourceID="SqlDataSource1" runat="server"
   KeyFieldName="ID" Width="100%">
   <columns>
   <dxg:GridViewDataColumn FieldName="intGlCode" VisibleIndex="0">
   </dxg:GridViewDataColumn>
   <dxg:GridViewDataColumn FieldName="varAreaCode" VisibleIndex="1">
   </dxg:GridViewDataColumn>
   <dxg:GridViewDataColumn FieldName="varAreaName" VisibleIndex="2">
   </dxg:GridViewDataColumn>
   <dxg:GridViewDataTextColumn FieldName="varAreaHead" VisibleIndex="3">
   </dxg:GridViewDataTextColumn>
   <dxg:GridViewDataProgressBarColumn FieldName="Complete" VisibleIndex="4" PropertiesProgressBar-ShowPosition="true">
   </dxg:GridViewDataProgressBarColumn>
   <dxg:GridViewDataColumn FieldName="varEmail" VisibleIndex="5">
   </dxg:GridViewDataColumn>
   </columns>
   </dxg:ASPxGridView>
   <dxrp:ASPxRoundPanel ID="rndpnlSample" runat="server" ShowHeader="False">
   <PanelCollection>
   <dxp:PanelContent>
   <dxtc:ASPxTabControl runat="server" ID="tbSample" DataSourceID="XmlDataSource1">
   </dxtc:ASPxTabControl>
   <asp:XmlDataSource ID="XmlDataSource1" runat="server" DataFile="~/XMLFile.xml">
   </asp:XmlDataSource>
   </dxp:PanelContent>
   </PanelCollection>
   </dxrp:ASPxRoundP nel>
   </div>
   </form>
</body>
</html>

Code behind page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ValidationsDev : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {

   }
   protected void txtSample_Validation(object sender, DevExpress.Web.ASPxEditors.ValidationEventArgs e)
   {
   if ((e.Value as string).Length < 2)
   e.IsValid = false;
   }
   protected void txtAge_Validation(object sender, DevExpress.Web.ASPxEditors.ValidationEventArgs e)
   {
   string strAge = ((string)e.Value).TrimStart('0');
   if (strAge.Length == 0)
   return;
   UInt32 age = 0;
   if (!UInt32.TryParse(strAge, out age) || age < 18)
   e.IsValid = false;
   }
}

Insert/Add, Update, Edit, Delete in ASPXgridview - Devexpress

noreply@blogger.com (Deepika) — Mon, 10 May 2010 17:30:00 +0000

This code demonstrates how to Add/Insert, Update, delete, edit in Devexpress aspxgridview. Sorting and changing position of columns is by default enabled. Only to disable it you need to specify.

Note : You can download Devexpress controls from www.devexpress.com

For validation on controls visit this link

I have tried this with versions 9.2.3 and 9.3.2. For example purpose I have used connection strings and queries in same file but for you, you need to retrieve it from web.config file and also use stored procedures.

Aspx Page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridviewWithAddUpdateEditDelete.aspx.cs" Inherits="GridviewWithDelete" %>

<%@ Register Assembly="DevExpress.Web.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.Web.ASPxPopupControl" TagPrefix="dx" %>
<%@ Register Assembly="DevExpress.Web.ASPxGridView.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.Web.ASPxGridView" TagPrefix="dx" %>
<%@ Register Assembly="DevExpress.Web.ASPxEditors.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.Web.ASPxEditors" TagPrefix="dxe" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title>Gridview With Delete</title>
</head>
<body>
   <form id="form1" runat="server">
   <div>
   <dxe:ASPxLabel ID="lblMessage" runat="server" Text="" ForeColor="Red" Font-Bold="true" Visible="false" />
   <dxe:ASPxButton ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click">
   </dxe:ASPxButton>
   <dx:ASPxGridView ID="devg" runat="server" ClientInstanceName="devg"
   KeyFieldName="intGLCode" AutoGenerateColumns="False"
   Width="554px" onafterperformcallback="devg_AfterPerformCallback"
   onrowinserting="devg_RowInserting" onrowupdating="devg_RowUpdating">
   <Columns>
   <dx:GridViewCommandColumn Caption="New" VisibleIndex="1">
   <NewButton Visible="true" />
   <EditButton Visible="true" />
   </dx:GridViewCommandColumn>
   <dx:GridViewCommandColumn Caption="Delete" ShowSelectCheckbox="True" VisibleIndex="0">
   <HeaderTemplate>
   <%--To select/unselect all rows in gridview--%>
   Delete<input type="checkbox" id="CheckBoxControl" name="SelectAllCheckbox"
   onclick="devg.SelectAllRowsOnPage(this.checked);" runat="server" />
   </HeaderTemplate>
   </dx:GridViewCommandColumn>

   <dx:GridViewDataTextColumn Caption="ID" FieldName="ID" Name="ID" Visible="false">
   </dx:GridViewDataTextColumn>
   <dx:GridViewDataTextColumn Caption="Name" FieldName="varName" Name="varName" Settings-AllowSort="False" VisibleIndex="2">
   </dx:GridViewDataTextColumn>
   <dx:GridViewDataTextColumn Caption="Contact No" FieldName="varContactNo" Name="varContactNo" Settings-AllowSort="False" VisibleIndex="3">
   </dx:GridViewDataTextColumn>
   <dx:GridViewDataTextColumn Caption="Email" FieldName="varEmail" Name="varEmail" VisibleIndex="4">
   </dx:GridViewDataTextColumn>
   <dx:GridViewDataTextColumn Caption="Address" FieldName="varAddress" Name="varAddress" VisibleIndex="5">
   </dx:GridViewDataTextColumn>
   </Columns>
   <Settings ShowStatusBar="Visible" />
   <SettingsEditing Mode="PopupEditForm" />
   </dx:ASPxGridView>
   <dx:ASPxPopupControl ID="popupSample" runat="server" ShowCloseButton="true" ShowHeader="true"
   PopupHorizontalAlign="WindowCenter" PopupVerticalAlign="WindowCenter">
   </dx:ASPxPopupControl>
   </div>
   </form>
</body>
</html>

Code behind page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using DevExpress.Web.Data;
using DevExpress.Web.ASPxGridView;

public partial class GridviewWithDelete : Page
{
   protected void Page_Load(object sender, EventArgs e)
   {

   }

   protected void Page_Init(object sender, EventArgs e)
   {
   BindGrid();
   }

   private void BindGrid()
   {
   SqlConnection conn = new SqlConnection("Connection string here");
   try
   {
   conn.Open();
   SqlCommand cmd;

   cmd = new SqlCommand("select ID,varName,varContactNo,varEmail,varAddress from ContactDetails");
   cmd.Connection = conn;
   DataSet dsClaimCat = new DataSet();

   SqlDataAdapter dapt = new SqlDataAdapter(cmd);
   dapt.Fill(dsClaimCat);
   devg.DataSource = dsClaimCat;
   devg.DataBind();
   conn.Close();
   }
   catch
   {
   Response.Write("Error");
   }
   }
   protected void btnDelete_Click(object sender, EventArgs e)
   {
   string strMultiIds;
   List<object> selectedRowList;

   strMultiIds = string.Empty;
   selectedRowList = (devg.GetSelectedFieldValues(new string[] { "ID" }));
   if (selectedRowList.Count == 0)
   {
   System.Windows.Forms.MessageBox.Show("Please Select Atleast one Record to Delete");
   return;
   }

   for (int i = 0; i < selectedRowList.Count; i++)
   { strMultiIds += selectedRowList[i].ToString() + ","; }

   strMultiIds.TrimEnd(',');
   try
   {
   delete(strMultiIds.TrimEnd(','));
   }
   catch(Exception ex)
   {
   lblMessage.Visible = true;
   lblMessage.Text = ex.Message;
   }
   BindGrid();
   }

   private void delete(string p)
   {
   if (p.Equals(""))
   return;
   try
   {
   deleteRows(p, "ContactDetails");
   }
   catch(Exception ex)
   {
   lblMessage.Visible = true;
   lblMessage.Text = ex.Message;
   }
   }

   private void deleteRows(string selectedid, string tablename)
   {
   StringBuilder sqlQuery = new StringBuilder();
   selectedid = selectedid.TrimEnd(',') + ",0";
   StringBuilder whereClause = new StringBuilder();
   whereClause.Append(" WHERE ID IN (" + selectedid + ") ");
   sqlQuery.Append(" DELETE FROM ");
   sqlQuery.Append(tablename + whereClause.ToString());
   try
   {
   SqlConnection conn = new SqlConnection("Connection string here");
   conn.Open();
   SqlCommand cmd;

   cmd = new SqlCommand(sqlQuery.ToString());
   cmd.Connection = conn;
   DataSet dsClaimCat = new DataSet();

   SqlDataAdapter dapt = new SqlDataAdapter(cmd);
   dapt.Fill(dsClaimCat);
   devg.DataSource = dsClaimCat;
   devg.DataBind();
   conn.Close();
   lblMessage.Visible = true;
   lblMessage.Text = "Record(s) Deleted";
   }
   catch (Exception ex)
   {
   lblMessage.Visible = true;
   lblMessage.Text = ex.Message;
   }
   }
   protected void devg_AfterPerformCallback(object sender, ASPxGridViewAfterPerformCallbackEventArgs e)
   {
   BindGrid();
   }
   protected void devg_RowUpdating(object sender, ASPxDataUpdatingEventArgs e)
   {
   StringBuilder sqlQuery = new StringBuilder();
   SqlConnection conn = new SqlConnection("Connection string here");
   sqlQuery.Append("UPDATE ContactDetails SET ");
   sqlQuery.Append("varName = '" + e.NewValues["varName"].ToString() + "',");
   sqlQuery.Append("varContactNo = '" + e.NewValues["varContactNo"].ToString() + "',");
   sqlQuery.Append("varEmail = '" + e.NewValues["varEmail"].ToString() + "',");
   sqlQuery.Append("varAddress = '" + e.NewValues["varAddress"].ToString() + "'");
   sqlQuery.Append(" WHERE ID= "+ e.Keys["ID"]+"");
   conn.Open();
   SqlCommand cmd;

   cmd = new SqlCommand(sqlQuery.ToString());
   cmd.Connection = conn;
   DataSet dsClaimCat = new DataSet();

   SqlDataAdapter dapt = new SqlDataAdapter(cmd);
   int i = cmd.ExecuteNonQuery();
   if (i > 0)
   {
   e.Cancel = true;
   devg.CancelEdit();
   }
   lblMessage.Visible = true;
   lblMessage.Text = "Record(s) Updated";
   BindGrid();
   conn.Close();
   }
   protected void devg_RowInserting(object sender, ASPxDataInsertingEventArgs e)
   {
   StringBuilder sqlQuery = new StringBuilder();
   SqlConnection conn = new SqlConnection("connection string here");
   sqlQuery.Append("INSERT INTO ContactDetails(varName,varContactNo,varEmail,varAddress) VALUES ( ");
   sqlQuery.Append("'" + e.NewValues["varName"].ToString() + "',");
   sqlQuery.Append("'" + e.NewValues["varContactNo"].ToString() + "',");
   sqlQuery.Append("'" + e.NewValues["varEmail"].ToString() + "',");
   sqlQuery.Append("'" + e.NewValues["varAddress"].ToString() + "')");
   conn.Open();
   SqlCommand cmd;

   cmd = new SqlCommand(sqlQuery.ToString());
   cmd.Connection = conn;
   DataSet dsClaimCat = new DataSet();

   SqlDataAdapter dapt = new SqlDataAdapter(cmd);
   int i = cmd.ExecuteNonQuery();
   if (i > 0)
   {
   e.Cancel = true;
   devg.CancelEdit();
   }
   lblMessage.Visible = true;
   lblMessage.Text = "Record(s) Saved";
   BindGrid();
   conn.Close();
   }

}

Create Devexpress Bar chart - Webchartcontrol - Xtracharts

noreply@blogger.com (Deepika) — Tue, 04 May 2010 15:46:00 +0000

This code shows how its easy to use a Bar chart in your Web applications.

To start with first of all download and install Devexpress controls from www.devexpress.com

Here I am going to make a bar chart with the use of WebchartControl (Xtracharts)

Note : I have used version 9.2.3

Code .cs Page

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using DevExpress.XtraCharts;
using System.Drawing;

public partial class _Default : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
   BindChart();
   }

   private void BindChart()
   {
   SqlConnection conn = new SqlConnection("Connection string here");
   try
   {
   conn.Open();
   SqlCommand cmd;
   cmd = new SqlCommand("select * from Transactions"); // my sample query
   cmd.Connection = conn;
   DataSet dsClaimCat = new DataSet();

   SqlDataAdapter dapt = new SqlDataAdapter(cmd);
   dapt.Fill(dsClaimCat);
   WebChartControl1.DataSource = dsClaimCat.Tables[0];
   //Values for X-axis
   WebChartControl1.SeriesDataMember = dsClaimCat.Tables[0].Columns["percentage"].ToString();
   //Values for Y-axis
   WebChartControl1.SeriesTemplate.ArgumentDataMember = dsClaimCat.Tables[0].Columns["paymentDays"].ToString();
   WebChartControl1.SeriesTemplate.ValueDataMembers.AddRange(new string[] { dsClaimCat.Tables[0].Columns["percentage"].ToString() });
   string paletteName = "MyPalette";
   PaletteEntry entry1 = new PaletteEntry(Color.Green, Color.LightGreen);
   Palette palette = new Palette(paletteName, new PaletteEntry[] { entry1 });
   WebChartControl1.PaletteRepository.Add(paletteName, palette);
   WebChartControl1.PaletteName = paletteName;
   SideBySideBarSeriesView v = new SideBySideBarSeriesView();
   v.BarWidth = 0.4;

   WebChartControl1.SeriesTemplate.View = v;
   WebChartControl1.DataBind();

   conn.Close();
   }
   catch
   {
   Response.Write("Error");
   }
   }
}

Design Page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Register Assembly="DevExpress.XtraCharts.v9.2.Web, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.XtraCharts.Web" TagPrefix="dxchartsui" %>
<%@ Register Assembly="DevExpress.XtraCharts.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.XtraCharts.Web" TagPrefix="cci" %>
<%@ Register Assembly="DevExpress.XtraCharts.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.XtraCharts" TagPrefix="cc1" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title></title>
</head>
<body>
   <form id="form1" runat="server">
   <div>
   <dxchartsui:WebChartControl ID="WebChartControl1" runat="server" Height="200px" Width="300px"
   PaletteName="The Trees">
   <SeriesTemplate LabelTypeName="SideBySideBarSeriesLabel" PointOptionsTypeName="PointOptions"
   SeriesViewTypeName="SideBySideBarSeriesView">

   <View HiddenSerializableString="to be serialized">
   </View>
   <Label HiddenSerializableString="to be serialized" LineVisible="True">
   <FillStyle FillOptionsTypeName="SolidFillOptions">
   <Options HiddenSerializableString="to be serialized"></Options>
   </FillStyle>
   </Label>
   <PointOptions HiddenSerializableString="to be serialized">
   </PointOptions>
   <LegendPointOptions HiddenSerializableString="to be serialized">
   </LegendPointOptions>
   </SeriesTemplate>
   <FillStyle FillOptionsTypeName="SolidFillOptions">
   <Options HiddenSerializableString="to be serialized"></Options>
   </FillStyle>
   </dxchartsui:WebChartControl>
   </div>
   </form>
</body>
</html>

Pivoting INSERT - Oracle

noreply@blogger.com (Deepika) — Mon, 26 Apr 2010 16:10:00 +0000

Pivoting is an operation in which you need to build a transformation such that each record from any input stream, such as, a nonrelational database table, must be converted into multiple records for a more relational database table environment.

•Suppose you receive a set of sales records from a nonrelational database table, SALES_SOURCE_DATA in the following format:

EMPLOYEE_ID,WEEK_ID,SALES_MON,SALES_TUE,SALES_WED, SALES_THUR,SALES_FRI

•You would want to store these records in the SALES_INFO table in a more typical relational format:

EMPLOYEE_ID, WEEK, SALES

•Using a pivoting INSERT, convert the set of sales records from the nonrelational database table to relational format.

INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO sales_info VALUES (employee_id,week_id,sales_TUE) INTO sales_info VALUES (employee_id,week_id,sales_WED) INTO sales_info VALUES (employee_id,week_id,sales_THUR) INTO sales_info VALUES (employee_id,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data;
5 rows created.

In the example in the slide, the sales data is received from the nonrelational database table SALES_SOURCE_DATA, which is the details of the sales performed by a sales representative on each day of a week, for a week with a particular week ID.

DESC SALES_SOURCE_DATA

Name Null? Type
EMPLOYEE_ID NUMBER(6)
WEEK_ID NUMBER(2)
SALES_MON NUMBER(8,2)
SALES_TUE NUMBER(8,2)
SALES_WED NUMBER(8,2)
SALES_THUR NUMBER(8,2)
SALES_FRI NUMBER(8,2)

SQL> SELECT * FROM SALES_SOURCE_DATA;

EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI

176 6 2000 3000 4000 5000 6000

SQL> DESC SALES_INFO

Name Null? Type
EMPLOYEE_ID NUMBER(6)
WEEK NUMBER(2)
SALES NUMBER(8,2)

SQL>
SELECT * FROM sales_info;

EMPLOYEE_ID WEEK SALES
176 6 2000
176 6 3000
176 6 4000
176 6 5000
176 6 6000

Observe in the preceding example that using a pivoting INSERT, one row from the SALES_SOURCE_DATA table is converted into five records for the relational table, SALES_INFO.

SQL * Plus File commands - Oracle

noreply@blogger.com (Deepika) — Mon, 19 Apr 2010 16:44:00 +0000

SQL * Plus File commands - Oracle

1. SAVE filename
2. GET filename
3. START filename
4. @ filename
5. EDIT filename
6. SPOOL filename
7. EXIT

SQL> L
1. Select lastname, manager_id, dept_no
2* from emp;

SQL > SAVE my_query
File created my_query

SQL > START my_query
lastname manager_id dept_id
-----------------------------------------
King 1
Kochaar 80 1
..........
10 rows updated

Using LIST, n, APPEND commands in SQL * plus - Oracle

noreply@blogger.com (Deepika) — Sun, 18 Apr 2010 16:15:00 +0000

In earlier post http://angeldeeps.blogspot.com/2010/04/sql-plus-oracle-editing-commands.html I discussed about list of editing commands used in SQL * plus - Oracle.

Here, I am going to discuss few of the commands from that list.

//Here I have created a table called emp having empno and empname fields.

SQL> create table emp (empno number, empname varchar(20));

Table created.

//Inserting few records into emp table
SQL> insert into emp values(1,'Deepika');

1 row created.

SQL> insert into emp values(2, 'Aarti');

1 row created.

SQL> insert into emp values(3, 'Rupal');

1 row created.

SQL> insert into emp values(4, 'Ratna');

1 row created.

SQL> insert into emp values(5, 'Sonal');

1 row created.

//List command will show your last command executed
SQL> list
  1* insert into emp values(5, 'Sonal')

//Retrieving values to view

SQL> select * from emp;

   EMPNO EMPNAME
---------- --------------------
   1 Deepika
   2 Aarti
   3 Rupal
   4 Ratna
   5 Sonal

//Retrieving particular field records
SQL> select empno
  2 , empname from emp;

   EMPNO EMPNAME
---------- --------------------
   1 Deepika
   2 Aarti
   3 Rupal
   4 Ratna
   5 Sonal

//n - here n is 1 means it will show me statement at that line no
SQL> 1
  1* select empno

//Added jobcode field to emp table

SQL> alter table emp add (jobcode varchar(10));

Table altered.

SQL> select * from emp;

   EMPNO EMPNAME JOBCODE
---------- -------------------- ----------
   1 Deepika
   2 Aarti
   3 Rupal
   4 Ratna
   5 Sonal

//Updating jobocde
SQL> update emp set jobcode = 'A001' where empno =1;

1 row updated.

SQL> update emp set jobcode = 'B001' where empno = 2;

1 row updated.

SQL> update emp set jobcode = 'C001' where empno = 3;

1 row updated.

SQL> update emp set jobcode = 'D001' where empno = 4;

1 row updated.

SQL> update emp set jobcode = 'E001' where empno = 5;

1 row updated.

//retrieving particular field records

SQL> select empname
  2 from emp;

EMPNAME
--------------------
Deepika
Aarti
Rupal
Ratna
Sonal

// A - it will append jobcode to my last statement executed
SQL> A, jobcode;
  2* from emp, jobcode

//L will show last statement
SQL> L
  1 select empname
  2* from emp, jobcode

SQL> select * from emp;

   EMPNO EMPNAME JOBCODE
---------- -------------------- ----------
   1 Deepika A001
   2 Aarti B001
   3 Rupal C001
   4 Ratna D001
   5 Sonal E001

SQL> L
  1* select * from emp

//C - will change. Here I am changing my last statement's emp table with dept table. So my query will now be select * from dept;
SQL> c/emp/dept
  1* select * from dept

SQL> L
  1* select * from dept

SQL * Plus - oracle Editing commands

noreply@blogger.com (Deepika) — Sun, 18 Apr 2010 15:40:00 +0000

SQL * Plus editing commands -

1. A[PPEND] text
2. C[HANGE] / old / new
3. C[HANGE] / text
4. CL[EAR] BUFF[ER]
5. DEL
6. DEL n
7. DEL m n
8. I[NPUT]
9. I[NPUT] text
10. L[IST]
11. L[IST] n
12. L[IST] m n
13. R[UN]
14. n
15. n text
16. 0 text

Note :- Will be discussed in detail in future posts..