Solved

Classic ASP Error Message after convert db MS ACCESS db to Mysql

Posted on 2013-05-28
4
543 Views
Last Modified: 2013-05-29
Dear guys,

I'm really having like a nightmare in solving this error. After i convert my MS ACCESS db to Mysql.  (in ms access works fine).

--------------------------
      
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-5.1.68-rel14.5-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 * from mensalidades where id_aluno=904 and data_vencimento=2' at line 1

/totalensino/finance/pinheiralmysql/scripts_relatorios/alunos_inativos.asp, line 95

--------------------------

Please see the attached page.

Thanks a lot in advance. Daniel.
alunos-inativos.txt
0
Comment
Question by:artmx
  • 2
4 Comments
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 250 total points
ID: 39201518
Can't tell anything without line numbers.  Next time use the code tag.

<% option explicit %>
<!--#include file="../includes/config.asp"-->
<%if session("pag_alunos") ="0"  then response.redirect("../default.asp") %>
<html>
<head>
<title><%=mm_title%></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../css/style.css" rel="stylesheet" type="text/css">
</head>

<body>
<table width="100%" border="0" cellpadding="2" cellspacing="0" class="bodymaior">
  <% dim cor, cor1, cor2, cont
							cor1="#FFFFFF"
							cor2="#EAEAEA"
							cor=cor2 
							cont=0%>
  <tr> 
    <td><div align="center" class="bodytitulo">Rela&ccedil;&atilde;o dos Alunos 
        Inativos - <%=session("unidade")%></div></td>
  </tr>
  <tr> 
    <td><div align="right"><%=now%></div></td>
  </tr>
  <tr> 
    <td> <% dim confinanc, rscursos, rsturmas, rsalunos, cursoatual, turmaatual, rsmensalidades, rsalunoseof, contaluno, totalalunos
	                          totalalunos=0
	                          contaluno=1
							  rsalunoseof=true
							  dim mesatual, devedor, mes_inicio, mes_fim, dia, ano, i
							  set confinanc=server.createobject("adodb.connection")
							  confinanc.open mm_financ
							  
							  set rscursos=server.createobject("adodb.recordset")
							  set rsturmas=server.createobject("adodb.recordset")
							  set rsalunos=server.createobject("adodb.recordset")
							  set rsmensalidades=server.createobject("adodb.recordset")
							  
							  set rscursos=confinanc.execute ("select * from cursos where id_unidade="&session("id_unidade")&" order by curso")
							  while not rscursos.eof
							  cursoatual=rscursos("id_curso")%> 
      <!-- Cursos inicio loop -->
      <table width="100%" border="0" cellspacing="0" cellpadding="2">
        <tr> 
          <td class="bodymaior"><strong><%=server.htmlencode(rscursos("curso"))%></strong></td>
        </tr>
        <tr> 
          <td> <div id="div_curso<%=cursoatual%>" > 
              <%set rsturmas=confinanc.execute ("select * from turmas where id_curso="&cursoatual&" order by turma")
									if rsturmas.eof then response.write "&nbsp;"
									while not rsturmas.eof
									turmaatual=rsturmas("id_turma")%>
              <!-- Turmas inicio loop -->
              <table width="100%" border="0" cellspacing="0" cellpadding="0">
                <tr> 
                  <td width="20">&nbsp;</td>
                  <td class="bodymaior"><strong><em><%=server.htmlencode(rsturmas("turma"))%></em></strong></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td> <div id="div_turma<%=server.htmlencode(turmaatual)%>" > 
                      <table width="100%" border="0" cellspacing="0" cellpadding="0">
                        <% set rsalunos=confinanc.execute ("select * from alunos where id_turma="&turmaatual&" and hidden=1 order by nome")
											 while not rsalunos.eof 
											 rsalunoseof=false%>
                        <!-- Alunos inicio loop -->
                        <tr> 
                          <td width="30"><span class="body"><%=contaluno%></span>.</td>
                          <td class="bodymaior"> 
                            <!-- Checa debito inicio -->
                            <%
													 devedor=false
													 mes_inicio=0
													 dia=0
													 ano=0
													 mes_fim=0
													  mes_inicio=cint(datepart("m",rsalunos("data_primeira")))
							dia=cint(datepart("d", rsalunos("data_primeira")))
							ano=datepart("yyyy",rsalunos("data_primeira"))
							mes_fim=cint(mes_inicio+cint(rsalunos("quant_parcelas")))-1
							if mes_fim>=datepart("m",now) then
							   if dia>=datepart("d",now) then
							      mes_fim=cint(datepart("m",now))-1
							   else
							      mes_fim=cint(datepart("m",now))
							   end if
							end if
							
							for i=mes_inicio to mes_fim
							mesatual=i
							if mesatual>12 then
							   mesatual=1
							   ano=ano+1
							end if
							   set rsmensalidades=confinanc.execute ("select top 1 * from mensalidades where id_aluno="&rsalunos("id_aluno")&" and month(data_vencimento)="&mesatual)   
							   if rsmensalidades.eof then
							    devedor=true   
							   end if
							   rsmensalidades.close
							next %> 
                            <!-- checa debito final -->
                          <font color="#000000"> <b>Cod.: <%=server.htmlencode(rsalunos("id_aluno"))%></b></font> -   <font color="#000000"><%=server.htmlencode(rsalunos("nome"))%></font></td>
                        </tr>
                        <!-- Alunos Final Loop -->
                        <% contaluno=contaluno+1 
						totalalunos=totalalunos + 1%>
                        <% rsalunos.movenext
											  wend
											  
											  rsalunos.close
											  %>
                      </table>
                    </div></td>
                </tr>
              </table>
              <!-- Turmas final loop -->
              <% 
			  contaluno=1%>
              <% rsturmas.movenext
									  wend
									  
									  rsturmas.close
									  %>
            </div></td>
        </tr>
      </table>
      <!-- Cursos final loop-->
      <%rscursos.movenext
								wend
								
								rscursos.close
								set rsmensalidades=nothing						
								set rsalunos=nothing
								set rsturmas=nothing
								set rscursos=nothing
								%> </td>
  </tr>
  <tr> 
    <td><hr></td>
  </tr>
  <tr> 
    <td height="20" colspan="4"><div align="right">
        <p class="bodytitulo">Total de Alunos Inativos: <%=totalalunos%></p>
      </div></td>
  </tr>
