SQL 如何从 vb.net 传递参数

How to pass a parameter from vb.net

提问by Shmewnix

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:

我有一个程序，每晚都会自动运行，运行查询并通过电子邮件发送结果。在我的程序中，我正在调用一个函数作为查询的一部分......我想要的是传递程序运行的日期作为参数。（@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

回答by user1974729

//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)

//This is the SQL Command that executes in SQL Server

//这是在SQL Server中执行的SQL Command

  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

//This is the function u need to write to make the same SQL run on VB

//这是你需要编写的函数，以在VB上运行相同的SQL

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

回答by Marcus Vinicius

To call this function in VB.Net code, you should place the function call in a Stored Procedure and call this Procedure from VB.NET, passing the parameters as follows:

要在 VB.Net 代码中调用这个函数，你应该把函数调用放在一个存储过程中，并从 VB.NET 调用这个过程，传递参数如下：

 Dim sqlcmd As New SqlClient.SqlCommand()
 sqlcmd.CommandType = CommandType.StoredProcedure
 sqlcmd.CommandText = "PROCEDURE_NAME"
 sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@startdate", DateTime.Now.Date))
 sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@enddate", DateTime.Now.Date.AddDays(1).AddSeconds(-1)));
 Dim obj As Object = sqlcmd.ExecuteScalar()

If you can't create a Stored Procedure, you can do at the query level:

如果您无法创建存储过程，则可以在查询级别执行：

declare @startdate datetime
declare @enddate datetime

set @startdate = cast(floor(cast(getdate() as float))as datetime) -- truncate the time part
set @enddate = dateadd(S, -1, dateadd(d, 1, @startdate)) -- add 1 day, subtract 1 minute from today

回答by Abdusalam Ben Haj

Try This :

尝试这个 ：

Dim StartDate as string = DateTime.Today.ToString("yyyy/MM/dd") & " 00:00:00"
Dim EndDate as string = DateTime.Today.ToString("yyyy/MM/dd") & " 23:59:59"

I don't know what database server you are using. Try different date format if that didn't work, like DateTime.Today.ToString("yyyy-MM-dd")

不知道你用的是什么数据库服务器。如果不起作用，请尝试不同的日期格式，例如DateTime.Today.ToString("yyyy-MM-dd")