Insert C# SQL/Connection String

string strSQL1;

            strSQL1 = "   insert into [tblcontrol_Comt] ( [SLNO],[Status],[Reason]) select " + t1 + ",'" + DropDownList4.SelectedItem + "','" + TB_Comment.Text + "'";

            strSQL = strSQL + strSQL1;

            //Response.Write(strSQL);

            //Response.End();

            SqlCommand cmd = new SqlCommand(strSQL, myConnection);

            cmd.CommandTimeout = 30000;

            cmd.CommandType = CommandType.Text;

            myConnection.Open();

            cmd.ExecuteNonQuery();

            myConnection.Close();

 private string ConnectionString

    {

        get

        {

            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;

            return connectionString;

        }

    }

On Sat, Mar 10, 2018 at 10:42 PM, Rajeev Kumar <rajeevkum@gmail.com> wrote:

 string strSQL1;

            strSQL1 = "   insert into [tblcontrol_Comt] ( [SLNO],[Status],[Reason]) select " + t1 + ",'" + DropDownList4.SelectedItem + "','" + TB_Comment.Text + "'";

            strSQL = strSQL + strSQL1;

            //Response.Write(strSQL);

            //Response.End();

            SqlCommand cmd = new SqlCommand(strSQL, myConnection);

            cmd.CommandTimeout = 30000;

            cmd.CommandType = CommandType.Text;

            myConnection.Open();

            cmd.ExecuteNonQuery();

            myConnection.Close();

--

Rajeev Kumar
949-378-6094

insert C# SQL

 string strSQL1;

            strSQL1 = "   insert into [tblcontrol_Comt] ( [SLNO],[Status],[Reason]) select " + t1 + ",'" + DropDownList4.SelectedItem + "','" + TB_Comment.Text + "'";

            strSQL = strSQL + strSQL1;

            //Response.Write(strSQL);

            //Response.End();

            SqlCommand cmd = new SqlCommand(strSQL, myConnection);

            cmd.CommandTimeout = 30000;

            cmd.CommandType = CommandType.Text;

            myConnection.Open();

            cmd.ExecuteNonQuery();

            myConnection.Close();

JD Edwards Excel Integration - JE

Load Excel Name Range Data to SQL Server table, VBA

Sub ShowRecordCount()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=C:\temp\JW_E&PM_041.xlsx;" & _
"Extended Properties=Excel 12.0;"
    .Open
End With

sSQL = "SELECT * FROM rngloaddata"

Set oRs = New ADODB.Recordset
oRs.Open sSQL, cn, adOpenStatic, adLockBatchOptimistic, adCmdText

Set cn2 = CreateObject("ADODB.Connection")
    With cn2
        .CursorLocation = adUseClient
        .Open "Driver={SQL Server};Server=Server01; Database=budget; UID=DB; PWD=xxxxxx"
        .CommandTimeout = 0
        oRs.MoveFirst
       
   
   
    For i = 0 To Range("rngloaddata").Columns.Count - 1
        strcolnmName = strcolnmName & ", CAST('' AS VARCHAR(255))" & oRs.Fields(i).Name
       
    Next
   
    tablstr = "IF OBJECT_ID('tempdb..##temp1345') IS NOT NULL  drop table ##temp1345     select  " & Right(strcolnmName, Len(strcolnmName) - 1) & " into ##temp1345"
    Debug.Print tablstr
    .Execute tablstr
    Debug.Print tablstr
   
    Do While Not oRs.EOF
    strcolnmValue = ""
            For i = 0 To Range("rngloaddata").Columns.Count - 1
                strcolnmValue = strcolnmValue & ",'" & oRs.Fields(i) & "'"
            Next
       
        .Execute "INSERT INTO ##temp1345 select " & Right(strcolnmValue, Len(strcolnmValue) - 1)
           
            oRs.MoveNext
            Loop
    End With

cn.Close
End Sub

Insert Data Using Named range in SQL server table Excel VBA

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=C:\temp\JW_E&PM_041.xlsx;" & _
"Extended Properties=Excel 12.0;"
    .Open
End With

sSQL = "SELECT * FROM testrangdata"
'sSQL = "SELECT * FROM [Sheet1$A1:E46]"
Set oRs = New ADODB.Recordset
oRs.Open sSQL, cn, adOpenStatic, adLockBatchOptimistic, adCmdText

