Overpunch characters vb.net Function for Money & decimal

 Private Function overpunchDec(def As String) As Decimal

        Dim Intvalint As Decimal

        Dim strval

        Dim strvalreal

        Dim strover = def.Trim().Substring((Len(def) - 1), 1)

        strvalreal = def.Trim().Substring(0, (Len(def) - 1))

        Dim strovernum As String

        'Dim strsignI As Decimal = 1 / 100

        'Dim strsignD As Decimal = -1 / 100

        Dim strsign As Decimal

        ''decimal

        Select Case strover

            Case "{"

                strovernum = "0"

                strsign = 1 / 100

            Case "A"

                strovernum = "1"

                strsign = 1 / 100

            Case "B"

                strovernum = "2"

                strsign = 1 / 100

            Case "C"

                strovernum = "3"

                strsign = 1 / 100

            Case "D"

                strovernum = "4"

                strsign = 1 / 100

            Case "E"

                strovernum = "5"

                strsign = 1 / 100

            Case "F"

                strovernum = "6"

                strsign = 1 / 100

            Case "G"

                strovernum = "7"

                strsign = 1 / 100

            Case "H"

                strovernum = "8"

                strsign = 1 / 100

            Case "I"

                strovernum = "9"

                strsign = 1 / 100

            Case "}"

                strovernum = "0"

                strsign = -1 / 100

            Case "J"

                strovernum = "1"

                strsign = -1 / 100

            Case "K"

                strovernum = "2"

                strsign = -1 / 100

            Case "L"

                strovernum = "3"

                strsign = -1 / 100

            Case "M"

                strovernum = "4"

                strsign = -1 / 100

            Case "N"

                strovernum = "5"

                strsign = -1 / 100

            Case "0"

                strovernum = "6"

                strsign = -1 / 100

            Case "P"

                strovernum = "7"

                strsign = -1 / 100

            Case "N"

                strovernum = "8"

                strsign = -1 / 100

            Case "N"

                strovernum = "9"

                strsign = -1 / 100

        End Select

        strval = strvalreal & strovernum

        Intvalint = Convert.ToInt32(strval) * strsign

        Return Intvalint

    End Function

VB.NET Read/Parse text file and Insert Data into SQL Server Table

VB.NET Read text file and Insert Data into SQL Server Table

Dim FILE_NAME As String = "c:/test/FileToUpload_20230102.013721.txt"

        Dim TextLine As String

        If System.IO.File.Exists(FILE_NAME) = True Then

            Dim objReader As New System.IO.StreamReader(FILE_NAME)

            Dim Str1

            Dim Str2

            Dim Str3

            Dim Str4

            Dim lineCount As Integer 'lines read so far in file

            Do While objReader.Peek() <> -1

                '                TextLine =   objReader.ReadLine() 

                TextLine = objReader.ReadLine()

                Str1 = TextLine.Substring(0, 2)

                If Str1 = "DE" Then

                    Str2 = TextLine.Substring(3, 1)

                    Str3 = TextLine.Substring(4, 3)

                    Str4 = TextLine.Substring(5, 10)

                    ' insert data into sql table

                    Dim query As String = "INSERT INTO  DBO.LoaddataPharmacy_Pal([RECORD TYPE],[RECORD INDICATOR],[ELIGIBLE COVERAGE CODE],[USER BENEFIT ID]) "

                    query = query & " VALUES ('" & Str1 & "' , '" & Str2 & "','" & Str3 & "','" & Str4 & "')"

                    Dim cmd As SqlCommand = New SqlCommand(query, _cn)

                    'cmd.Parameters.AddWithValue("@ROLEID", txtroleId.Text)

                    _cn.Open()

                    cmd.ExecuteNonQuery()

                    _cn.Close()

                End If

                Str1 = ""

                lineCount = lineCount + 1 'increment lineCount

           Loop

            ' Textbox1.Text = TextLine

        Else

            MessageBox.Show("File Does Not Exist")

        End If

Read & Write Parquet file using Databrick and Spark

Save Excel Without dropping leading zeros

 https://youtu.be/O_Fy308BlEg

#Excel #SQL-Server

PowerBI run SP in DirectQuery

When I try to run a Stored Procedure in PowerBI getting this error 

Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.

You may need to configure SQL Server , but after all setup , you have to do following

Create the SP, and use in OPENROWSET as following , test this is in SSMS

SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')

If this is working fine create a view  as following:-

Create view [dbo].[VTest_dataTB_Delthis] as

 SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')

 

use this in Power BI as following :

Ad Hoc Distributed Queries turned off as part of the security configuration

 Msg 15281, Level 16, State 1, Procedure PBIView_encrypted_Test1, Line 6 [Batch Start Line 0]

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Run Following-

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

GO

REBUILD indexes and update Stat SQL Server

Sometime when you load data, we are not able to get fast access using application. In order to fix this do following:- 

 ALTER INDEX ALL ON TBL_PlanBUDGET REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, FILLFACTOR = 80)

