Forum Moderators: open

Message Too Old, No Replies

ASP/Access hosted in USA

problem with updating Access DB via ASP

         

rencat

3:20 am on Mar 14, 2003 (gmt 0)

10+ Year Member



HI,

I'm trying to edit some records in a Access DB I built that is stored on a Windows2000 webserver in USA. I am in Australia. Not sure if it has to do with date format differences. I can get the dates to display as I desire (dd/mm/yyyy), but it's when I try to update them that the server gives a "page cannot be displayed" error.

Here is my code:

Page1 (shown for convenience only):
<%' Convert Date if when hosted in USA -----------
function FormatDate(InDate)
if IsNull(InDate) then
FormatDate = ""
else
FormatDate = Day(InDate) & "/" & Month(InDate) & "/" & Year(InDate)
end if
end function

Dim DBconn, courses, RS
courses = "SELECT * FROM courseList WHERE ccode = '"&Request.Form("ccode")&"'"
DBconn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=d:\websites\DB.mdb;"
%>
<form name="edit" method="post" action="edCourse.asp">
<%
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open courses, DBconn 'get course info
RS.MoveFirst%>
<b>Program Type: </b><input type="text" name="progName" size="40" value="<%=RS("progName")%>">
<br><b>Location: </b><input type="text" name="location" value="<%=RS("location")%>">
<br><b>Day: </b><input type="text" name="day" value="<%=RS("day")%>">
<br><b>Date: </b><input type="text" name="date" value="<%=FormatDate(RS("date"))%>">
<%RS.Close
Set RS = Nothing
%>
<p/>
<input type="hidden" name="ccode" value="<%=Request.Form("ccode")%>">
<input type="submit" name="submit" value=" Commit Changes ">
<input type="reset" name="reset" value=" Clear Changes ">
</form>

Page2 (the page that does the work - well it's suppose to):

<% Dim edit_course, DBconn, RS

'edit_course = "UPDATE courseList SET progName = '"&Request.Form("progName")&"', location = '"&Request.Form("location")&"', date = '"&DateValue(Request.Form("date"))&"', day = '"&Request.Form("day")&"' WHERE ccode = '"&Request.Form("ccode")&"'"

edit_course = "UPDATE courseList SET progName = '"&Request.Form("progName")&"', location = '"&Request.Form("location")&"', day = '"&Request.Form("day")&"' WHERE ccode = '"&Request.Form("ccode")&"'"

DBconn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=d:\websites\DB.mdb;"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open edit_course, DBconn ' query to add coaching log entry
Set RS = Nothing
Response.Write edit_course
%>
<br>Course updated
<p><div align="center"><button type="button" onClick="history.go(-1)">Go Back</button></div>

If I leave out the date field it works fine, but not when I include it (commented out).

Please help,
TIA

rencat

korkus2000

12:48 pm on Mar 14, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Hey rencat welcome to WebmasterWorld,

I believe it is caused by your formating of ther date. Test it as US date. I have heard about this because US servers are set to the US date format and I believe they need it that way.

Woz

1:05 pm on Mar 14, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



a recent and similar discussion - [webmasterworld.com...]

Onya
Woz

rencat

11:34 pm on Mar 16, 2003 (gmt 0)

10+ Year Member



Hi and thanks for the replies.

I have checked out your options and also the recent thread, and I think I've tried everything suggested but it still don't work. For example, even if I put in a date of 4/4/2003, I still get an error.
I have not problem calling the date from the database (using ...WHERE date = #"&FormatDateUS(StrDate)&"#), but it just seems to be the insert/update operation that it don't like.

Thanks,
rencat

PS FormatDateUS is a function to convert the date obviously

Woz

11:48 pm on Mar 16, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Try putting date delimiters (#) around the date instead of the text delimiter ('). That would mean using the following in your insert/update string.

"date = #" & DateValue(Request.Form("date")) & "#"

Text delimiters sometimes work but it is best to keep to date delimiters.

Onya
Woz

rencat

12:32 am on Mar 17, 2003 (gmt 0)

10+ Year Member



HI Woz,

Tried that too as I thought that's what it was, but still no good. I'm running out of answers, and getting pretty frustrated.

Thanks,
rencat

chicagohh

4:52 am on Mar 19, 2003 (gmt 0)

10+ Year Member



What is the exact error you are getting?

Date is a reserved word in Access.

Woz

5:13 am on Mar 19, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Ha! That might be it chicagohh (slaps head).

Sorry rencat, forgot about this thread, Try that and see how you go. If no success then post your error message as chicagohh suggests.

Onya
Woz

rencat

11:35 pm on Mar 19, 2003 (gmt 0)

10+ Year Member



Hi,

Are you saying that since "date" is a reserved word in Access, that I should use another variable name in the asp page? Also, do I need to change the field name (date) in the Access DB also?

Thanks,
rencat

[edited by: rencat at 11:49 pm (utc) on Mar. 19, 2003]

Woz

11:38 pm on Mar 19, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Yes and Yes.

Use something like dtDate for the variable and CourseDate for the field name.

Onya
Woz

rencat

11:50 pm on Mar 19, 2003 (gmt 0)

10+ Year Member



OK,

I'll change everything in the application! Doh!

Then I'll let you know how it goes.

Thanks,
rencat

rencat

10:33 am on Mar 21, 2003 (gmt 0)

10+ Year Member



chicagohh & woz,

Thanks! You're the best! Everything's working great now.

Thanks again,
rencat

chicagohh

2:11 am on Mar 22, 2003 (gmt 0)

10+ Year Member



I am glad you got everything working :)