UpdateParameters Session variable Gridview

<%@ Page Title="" Language="C#" MasterPageFile="MasterPage2.master" AutoEventWireup="true" CodeFile="ownerdet.aspx.cs" Inherits="ownerdet" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <form id="form1" runat="server">
    <center>

     <br />
        <b>Insert Owner</b>
        <br/>
        <table style="width: 60%">
            <tr>
                <td style="height: 17px">
                    <asp:Label ID="Label5" runat="server" Text="First Name"></asp:Label>
                </td>
                <td style="height: 17px">
                    <asp:Label ID="Label1" runat="server" Text="Last Name"></asp:Label>
                </td>
                <td style="height: 17px">
                    <asp:Label ID="Label2" runat="server" Text="Company"></asp:Label>
                </td>
                <td style="height: 17px">
                    <asp:Label ID="Label3" runat="server" Text="Email ID"></asp:Label>
                </td>
                <td style="height: 17px">
                    &nbsp;</td>
                <td style="height: 17px">
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:TextBox ID="TextBox3" runat="server" ></asp:TextBox>
                </td>
                <td>
                    <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
                </td>
                <td>
                    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Add" />
                </td>
                <td>
                    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td colspan="2">
                    <asp:Label ID="Label6" runat="server"
                        Text="Duplicate Owner , Do you want to continue?"></asp:Label>
                </td>
                <td>
                    <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="Yes" />
                    <asp:Button ID="Button3" runat="server" onclick="Button3_Click" Text="No" />
                </td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
        </table>
       
        <b> Owner Details</b>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333"  DataKeyNames="OWID"
        GridLines="None" AutoGenerateEditButton="true" AutoGenerateDeleteButton="true" AllowPaging="true" PageSize="10">
        <AlternatingRowStyle BackColor="White"   />
        <Columns>
            <asp:BoundField DataField="OWID" HeaderText="OWID" InsertVisible="False"
                ReadOnly="True" SortExpression="OWID" />
            <asp:BoundField DataField="OWFNAM" HeaderText="First Name"
                SortExpression="OWFNAM" />
            <asp:BoundField DataField="OWLNAM" HeaderText="Last Name"
                SortExpression="OWLNAM" />
            <asp:BoundField DataField="OWCOMP" HeaderText="Company"
                SortExpression="OWCOMP" />
            <asp:BoundField DataField="OWEMAL" HeaderText="Email Id"
                SortExpression="OWEMAL" />
           <asp:BoundField DataField="OWACTYN" HeaderText="Active(Y/N)"
                SortExpression="OWACTYN" />
          <asp:BoundField DataField="OWUSER" HeaderText="OWUSER"
                SortExpression="OWUSER"  ReadOnly="true" />
           <asp:BoundField DataField="OWUPMJ" HeaderText="Date Update"
                SortExpression="OWUPMJ" ReadOnly="true" />
          
               
        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
       
    </asp:GridView>
        <br />
        <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
            DataSourceID="SqlDataSource2" Visible="false">
            <Columns>
                <asp:BoundField DataField="OWID" HeaderText="OWID" InsertVisible="False"
                    ReadOnly="True" SortExpression="OWID" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString51 %>"
            ></asp:SqlDataSource>
      
    </center>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString51 %>"
        SelectCommand="SELECT [OWID], [OWFNAM], [OWLNAM], [OWCOMP], [OWEMAL],[OWACTYN],[OWUSER],[OWUPMJ] FROM [Owdetails]"
         UpdateCommand="update Owdetails set OWFNAM=@OWFNAM, OWLNAM=@OWLNAM, OWCOMP=@OWCOMP, OWEMAL=@OWEMAL,OWACTYN=@OWACTYN,OWUPMJ=@OWUPMJ,OWUSER=@OWUSER where OWID=@OWID"
          DeleteCommand="Delete FROM Owdetails where OWID=@OWID"
        >
        <UpdateParameters>
                    <asp:Parameter Name="OWFNAM" Type="String" />
                    <asp:Parameter Name="OWLNAM" Type="String" />
                    <asp:Parameter Name="OWCOMP" Type="String" />
                    <asp:Parameter Name="OWEMAL" Type="String" />
                    <asp:Parameter Name="OWACTYN" Type="String" />
                    <asp:SessionParameter Name="OWUPMJ" SessionField="datetime" Type="String" />
                    <asp:SessionParameter Name="OWUSER" SessionField="Usrid" Type="String" />
        
                </UpdateParameters>

    </asp:SqlDataSource>
    </form>
