CODEWITHSUNDEEP
sqlvb.net
finjay
finjay

Reputation: 15

SQL passing date as parameter?

I'm pretty new to coding and SQL. I have the following (part of) code, which works, but dates should passed as parameter instead. How do i do that?

sqlStr = "DECLARE @MYDATE DATETIME, @MYDATE2 DATETIME"
sqlStr &= " SET @MYDATE = '" & fromDate.ToString("MM-dd-yyyy") & "'" & ""
sqlStr &= " SET @MYDATE2 = '" & toDate.ToString("MM-dd-yyyy") & "'" & ""

Dim da As New SqlDataAdapter(sqlStr, conn)
ds = New DataSet
da.Fill(ds, "DevLog")
da.Dispose()
Return ds

I need to do that .ToString operation, because dates coming from system are xx.xx.xxxx (fi-FI) and not xx-xx-xxxx (en-US) as SQL want's them (damn americans... :)).

So, I want to pass those dates as SQL parameter, like @fromDate = fromDate, but as I am new to coding and SQL in general, I need help. Where and how do I insert??

command.parameters.Add(@fromDate, DATETIME)

command.parameters.Add(@toDate, DATETIME)

EDIT: Sorry, I made a mistake. Actually what I ment to say was:

command.parameters.Add("@toDate", SqlDbType.DateTime).Value = toDate

I should somehow pass fromDate and toDate as correct DATETIME parameters form in SQL string (@fromdate and @toDate) and not the way I'm doing it now... as ToString conversion.

@Saagar Elias Jacky

sqlStr = "DECLARE @MYDATE DATETIME, @MYDATE2 DATETIME"
sqlStr &= " SET @MYDATE = '" & fromDate.ToString("MM-dd-yyyy") & "'" & ""
sqlStr &= " SET @MYDATE2 = '" & toDate.ToString("MM-dd-yyyy") & "'" & ""
sqlStr &= " SELECT FixedDate, persons.FirstName, persons.lastName, TimeStampRecord, HoursSpent, HoursSpentRow, HoursSpentType"
sqlStr &= " FROM DevLog"
sqlStr &= " INNER JOIN Persons ON DevLog.OwnerUserID=Persons.UserID"
sqlStr &= " WHERE TimeStampRecord=1"
sqlStr &= " AND FixedDate BETWEEN @MYDATE AND @MYDATE2"

Upvotes: 0

Views: 3366

Answers (1)

Thomas Krojer
Thomas Krojer

Reputation: 1018

sqlStr = "SELECT * FROM DevLog "
sqlStr &= " INNER JOIN Persons ON DevLog.OwnerUserID=Persons.UserID"
sqlStr &= " WHERE TimeStampRecord = 1"
sqlStr &= " AND FixedDate BETWEEN @MYDATE AND @MYDATE2"

Dim ds As DataSet       

Using sqlComm As New SqlCommand(sqlStmt, sqlConn)
    sqlComm.Parameters.AddWithValue("@fromDate", fromDate)
    sqlComm.Parameters.AddWithValue("@toDate", toDate)

    Dim da As New SqlDataAdapter(sqlStmt, sqlConn)
    ds = New DataSet()
    da.Fill(ds, "DevLog")
    da.Dispose()

End Using

Not tested, but hope it helps

Upvotes: 1

Related Questions