Splitting date into array

Hi there,

I try to split a date sent via a form into an array to make it insertable into Access. This is the code:

<%
aDate = split(Request.form("bookingDate"),"/")
bookingDate = aDate(0) & "-" & left(monthname(aDate(1)),3) & "-" & aDate(2)
%>

It comes up with the following error message:

Microsoft VBScript runtime error '800a0009'
Subscript out of range: '[number 1]'

HELP please - what's wrong

Thanks very much

have you checked that Request.form("bookingDate") is coming through as expected...
response.write Request.form("bookingDate") and see what you get.

Hi there,

Thanks for the quick reply. I did your test and it came up with a date format like:

30.8.2005

I work on a German machine/server if this is of any help.

Thanks anyway

change this line:
aDate = split(Request.form("bookingDate"),"/")
to
aDate = split(Request.form("bookingDate"),".")

Your issue is that the date is separated by . rather than /

I think we are getting there, cheers!

Next error:

Microsoft VBScript runtime (0x800A01C2)
Wrong number of arguments or invalid property assignment: 'left'
booking2.asp, line 120

Line 120 says:

bookingDate = aDate(0) & "-" & left(monthname(aDate(1))) & "-" & aDate(2)%>


THANKS AGAIN FOR YOUR HELP

dont know if you've changed that line or not but in your original you had this:
bookingDate = aDate(0) & "-" & left(monthname(aDate(1)),3) & "-" & aDate(2)
rather than

bookingDate = aDate(0) & "-" & left(monthname(aDate(1))) & "-" & aDate(2)

