Solved

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

Posted on 2013-05-28
4
542 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
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/…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

831 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