Solved

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

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

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…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now