Wednesday, October 12, 2005

How to call user controls value (like Textbox, button, checkbox etc.) in aspx page.

How to call user controls value (like Textbox, button, checkbox etc.) in aspx page.

When you use usercontrols in your form it’s very easy to get that user control value in your form.

I am taking one example you are creating one Usercontrol that have two textbox with User Id and Password.
And this usercontrols you can use in your form either Load that control run time or Drag that control in your form design time.

1) Create one usercontrol name: Test.ascx
That have one panel on design time name: Panel1
Call this method (AddControls) in Page_Load() event in your
UserContorl Page (Test.ascx)


public void AddControls()
{
Label lblUserName;
Label lblPassword;
TextBox txtUserName;
TextBox txtPassword;

lblUserName = new Label();
lblUserName.Text ="User Name : ";
txtUserName = new TextBox();
txtUserName.ID="txtUserId1";
lblPassword = new Label();
lblPassword.Text="Password : ";
txtPassword = new TextBox();
txtPassword.ID="txtPassword1";
txtPassword.TextMode =
System.Web.UI.WebControls.
TextBoxMode.Password;

Panel1.Controls.Add(lblUserName);
Panel1.Controls.Add(txtUserName);
Panel1.Controls.Add(lblPassword);
Panel1.Controls.Add(txtPassword);
}


2) Create one form name: TestUserControls.aspx now
There are two ways to use this usercontrol in your form(TestUserControls.aspx)

a) Drag this usercontrol in your form

<%@ Register TagPrefix="uc1" TagName="Test" Src="Test.ascx" %>


OR

b) Load this user control in Page_Load() event in your form
(TestUserControls.aspx) where you are using this control.

Load user control at Run time

private void Page_Load(object sender, System.EventArgs e)
{
private UserControl LevelControl;
LevelControl = (UserControl) LoadControl("Test.ascx");
LevelControl.ID =" MyUserControl";
LevelControl.EnableViewState = true;
Type LevelControlType = LevelControl.GetType();
Panel1.Controls.Add(LevelControl);
}

3) In your Form (TestUserControls.aspx) put one button name Button1
And print that user control textboxes value in one label control

This is the way to get UserContol value in your page.

private void Button1_Click(object sender, System.EventArgs e)
{
string userId;
string password;
userId = ((TextBox)Page.FindControl("MyUserControl")
.FindControl("txtUserId1")). Text.ToString();

password = ((TextBox)Page.FindControl("MyUserControl")
.FindControl("txtPassword1")).Text.ToString();

Label1.Text= "Welcome : " + userId + " Your Password id : " + password;
}


The Output will be :

"Welcome : ritesh your Password : kesharwani"


Ritesh Kumar Kesharwani
A D I T I , B A N G A L O R E
Software Professional
Cell : 91-9845657636
Page: http://www.riteshk.blogspot.com/

Tuesday, October 11, 2005

How to fire events in runtime generated controls in C#. NET

How to fire events in runtime generated controls in C#. NET

 

I am taking an example of Link button controls

 

Generate runtime Link button and after clicking each and every button some

Alert message should get fired. This can be done in simple 2 steps.

 

Client Side Events

 

1) Make one method that will generate runtime Link button

 

public void AddButton()

{

LinkButton lbnew;

      int i=1;

      while (i<5)

      {

        lbnew=new LinkButton();             

        lbnew.Text="Link" + i + "<br>" ;

        lbnew.ID="Lnk" + i;

        Panel.Controls.Add(lbnew);

        i++;

      }

}

 

2) Call AddButton() method in OnLoad Event

 

private void Page_Load(object sender, System.EventArgs e)

{

  AddButton();

  int i;

  for(i=1;i<5;i++)

  {

     ((LinkButton)Page.FindControl("Lnk"+i)).Attributes

.Add("Onclick","alert('you have clicked link - " + i + "')");

  }

 

Server Side Events

 

1) Make one method that will generate runtime Link button

 

public void AddButton()

{

LinkButton lbnew;

      int i=1;

      while (i<5)

      {

        lbnew=new LinkButton();             

        lbnew.Text="Link" + i + "<br>" ;

        lbnew.ID="Lnk" + i;

        lbnew.CommandArgument ="Lnk" +i;

      lbnew.Click += new

System.EventHandler(this.LinkButton_Click);     

Panel.Controls.Add(lbnew);

      i++;

      }

}

 

2) This events handler will call the LivkButton_click()

 

private void LinkButton_Click(Object sender,System.EventArgs e)

{

   Response.Write("you have clicked link button”);

}

 

Summary

 

Client side

When you generate Runtime controls you must give the Id of that controls

And need to be fire with Attribute properties of that control.

 

Server side

When you what to fire event in server side you need to add Event Handler on that link button control

 

 

 

 

 

 

 


Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

Thursday, October 06, 2005

How to prevent Caching in ASP.NET

There are two ways to prevent cache in ASP.NET

1) Response.Cache.SetCacheability(HttpCacheability.NoCache)

And

2) Response.Cache.SetAllowResponseInBrowserHistory(False)

This code you can right in code behind on_Load events this will not allow user to cache the page or records.




Ritesh Kumar Kesharwani

A D I T I , B A N G A L O R E
Software Professional
Cell : 91-9845657636



Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.

Wednesday, October 05, 2005

Basic CURSOR example in SQL server

Basic CURSOR example in SQL server

 

Basic Syntax of Cursor

 

--Declare cursor in declare section

DECLARE CURSOR

--Open cursor for operation    

OPEN CURSOR

FETCH CURSOR

--Fetch record till end

WHILE  @@FETCH_STATUS =0

BEGIN

                                    USE CURSOR

END

--Close cursor

CLOSE CURSOR

--Remove from the memory

DEALLOCATE CURSOR

 

Different fetch statement

 

-- Fetch the last row in the cursor.

FETCH LAST FROM authors_cursor
-- Fetch the row immediately prior to the current row in the cursor.
FETCH PRIOR FROM authors_cursor
-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM authors_cursor
-- Fetch the row that is three rows after the current row.
FETCH RELATIVE 3 FROM authors_cursor
-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor

Use Cursor to print database table records

 

IF EXISTS (SELECT name FROM sysobjects

      WHERE name = 'ReadData' AND type = 'P')

   DROP PROCEDURE ReadData

GO

 

CREATE PROCEDURE ReadData

AS

BEGIN

            DECLARE @STRINGNAME NVARCHAR (3999)

           

            DECLARE CUR_NAME CURSOR FOR

SELECT USERNAME FROM TBLUSERINFO

 

            OPEN CUR_NAME

                        FETCH NEXT FROM CUR_NAME INTO

@STRINGNAME

                        WHILE @@FETCH_STATUS =0      

           

            PRINT @STRINGNAME

                        BEGIN

                                    FETCH NEXT FROM CUR_NAME INTO

@STRINGNAME

            END

            CLOSE CUR_NAME

            DEALLOCATE CUR_NAME

END   

GO

 

Use Cursor to return Set of Data/Records

 

IF EXISTS (SELECT name FROM sysobjects

      WHERE name = 'ReturnDataSet' AND type = 'P')

   DROP PROCEDURE ReturnDataSet

GO

 

CREATE PROCEDURE ReturnDataSet

(

    @CUR_DATASET            CURSOR VARYING OUTPUT

)

AS

BEGIN

            SET @CUR_DATASET = CURSOR FOR

                        SELECT USERNAME FROM TBLUSERINFO

 

END   

GO

 

Check cursor status

 

IF EXISTS (SELECT name FROM sysobjects

      WHERE name = 'ReadCursor' AND type = 'P')

   DROP PROCEDURE ReadCursor

GO

 

CREATE PROCEDURE ReadCursor

(

@CUR_STATUS            NVARCHAR (200) OUTPUT,

@CUR_DATASET            CURSOR VARYING OUTPUT

)

AS

BEGIN

DECLARE @RETURN_CUR  CURSOR

EXEC ReturnDataSet @RETURN_CUR OUTPUT

           

IF CURSOR_STATUS ('VARIABLE','@RETURN_CUR') <= 0

            BEGIN

                        SET @CUR_STATUS =

'NO DATA AVAILABLE IN CURSOR.'           

            END

ELSE

            BEGIN

                        SET @CUR_DATASET = @RETURN_CUR

            END

 

END   

GO

 

 



Ritesh Kumar Kesharwani

A D I T I , B A N G A L O R E
Software Professional
Cell  : 91-9845657636


Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.

Friday, September 30, 2005

How to open new window using �HyperLinkColumn� in datagrid.

