?
Solved

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

Posted on 2013-05-28
4
Medium Priority
?
554 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 1000 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 500 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 500 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 1000 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

Independent Software Vendors: 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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 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