Why not just use vbscript's inbuilt date functions to get the different parts of the date?
First do an isDate() test on the input then use the DatePart function to grab the parts you want...
See here (http://www.w3schools.com/vbscript/func_datepart.asp)

Exampl...
if isDate(booking) then
month = datePart("m",booking)
day = datePart("d",booking)
.
.
.
else
'redirect to error page...
end if
And that way you'll have flexibility where the delimiters are concerned. You won't have to necessarily use the "/" character. Especially if its user input... users tend to not comply ever so often with formats specified. :rolleyes:

datePart Doesnt work on dates delimited with '.' and isdate would return false.
Dates have to be delimeted with spaces, - , or /

datePart Doesnt work on dates delimited with '.' and isdate would return false.
Dates have to be delimeted with spaces, - , or /
Yah.. i'm aware. I was just suggesting more use of vb functions to introduce some flexibility. I'm not sure if its user input or not. If he's using only that one format it'll be ok

NancyJ,

You're a genius, works lovely, thanks very much.

I've been through many forums in my time as a developer but that was probably the quickest problem solving I've ever met. Thumbs up!

Cheers

It worked fine but now I get an error message:

Microsoft JET Database Engine error '80040e07'

Syntaxfehler in Datum in Abfrageausdruck 'staffID = 2 AND bookingDate = #14-Okt-2005#'. line 174

Here is line 174:

strSQL = "SELECT * FROM tblBookings WHERE staffID = " & Request.Form("staffID") & " AND bookingDate = #" & bookingDate & "# ORDER BY bookingTimeFrom"

Any ideas?

Cheers

I'm guessing the problem is that access doesnt recognise "14-Okt-2005" as a date.
Can you post more of your code... particularly anything that processes the date input.
I think access will only be happy with the date as either 14/10/2005 or more likely 10/14/2005

Hi NancyJ,

funny thing is, it worked quite well last time I looked :mad:

You are back - I'm quite positive now

CHEERS

Here is the whole code:


<%@LANGUAGE = "VBSCRIPT"%>
<%Option Explicit%>
<!--#include virtual="/hairdresser/inc/conn.asp"-->


<%

Function fRandomPassword()

Const sValidChars = "abcdefghijklmnopqrstuvxyz"

Dim nCount

Dim sRet

Dim nNumber

Dim nLength

Randomize

nLength = Len( sValidChars )

For nCount = 1 To 10

nNumber = Int((nLength * Rnd) + 1)

sRet = sRet & Mid( sValidChars, nNumber, 1 )

Next

fRandomPassword = sRet

End Function

%>

<%

Dim rsServices, strSQL, aServices, i , whereStr, total, hours, noservice, rsStaff, staffName, openTime, closeTime, availSlot, x, rsBookings, strBooking, bookingDate, aDate

openTime = "09:00"

closeTime = "17:30"



if request.form("serviceID") <> "" and request.form("bookingDate") > "" and request.form("staffID") > "" then



aServices = Split(Request.Form("serviceID"), ",")

for i = 0 to ubound(aServices)

whereStr = whereStr & " OR serviceID = " & aServices(i)

next

Set rsServices = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT serviceID, serviceName, servicePrice, serviceTime FROM tblServices WHERE serviceID = 0 " & whereStr

rsServices.open strSQL, conn



rsStaff = conn.execute("SELECT staffName FROM tblStaff WHERE staffID = " & Request.Form("staffID"))

staffName = rsStaff(0)

else

noservice = true

end if

%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>Curl Up and Dye Hairdressers - Book your appointments online!</title>

<link rel="stylesheet" type="text/css" href="inc/styles.css" />

</head>


<body>


<div id="wrapper">

<div id="col">

<img src="img/girl.jpg" alt="Girl with a funky haircut" width="260" height="390" border="0" />

</div>



<div id="main">

<h1>Buchen Sie Ihren Termin</h1>

<% if noservice = true then %>
<p>Sie müssen einen Service auswählen, um eine Buchung vornehmen zu können.</p>

<%else %>
<p>Sie haben folgende(n) Service(s) gewählt:</p>

<ul class="box">

<% total = 0

hours = 0

do while not rsServices.eof

total = total + rsServices("servicePrice")

hours = hours + rsServices("serviceTime")%>

<li><%=rsServices("serviceName")%> - <%=FormatCurrency(rsServices("servicePrice"))%></li>

<% rsServices.movenext

loop

%>

</ul>

<p>Die Gesamtkosten für Ihren Besuch belaufen sich auf <strong><%=FormatCurrency(total)%>.</strong> Ihr Termin wird etwa <strong><%=hours%></strong> Minuten dauern.</p>



<p>Ihr gewünschter Stylist ist: <strong><%=staffName%></strong></p>

<p>Ihr gew&uuml;nschter Termin ist: <strong><%=FormatDateTime(Request.Form("bookingDate"),vbLongDate)%></strong></p>

<p>Sie müssen jetzt noch Ihre gewünschte Uhrzeit angeben:</p>

<%aDate = split(Request.form("bookingDate"),".")

bookingDate = aDate(0) & "-" & left(monthname(aDate(1)),3) & "-" & aDate(2)%>

<form method="post" action="confirm.asp">

<input type="hidden" name="serviceID" value="<%=Request.form("serviceID")%>" />

<input type="hidden" name="staffID" value="<%=Request.form("staffID")%>" />

<input type="hidden" name="bookingDate" value="<%=bookingDate%>" />

<%



Set rsBookings = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM tblBookings WHERE staffID = " & Request.Form("staffID") & " AND bookingDate = #" & bookingDate & "# ORDER BY bookingTimeFrom"



rsBookings.open strSQL, conn

if not rsBookings.eof then

strBooking = "<ul>"

x = openTime

Do while not rsBookings.eof

availSlot = DateDiff("n", x, rsBookings("bookingTimeFrom"))



if availSlot >= hours then

strBooking = strBooking & "<li><input type=""radio"" name=""bookingFrom"" value=""" & x & """ />" & FormatDateTime(x,vbShortTime) & "</li>" &vbcrlf

end if

x = rsBookings("bookingTimeTo")

rsBookings.movenext

loop

availSlot = DateDiff("n", x, closeTime)

if availSlot >= hours then

strBooking = strBooking & "<li><input type=""radio"" name=""bookingFrom"" value=""" & x & """ />" & FormatDateTime(x,vbShortTime) & "</li>" &vbcrlf

end if

x = DateAdd("h", 1, x)

Do While DateDiff("n", x, DateAdd("n",-hours,closeTime)) > 0

strBooking = strBooking & "<li><input type=""radio"" name=""bookingFrom"" value=""" & x & """ />" & FormatDateTime(x,vbShortTime) & "</li>" &vbcrlf

x = DateAdd("h", 1, x)

Loop

strBooking = strBooking & "</ul>"

else

strBooking = "<ul>"&vbcrlf

x = openTime

Do While DateDiff("n", x, DateAdd("n",-hours,closeTime)) > 0

strBooking = strBooking & "<li><input type=""radio"" name=""bookingFrom"" value=""" & x & """ />" & FormatDateTime(x,vbShortTime) & "</li>" &vbcrlf

x = DateAdd("h", 1, x)

Loop



strBooking = strBooking & "</ul>"

end if

response.write strBooking

%>
<input type="hidden" name="ActivateCode" value="<%=fRandomPassword()%>" />
<input type="hidden" name="Active" value="Nein" />
<p><label for="bookingName">Ihr Name:</label> <br />
<input type="text" name="bookingName" id="bookingName" /></p>

<p><label for="bookingEmail">Ihre E-Mail-Adresse:</label> (für die E-Mail-Bestätigung)<br />
<input type="text" name="bookingEmail" id="bookingEmail" /></p>

<p><label for="bookingTel">Ihre Telefonnummer: </label>(bei Rückfragen)
<br />
<input type="text" name="bookingTel" id="bookingTel" /></p>

<input type="submit" name="btnSubmit" value="Termin buchen" />
</p>

</form>

<%end if%>
</div>

</div>

</body>
</html>

<%

if not noservice = true then

rsServices.close

set rsServices = nothing

end if

conn.close

set conn = nothing

%>

If it was working before then is it possible your host has changed their settings? Try using LCID to change it into english.
Session.LCID=2048orSession.LCID=2057 or using month instead of monthname for the month part of the date.

LCID didn't work

It's the same locally on my machine and on the provider's server online.

I did the splitting into 21-Okt-2005 and it worked. What happend to the Access DB? Am I stupid or what?

The origin of the DB is England, I edited it over here in Germany. Are there different country settings, I might have moddled up?

CHEERS

I believe that is the issue (originated in England, edited in Germany). If you are using a different language pack, that might have been what caused the date format to change.

By the way, love the name "Curl Up and Dye Hairdressers". ;)

LCID didn't work

It's the same locally on my machine and on the provider's server online.

I did the splitting into 21-Okt-2005 and it worked. What happend to the Access DB? Am I stupid or what?

The origin of the DB is England, I edited it over here in Germany. Are there different country settings, I might have moddled up?

CHEERS
Not sure what language settings access uses or if it uses different settings for foreign language settings. Did you try using the number of the month instead of the month name? Is there some reason why it has to use the name of the month?

Have you tried running the SQL statement in access rather than through a webpage? If it fails try using oct rather than okt and see what happens

Hi NancyJ,

I tried your suggestion and worked in Access.

Okt doesn't work, Oct does work.

But what does that mean?

I tried month instead of monthname as well. Result: It gives me a 1 for October because of the 10.

CHEERS

I think I need to change the monthname (let's say Okt) to Oct in a function before everything is processed. My Access DB allows the format 20-Oct-05, but not 20-Okt-05 (the German type)

How can I do that?

Thanks for help

setting your lcid to 2057 should return the monthname in english rather than german.

Thanks, I know, but also currency :confused:

I need a function to change Okt into Oct

CHEERS

Thanks, I know, but also currency :confused:

I need a function to change Okt into Oct

CHEERS
Well other than recreating the database in german you have 3 options.
switch LCID each time you need to display currency or search the db. or write a function that converts all possible german month names into english month names or use the number for the month instead of the name.