Set cn2 = CreateObject("ADODB.Connection")
    With cn2
        .CursorLocation = adUseClient
        .Open "Driver={SQL Server};Server=Server1\SQL1; Database=mydb; UID=bbbbb; PWD=MMMMM"
        .CommandTimeout = 0
        oRs.MoveFirst
        Do While Not oRs.EOF
        Debug.Print oRs.Fields(1)
     
            .Execute "INSERT INTO budget.dbo.delthisTEMP_TABLE ( [BU] ) VALUES ('" & oRs.Fields(1) & "')"
            oRs.MoveNext
            Loop
    End With

cn.Close

XLSX excel vba connectionstring

Sub test()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\temp\JW_E&PM_04.xlsx;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
End Sub


Sub ShowRecordCount()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=C:\temp\JW_E&PM_04.xlsx;" & _
"Extended Properties=Excel 12.0;"
    .Open
End With

sSQL = "SELECT * FROM [sheet1$]"
Set oRs = New ADODB.Recordset
oRs.Open sSQL, cn, adOpenStatic, _
               adLockBatchOptimistic, adCmdText
MsgBox oRs.RecordCount
cn.Close
End Sub


read named range in VBA


Sub ShowRecordCount()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=C:\temp\JW_E&PM_04.xlsx;" & _
"Extended Properties=Excel 12.0;"
    .Open
End With

sSQL = "SELECT * FROM testrange"
'sSQL = "SELECT * FROM [Sheet1$A1:E46]"
Set oRs = New ADODB.Recordset
oRs.Open sSQL, cn, adOpenStatic, _
               adLockBatchOptimistic, adCmdText
MsgBox oRs.RecordCount
cn.Close
End Sub

Convert Amount into currency with $ sign and commas using jquery/javascript

currentamount.toString().replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,")

Launch Batch Application JD Edwards

JD Edwards - Pass Value to UBE/Application in URL

Topic related to this:-
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=371210426635792&id=748331.1&_afrWindowMode=0&_adf.ctrl-state=ydq9vvmc_4




How to create and get access of JD Edwards application
http://oooooo.sssssss.com/jde/E1Menu.maf?OID=P0011_W0011A&User=rajeev&Password=xxxxxx&Environment=p&FormDSTmpl=|2|&FormDSData=|99999|&selectJPD910=*ALL

http://oracle.SSSSSSSS.com/jde/E1Menu.maf?OID=P0011_W0011_ZJDE0001A&FormDSTmpl=|1|&FormDSData=|G|

Create Excel Office addin using C# – Part 1 (Ribbon Designer)

https://sandeepfabiani.wordpress.com/2013/08/24/create-excel-office-addin-using-csharp-ribbon-designer/

Inventory Stock Report - JD Edwards UBE

Listing of ER for Report: Report : Expected Sales/GP by  Branch Plant * Item (R55COST02A)

***********************************************************************

     GLOBALS: Variables (Global)

***********************************************************************

     rpt_LedgType_LEDG21

     rpt_MCU_NO_Space

     rpt_MCU_AN8

     rpt_SalesReportingCode1

     rpt_SalesReportingCode2

     rpt_SalesReportingCode3

     rpt_SalesReportingCode4

     rpt_AlternateAddressKey_ALKY

     rpt_ForOwner_AN8

     rpt_NameGiven_GNNM

     rpt_NameSurname_SRNM

     rpt_HubMgr_CMC1

     rpt_ForHub_AN8

=======================================================================

     SECTION: Page Header [PAGE HEADER] (S3)

=======================================================================

     OBJECT:  SECTION

     EVENT:  Do Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0001 RV VersionName = SL VersionName

=======================================================================

     SECTION: OnHand qty  by item/BP/SRP1/Company [GROUP SECTION] (S4)

=======================================================================

     OBJECT:  SECTION

     EVENT:  Initialize Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0001 Set Selection Append Flag(<Yes>)

0002 Set User Selection(BC Cost Method (F4105)(LEDG), <Equal To>, "02", <And>)

0003 Set User Selection(BC Quantity on Hand - Primary units (F41021)(PQOH), <Not Equal To>, <Zero>, <And>)

0004 Set User Selection(BC Quantity on Hand - Primary units (F41021)(PQOH), <Not Equal To>, "0", <And>)

0005 Set User Selection(BC Quantity on Hand - Primary units (F41021)(PQOH), <Not Equal To>, <Blank>, <None>)

-----------------------------------------------------------------------

     EVENT:  Do Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0004 RV Extended Cost02 = [BC Quantity on Hand - Primary units (F41021)(PQOH)]*[BC Amount - Unit Cost (F4105)(UNCS)]

0005 RV CompanyFooter = BC Company (F0006)(CO)

0006 RV BusinessUnitFooter = BC Business Unit (F41021)(MCU)

0007 RV DescriptionCompressedBUFooter = BC Description - Compressed (F0006)(DC)

