如何从vb.net传递参数 [英] How to pass a parameter from vb.net
问题描述
我有一个程序,它将每晚自动运行,运行查询并通过电子邮件发送结果.在我的程序中,我正在调用函数作为查询的一部分...我想要的是将程序运行的日期作为参数传递. (@startdate和@enddate)@startdate始终是00:00:00的今天"日期,而enddate始终是23:59:59的今天的日期".因此,例如.如果该程序今晚运行,它将以1/31/13作为日期.明天,它将传递2/1/13作为日期,下一个日期传递2/2/13,依此类推.如果我也可以在查询级别执行此操作,如下所示:
I have a Program that will auto run each night, run a query, and email results. In my program I am calling a function as part of the query... What i'd like to is pass the date the program is run as the parameter. (@startdate and @enddate) @startdate will always be "today's" date at 00:00:00 and enddate will always be "Todays date" at 23:59:59. So for example. If the program was run tonight, it would pass 1/31/13 as the date. Tomorrow, it would pass 2/1/13 as the date, the next date 2/2/13, etc. If I can do this at the query level that is fine as well... Below is my code:
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
推荐答案
//这些是日期变量..如果您需要单独使用它们
//These are the date variables.. if u need them seperately
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命令
//This is the SQL Command that executes in SQL Server
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上运行
//This is the function u need to write to make the same SQL run on 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
这篇关于如何从vb.net传递参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!