The multi-part identifier "" could not be bound

Hi All,

Im having really big problems with a WHERE statment in the below query. Query is in classic ASP and the table is in MS SQL 2005

I get this error on line 5.

Microsoft OLE DB Provider for SQL Server error '80040e14'

The multi-part identifier "dbo.FLEET_VEHICLES.DATE_ADDED" could not be bound.

/fleet_schemes/reviewfleetvehicles_new.asp, line 5

Any ideas?

Marc
msql = "SELECT CONVERT(VARCHAR(10),[dbo.FLEET_VEHICLES].[DATE_ADDED],103) AS DATE_ADDED " & _
	"FROM dbo.FLEET_VEHICLES " & _
	"WHERE (((CONVERT(VARCHAR(10),[dbo.FLEET_VEHICLES].[DATE_ADDED],103))=" & Date()-7 & "))"
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open msql, objConn, adOpenDynamic, adCmdTable
	
	If Not objRS.EOF Then
	  Response.Write(objRS.Fields("DATE_ADDED"))
	End If
	
	objRS.Close
	Set objRS = Nothing
	objConn.Close
	Set objConn = Nothing

Open in new window

marc_butlerAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your initial question used = in the condition... your side needs indeed >=:
last 7 days:
msql = "SELECT CONVERT(VARCHAR(10),f.[DATE_ADDED],103) AS DATE_ADDED " & _
      "FROM dbo.FLEET_VEHICLES f " & _
      "WHERE f.[DATE_ADDED] >= DATEADD(day, -7, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)) " &
      Set objRS = Server.CreateObject("ADODB.Recordset")
 
 
last 30 days:
msql = "SELECT CONVERT(VARCHAR(10),f.[DATE_ADDED],103) AS DATE_ADDED " & _
      "FROM dbo.FLEET_VEHICLES f " & _
      "WHERE f.[DATE_ADDED] >= DATEADD(day, -30, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)) " &
      Set objRS = Server.CreateObject("ADODB.Recordset")
 
 
 
last 90 days:
 
msql = "SELECT CONVERT(VARCHAR(10),f.[DATE_ADDED],103) AS DATE_ADDED " & _
      "FROM dbo.FLEET_VEHICLES f " & _
      "WHERE f.[DATE_ADDED] >= DATEADD(day, -90, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)) " &
      Set objRS = Server.CreateObject("ADODB.Recordset")

Open in new window

0
 
chapmandewCommented:
"SELECT CONVERT(VARCHAR(10),[dbo.FLEET_VEHICLES].[DATE_ADDED],103) AS DATE_ADDED " & _
      "FROM dbo.FLEET_VEHICLES " & _
      "WHERE (((CONVERT(VARCHAR(10),dbo.FLEET_VEHICLES.[DATE_ADDED],103))=" & Date()-7 & "))"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you put the [] wrong:
msql = "SELECT CONVERT(VARCHAR(10),[dbo].[FLEET_VEHICLES].[DATE_ADDED],103) AS DATE_ADDED " & _
      "FROM dbo.FLEET_VEHICLES " & _
      "WHERE (((CONVERT(VARCHAR(10),[dbo].[FLEET_VEHICLES].[DATE_ADDED],103))=" & Date()-7 & "))"

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
tip: use table aliases:
msql = "SELECT CONVERT(VARCHAR(10), f.[DATE_ADDED],103) AS DATE_ADDED " & _
      "FROM dbo.FLEET_VEHICLES f " & _
      "WHERE (((CONVERT(VARCHAR(10),f.[DATE_ADDED],103))=" & Date()-7 & "))"

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
finally: for the date operations, you could rely on the sql server functions.
assuming that your date_added has time also:
msql = "SELECT CONVERT(VARCHAR(10),f.[DATE_ADDED],103) AS DATE_ADDED " & _
      "FROM dbo.FLEET_VEHICLES f " & _
      "WHERE f.[DATE_ADDED] >= DATEADD(day, -7, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)) " &
      "  AND f.[DATE_ADDED] < DATEADD(day, -6, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)) "
      Set objRS = Server.CreateObject("ADODB.Recordset")

Open in new window

0
 
marc_butlerAuthor Commented:
Hi Angellll,

Sorry about that I fixed the problem by doing it the old fashion way. Please see attached;

But I have given your code a go and can't get it to work. But never mind I will just do it the old fashion way.

