CODEWITHSUNDEEP
sqlvb.net
indofraiser
indofraiser

Reputation: 1024

Casting two variables

I am looking to cast two variables to a date.

I can get the SQL to work for casting one (first code) but when I want to cast two variables I keep getting errors (see two attempts below).

I have searched for cast double and cast two variables but double comes up with the type double and variable has the same issue. What work should I be searching for or what 'join' do I need to cast two variables.

-Two cast (not working)

 sSQL = "SELECT datestart, DateEnd, runcompleted FROM [tblweeklyreports] where Cast(moduledatestart as date, moduledatecomplete as date) between '" & vDateStart & "' and '" & vDateEnd & "'"

and

 sSQL = "SELECT datestart, DateEnd, runcompleted FROM [tblweeklyreports] where Cast(moduledatestart as date),(moduledatecomplete as date) between '" & vDateStart & "' and '" & vDateEnd & "'"

It must of come up before (a link is all I need, but I can not seem to search for the right wording!)

------------------- Amended version as per discussion -------------- 

Dim cn As SqlConnection = New SqlConnection()
        Dim cmd As SqlCommand = New SqlCommand()
        Dim dr As SqlDataReader
        Dim vRunCompleted As String = ""
        Dim vDateStartExist As String = ""
        cn.ConnectionString = ConfigurationManager.ConnectionStrings("mySQLConnectionString").ConnectionString
        cmd.Connection = cn
        'Open the connection to the database
        cn.Open()

Dim vDateStartDate As Date = vDateStart Dim vDateEndDate As Date = vDateEnd

        cmd.CommandText = "SELECT * FROM [tblresults] where moduledatestart between @from and @to"
        cmd.Parameters.AddWithValue("@from", vDateStart)
        cmd.Parameters.AddWithValue("@to", vDateEnd)

        Dim str As New StringBuilder
        Dim vdrOkay As String
        str.Append(Chr(34) & " Employee Id" & Chr(34) & "," & "Forename" & "," & Chr(34) & "Surname" & Chr(34) & "," & "Control 1" & "," & Chr(34) & "Control 2" & Chr(34) & "," & "Full code" & "," & "Class Name" & "," & "Year" & ",")
        str.Append(Chr(34) & " Number" & Chr(34) & "," & "Class Start Date" & "," & Chr(34) & "Class End Date" & Chr(34) & "," & "Course Type" & "," & Chr(34) & "Provider Type" & Chr(34) & "," & "Provider" & "," & "Venue" & "," & "Evaluation" & "," & "Duration Hrs" & ",")
        str.Replace(",", vbNewLine, str.Length - 1, 1) ' go to next line

        dr = cmd.ExecuteReader()

        If (dr.HasRows = True) Then

            Label1.Text = "running"

            While (dr.Read())

                vdrOkay = Trim(StrConv(dr("EmployeeID"), VbStrConv.Uppercase))
                str.Append("=" & Chr(34) & vdrOkay & Chr(34) & ",")

 End While

            Dim vFileName As String = Replace(vDateStart, "/", "_") & "_" & Replace(vDateEnd, "/", "_")
            My.Computer.FileSystem.WriteAllText(("C:\sites\Examples\WeeklyReport\" & "ELearning_Report_" & vFileName & ".csv"), str.ToString, False)

        Else
 Label1.Text = "did not run"
        End If

Upvotes: 0

Views: 155

Answers (1)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56727

There would be no need to cast anything if you went the recommended way of using parameterized queries, which also has the nice additional effect of avoiding SQL injection.

In your case this might look like this:

cmd.CommandText = "SELECT datestart, DateEnd, runcompleted FROM [tblweeklyreports] where datestart between @from and @to"
cmd.Parameters.AddWithValue("@from", vDateStart)
cmd.Parameters.AddWithValue("@to", vDateEnd)

The problem lies in the way you construct your WHERE clause. You can not compare two fields in one go as you're trying. You need to write something like this:

SELECT 
    datestart, 
    DateEnd, 
    runcompleted 
FROM [tblweeklyreports] 
where 
    moduledatestart between @from and @to and
    moduledatecomplete between @from and @to

Then use SQL parameters as above.

Upvotes: 4

Related Questions