[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

The multi-part identifier "" could not be bound

Posted on 2009-04-30
15
Medium Priority
?
1,297 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:marc_butler
  • 8
  • 6
15 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24268972
"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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24268990
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24268995
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24269009
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
 

Author Comment

by:marc_butler
ID: 24271656
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24271717
>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
 

Author Comment

by:marc_butler
ID: 24271739
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
 

Author Comment

by:marc_butler
ID: 24271760
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
 

Author Comment

by:marc_butler
ID: 24271801
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
 
LVL 143

Expert Comment

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

that is NOT what I suggested.
please reread.
0
 

Author Comment

by:marc_butler
ID: 24272139
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
 

Author Comment

by:marc_butler
ID: 24272179
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24275153
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
 

Author Comment

by:marc_butler
ID: 24277429
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
 

Author Closing Comment

by:marc_butler
ID: 31576378
Thank you Angellll,

Jobs a good'un.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question