</asp:Content>

 

Load data in Account balance file F0902 - JDE

create

proc fteupload as

select

* into #FTE from dbo.BudgetData where obj_desc like '%FTE%'

declare

@edtn integer

select

@edtn=max(gbedtn) from JDE_CRP.CRPDTA.F0902Z1

INSERT

INTO JDE_CRP.CRPDTA.F0902Z1

(

GBAID,GBCTRY,GBFY,GBLT,GBCO,GBEDUS,GBCTID,GBEDTN,GBAN01,GBAN02,GBAN03,GBAN04,

GBAN05

,GBAN06,GBAN07,GBAN08,GBAN09,GBAN10,GBAN11,GBAN12,GBMCU,GBOBJ,GBSUB,GBUSER,GBPID,GBUPMJ,GBJOBN,GBUPMT,GBCRCX,GBCRCD,GBIST,GBFQ,GBSBL,GBPRGF,GBSBLT,GBAPYC)

select

gmaid,'20' ,'12','H1',GMCO,'SQL','DEREK',row_number() OVER (ORDER BY GMAID)+@edtn as GBEDTN, Period_01,Period_02,Period_03,Period_04,Period_05,

Period_06

,Period_07,Period_08,Period_09,Period_10,Period_11,Period_12, GMMCU,GMOBJ,GMSUB,'DEREK','SQL','111347','SQL-SERVER','120000','USD','USD','A','','','','',0

from

#FTE

left

outer join jde_crp.crpdta.f0901 on substring(ltrim(objsub),1,5)=ltrim(gmobj) and ltrim(BU)= ltrim(gmmcu)

where

gmaid is not null

Upload Budget Data in F0902 JD Edwards

Upload budget data in F0902Z1

Verify in         P0902Z1

Run  R14110 to Upload data in F0902.

 

 

 

JD Edwards - Get max sequence in role -security

select

distinct FSUSER,FSOBNM,RLTOROLE,AUSEQNO ,FSSETY,FSINSL,FSRUN into #temp_03

from

SY900.F00950

left

outer join SY900.F95921 on fsuser=RLFRROLE

left

outer join SY900.F00926 on auuser=fsuser

where

RLFRROLE is not null and FSUSER not in('ESS','MSS','SYSBLOCK','ERREQ') AND AUSEQNO is not null

AND

FSOBNM<>'' AND FSSETY='3'

order

by 1

select

distinct FSUSER,FSOBNM,RLTOROLE,AUSEQNO ,FSSETY,FSA,FSCHNG,FSDLT,FSTHDV,FSOBID,FSADDC,FSIOK,FSICPY

into

#temp_01

from

SY900.F00950

left

outer join SY900.F95921 on fsuser=RLFRROLE

left

outer join SY900.F00926 on auuser=fsuser

where

RLFRROLE is not null and FSUSER not in('ESS','MSS','SYSBLOCK','ERREQ') AND AUSEQNO is not null

AND

FSOBNM<>'' AND FSSETY='1'

order

by 1

--select * from #temp_01 a

--select * from #temp_03 a

select

a.* ,FSA,FSCHNG,FSDLT,FSTHDV,FSADDC,FSIOK,FSICPY into #temp4

from

#temp_03 a

left

outer join #temp_01 b on a.FSUSER=b.FSUSER and a.FSOBNM=b.FSOBNM and a.RLTOROLE=b.RLTOROLE

where

b.FSUSER is not null

insert into #temp4

select

a.* ,'Y' FSA,'Y'FSCHNG,'Y'FSDLT,'Y'FSTHDV,'Y'FSADDC,'Y'FSIOK,'Y'FSICPY

from

#temp_03 a

left

outer join #temp_01 b on a.FSUSER=b.FSUSER and a.FSOBNM=b.FSOBNM and a.RLTOROLE=b.RLTOROLE

where

b.FSUSER is null

select

FSUSER Roles

, FSOBNM Program, RLTOROLE Users, AUSEQNO SeqNo,FSINSL Install,FSRUN Run,FSA [Add],FSCHNG Change,

FSDLT Del

,FSIOK Ok,FSICPY Copy --into #temp5

from

#temp4 order by 3,2,1,4 desc

Copy Sql-server database

very simple....

