Problems building dynamic array

Results 1 to 2 of 2

Thread: Problems building dynamic array

  1. #1
    Join Date
    Dec 1969

    Default Problems building dynamic array

    Hello All<BR>I am accessing expiry dates from a database(eg 02/2005) and trying to see if any of those returned are not expired(eg 02/2005 is valid for another month)<BR><BR>What I am doing is first reading the values into an array in full(02/2005). After this is where the problems are. I wish to take the 4 characters signifying the year and build a new array wih them eg(2005)<BR><BR>&#039; Comments marked like so explain what i am doing(hopefully)<BR><BR>**********CODE************ <BR><BR>dim conn_accesscode, rs_accesscode, mySQL, strSQL, NowStr, arrDBdata, MyArray, i, NumRecords, j<BR><BR> mySQL = "select [Expiry Date] from [AccessInfo] where [User ID] = &#039;" & trim(Request.Cookies("SessionUserInfo")("userID")) & "&#039;"<BR> strSQL = "select count([Expiry Date]) from [AccessInfo] where [User ID] = &#039;" & trim(Request.Cookies("SessionUserInfo")("userID")) & "&#039;"<BR> set conn_accesscode = server.CreateObject("ADODB.Connection")<BR> conn_accesscode.Open Application ("ConnectionStr")<BR> set rs_accesscode = conn_accesscode.Execute(mySQL)<BR> <BR> arrDBData = rs_accesscode.Getrows() &#039;Poulate array with values eg - 02/2004<BR> <BR> NumRecords= UBound(arrDBData,2) &#039;Get Number of values in array<BR> <BR> &#039;MyArray(NumRecords) &#039; Get an error, "Type Mismatch"<BR> For i = 0 to NumRecords<BR> <BR> response.Write(right(arrDBData(0,i),4)) &#039; (eg)Split 02/2004 to get 2004<BR> &#039;MyArray.add(right(arrDBData(0,i),4)) &#039;&#039;Trying to write to a new array (MyArray)<BR> <BR> Next<BR><BR>********/CODE************<BR><BR>Can somebody please help me with this or suggest an alternative solution, I looked into using left and right functions in SQL but I think this is not supported in SQL Server<BR><BR>Cheers

  2. #2
    Join Date
    Dec 1969

    Default Why are you doing it that way???

    Why not use SQL to get the unexpired records? (Or expired ones, if that&#039;s what you are after.)<BR><BR>And would you *really* ever have more than ONE record from this query:<BR><BR>mySQL = "select [Expiry Date] from [AccessInfo] where [User ID] = &#039;" & trim(Request.Cookies("SessionUserInfo")("userID")) & "&#039;"<BR><BR>???<BR><BR>Why would ONE UserID have more than one ExpiryDate??<BR><BR>You would have been a *LOT* better off using a DATETIME field in the DB to hold ExpiryDate. If you only care about month and year, then just use a dummy day (presumably 1) for the day of the month. So instead of storing the string <BR> &#039;04/2005&#039;<BR>you store the *DATE*<BR> &#039;1 April 2005&#039;<BR>or<BR> &#039;4/1/2005&#039;<BR>(or &#039;1/4/2005&#039; depending upon where you live).<BR><BR>You&#039;ve just made things MUCH more difficult by going with &#039;04/2005&#039;, honest.<BR><BR>And yes, LEFT and RIGHT *can* be used in SQL Server. Or at least equivalent functions can be. But I wouldn&#039;t recommend that route.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts