VB / SQL Query problem

J

Jim in Arizona

I built a ticket system a while back but I never could get the queries quite
right. My queries work as long as I don't use a date range. Here is the code
I use for the query portion (the relevant part):

If ddlReportType.SelectedValue = "Everything" And
ddlQueryTechnician.SelectedValue = "Select All" Then
strSQLCount = "SELECT COUNT(*) FROM TTickets WHERE dtemp
BETWEEN CONVERT(DATETIME, " & _
"'" & bd & "', 110) AND CONVERT(DATETIME,
'" & ed & " 23:59:59', 110)"

strSQL = "SELECT * FROM TTickets WHERE dtemp BETWEEN
CONVERT(DATETIME, '" & bd & "', 110) " & _
"AND CONVERT(DATETIME, '" & ed & " 23:59:59',
110)"

strSQL1 = "SELECT * FROM TNotes"

ElseIf ddlReportType.SelectedValue = "Everything" Then
strSQLCount = "SELECT COUNT(*) FROM TTickets WHERE
assign='" & ddlQueryTechnician.SelectedValue & "' " & _
"AND dtemp BETWEEN CONVERT(DATETIME, '" &
bd & "', 110) AND CONVERT(DATETIME, '" & _
ed & " 23:59:59', 110)"

strSQL = "SELECT * FROM TTickets WHERE assign='" &
ddlQueryTechnician.SelectedValue & "'" & _
" AND dtemp BETWEEN CONVERT(DATETIME, '" & bd &
"', 110) AND CONVERT(DATETIME, '" & _
ed & " 23:59:59', 110) ORDER BY prioritynumber"

strSQL1 = "SELECT * FROM TNotes WHERE FK IN(SELECT PK
FROM TTickets WHERE assign='" & _
ddlQueryTechnician.SelectedValue & "')"

ElseIf ddlQueryTechnician.SelectedValue = "Select All" Then
strSQLCount = "SELECT COUNT(*) FROM TTickets WHERE " &
ddlReportType.SelectedValue & _
" AND dtemp BETWEEN CONVERT(DATETIME, " &
_
"'" & bd & "', 110) AND CONVERT(DATETIME,
'" & ed & " 23:59:59', 110)"

strSQL = "SELECT * FROM TTickets WHERE " &
ddlReportType.SelectedValue & _
" AND dtemp BETWEEN CONVERT(DATETIME,'" & bd &
"', 110) AND CONVERT(DATETIME, '" & _
ed & " 23:59:59', 110) ORDER BY prioritynumber"

strSQL1 = "SELECT * FROM TNotes WHERE FK IN(SELECT PK
FROM TTickets WHERE " & _
ddlReportType.SelectedValue & ")"
Else
strSQLCount = "SELECT COUNT(*) FROM TTickets WHERE " &
ddlReportType.SelectedValue & " AND assign='" & _
ddlQueryTechnician.SelectedValue & "' AND dtemp
BETWEEN CONVERT(DATETIME, " & _
"'" & bd & "', 110) AND CONVERT(DATETIME,
'" & ed & " 23:59:59', 110)"

strSQL = "SELECT * FROM TTickets WHERE " &
ddlReportType.SelectedValue & " AND assign='" & _
ddlQueryTechnician.SelectedValue & "' AND dtemp
BETWEEN CONVERT(DATETIME, " & _
"'" & bd & "', 110) AND CONVERT(DATETIME,
'" & ed & " 23:59:59', 110) ORDER BY prioritynumber"

strSQL1 = "SELECT * FROM TNotes WHERE FK IN(SELECT PK
FROM TTickets WHERE " & _
ddlReportType.SelectedValue & ")"
End If



I can do queries ok as long as I don't use date ranges (I have the same SQL
statements that don't use the date text fields). But, if I use dates, I get
the following error:

This constraint cannot be enabled as not all values have corresponding
parent values.

The Database back end is SQL2000 with two tables (TTickets and TNotes). The
TNotes is for us IT Staff to post a note to a ticket, and is related via
foreign key to the primary key of TTickets.

Any ideas how I would be able to do queries using a date range?

TIA,
Jim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top