Pick a source and destination server.

Select databases to  copy.:- must see this step

 

http://msdn.microsoft.com/en-us/library/ms188664.aspx

Employee History by Job tile and salary JD Edwards

distinct jwan8 Emp#,yaalph Name,yahmcu BU,abalph Supervisor,jmdl01 Title, DATEADD(dy, cast(jwefto as varchar(10)) % 1000, DATEADD(yy, cast(jwefto as varchar(10)) / 1000,-1))EffDate ,yesal/100 Salary,DRDL01 JobCategory fromjde_crp.crpdta.F08042 leftouter join jde_crp.crpdta.F08001 on ltrim(jmjbcd) = ltrim(JWHSTD) leftouter join jde_crp.crpdta.F060119 on jwan8=yean8 and jwefto=yeefto leftouter join jde_crp.crpdta.F060116 on jwan8=yaan8 leftouter join jde_crp.crpdta.F0101 on yaanpa=aban8 LEFTOUTER JOIN jde_crp.crpCTL.f0005 ON DRSY='06' AND DRRT='J' AND LTRIM(YAEEOJ)=LTRIM(DRKY) wherejwdtai='JBCD' and jmjgrp is not null and jwuser<>'kumaraje' --and jwefto>111000 andjwtrs<>'' andyapast in('0','1','2') --and yaan8=106491 orderby 1,3




select

JD Edwards EnterpriseOne Create BI Report

Introduction to LDAP

What is LDAP

• Lightweight Directory Access Protocol
• Based on X.500
• Directory service (RFC1777)
• Stores attribute based data
• Data generallly read more than written to
  • No transactions
  • No rollback
• Hierarchical data structure
  • Entries are in a tree-like structure called Directory Information Tree (DIT)

 

http://quark.humbug.org.au/publications/ldap/ldap_tut.html

SQL Trigger Error - The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (2 rows).

The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (2 rows).

Put SET NOCOUNT ON  as following:-

 

Create TRIGGER [dbo].[OBIEE_USERSTRI_DEL]

ON

[dbo].[WC_OBIEE_USERS]

FOR

DELETE

AS

SET

NOCOUNT ON

BEGIN

DECLARE

@USERNAME1 VARCHAR(100)

DECLARE

@GROUPNAME1 VARCHAR(2000)

SELECT

@USERNAME1 =USERNAME,@GROUPNAME1=GROUPNAME FROM Deleted

insert

into WC_OBIEE_AUDIT(USERNAME,GROUPNAME,USERID,DATEUPD,WORKSTN,[ACTION])

select

@USERNAME1,@GROUPNAME1,SYSTEM_USER,getdate(),host_name() ,'Delete'

END

 

TRIGGER Sql-server on OBIEE user table insert and delete

CREATE

TRIGGER [dbo].[OBIEE_USERSTRI_INS]

ON

[dbo].[WC_OBIEE_USERS]

FOR

INSERT

AS

BEGIN

DECLARE

@USERNAME VARCHAR(100)

DECLARE

@GROUPNAME VARCHAR(100)

SELECT

@USERNAME = (SELECT USERNAME FROM Inserted)

SELECT

@GROUPNAME = (SELECT GROUPNAME FROM Inserted)

insert

into WC_OBIEE_AUDIT(USERNAME,GROUPNAME,USERID,DATEUPD,WORKSTN,[ACTION])

select

@USERNAME,@GROUPNAME,SYSTEM_USER,getdate(),host_name() ,'Insert'

/*

insert into WC_OBIEE_AUDIT(USERNAME,GROUPNAME,USERID,DATEUPD,WORKSTN,[ACTION])

select a.[USERNAME],a.GROUPNAME,SYSTEM_USER,getdate(),host_name() ,'Insert' "ACTION" from WC_OBIEE_USERS a

left outer join WC_OBIEE_AUDIT b on a.[USERNAME]=b.[USERNAME] and a.[GROUPNAME]=b.[GROUPNAME]

where b.[USERNAME] is null

*/

END

Create

TRIGGER [dbo].[OBIEE_USERSTRI_DEL]

ON

[dbo].[WC_OBIEE_USERS]

FOR

DELETE

AS

BEGIN

DECLARE

@USERNAME1 VARCHAR(100)

DECLARE

@GROUPNAME1 VARCHAR(2000)

SELECT

@USERNAME1 =USERNAME,@GROUPNAME1=GROUPNAME FROM Deleted

insert

into WC_OBIEE_AUDIT(USERNAME,GROUPNAME,USERID,DATEUPD,WORKSTN,[ACTION])

select

@USERNAME1,@GROUPNAME1,SYSTEM_USER,getdate(),host_name() ,'Delete'

/*

insert into WC_OBIEE_AUDIT(USERNAME,GROUPNAME,USERID,DATEUPD,WORKSTN,[ACTION])

select a.[USERNAME],a.[GROUPNAME],SYSTEM_USER,getdate(),host_name() ,'Delete' "ACTION" from WC_OBIEE_AUDIT a

left outer join WC_OBIEE_USERS b on a.[USERNAME]=b.[USERNAME] and a.[GROUPNAME]=b.[GROUPNAME]

where b.[USERNAME] is null

*/

END

no Forms Authentication Login Page Redirect When You Don’t Want It

In an ASP.NET web application, it's very common to write some jQuery code that makes an HTTP request to some URL (a lightweight service) in order to retrieve some data. That URL might be handled by an ASP.NET MVC controller action, a Web API operation, or even an ASP.NET Web Page or Web Form. If it can return curly brackets, it can be respond to a JavaScript request for JSON.

 

http://haacked.com/archive/2011/10/04/prevent-forms-authentication-login-page-redirect-when-you-donrsquot-want.aspx

Coupa API

http://www.coupa.com/general/pain-relief-in-the-cloud-integration-part-1/

Create Trigger JD Edwards Table

Create Trigger JD Edwards

1.Check out the table to which you want to attach event rules and click Design.

2.On the Table Design form, select the Design Tools tab and click Start Table Trigger Design Aid.

3.Select an event from the Events list.

4.On Event Rules Design, click one of the event rule buttons and complete the event rules.

5.Click Save to save the event rule specifications and then click Close.

6.If you are creating a new table in Oracle's JD Edwards EnterpriseOne Table Design Aid, select the Table Operations tab and click Generate Table.

http://download.oracle.com/docs/cd/E17984_01/doc.898/e14702/createtableeventrules.htm

IDENTITY column in sql-server in select into

select

IDENTITY(INT, 10, 1) idnt ,* from testdta.F5510004

my first TRIGGER ever sql-server

ALTER

TRIGGER testTRIGGER

ON

dbo.QTD_HOURS1

FOR

INSERT

AS

BEGIN

insert

into QTD_HOURS1Trg

select

a.* from QTD_HOURS1 a

left

outer join QTD_HOURS1Trg b on a.[column 0]=b.[column 0] and a.[column 1]=b.[column 1]

where

b.[column 0] is null

END

Convert Date range into month

select

--startdate,enddate,datepart(MM,startdate),datepart(MM,enddate),enddate,

case

when (datepart(MM,startdate)<='1' and datepart(MM,enddate)>'1') then 1 else 0 end Jan,

case

when (datepart(MM,startdate)<='2' and datepart(MM,enddate)>'2') then 1 else 0 end Feb,

case

when (datepart(MM,startdate)<='3' and datepart(MM,enddate)>'3') then 1 else 0 end Mar,

case

when (datepart(MM,startdate)<='4' and datepart(MM,enddate)>'4') then 1 else 0 end Apr,

case

when (datepart(MM,startdate)<='5' and datepart(MM,enddate)>'5') then 1 else 0 end May,

case

when (datepart(MM,startdate)<='6' and datepart(MM,enddate)>'6') then 1 else 0 end Jun,

case

when (datepart(MM,startdate)>='1' and datepart(MM,enddate)>'7') then 1 else 0 end Jul,

case

when (datepart(MM,startdate)>='1' and datepart(MM,enddate)>'8') then 1 else 0 end Aug,

case

when (datepart(MM,startdate)>='1' and datepart(MM,enddate)>'9') then 1 else 0 end sep,

case

when (datepart(MM,startdate)>='1' and datepart(MM,enddate)>'10') then 1 else 0 end Oct,

case

when (datepart(MM,startdate)>='1' and datepart(MM,enddate)>'11') then 1 else 0 end Nov,

case

when (datepart(MM,startdate)>='1' and datepart(MM,enddate)>'12') then 1 else 0 end Dec,

*

from dbo.EmpNew

where

emplno IN(101274,101429,101483,101513,101560,101592,101605)