0008 RV Description_ITEM = substr([BC Description (F4101)(DSC1)],0,30)

0009 RV Location_ITEM = substr([BC Location (F41021)(LOCN)],0,20)

0010 VA rpt_LedgType_LEDG21 = "21"

0011 RV Unit Cost21 = 0.00

0012 F4105.Fetch Single

0013 RV Extended Cost21 = [BC Quantity on Hand - Primary units (F41021)(PQOH)]*[RV Unit Cost21]

0014 If RV Extended Cost21 is greater than <Zero> Or RV Extended Cost21 is greater than <Blank>

0015    RV GrossProfit = (([RV Extended Cost21]-[RV Extended Cost02])/[RV Extended Cost21])*100

0016 Else

0017    RV GrossProfit = 0.00

0018 End If

0020 ! Get UDC Description (Obsolete)

0021 ! Get UDC Description (Obsolete)

0022 ! Get UDC Description (Obsolete)

0023 ! Get UDC Description (Obsolete)

0025 RV SRP1 = BC Sales Catalog Section (F4101)(SRP1)

0026 RV SRP2 = BC Sub Section (F4101)(SRP2)

0027 RV SRP3 = BC Sales Category Code 3 (F4101)(SRP3)

0028 RV SRP4 = BC Sales Category Code 4 (F4101)(SRP4)

0029 RV SRP5 = BC Sales Category Code 5 (F4101)(SRP5)

0031 VA rpt_AlternateAddressKey_ALKY = rtrim(ltrim([BC Business Unit (F41021)(MCU)],' '),' ')

0034 VA rpt_NameGiven_GNNM = ""

0035 VA rpt_NameSurname_SRNM = ""

0036 VA rpt_ForHub_AN8 = ""

0037 VA rpt_HubMgr_CMC1 = ""

0038 F0101.Fetch Single

0040 F41001.Fetch Single

0041 F0111.Fetch Single

0043 RV Ownername = concat(concat(rtrim([VA rpt_NameGiven_GNNM],' '),"  "),[VA rpt_NameSurname_SRNM])

0044 VA rpt_NameGiven_GNNM = ""

0045 VA rpt_NameSurname_SRNM = ""

0047 F0101.Fetch Single

0049 F550354.Fetch Single

0050 F0111.Fetch Single

0051 RV HubMgrName = concat(concat(rtrim([VA rpt_NameGiven_GNNM],' '),"  "),[VA rpt_NameSurname_SRNM])

=======================================================================

     SECTION: On Business Unit [GROUP SECTION] (S5)

=======================================================================

     OBJECT:  SECTION

     EVENT:  Do Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0002 VA rpt_MCU_NO_Space = ltrim([PC Business Unit (F0006)(MCU)],' ')

0003 F0101.Fetch Single

0004 F0111.Fetch Single

0005 RV FirstName = rtrim([RV FirstName],'  ')

0006 RV FirstName = concat(concat([RV FirstName],"  "),[RV LastName])

0008 If RV Total of Extended Cost21_OnBu is greater than <Zero> Or RV Total of Extended Cost21_OnBu is greater than <Blank>

0009    RV GrossProfit_OnBU = (([RV Total of Extended Cost21_OnBu]-[RV Total of Extended Cost02_OnBU])/[RV Total of Extended Cost21_OnBu])*100

0010 Else

0011    RV GrossProfit_OnBU = 0.00

0012 End If

=======================================================================

     SECTION: On Company [GROUP SECTION] (S8)

=======================================================================

     OBJECT:  SECTION

     EVENT:  Do Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0001 If RV Total of Extended Cost21_OnComp is greater than <Zero> Or RV Total of Extended Cost21_OnComp is greater than <Blank>

0002    RV GrossProfit_OnComp = (([RV Total of Extended Cost21_OnComp]-[RV Total of Extended Cost02_OnComp])/[RV Total of Extended Cost21_OnComp])*100

0003 Else

0004    RV GrossProfit_OnComp = 0.00

0005 End If

Saturday October 14, 2017  11:22

Convert Row Into Column Dynamic completely PIVOT SQL Server

Need to create 12 month rolling report , user will enter year and Month , need to show report Month (YYYY-MM) on Top from F0911 , which is a detail table in JD Edwards , Amount is in row.
also Year and Month is in Row.


alter proc SP_ActualByAdminRollingAmt
--declare
@frmPN varchar(7) ='2017-08'

as



declare @frmdate datetime =@frmPN+'-01'
declare @TOdate datetime =DATEADD(M,-12, @frmPN+'-01')