Cheers Marc
	  If strDatePeriod = "7days" Then
		strStartDate = Date()-7
	    ElseIf strDatePeriod = "30days" Then
		  strStartDate = Date()-30
		  ElseIf strDatePeriod = "90days" Then
		    strStartDate = Date()-90
	  End If
	  strStartDay = day(strStartDate)
	  strStartMonth = month(strStartDate)
	  strStartYear = year(strStartDate)
	  strStartDate = strStartMonth & "/" & strStartDay & "/" & strStartYear
	  strEndDay = day(Date())
	  strEndMonth = month(Date())
	  strEndYear = year(Date())
	  strEndDate = strEndMonth & "/" & strEndDay & "/" & strEndYear
	  strWhere = "WHERE (((dbo.FLEET_VEHICLES.DATE_ADDED)>='" & strStartDate & "' And Not (dbo.FLEET_VEHICLES.DATE_ADDED)>'" & strEndDate & "') AND ((dbo.FLEET_VEHICLES.FLEET_SCHEME_ID)=" & strScheme_ID & ")) OR (((dbo.FLEET_VEHICLES.NO_LONGER_ONFLEET_DATE)>='" & strStartDate & "' And Not (dbo.FLEET_VEHICLES.NO_LONGER_ONFLEET_DATE)>'" & strEndDate & "') AND ((dbo.FLEET_VEHICLES.FLEET_SCHEME_ID)=" & strScheme_ID & "))"

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>by doing it the old fashion way
please don't

