Click here to Skip to main content
15,305,969 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
HI I AM WORKING ON VB.NET PROJECT IN VISUAL STUDIO 2010 IN THIS PROJECT I WANT TO CALCULATE SOME DATA TO SHOW AS OPENING BALANCE IN MY RDLC REPORT SO I MADE CUSTOM ASSEMBLY AND PASS IT TO MY RDLC REPORT BUT IT IS SHOWING #ERROR WHEN RDLC REPORT POPULATE.

I ALSO USE THAT ASSEMBLY IN MY WINDOW FORM WHERE ITS WORKING LIKE CHARM . BUT IT IS NOT WORKINNG IN RDLC REPORT MY WHOLE CODE ARE AS FOLLOWING
THIS IS MY ASSEMBLY CODE.

What I have tried:

Imports System.Data.OleDb
Imports System.Data
Imports System
Imports System.Reflection
Imports System.Runtime.InteropServices
Imports System.Security
<Assembly: AllowPartiallyTrustedCallers()> 
Public Class Class1
    Shared PRO As String
    Shared CONNSTRING As String
    Shared COMMAND As String
    Shared MYCONNECTION As OleDbConnection = New OleDbConnection
    Shared DT As DataTable = New DataTable
    Shared VN As String
    Public Shared Function OPB(ByVal date1 As Date) As String

        Dim dt11 As New DataTable
        Dim tarikh As Date = date1


        Dim dtdate2 As DateTime = DateTime.Parse(tarikh)
        Dim dtdate1 As DateTime = "01/01/2000"
        Dim DATA As Decimal



        PRO = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\rojmel.accdb;Jet OLEDB:Database Password=rojmel"
        CONNSTRING = PRO
        MYCONNECTION.ConnectionString = CONNSTRING
        If MYCONNECTION.State = ConnectionState.Closed Then
            MYCONNECTION.Open()

            COMMAND = "select IIf(IsNull(sum([rojmel]![jrs])),0,(sum([rojmel]![jrs]))) AS INCOME from rojmel  where date1 between #" & _
                                                                  dtdate1.ToString("MM/dd/yyyy") & "# and #" & _
                                                                  dtdate2.ToString("MM/dd/yyyy") & "# AND  ([group] = 'cust' OR [group] = 'in') UNION ALL select IIf(IsNull(sum([rojmel]![urs])),0,(sum([rojmel]![jrs]))) AS INCOME from rojmel  where date1 between #" & _
                                                                  dtdate1.ToString("MM/dd/yyyy") & "# and #" & _
                                                                  dtdate2.ToString("MM/dd/yyyy") & "# AND  ([group] = 'sell' OR [group] = 'out')"

            Dim DTA As OleDbDataAdapter = New OleDbDataAdapter(COMMAND, MYCONNECTION)

            DTA.Fill(DT)

            If DT.Rows.Count = Nothing Then
            Else




                Dim INCOME As Decimal
                Dim EXPENSE As Decimal

                INCOME = DT.Rows(0)(0)
                EXPENSE = DT.Rows(1)(0)


                Dim SP As String

                SP = INCOME - EXPENSE


                DATA = SP
            End If
        End If
        Return DATA
        MYCONNECTION.Close()

    End Function

End Class
Posted
Updated 25-Nov-21 20:04pm
Comments
OriginalGriff 26-Nov-21 1:56am
   
DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalization if you want to be taken seriously.

1 solution

In addition to the advice above - which I believe merits repetition since it's clearly part of your whole life as well as your writing style: DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalization if you want to be taken seriously.

See how annoying that could be? DON'T SHOUT. USING ALL CAPITALS IS CONSIDERED SHOUTING ON THE INTERNET, AND RUDE (USING ALL LOWER CASE IS CONSIDERED CHILDISH). USE PROPER CAPITALIZATION IF YOU WANT TO BE TAKEN SERIOUSLY.
It's rude. So stop doing it. Seriously.

The first thing to do is to fix your whole app, not just that fragment. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
In this case, the use of concatenation could easily be causing your problem, as you are forcing a date format on a system which has to interpret that string-based date as a DATETIME object - and that means making assumptions about the format from the system settings which may not match the format you have decided is "right". If it doesn't match, the DB will reject the SELECT and that may be the problem you have seen.

Fix it through your whole app, and the problem may go away.

Oh, and by the way: STOP SHOUTING.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900