如何从vb.net传递参数

小编典典

如何从vb.net传递参数

sql

我有一个程序，它将每晚自动运行，运行查询和发送电子邮件结果。在我的程序中，我正在调用函数作为查询的一部分…我想要的是将程序运行的日期作为参数传递。（@startdate和@enddate）@startdate始终是00:00:00的“今天”日期，而enddate始终是23:59:59的“今天的日期”。因此，例如。如果该程序今晚运行，它将以1/31/13作为日期。明天，它将传递2/1/13作为日期，下一个日期传递2/2/13，依此类推。如果我可以在很好的查询级别执行此操作，则…下面是我的代码：

SELECT
  SUM(QTY) AS Discounts
FROM
  dbo.fFinancialDataFull('Date Range Report', @startdate , @enddate, '1', '1', 'ALL', 'ALL', 'ALL', 'ALL', '1', '1', '1', '1', '1') AS fFinancialDataFull_1
WHERE
  (ReportCategoryID = 62)) AS unlimitedtbl

阅读 244

2021-03-17

共1个答案

小编典典

//这些是日期变量..如果您需要单独使用它们

Dim TodayDt As DateTime = DateTime.Today
Dim Tomorrow As DateTime = DateTime.Today.AddDays(1)
Dim TodayEnd as DateTime
TodayEnd = Tomorrow.AddSeconds(-1)

//这是在SQL Server中执行的SQL命令

  SELECT
  SUM(QTY) AS Discounts
FROM
  dbo.fFinancialDataFull('Date Range Report', startdate , enddate, '1', '1', 'ALL', 'ALL', 'ALL', 'ALL', '1', '1', '1', '1', '1') AS fFinancialDataFull_1
WHERE ReportCategoryID = 62
 AND startdate = TodayDt
 AND enddate = TodayEnd AS unlimitedtbl

//这是您需要编写的功能，以使相同的SQL在VB上运行

Public Function GetValueByDates() As String
    Dim TodayDt As DateTime = DateTime.Today
    Dim Tomorrow As DateTime = DateTime.Today.AddDays(1)
    Dim TodayEnd as DateTime
    TodayEnd = Tomorrow.AddSeconds(-1)
    Dim ReportCategoryID = 62

    Dim sql As String = "       SELECT
      SUM(QTY) AS Discounts
    FROM
      dbo.fFinancialDataFull('Date Range Report', startdate , enddate, '1', '1', 'ALL', 'ALL', 'ALL', 'ALL', '1', '1', '1', '1', '1') AS fFinancialDataFull_1
    WHERE ReportCategoryID = @ReportCategoryID
     AND startdate = @TodayDt
     AND enddate = @TodayEnd AS unlimitedtbl"

    Using cn As New SqlConnection("Your connection string here"), _
        cmd As New SqlCommand(sql, cn)

        cmd.Parameters.Add("@TodayDt", SqlDbTypes.DateTime).Value = TodayDt
        cmd.Parameters.Add("@TodayEnd", SqlDbTypes.DateTime).Value = TodayEnd
        cmd.Parameters.Add("@ReportCategoryID", SqlDbTypes.int).Value = ReportCategoryID 
     Return cmd.ExecuteScalar().ToString()
    End Using
End Function

2021-03-17