How to open new window using “HyperLinkColumn” in datagrid.

Client Side

Opening window from Hyperlink column problem comes “In Parent Window text [Object] comes and child window open properly” When you try to open window like this

Example:

DataNavigateUrlFormatString ="javascript:varedit window.open('LoyaltyRewardDetail.aspx?RewardID={0}',

'childwindow','width=600,height=450,scrollbars=yes, resizable=yes')"

Solution

Insead of “varedit” type “void” keyword then parent window will remain same.

Example;

<asp:BoundColumn

DataField="InvoiceId"

HeaderText="Invoice Id" >

</asp:BoundColumn>

<asp:HyperLinkColumn

DataTextField="programname"

DataNavigateUrlField="RewardID"

HeaderText="Program Name"

DataNavigateUrlFormatString =

"javascript:void window.open('LoyaltyRewardDetail.aspx?RewardID={0}',

'childwindow','width=600,height=450,scrollbars=yes, resizable=yes')" >

</asp:HyperLinkColumn>

Server Side

1) Add OnItemDataBound="PointHistoryBinding" into datagrid.

2) Bound the column as it in client side.

2) write code behind C#.net (in this way you can add multiple parameter in queryString

protected void LoyalityHistoryBinding( object sender, DataGridItemEventArgs e )

{

if( e.Item.ItemType == ListItemType.Header )

{

return;

}

string ProgID = e.Item.Cells[5].Text;

string RetailerId = e.Item.Cells[6].Text;

string ProgName = e.Item.Cells[3].Text;

string VersionId = e.Item.Cells[4].Text;

string ReachDate = e.Item.Cells[1].Text;

string passPageLink = "<a href='#'

onclick=\"window.open('LoyaltyDetail.aspx?ProgId={0}&ReachDate={1}&ProgName={2}&VersionId={3}&RetailerId={4}','childwindow','width=600,height=450,

scrollbars=yes, resizable=yes');\">{2}</a>";

string passType = Convert.ToString( DataBinder.Eval(e.Item.DataItem, "ProgramName") ).Trim();

e.Item.Cells[3].Text = String.Format(passPageLink,ProgID,

ReachDate,ProgName,VersionId,RetailerId,passType);

}

Another way to use Hyperlink column in datagrid with more then one parameter QueryString

("How do I specify more than one parameter for my HyperlinkColumn?")

<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False">

<Columns>

<asp:TemplateColumn HeaderText="Sample Column">

<ItemTemplate>

<asp:Hyperlink runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "ProductName").ToString()%>' NavigateUrl='<%# "page1.aspx?id=" + DataBinder.Eval(Container.DataItem,"ProductId").ToString() + "&Name=" + DataBinder.Eval(Container.DataItem,"ProductName").ToString()%>' ID="Hyperlink1" NAME="Hyperlink1"/>

</ItemTemplate>

</asp:TemplateColumn>

</Columns>

</asp:DataGrid>





Ritesh Kumar Kesharwani

A D I T I , B A N G A L O R E
Software Professional
Cell : 011-91-9845657636






Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.

How to Change Mouse Pointer server side for button control.

How to Change Mouse Pointer server side for button control.

 

If button control are deactivate then you want to change Mouse Pointer when user go to that button

 

When button deactivate

BtnDelete.enabled = false;

btnDelete.Style["CURSOR"] = "default";

 

When button active

btnDelete.Style["CURSOR"] = "hand";

BtnDelete.enabled = true;

 



Ritesh Kumar Kesharwani

A D I T I , B A N G A L O R E
Software Professional
Cell  : 011-91-9845657636


Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.

How to Control button event in Web Forms when user press Enter button

How to Control button event in Web Forms when user press Enter button

 

Suppose you have more then one button (three button named: btnFirstButton,btnSecondButton,btnThirdButton) in your web page and after enter button press

First button called.

If you want some particular button should called after enter button press then you can do one this after come out from some control (textbox) you can call one JavaScript function