--drop table #temp11
--drop table #temp12
--drop table #temp13



select
glaid,
glmcu,
glObj ,glsub,
glsblt,GLSBL,
glan8 ,
glfy,
glpn,
glaa
into #temp11
from mfdb.proddta.f0911 with(nolock)
where glfy in(17,16) and gllt='AA'  and left(globj,2) in('60','61')
 and gldgj>=[dbo].[JDT_GTJ](@TOdate) and gldgj<=[dbo].[JDT_GTJ](@frmdate)
--JDT_GTJ - function to convert calendar date to Julian date
and glco=1 and glaa<>0

--print [dbo].[JDT_GTJ](@frmdate)
--print [dbo].[JDT_GTJ](@TOdate)


select
glmcu,
mcdl01,
glObj Obj,
GmDL01 Obj_Desc,
ltrim(glsblt+ltrim(GLSBL)) Subledger,
DRDL01 Subledger_Desc,
glan8 VendorNo,
abalph VendorName,
glfy,
glpn,
sum(glaa)/100 glaa,
 case when glfy=17 then '2017-' + right('0'+cast(glpn as varchar(2)),2) else '2016-' + right('0'+cast(glpn as varchar(2)),2)  end  Period  into #temp12
 from  #temp11
left outer join proddta.f0101 on aban8=glan8
left outer join proddta.f0901 on gmaid=glaid
left outer join proddta.f0006 on mcmcu=glmcu
LEFT OUTER JOIN PRODCTL.F0005 ON DRSY='55' AND DRRT='SL'  AND LTRIM(DRKY)=LTRIM(GLSBL)
where left(glObj,2) in('60','61')
group by glObj,GmDL01,glsblt+ltrim(GLSBL) ,DRDL01,glan8,abalph,glfy,
glpn,glmcu,mcdl01



----main logic PIVOT

select distinct Period into #temp13 from  #temp12
DECLARE @cols AS NVARCHAR(MAX);

-- need to do following as amount filed which are in Row---

--set @cols='[2016-08],[2016-09],[2016-10],[2016-11],[2016-12], [2017-01],[2017-02],[2017-03],-----[2017-04],[2017-05],[2017-06],[2017-07],[2017-08],[2017-09],[2017-10],[2017-11],[2017-12]'



select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(Period)
                      FROM #temp13
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)')
                        , 1, 1, '');


DECLARE @query AS NVARCHAR(MAX);
set @query=
'SELECT * FROM(SELECT ltrim(glmcu) BU,mcdl01 BU_Desc,Obj, Obj_Desc, Subledger, Subledger_Desc, VendorNo, VendorName ,glaa,[Period]
  FROM #temp12 )t PIVOT (SUM([glaa])
  FOR [Period] IN ( ' + @cols + ' )' +
' ) AS pvt
order by 1,3,5'
execute(@query);
 

Getting Error in JDE Developer tool - Form Interconnect

  During development when I am joining two screen getting this error in JDE Development environment, not able to proceed further in development. 

How to Fix:

In order to fix this error, We can try to deploying full package at FAT machine.  

 

--

Rajeev Kumar
949-378-6094

Currency Validation textbox asp.net

<asp:RegularExpressionValidator ID="rvcheckno" runat="server"

                                                ErrorMessage="Amount receive numbers only!" ControlToValidate="tbamount"

                                                InitialValue="0" ValidationGroup="number">

                                                </asp:RegularExpressionValidator>

Send Email using SmtpClient C# ASP.Net

string MessageBody;

        MailAddress mfrom = new MailAddress("ControlsTeam@xxxxxxxx.com");

        MailAddress mTo = new MailAddress(getemail);

        //MailAddress mcc = new MailAddress(getemail);

        string EmailSubject = "This Control ID:" + ctrlID + "has been Update";

        MailMessage message = new MailMessage();

        message.From = mfrom;

        message.To.Add(mTo);

        //message.CC.Add(mcc);

        if (string.Equals(mfrom.ToString().ToUpper().ToString(), mTo.ToString().ToUpper().ToString()))

        {

            MessageBody = "This control ID" + ctrlID + "has been changed";

            EmailSubject = TextBox1.Text + "Test Email";

        }

        message.Subject = EmailSubject;

        message.Body = "This is the test Email for Control Update"; ;

        message.IsBodyHtml = true;

        SmtpClient client = new SmtpClient("xxxxx.xxxx.xxxx", 25);//server name

        // Credentials are necessary if the server requires the client

        // to authenticate before it will send e-mail on the client's behalf.

        client.UseDefaultCredentials = true;

        message.IsBodyHtml = true;

        client.Send(message);