1

I'm having a problem when trying to insert parameters into a derived table that is part of my table adapter fill query in a vb.net application.

I'm getting the "Conversion failed when converting date and/or time from character string" when I try to use the query in vb.net through the preview window of the Dataset Designer or using the datatableadapter.fill method in my application. 

SELECT Date, SUM(RegHours) AS RegTotal, SUM(OTHours) AS OTTotal, SUM(VacHours) AS VacTotal, SUM(SickHours) AS SickTotal

FROM (SELECT EmployeeId, Date, RegHours, OTHours, (CASE WHEN TaskId=4 THEN RegHours ELSE NULL END) AS VacHours, (CASE WHEN TaskId=5 THEN RegHours ELSE NULL END) AS SickHours FROM TimeEntry

WHERE (EmployeeId=@EmployeeId) AND (Date>=@StartDate)) AS SubTable

GROUP BY Date

The query works perfectly when I have just the @EmployeeId parameter so the issue is with the date conversion.

The query also works fine if I do just the derived table portion:

SELECT EmployeeId, Date, RegHours, OTHours, (CASE WHEN TaskId=4 THEN RegHours ELSE NULL END) AS VacHours, (CASE WHEN TaskId=5 THEN RegHours ELSE NULL END) AS SickHours 

FROM TimeEntry 

WHERE (EmployeeId=@EmployeeId) AND (Date>=@StartDate)

Any help would be greatly appreciated!

Andrew Gendre
  • 11
  • 2
  • Are the parameters declared as date? How do you fill the parameter value? – Zohar Peled May 13 '15 at 17:34
  • It is filled with the following code, where startdate is a DateTime datatype. `Me.PayrollTimeSummaryTableAdapter.Fill(Me.RRAPPSDataSet.PayrollTimeSummary, 254, startdate)`. The Date column is configured as a Date type in SQL Server. – Andrew Gendre May 13 '15 at 17:56

1 Answers1

0

I was able to solve this problem by adding the VacHours and StatHours as computed columns in the table definition and then selecting without needing a derived table.

SELECT Date, SUM(RegHours) AS TotalReg, SUM(OTHours) AS TotalOT, SUM(VacHours) AS TotalVac, SUM(StatHours) AS TotalStat
FROM TimeEntry
WHERE (EmployeeId = @EmployeeId) AND (Date >= @StartDate) AND (Date <= @EndDate)
GROUP BY Date

I suspect I may still not be filling with the correct data type or casting correctly but this did provide a simple solution for me.

Andrew Gendre
  • 11
  • 2