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?
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