</table>
</body>
</html>

Open in new window

0
 
LVL 10

Assisted Solution

by:plummet
plummet earned 125 total points
ID: 39201538
The problem is that mySQL doesn't support TOP, so you need to do something like:

set rsmensalidades=confinanc.execute ("select * from mensalidades where id_aluno="&rsalunos("id_aluno")&" and mon   set rsmensalidades=confinanc.execute ("select top 1 * from mensalidades where id_aluno="&rsalunos("id_aluno")&" and month(data_vencimento)="&mesatual)th(data_vencimento)="&mesatual & " order by id_aluno LIMIT 0,1")

Open in new window

This replaces the line:    set rsmensalidades=confinanc.execute ("select top 1 * from mensalidades where id_aluno="&rsalunos("id_aluno")&" and month(data_vencimento)="&mesatual)

I'm not sure about your table structure so you may need to change the "ORDER BY" clause.

Hope it helps
John
0
 
LVL 22

Accepted Solution

by:
Om Prakash earned 125 total points
ID: 39201540
select * from mensalidades where id_aluno=904 and data_vencimento=2 limit 1;
use limit instead of top 1
0
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 250 total points
ID: 39201546
There are going to be differences in access/mssql/mysql  You will need to use LIMIT for what you want to do http://www.w3schools.com/sql/sql_top.asp

SELECT TOP 1
FROM table_name;

in mysql

SELECT column_name(s)
FROM table_name
LIMIT 1;
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question