In textbox onkeypress client side (onkeypress="return OnLevelBlur(event);).

 

<asp:textbox id="txtbox1" Runat="server" MaxLength="10" onkeypress="return OnLevelBlur(event);">

 

JavaScript Function

 

function OnLevelBlur(e)

{

char = String.fromCharCode(e.keyCode);

xCode=char.charCodeAt(0);

if (xCode == 13)

{

document.all["btnFirstButton"].disabled="false"; document.all["btnSecondButton"].disabled="false"; document.Form1.txtbox1.focus();

}

}

 

How it’s Work

 

Basically you catch Enter button events and put the condition that if enter button pressed then disable btnFirstButton  and btnSecondButton  then automatically btnThirdButton click event will fire and after server trip  that two buttons btnFirstButton and btnSecondButton will active again.

 

Summary

 

You are disabling some button which you don’t want to click after enter button.

       

 



Ritesh Kumar Kesharwani

A D I T I , B A N G A L O R E
Software Professional
Cell  : 011-91-9845657636


Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.

Wednesday, July 13, 2005

How to find Perticuler Words or Sentences in Procedure/Function

How to find dependent Objects (Sproc, Function or View) or How to find Perticuler Words or sentences in Procedure/Function
 
For Example : If you want to Search the words "Modified By : Ritesh Kesharwani" in All Used Objects like Function, Procedure, View or Triggrs.
 
Execute below Procedure in your SQL Server. to Run that Procedure User must have Administrator rights.
 
--------------------------------------------------------------------------------------------------------------------------------------
Create proc ALL_sp_depends 
@p_c1 nvarchar(100), 
@p_c2 nvarchar(100) = '', 
@p_c3 nvarchar(100) = '' 
 
as 
 
begin 
set nocount on 
print @p_c2 
print @p_c3 
 declare @l_objname nvarchar(100), @l_Objid int, @l_type as nvarchar(10) 
 declare @l_text varchar(8000), @l_fnd1 bit, @l_fnd2 bit, @l_fnd3 bit 
 declare @l_objCnt int, @l_commCnt int 
 declare @l_totObjCnt int, @l_TotCommCnt int 
 select @l_objCnt = 1, @l_commCnt = 1 
 select @p_c1 = '%' + @p_c1 + '%', @p_c2 = case when @p_c2 in('',null) then '' else '%' + @p_c2 + '%' end,  
    @p_c3 = case when @p_c3 in('',null) then '' else '%' + @p_c3 + '%' end 
 
 declare @output table(ObjectName nvarchar(200), Type nvarchar(50)) 
 declare @sysobjs table (tid int identity(1,1) primary key, objname nvarchar(200), objid int, type nvarchar(10)) 
 create table #sysComments(tid int identity(1,1) primary key, ObjText varchar(8000)) 
 
 insert into @sysobjs select name, id, Type from sysobjects 
 set @l_totObjCnt = @@rowcount 
 
 while @l_objCnt <= @l_totObjCnt 
 begin 
  select @l_objname = objname, @l_Objid = objid,@l_type = type  from @sysobjs where tid = @l_objCnt  
 
  set @l_commCnt = 1 
  delete from #sysComments 
  DBCC CHECKIDENT (#sysComments, RESEED, 0) 
  insert into #sysComments select text from syscomments where id = @l_Objid 
  set @l_TotCommCnt = @@rowcount 
 
  while @l_commCnt <= @l_TotCommCnt 
  begin 
   set @l_text = '' 
   select @l_text = ObjText from #sysComments where tid = @l_commCnt 
 
   if patindex(@p_c1, @l_text) > 0  
    set @l_fnd1 = 1 
 
   if @p_c2 = ''  
    set @l_fnd2 = 1 
   else if patindex(@p_c2, @l_text) > 0  
    set @l_fnd2 = 1 
 
   if @p_c3 = ''  
    set @l_fnd3 = 1 
   else if patindex(@p_c3, @l_text) > 0  
    set @l_fnd3 = 1 
    
   if @l_fnd1 = 1 and @l_fnd2 = 1 and @l_fnd3 = 1 break 
 
   set @l_commCnt = @l_commCnt + 1 
  end 
  if @l_fnd1 = 1 and @l_fnd2 = 1 and @l_fnd3 = 1 
  begin 
   insert into @output values(@l_objname,  
    case @l_type  when 'C' then 'CHECK constraint' 
         when 'D' then 'Default or DEFAULT constraint' 
         when 'F' then 'FOREIGN KEY constraint' 
         when 'FN' then 'Scalar function' 
         when 'IF' then 'Inlined table-function' 
         when 'K' then 'PRIMARY KEY or UNIQUE constraint' 
         when 'L' then 'Log' 
         when 'P' then 'Stored procedure' 
         when 'R' then 'Rule' 
         when 'RF' then 'Replication filter stored procedure' 
         when 'S' then 'System table' 
         when 'TF' then  'Table function' 
         when 'TR' then 'Trigger' 
         when 'U' then 'User table' 
         when 'V' then 'View'  
         when 'X' then 'Extended stored procedure' 
         else 'Other' end  
    ) 
  end 
  select @l_fnd1 = 0, @l_fnd2 = 0, @l_fnd3 = 0 
  set @l_objCnt = @l_objCnt + 1 
 end 
 drop table #sysComments 
 select * from @output 
end  
 
-------------------------------------------------------------------------------------------------------------------------------------- 
 
How To Use This Function:
 
Exec ALL_sp_depends 'Modified By : Ritesh Kesharwani'
It will give you ObjectName and Type of the procedure/ view/function where that strings used.
 
this function can take three parameter like table name , column name and other , last two parameter is not mandatory.
 


Have a Nice Day
 
Ritesh Kumar Kesharwani
Software Professional (MCAD)
Cell  :  011-91-9845657636


Start your day with Yahoo! - make it your home page

How to split the String in SQL Server like IndexOf function

Some time you need to split the String in SQL Server. for example If you are sending Concate String to Store Procedure that time you need to split the string inside the procedure use thisfunction to split each string with perticuler dissimulator (like , ! @ # $ % ^ & * ( )).
This function will return you String which you want. and it will return null if dissimulator not present.
 
Flexibility : You can use this function as IndexOf Function as Other Language
 
Execute below Function in your SQL Query Analyser
-------------------------------------------------------------------------------
Create function dbo.Function_split( 
 @p_value  varchar(4000),  --Pass String
 @p_pos  int,   --Position
 @p_deli  varchar(1)) returns varchar(500)  --Delimiter
as 
begin 
 declare @l_value varchar(500),@l_pati int,@l_pos int,@l_avalue varchar(2000) 
 set @l_avalue=@p_value; 
 if @p_pos<=0 
  return null 
 set @l_pos=0 
 while len(@p_value)<>0 
  begin 
   select @l_pati=charindex(@p_deli,@p_value),@l_pos=@l_pos+1 
   if @l_pati<=0 or @l_pos=@p_pos 
    begin 
     if @l_pati<=0 
      set @l_value=@p_value 
     else 
      set @l_value=substring(@p_value,1,@l_pati-1)       
     break 
    end 
   set @p_value=stuff(@p_value,1,@l_pati,'') 
  end 
 if @p_pos>@l_pos 
  return '' 
 else 
  return isnull(@l_value,'') 
 return null 
end 
----------------------------------------------------------------------
Example : How to Use this function.
  
Declare @A varchar(100)
Set @A = dbo.Function_split ('Ritesh,Kumar,Kesharwani', 3 , ',')
print @A
 
Return Value : Kesharwani
 
Declare @A varchar(100)
Set @A = dbo.Function_split ('Ritesh,Kumar,Kesharwani', 1 , ',')
print @A
 
Return Value : Ritesh
 
Declare @A varchar(100)
Set @A = dbo.Function_split ('Ritesh,Kumar,Kesharwani', 1 , '*')
print @A
 
Return Value : Null
 
Declare @A varchar(100)
Set @A = CAST(dbo.Function_split ('100,200,300', 1 , '*') AS Integer)
print @A
 
Return Value : 100
 
 


Have a Nice Day
 
Ritesh Kumar Kesharwani
Software Professional (MCAD)
Cell  :  011-91-9845657636

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

How To Add Scroll Bar in ASPX Server Control

Use Division Tag to Add ScrollBar in any server Control in ASPX Page.

<div style=" OVERFLOW: auto; WIDTH: 800px; HEIGHT: 152px">

<!-- You can add any Contol here which you want to add Scroll Bar
Like : DataGrid , TextBox, Panel ..Etc -->

</div>

Note:One common problem comes with Datagrid the header part will also scroll
with scrollbar to fix that create one division for HTML table which contain header
only another division for Datagrid where header will be visible false (ShowHeader="False")
Have a Nice Day
Ritesh Kumar Kesharwani
Software Professional
Cell : 011-91-9845657636


Start your day with Yahoo! - make it your home page

Monday, July 04, 2005

Date Validation ,Date Conversion

This Function will take All kind of Date Format and Finally it convert to "MM/DD/YYYY"
 
Format to be Acceptted  mm/dd/yyyy
                                    m/d/yy
                                    mm/dd\yyyy
                                    mm-dd-yyyy
                                    m-d-yyyy   
                                    mm/yyyy   
                                    m/yy     ....etc
 
Function for VB.NET
 

Public Shared Function ConvertToDate(ByVal dateString As String, ByRef result As DateTime) As Boolean

If dateString.Trim = String.Empty Then

Return False

End If

Try

dateString = dateString.Trim

dateString = dateString.Replace("\", "/")

dateString = dateString.Replace(":", "/")

dateString = dateString.Replace("-", "/")

Dim year, month, day As Int16

If dateString.IndexOf("/") = dateString.LastIndexOf("/") Then

' MM/YY format.

Dim index1 As Int16 = dateString.IndexOf("/")

Dim index2 As Int16 = dateString.LastIndexOf("/")

year = dateString.Substring(index2 + 1)

month = dateString.Substring(0, index1)

day = DateTime.DaysInMonth(year, month)

ElseIf dateString.IndexOf("/", dateString.IndexOf("/") + 1) = dateString.LastIndexOf("/") Then ' Check if the second "/" is the last "/"

' MM/DD/YY format.

Dim index1 As Int16 = dateString.IndexOf("/")

Dim index2 As Int16 = dateString.LastIndexOf("/")

year = dateString.Substring(index2 + 1)

month = dateString.Substring(0, index1)

day = dateString.Substring(index1 + 1, index2 - index1 - 1)

Else

Return False

End If

If year < 100 Then

year = year + 2000 ' Convert to 4 digit format.

End If

Dim expDate As String = MonthName(month) & day & ", " & year

If Not IsDate(expDate) Then

Return False

Else

result = CType(expDate, DateTime)

Return True

End If

Catch ex As Exception

Return False

End Try

End Function


Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.

Monday, June 20, 2005

Using the enter key to submit a form

Using the enter key to submit a form
 
<script language=javascript>
   function enterPressPickList()
  {
    if (window.event && window.event.keyCode == 13)
  {
      __doPostBack('txtPickList','');
   }
  }
</script>
 
<asp:TextBox id="txtPickList" onkeydown="return enterPressPickList();" runat="server" MaxLength="9" AutoPostBack="True"></asp:TextBox>
 
 


Have a Nice Day
 
Ritesh Kumar Kesharwani
Software Professional (MCAD)
Cell  :  011-91-9845657636


Yahoo! Sports
Rekindle the Rivalries. Sign up for Fantasy Football

Friday, June 17, 2005

Limiting Records per Page in Crystal Reports.net

Limiting Records per Page


Applies to
All versions
Conditional formulas
Formula evaluation

Synopsis
Having difficulty formatting information within your crystal reports? Maybe you need to limit your records being display per page.

Solution
Create a manual running total to count the records in the Details section. The running total consists of two formulas: @Reset and @Details.

Example
An order must be printed with only 6 records per page.

1. Create the @Reset and @Details formulas:

@Reset - this formula goes into the Page Header. It resets the counter to 0 when SCR moves to a new page.

WhilePrintingRecords;
NumberVar counter := 0

// declare a number variable and assigns
// it a value of zero

@Details - this formula goes in the Details section. It counts each record.

WhilePrintingRecords;
NumberVar counter;
counter := counter + 1

//increments the variable by 1 for each record on the page

2. Use the @Details formula to conditionally force a "New Page After". Every time the counter reaches the sixth record, it moves to a new page and prints the next set of six records.
  • Go to Format | Section, and select the Details section.
  • Click the "X+2" button beside the "New Page After" option (do NOT check the New Page After box). This opens the Condtional Formatting Formula Editor.
  • Enter the following condition:
    {@Details} = 6
This formula forces a new page every time CR reaches the sixth record displayed on every page. The count resets to zero on every page because of the {@reset} formula in the Page Header.


Have a Nice Day
 
Ritesh Kumar Kesharwani
Software Professional (MCAD)
Cell  :  011-91-9845657636


Yahoo! Sports
Rekindle the Rivalries. Sign up for Fantasy Football

Saturday, June 11, 2005

Sent Multiple data to StoreProcedure in the form of XML

Introduction

OPENXML provides an easy way to use an XML document as a data-source for your procedures. OPENXML allows the data in XML document to be treated just like the columns and rows of your database table i.e., xml tags as columns and the value as rows. Data can be inserted / updated very quickly and efficiently without multiple trips to the database.

For example, if 100 records are to inserted / updated, then the traditional SQL method is using 100 insert / update statements. This means that 100 trips are made to the database, which results in degradation of performance. Using XML, these 100 trips can be reduced to 1 trip. This increases the performance of your application.
In this article, we would look at the basic syntax of OpenXML and end with a simple example.

Basic Syntax

OPENXML (idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) [WITH SchemaDeclaration TableName)]
Arguments
Idoc is the document handle of the internal representation of an XML document.
This handle is obtained by calling the system stored procedure sp_xml_preparedocument, which is
discussed later.
Rowpattern is the XPath query used to identify the nodes to be processed as rows.
Flags indicates the mapping between the XML data and the relational rowset. (optional parameter)

0 - Defaults to attribute-centric mapping.
1 - Attribute-centric mapping. (Combined with XML_ELEMENTS)
2 - Element-centric mapping. (Combined with XML_ATTRIBUTES)
8 - Combined with XML_ATTRIBUTES or XML_ELEMENTS

SchemaDeclaration is the schema definition of the form:

ColName ColType [ColPattern MetaProperty][, ColName ColType [ColPattern MetaProperty]...]

Eg: WITH (EMPLOYEENAME VARCHAR(30), EMPLOYEESALARY INTEGER)

TableName is the table name that can be given, instead of Schema Declaration, if a table exists.

The WITH clause provides a table format using either SchemaDeclaration or specifying an existing TableName. If the optional WITH clause is not specified, the results are returned in an edge table format. Edge tables represent the fine-grained XML document structure (e.g. element/attribute names, the document hierarchy, the namespaces, PIs etc.) in a single table.

System Stored Procedures for OpenXML

SQL Server provides system stored procedures that are used in conjunction with OPENXML:

  • sp_xml_preparedocument
  • sp_xml_removedocument

To write queries using OPENXML, you must first create an internal representation of the XML document by calling sp_xml_preparedocument. It is similar to LoadXML() function provided in System.XML namespace. The stored procedure returns a handle to the internal representation of the XML document. This handle is then passed to OPENXML, which provides tabular view of the document based on Xpath query.

The internal representation of an XML document can be removed from memory by calling sp_xml_removedocument system stored procedure.

About sp_xml_preparedocument

Steps which occurs when this procedure is executed:

  1. Reads the XML text provided as input.
  2. Parses the text using the XML parser.
  3. Provides the parsed document, which is in tree form containing various nodes (elements, attributes, text, comments, and so on) in the XML document.
  4. It returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for until the connection is reset, or until the execution of sp_xml_removedocument.

Note: A parsed document is stored in the internal cache of SQL Server 2000. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

The syntax is:

sp_xml_preparedocument hdoc OUTPUT [, xmltext] [, xpath_namespaces]

hdoc is the handle to the newly created document.(Integer value)

[xmltext] is the original XML document. The default value is NULL, in which case an internal representation of an empty XML document is created.

[xpath_namespaces] Specifies the namespace declarations that are used in row and column XPath expressions in OPENXML.

Returns: 0 (success) or >0 (failure)

About sp_xml_removedocument

Removes the internal representation of the XML document specified by the document handle.

The syntax is: sp_xml_removedocument hdoc

hdoc is the handle to the newly created document.(Integer value)

Returns: 0 (success) or >0 (failure)

Basic structure of stored-procedure(using OPENXML)

CREATE PROCEDURE dbo.TestOpenXML(@strXML VARCHAR(2000))

AS
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument

@XMLDocPointer OUTPUT, @strXML
BEGIN TRANSACTION
------ Any DML can be used here -------(see examples)
COMMIT
EXEC sp_xml_removedocument @XMLDocPointer
RETURN

Examples

Consider two tables: Employees & Departments.
Employees: EmployeeID, EmployeeName, EmployeeSalary, DeptID.
Departments: DeptID, DeptName.
(EmployeeID and DeptID are Identity fileds.)
The XML file will follow the structure.
(XML Type 1)
<RECORD>
<EMPLOYEE>
<EMPLOYEENAME>Test Name</EMPLOYEENAME>
<EMPLOYEESALARY>1000</EMPLOYEESALARY>
<DEPTID>10</DEPTID>
</EMPLOYEE>
.
.
</RECORD>

If 100 employees have been recruited, u can generate a XML file having the following structure with 100 <EMPLOYEE> tags, the query should be:
INSERT INTO Employees(EmployeeName,  
EmployeeSalary, DeptID)
SELECT EMPLOYEENAME, EMPLOYEESALARY, DEPTID
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE',2)
WITH (EMPLOYEENAME VARCHAR(30), EMPLOYEESALARY INTEGER,
DEPTID INTEGER)
If 100 employees are transferred from Production 
Department to Stores Department, the query should be: 
UPDATE Employees SET DeptID = xmlTable.DEPTID
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE',2)
WITH (EMPLOYEEID INTEGER, DEPTID INTEGER) XmlTable
WHERE XmlTable.EMPLOYEEID = Employees.EmployeeID
Note: use of alias for XML document is required to avoid confusion.

If the XML file contains attributes, there is minor change to the syntax.
(XML Type 2)

<RECORD>
<EMPLOYEE DEPTID = ‘10’ NAME = ‘Test’ SALARY = ‘10000’/>
<EMPLOYEE DEPTID = ‘10’ NAME = ‘Test’ SALARY = ‘10000’/>
<EMPLOYEE DEPTID = ‘10’ NAME = ‘Test’ SALARY = ‘10000’/>
.
.
</RECORD>

INSERT INTO Employees(EmployeeName, 
EmployeeSalary,DeptID)
SELECT NAME, SALARY, DEPTID
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE',2)
WITH (NAME VARCHAR(30) @NAME,
SALARY INT @SALARY,
DEPTID INT '@DEPTID')
Another scenario for attributes
(accessing the parent node)
(XML Type 3)

<RECORD>
<DEPT ID=10>
<EMPLOYEE NAME = ‘Test101’ SALARY = ‘10000’/>
<EMPLOYEE NAME = ‘Test102’ SALARY = ‘10000’/>
</DEPT>
<DEPT ID=’11’>
<EMPLOYEE NAME = ‘Test111’ SALARY = ‘10000’/>
<EMPLOYEE NAME = ‘Test112’ SALARY = ‘10000’/>
<EMPLOYEE NAME = ‘Test113’ SALARY = ‘10000’/>
<EMPLOYEE NAME = ‘Test114’ SALARY = ‘10000’/>
</DEPT>
.
.
</RECORD>

INSERT INTO Employees 
(EmployeeName, EmployeeSalary, DeptID)
SELECT NAME, SALARY, DEPTID FROM OPENXML
(@XMLDocPointer,'/RECORD/DEPT/EMPLOYEE',2)
WITH (NAME VARCHAR(30) @NAME, SALARY INT
@SALARY,DEPTID INT '../@ID')

Finally, your store procedure should look like (using XML Type 2):
CREATE PROCEDURE dbo.TestOpenXML
( @strXML VARCHAR(2000)
)
AS
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument

@XMLDocPointer OUTPUT, @strXML
BEGIN TRANSACTION
INSERT INTO Employees(EmployeeName,
EmployeeSalary, DeptID)
SELECT NAME, SALARY, DEPTID
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE',2)
WITH (NAME VARCHAR(30)
@NAME,
SALARY INT @SALARY, 
DEPTID INT '@DEPTID')
COMMIT
EXEC sp_xml_removedocument @XMLDocPointer
RETURN

Conclusion

With SQL Server 2000, there are many ways you can get XML representation of relational data. One simple way is using the FOR XML clause with SELECT statement. A reverse mechanism, which allows turning XML document into tabular, relational rowset format is provided by T-SQL keyword named OPENXML.

The steps for using OPENXML can be simplified as:

  1. Call sp_xml_preparedocument. (system stored procedure which loads XML document string into memory, parses it and returns a handle).
  2. Use XPath querries for extracting required information from the XML through the handle.
  3. Call sp_xml_removedocument. (system stored procedure which frees up the memory allocated to the internal representation of the XML document).

In this article, we studied the OPENXML syntax and options available with it, with simpe examples. Future articles we will discuss other XML features offered by SQL Server 2000.

Oracle Database

If you are working with Oracle as a database and want to do same operation then see the following link

http://riteshk.blogspot.com/2008/09/sending-xml-string-as-input-parameter.html