Update STATISTICS TBL_PlanBUDGET

GRANT Permission SQL Server

 GRANT EXECUTE, SELECT, UPDATE, INSERT, DELETE ON SCHEMA::ABC_EXT TO [MMC\JDEDEV]

Convert From date and to date in Julian , JD Edwards in VBA

 

    Dim FrmDateImp

    Dim ToDateImp

    Dim tmpToDateImp

 

   

    FrmDateImp = Right(STR(PERIOD_BEG), 2) & "/01/" & Mid(str(PERIOD_BEG), 2, 4)

    tmpToDateImp = Right(STR(PERIOD_END), 2) & "/01/" & Mid(str(PERIOD_END), 2, 4)

    ToDateImp = DateSerial(YEAR(tmpToDateImp), Month(tmpToDateImp) + 1, 0)

 

   

    Dim FrmDate_DGJ As Long

    Dim ToDate_DGJ As Long

  FrmDate_DGJ = ((DatePart("YYYY", FrmDateImp) - 1900) * 1000) + DatePart("y", FrmDateImp)

ToDate_DGJ = ((DatePart("YYYY", ToDateImp) - 1900) * 1000) + DatePart("y", ToDateImp)

QryDateSummaryStr = "GLDGJ BETWEEN '" & FrmDate_DGJ & "' and '" & ToDate_DGJ & "'"

Change Date Format Excel Formula

 Change date format excel formula, this is good for developer too, who wants to create SQL statement from excel cell.

=TEXT(B3,"mm/dd/yyyy")

=TEXT(B3,"mm-dd-yyyy")

Dynamic SQL table columns for dates in Aging report, SQL Server

 

How to create Dynamic SQL table columns for dates in Aging report JD Edwards, SQL Server 

DECLARE @SQL_STR VARCHAR(8000)

DECLARE @DueDate VARCHAR(80)

DECLARE @DueDate14 VARCHAR(80)

DECLARE @DueDate28 VARCHAR(80)

DECLARE @DueDateAfter28 VARCHAR(80)

SET @DueDate =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+1, 101)+'],'

SET @DueDate14 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+14, 101)+'],'

SET @DueDate28 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'

SET @DueDateAfter28 =  '['+'Due After ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'

SET @SQL_STR = 

'SELECT RPAN8 AS Supplier_Number, sup.ABALPH AS Supplier_name, 

RPPST AS Pay_Stat, RPPYIN AS Pay_Inst, RPAN8 AS Payee_Number, PR.ABALPH AS Payee_Name,

RPDCT AS Doc_Type, RPDOC AS Doc_Number, RPSFX AS ITM, RPKCO AS CO, RPVINV AS Invoice_Number,

[dbo].[JDT_JTG](RPDIVJ) AS Invoice_Date,

[dbo].[JDT_JTG](RPDDJ) AS Due_Date,

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE(), 101) and  CONVERT(VARCHAR(10), GETDATE()+1, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+2, 101) and  CONVERT(VARCHAR(10), GETDATE()+14, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate14 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+15, 101) and  CONVERT(VARCHAR(10), GETDATE()+28, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) > CONVERT(VARCHAR(10), GETDATE()+28, 101) 

     THEN RPAG/100

ELSE 0 END AS '+ @DueDateAfter28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) >= CONVERT(VARCHAR(10), GETDATE(), 101) 

     THEN RPAG/100

ELSE 0 END AS Total_Due

FROM MFDB.PRODDTA.F0411

LEFT OUTER JOIN MFDB.PRODDTA.F0101 Sup ON Sup.ABAN8 = RPAN8

LEFT OUTER JOIN MFDB.PRODDTA.F0101 PR ON PR.ABAN8 = RPAN8

WHERE RPFY = 22 

--AND rpan8 = 642564 

AND RPPST = ''A'';

'

--PRINT (@SQL_STR)

EXEC (@SQL_STR)

Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)

Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)

   Dim myDate As Date

    myDate = "07/23/2022"

    Debug.Print (((DatePart("YYYY", myDate)) - 1900) * 1000) + DatePart("y", myDate)

find a table column in stored procedure in SQL Server

 SELECT  distinct OBJECT_NAME (id)

FROM syscomments

WHERE text LIKE '%GLMCU%'

GO

DROP INDEX SQL Server

 DROP INDEX SQL Server if exists

DROP INDEX IF EXISTS IDX_tbl_XXXData_RR ON tbl_XXXData_RR;

How to load Sql queries in SQL Server table

 Sometimes we need to load SQL code in a sql table and run that code in any application. you can load long SQL code using following:-

1. make your query in one line.

2.Open an excel and add a column name in excel, paste that query in a excel cell.

3. Load this excel in SQL server using sql import wizard.

4.  Create an insert statement to load in your main table with all the fields.

INSERT INTO table_sql(sqlname,sqldesc,date,user,sqlcode)

Select sqlname,sqldesc,date,user, Column01 sqlcode from YourUpoadedTable