Solved

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

Posted on 2013-05-28
4
546 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 53

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 53

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

696 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