mysql - 为 Crystal Reports SQL 命令变量赋值

Question

我在 Crystal Report Database Expert 上放置了一个 SQLCommand，以便我可以根据 SQL 查询的结果创建自定义报告。

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"

我的示例 VB.Net 代码

   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)

如何使用 VB.net 填充 (“@DateSelected”) SQL 变量？

Answer 1

向您的命令添加一个参数。对于 SQL 来说是这样的:

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

我确信 MySQL 会非常相似

这可能有帮助:Using DateTime in a SqlParameter for Stored Procedure, format error