SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Sep 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP/ODBC returns question marks

    My query output produces question marks when the same query in MySQL Workbench and Adminer.php works fine as follows;

    Code:
    strQ = "Select cast(Sum(If(departments.vat, If(weeklytransactions.weekendingdate Between '2011-01-04' And '2099-12-31', weeklytransactions.takings / 1.2, If(weeklytransactions.weekendingdate Between '2008-11-30' And '2010-01-01', weeklytransactions.takings / 1.15, weeklytransactions.takings / 1.175)), weeklytransactions.takings)) as unsigned) As Total, Week(weeklytransactions.weekendingdate), weeklytransactions.weekendingdate As wkend, round(datediff(weekendingdate, (if(month(weekendingdate)>7,concat(year(weekendingdate),'-08-01'),concat(year(weekendingdate)-1,'-08-01'))))/7,0)+1 as fyweek, Case When Month(weeklytransactions.weekendingdate) >7 Then Concat(Year(weeklytransactions.weekendingdate), '-',Year(weeklytransactions.weekendingdate) + 1) Else Concat(Year(weeklytransactions.weekendingdate) - 1, '-',Year(weeklytransactions.weekendingdate)) End As fy From departments Inner Join weeklytransactions On departments.deptid = weeklytransactions.departmentId Group By weeklytransactions.weekendingdate Order By fy desc, wkend asc"
    'response.Write(strQ)
    	set objRS = objDB.GetRecordset(strQ, objDataBase, adOpenStatic, adLockReadOnly)
    	numrecs = objRS.recordcount
    	fy = ""
    	strSeries="["
    	seriesname=""
    	objRS.movefirst
    	while not(objRS.EOF)
    			if cstr(fy)<> cstr(objRS("FY")) then
    				fy = objRS("fy")
    				strSeries = strSeries &"{ name: '"&cstr(objRS("fy"))&"', " &VbCrLf
    				strSeries = strSeries &"data: ["
    			end if
    			response.write("ss="&strSeries&"<br>")
    			response.write("ob=" & cstr(objRS("fy")) & "<br>")
    			response.write("<br> , fy=" & cstr(fy))
    produces

    s=[{ name: '?????? ??????? ?????ob=????????
    , fy=????4ss=[{ name: '?????? ??????? ???? ??????? ??? ??????? ??? ??????? ??? ??????? ??? ??????? ??? ???? ?? ??? ??????? ??? ??????? ??? ? ????? ?? ? ? ????? ???? ??????? ???? ??????? ???? ??????? ???? ??? ??? ???? ??????? ???? ??????? ???? ?? ???? ???? ? ????? ???? ? ?? ?? ?? ? ???? ?? ???? ??????? ???? ??????? ???? ??? ??? ???? ???? ?? ???? ??????? ???? ??????? ???? ??????? ???? ?? ? ?? ???? ??????

    Workbench produces

    Total, Week(weeklytransactions.weekendingdate), wkend, fyweek, fy
    '36569', '31', '2013-08-04 00:00:00', '1', '2013-2014'
    '38576', '32', '2013-08-11 00:00:00', '2', '2013-2014'
    '39921', '33', '2013-08-18 00:00:00', '3', '2013-2014'
    '37338', '34', '2013-08-25 00:00:00', '4', '2013-2014'
    '41193', '35', '2013-09-01 00:00:00', '5', '2013-2014'
    '29820', '36', '2013-09-08 00:00:00', '6', '2013-2014'
    '27414', '37', '2013-09-15 00:00:00', '7', '2013-2014'

    Adminer.php produces

    Total Week(weeklytransactions.weekendingdate) wkend fyweek fy
    36569 31 2013-08-04 00:00:00 1 2013-2014
    38576 32 2013-08-11 00:00:00 2 2013-2014
    39921 33 2013-08-18 00:00:00 3 2013-2014

    The database collation is set as utf-8_general_ci, the connection string has charset=utf8

    I have no idea why this is happening - any ideas?

    Regards
    Pete

  2. #2
    SitePoint Member
    Join Date
    Sep 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I managed to find a solution by adding the CAST to the CASE clause. I imagine that there are so many different kinds of fields that it needed setting.

    I do not know how to close this.

    Regards
    Pete


Tags for this Thread

Bookmarks

Posting Permissions

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