Gridview remove column

GridView1.Columns.RemoveAt(2);

Getting result

select

WWMLNM,* from crpdta.F0111 a

left

outer join crpdta.f0101 on wwan8=aban8

where

WWIDLN=0 and ABTAXC in('N' , 'P','1' )

and

wwan8 in(select a6an8 from crpdta.f0401)

and

WWMLNM like '%,%' and upper(WWMLNM) =WWMLNM COLLATE Latin1_General_BIN

Convert datetime with 00 time - sql-server

select cast(convert(char(11), getdate(), 113) as datetime) 

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/