explanation:
WHERE (((dbo.FLEET_VEHICLES.DATE_ADDED)>='" & strStartDate & "'
will do a implicit data type conversion between the sql field and the string value you put there.
also, you can never rely on the sql engine session using the format mm/dd/yyyy that you put into the string.
please believe me, my way is much better, at least in the end.


what is the problem you get with "my" way?
0
 
marc_butlerAuthor Commented:
This is the error im getting;

Conversion failed when converting the varchar value '06/02/2009' to data type int.

One thing you should know is that the field DATE_ADDED in SQL is a datetime field, if that makes any differance?
0
 
marc_butlerAuthor Commented:
Here is the code.
msql = "SELECT CONVERT(VARCHAR(10), f.[DATE_ADDED],103) AS DATE_ADDED " & _
    "FROM dbo.FLEET_VEHICLES f " & _
    "WHERE (((CONVERT(VARCHAR(10),f.[DATE_ADDED],103))=" & Date()-7 & "))"
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open msql, objConn
	
	If Not objRS.EOF Then
	  strTemp = objRS.Fields("DATE_ADDED")
	End If
	objRS.Close
	Set objRS = Nothing
	
	objConn.Close
	Set objConn = Nothing

Open in new window

0
 
marc_butlerAuthor Commented:
Sorted it. Needed single quote around the date()-7;
	msql = "SELECT CONVERT(VARCHAR(10), f.[DATE_ADDED],103) AS DATE_ADDED " & _
    "FROM dbo.FLEET_VEHICLES f " & _
    "WHERE (((CONVERT(VARCHAR(10),f.[DATE_ADDED],103))='" & Date()-7 & "'))"
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open msql, objConn
	
	If Not objRS.EOF Then
	  strTemp = objRS.Fields("DATE_ADDED")
	End If
	objRS.Close
	Set objRS = Nothing
	
	objConn.Close
	Set objConn = Nothing

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>    "WHERE (((CONVERT(VARCHAR(10),f.[DATE_ADDED],103))=" & Date()-7 & "))"

that is NOT what I suggested.
please reread.
0
 
marc_butlerAuthor Commented:
Hi angellll,

Ive now got it working if strDatePeriod = "7days", But if it is = to "30days" or "90days" it wont work.
	If strDatePeriod <> "" Then
	  Dim strStartDate
	  If strDatePeriod = "7days" Then
		strStartDate = Date()-7
	    ElseIf strDatePeriod = "30days" Then
		  strStartDate = Date()-30
		  ElseIf strDatePeriod = "90days" Then
		    strStartDate = Date()-90
	  End If
	  
	  strWhere = "WHERE (((CONVERT(VARCHAR(10),f.[DATE_ADDED],103))>='" & CDate(strStartDate) & "' And Not (CONVERT(VARCHAR(10),f.[DATE_ADDED],103))>'" & Date() & "') AND ((f.FLEET_SCHEME_ID)=" & strScheme_ID & ")) OR (((CONVERT(VARCHAR(10),f.[NO_LONGER_ONFLEET_DATE],103))>='" & CDate(strStartDate) & "' And Not (CONVERT(VARCHAR(10),f.[NO_LONGER_ONFLEET_DATE],103))>'" & Date() & "') AND ((f.FLEET_SCHEME_ID)=" & strScheme_ID & "))"
	  
	  msql = "SELECT f.FLEET_VEHICLE_ID, f.MAKE, f.MODEL, f.REG_NO, CONVERT(VARCHAR(10),f.[DATE_ADDED],103) AS DATE_ADDED, CONVERT(VARCHAR(10),f.[NO_LONGER_ONFLEET_DATE],103) AS NO_LONGER_ONFLEET_DATE " & _
	  "FROM dbo.FLEET_VEHICLES AS f " & _
	  strWhere & _
	  "ORDER BY CONVERT(VARCHAR(10),f.[DATE_ADDED],103) DESC"
	End If
	
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open msql, objConn
	
	If objRS.EOF Then
	  strNoVeh = True
	Else
	  strNoVeh = False
	  rows = objRS.getRows
	End If
	
	objRS.Close
	Set objRS = Nothing
	objConn.Close
	Set objConn = Nothing

Open in new window

0
 
marc_butlerAuthor Commented:
If you want to have a look at the page in action, go to https://www.kindertons.co.uk/fleet_schemes/reviewfleetvehicles_new.asp?strSource_ID=223&strScheme_ID=154&strDatePeriod=7days

And try changing the first list to 30days.
0
 
marc_butlerAuthor Commented:
Hi Angellll,

Thank you for all your help. I now have it up and running. Please see final code below.
	If strDatePeriod <> "" Then
	  If strDatePeriod = "7days" Then
		strWhere = "WHERE (((f.DATE_ADDED)>=DateAdd(day,-7,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120)) And (f.DATE_ADDED)<DateAdd(day,+1,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120))) AND ((f.FLEET_SCHEME_ID)=" & strScheme_ID & ")) OR (((f.NO_LONGER_ONFLEET_DATE)>=DateAdd(day,-7,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120)) And (f.NO_LONGER_ONFLEET_DATE)<DateAdd(day,+1,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120))) AND ((f.FLEET_SCHEME_ID)=" & strScheme_ID & ")) "
	    ElseIf strDatePeriod = "30days" Then
		  strWhere = "WHERE (((f.DATE_ADDED)>=DateAdd(day,-30,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120)) And (f.DATE_ADDED)<DateAdd(day,+1,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120))) AND ((f.FLEET_SCHEME_ID)=" & strScheme_ID & ")) OR (((f.NO_LONGER_ONFLEET_DATE)>=DateAdd(day,-30,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120)) And (f.NO_LONGER_ONFLEET_DATE)<DateAdd(day,+1,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120))) AND ((f.FLEET_SCHEME_ID)=" & strScheme_ID & ")) "
		  ElseIf strDatePeriod = "90days" Then
		    strWhere = "WHERE (((f.DATE_ADDED)>=DateAdd(day,-90,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120)) And (f.DATE_ADDED)<DateAdd(day,+1,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120))) AND ((f.FLEET_SCHEME_ID)=" & strScheme_ID & ")) OR (((f.NO_LONGER_ONFLEET_DATE)>=DateAdd(day,-90,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120)) And (f.NO_LONGER_ONFLEET_DATE)<DateAdd(day,+1,CONVERT(datetime,CONVERT(varchar(10),getdate(),120),120))) AND ((f.FLEET_SCHEME_ID)=" & strScheme_ID & ")) "
	  End If
	  
	  msql = "SELECT f.TYPE, f.REG_NO, f.MAKE, f.MODEL, f.FUEL, f.GROSS_WEIGHT, f.NO_SEATS, CONVERT(VARCHAR(10),f.[DATE_ADDED],103) AS DATE_ADDED, CONVERT(VARCHAR(10),f.[NO_LONGER_ONFLEET_DATE],103) AS NO_LONGER_ONFLEET_DATE " & _
	  "FROM dbo.FLEET_VEHICLES AS f " & _
	  strWhere & _
	  "ORDER BY CONVERT(VARCHAR(10),f.[DATE_ADDED],103) DESC"
	End If

Open in new window

0
 
marc_butlerAuthor Commented:
Thank you Angellll,

Jobs a good'un.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.