How To Pass A Parameter From Vb.net

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 th

Solution 1:

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

  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
WHEREReportCategoryID=62ANDstartdate= TodayDt
 ANDenddate= TodayEnd AS unlimitedtbl

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

PublicFunctionGetValueByDates() AsStringDimTodayDtAsDateTime = DateTime.TodayDimTomorrowAsDateTime = DateTime.Today.AddDays(1)
    DimTodayEndasDateTimeTodayEnd = Tomorrow.AddSeconds(-1)
    DimReportCategoryID = 62

    DimsqlAsString = "       SELECTSUM(QTY) ASDiscountsFROMdbo.fFinancialDataFull('Date Range Report', startdate , enddate, '1', '1', 'ALL', 'ALL', 'ALL', 'ALL', '1', '1', '1', '1', '1') ASfFinancialDataFull_1WHEREReportCategoryID = @ReportCategoryIDANDstartdate = @TodayDtANDenddate = @TodayEndASunlimitedtbl"

    UsingcnAsNewSqlConnection("Your connection string here"), _cmdAsNewSqlCommand(sql, cn)

        cmd.Parameters.Add("@TodayDt", SqlDbTypes.DateTime).Value = TodayDtcmd.Parameters.Add("@TodayEnd", SqlDbTypes.DateTime).Value = TodayEndcmd.Parameters.Add("@ReportCategoryID", SqlDbTypes.int).Value = ReportCategoryIDReturncmd.ExecuteScalar().ToString()
    EndUsingEndFunction

Solution 2:

Try This :

Dim StartDate asstring = DateTime.Today.ToString("yyyy/MM/dd") & " 00:00:00"Dim EndDate asstring = 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")

Solution 3:

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:

Dim sqlcmd AsNew 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 AsObject = 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() asfloat))as datetime) -- truncate the time partset@enddate= dateadd(S, -1, dateadd(d, 1, @startdate)) -- add 1 day, subtract 1 minute from today