SYntax error problem

Discussion in 'C#' started by pablobhz, Oct 5, 2005.

0
  1. #1
    Hello.
    I've made a code to online insert a TXT file into an Access database(MDB) file

    It is the second code like this i built. The first one, had the problem that it could convert only one time...the second time it do not work.

    This one,now...it looks like will work(don't know,i got an error)...but i need the help of u all to test.

    Here's the code...i am getting the error:
    Microsoft JET Database Engine error '80040e14'

    Syntax error in query expression 'cod;nome;telefone;email'.

    /pablo/conversor2.asp, line 43

    
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 
<html> 
<head> 
<%
'Convertendo TXT diretamente para MDB online - By Pablo 
'Declaração das variáveis
Dim fsoObject 
Dim filObject 
Dim tsObject 
Dim strBuffer 

Dim objConn, objRS,Sql 
'Criando o objeto de conexão ADO
set objConn=server.CreateObject("ADODB.Connection") 
'Criando o Recordset
set objrs=server.CreateObject("ADODB.Recordset") 
'Informando o driver a ser usado, e o local aonde se encontra o banco de dados de destino.
objConn.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & server.MapPath ("conv.mdb") 

Const ForReading = 1 
Const TristateFalse = 0 
'Crio o objeto FSO(File System Object)
Set fsoObject = Server.CreateObject("Scripting.FileSystemObject") 
'Com o FSO, abro o arquivo txt
Set filObject = fsoObject.GetFile(server.MapPath ("arquivo.txt")) 
'Abro ele para leitura,indicando os parâmetros pré-definidos
set tsObject = filObject.OpenAsTextStream(ForReading,TristateFalse) 
%> 
<title>Converte TXT para MDB</title> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
</head> 

<body> 
<% 
'Loop para inserir na tabela
do While Not tsObject.AtEndOfStream 
'Atribuo a strline, a leitura de cada linha do arquivo
strLine = tsObject.ReadLine
'Sintaxe SQL para inserir dentro do banco 
sql = "insert into tabela (cod,nome,telefone,email) values ("&strLine&")" '===>This is the line where i think the error is 
'Inserindo dentro do banco
set objrs=objconn.execute(sql) '===>This is the line 43
'Fazer o loop
loop 
'Fim do código
%> 
</body> 
</html>
    Code (markup):
     
    pablobhz, Oct 5, 2005 IP
  2. maro

    maro Peon

    Messages:
    356
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Can you please post the format of your table and a sample line or two of the TXT file?
     
    maro, Oct 6, 2005 IP
  3. roy77

    roy77 Active Member

    Messages:
    1,088
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    78
    #3
    this looks like a complicated script. What language and can ypu please post the format of ur second table like maro said :)
    have a nice day
     
    roy77, Jan 6, 2007 IP
  4. ccoonen

    ccoonen Well-Known Member

    Messages:
    1,606
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    160
    #4
    It looks like your SQL is F'd up - please show us your variable strLine :)

    Also - is it being protected? (as in are you removing the ' ticks for sql injection)?

    Someone might be trying to hack you...
     
    ccoonen, Jan 7, 2007 IP
  5. DaDMan

    DaDMan Guest

    Messages:
    566
    Likes Received:
    25
    Best Answers:
    0
    Trophy Points:
    0
    #5
    You already have your string inside strLine variable right? Then do no enclose it into quotes as it's already a string variable.
    Try
    sql = "insert into tabela (cod,nome,telefone,email) values (strLine)"
    instead of
    sql = "insert into tabela (cod,nome,telefone,email) values ("&strLine&")"

    and this is according the fact that strLine contains 4 values separated with the default field separators and that they are the same type as your db fields off course..
     
    DaDMan, Jan 10, 2007 IP
  6. smallbuzz

    smallbuzz Peon

    Messages:
    125
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Here's a easy way to solve your problem. Since you are looping, just print out the 1st sql. Then execute that sql in Access. At this point, you should be able to see what the problem is. My guess is that you did not quote one or more columns.
     
    smallbuzz, Jan 10, 2007 IP