469,322 Members | 1,583 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,322 developers. It's quick & easy.

Passing a date parameter to an Access query in ASP

17
I have the followig code:


Expand|Select|Wrap|Line Numbers
  1. set conn=Server.CreateObject("ADODB.Connection")
  2. conn.Provider=provider
  3. conn.Open dbpath
  4. set rs = Server.CreateObject("ADODB.recordset")
  5.  
  6. ...
  7.  
  8.  
  9. sql="SELECT department, job, start, [end] FROM Qry_AssignedWeek WHERE employee='" & employee & "' ORDER BY start"
  10. rs.open sql, conn, sunday
Where the Qry_AssignedWeek is the following query in Access2003:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS sunday DateTime;
  2. SELECT *
  3. FROM Qry_AssignedNotCancelled
  4. WHERE (((Qry_AssignedNotCancelled.start) Between [sunday] And [sunday]+7)) OR (((Qry_AssignedNotCancelled.end) Between [sunday] And [sunday]+7));

this code generates the ASP error:

Provider (0x80020005)
Type mismatch.
/schedule/ViewMyShifts.asp, line 70


The "sunday" variable is a string representing a date in format "dd/mm/yyyy"

I have tried to change the "sunday" in "rs.open sql, conn, sunday" with many different options:
int(sunday)
CDate(sunday)
#23/09/2007#

etc.

All returning various errors.

Please, can anyone guide me around this problem?
Sep 20 '07 #1
2 5770
markrawlingson
346 Expert 100+
You could try something like this... and scrap the msAccess query all together.

This would calculate when sunday is, and return all records which fall within the last sunday to the next sunday. In the case of this week, as an example, it would return all records from Sunday the 15th of september to Sunday the 22nd of september.

Expand|Select|Wrap|Line Numbers
  1. If WeekDay(Date()) = 1 Then 'it's sunday
  2.    dDateStart = Date() ' it's sunday today so we want to start from today's date
  3. Else
  4.    'Today is not sunday, so we need to find when sunday is. To do this, we'll find the integer value of the weekday, and count backwards to sunday.
  5.    'If today is friday weekDay will return 6 - sunday would be 5 days ago. So weekday*-1 = -6 + 1 = -5
  6.    iDateChange = (WeekDay(Date())*-1)+1
  7.    dDateStart = DateAdd(d,iDateChange,Date())
  8. End If
  9. 'Now we have our start date, we just need our end date, which is easy
  10.  
  11. dDateEnd = DateAdd(d,7,dDateStart)
  12.  
  13. sSQL="SELECT department, job, start, datefield FROM yourtable WHERE employee='" & employee & "' AND datefield BETWEEN " & dDateStart& " AND " & dDateEnd & " ORDER BY datefield"
  14. rs.open sql, conn, 3, 3
  15.  
  16.  
I also don't know why you have rs.open sql, conn, sunday

You can't pass a variable through your recordset opening statement. This statement accepts: your sql query, the connection string to your db, and your vb constants. (adReadOnly, adLockOptomistic, etc)

Hope this helps,

Sincerely
Mark

I have the followig code:


Expand|Select|Wrap|Line Numbers
  1. set conn=Server.CreateObject("ADODB.Connection")
  2. conn.Provider=provider
  3. conn.Open dbpath
  4. set rs = Server.CreateObject("ADODB.recordset")
  5.  
  6. ...
  7.  
  8.  
  9. sql="SELECT department, job, start, [end] FROM Qry_AssignedWeek WHERE employee='" & employee & "' ORDER BY start"
  10. rs.open sql, conn, sunday
Where the Qry_AssignedWeek is the following query in Access2003:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS sunday DateTime;
  2. SELECT *
  3. FROM Qry_AssignedNotCancelled
  4. WHERE (((Qry_AssignedNotCancelled.start) Between [sunday] And [sunday]+7)) OR (((Qry_AssignedNotCancelled.end) Between [sunday] And [sunday]+7));

this code generates the ASP error:

Provider (0x80020005)
Type mismatch.
/schedule/ViewMyShifts.asp, line 70


The "sunday" variable is a string representing a date in format "dd/mm/yyyy"

I have tried to change the "sunday" in "rs.open sql, conn, sunday" with many different options:
int(sunday)
CDate(sunday)
#23/09/2007#

etc.

All returning various errors.

Please, can anyone guide me around this problem?
Sep 20 '07 #2
scf1984
17
Thanks! it's working:

Expand|Select|Wrap|Line Numbers
  1. sunday = CDate(sunday)
  2. sql="SELECT department, job, start, [end] FROM Qry_AssignedNotCancelled WHERE employee='"
  3. sql=sql & employee & "' " & "AND start BETWEEN #" & sunday & "# AND #" & sunday + 7 & "#"
  4.  
  5. rs.open sql, conn
(the varaiable "sunday" is given as a string "dd/mm/yyyy")

But I am still quite puzzled...
Is there a way to run an Access parameterized query from ASP?




You could try something like this... and scrap the msAccess query all together.

This would calculate when sunday is, and return all records which fall within the last sunday to the next sunday. In the case of this week, as an example, it would return all records from Sunday the 15th of september to Sunday the 22nd of september.

Expand|Select|Wrap|Line Numbers
  1. If WeekDay(Date()) = 1 Then 'it's sunday
  2.    dDateStart = Date() ' it's sunday today so we want to start from today's date
  3. Else
  4.    'Today is not sunday, so we need to find when sunday is. To do this, we'll find the integer value of the weekday, and count backwards to sunday.
  5.    'If today is friday weekDay will return 6 - sunday would be 5 days ago. So weekday*-1 = -6 + 1 = -5
  6.    iDateChange = (WeekDay(Date())*-1)+1
  7.    dDateStart = DateAdd(d,iDateChange,Date())
  8. End If
  9. 'Now we have our start date, we just need our end date, which is easy
  10.  
  11. dDateEnd = DateAdd(d,7,dDateStart)
  12.  
  13. sSQL="SELECT department, job, start, datefield FROM yourtable WHERE employee='" & employee & "' AND datefield BETWEEN " & dDateStart& " AND " & dDateEnd & " ORDER BY datefield"
  14. rs.open sql, conn, 3, 3
  15.  
  16.  
I also don't know why you have rs.open sql, conn, sunday

You can't pass a variable through your recordset opening statement. This statement accepts: your sql query, the connection string to your db, and your vb constants. (adReadOnly, adLockOptomistic, etc)

Hope this helps,

Sincerely
Mark
Sep 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Michael DeLawter | last post: by
10 posts views Thread by Kenneth | last post: by
7 posts views Thread by Nicolae Fieraru | last post: by
4 posts views Thread by Tony | last post: by
2 posts views Thread by Julie Wardlow | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.