Assigning a value on Crystal Reports SQL Command Variable

Question

I had put an SQLCommand on my Crystal Report Database Expert so that I can create a custom Report based on the result of my SQL Query.

Here is the Sample SQL Code:
"SELECT Inventory.ItemID, Inventory.Item, 
 SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) < @DateSelected THEN Inventory.Quantity ELSE 0 END) - 
 SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) < @DateSelected THEN consumeditemmonitoring.Quantity ELSE 0 end) - 
 SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate)< @DateSelected THEN damagedinventory.Quantity ELSE 0 end) AS 'PrevBalance',

 SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) = @DateSelected THEN Inventory.Quantity else 0 END) AS 'DeliveredToday',

 SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate) = @DateSelected THEN damagedinventory.Quantity ELSE 0 END) AS 'DamagedToday',

 SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) = @DateSelected THEN consumeditemmonitoring.Quantity ELSE 0 END) AS 'ConsumedToday',

 SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) < @DateSelected THEN Inventory.Quantity else 0 end)-
 SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) < @DateSelected THEN consumeditemmonitoring.Quantity ELSE 0 END)-
 SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate) < @DateSelected THEN damagedinventory.Quantity ELSE 0 END)-
 SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) = @DateSelected THEN consumeditemmonitoring.Quantity ELSE 0 END)-
 SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate) = @DateSelected THEN damagedinventory.Quantity ELSE 0 END)+
 SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) = @DateSelected then Inventory.Quantity ELSE 0 end) AS 'Total Balance' 

 FROM Inventory 
 LEFT OUTER JOIN consumeditemmonitoring ON consumeditemmonitoring.ID = Inventory.ID 
 LEFT OUTER JOIN damagedinventory ON damagedinventory.ID = Inventory.ID
 GROUP BY  Inventory.ItemID"

My sample VB.Net Code

   If MysqlConn.State = ConnectionState.Open Then
        MysqlConn.Close()
    End If
    MysqlConn.Open()

    Dim PrintingMyDataAdapter As New MySqlDataAdapter
    Dim PrintingMyDataTable As New DataTable
    Dim PrintingDDataQuery As String

    PrintingDDataQuery ="[SQLCommand]"
    Command = New MySqlCommand(PrintingDDataQuery, MysqlConn)
    PrintingMyDataAdapter.SelectCommand = Command
    PrintingMyDataAdapter.Fill(PrintingMyDataTable)

    Dim objRpt As New CustomerOrder
    objRpt.SetDataSource(PrintingMyDataTable)

How can I be able to Fill the ("@DateSelected") SQL variable using VB.net?

score 0 · Accepted Answer · answered Jul 22 '19 at 07:50

Add a parameter to your command. For SQL it would be this:

SqlParameter parameter = Command.Parameters.Add("@DateSelected",System.Data.SqlDbType.DateTime);
parameter.Value = DateTime.Now;

I'm sure it will be very similar for MySQL

This may help: Using DateTime in a SqlParameter for Stored Procedure, format error

Assigning a value on Crystal Reports SQL Command Variable

1 Answers1