gustavoneves
asked on
Error 0x8004e10 when inserting records with the execute method
At a certain point my program goes like this:
-------------------------- ---------- ---------- -----
inputDataLimIniPublic = dateadd("yyyy",2,now())
sql="insert into tblN2PecasVDAnuncios(IDCom pra, DataLimIniPublic, Destacado, ComFoto) values("
sql=sql & inputIDCompra
sql=sql & ", #" & inputDataLimIniPublic & "#"
sql=sql & ", " & inputDestacados
sql=sql & ", " & inputComFoto
sql=sql & ")"
for anuncio=1 to inputNumAnuncios
%>
<!--#include file="ConexaoBDClientes_Ex ec.inc"-->
<%
oConn.close
set oConn = Nothing
next
%>
The include file is as follows:
-------------------------- -------
<%
Set oConn = Server.CreateObject("ADODB .Connectio n")
oConn.Open "DSN=Clientes"
set rs = Server.CreateObject("ADODB .RecordSet ")
oConn.execute sql,,129
%>
Now some facts:
0 - The browser shows:
Microsoft OLE DB Provider for ODBC Drivers (0x8004e10)
[Microsoft][Microsoft Access ODBC Driver] Few parameters, expected 1.
ConexaoBDClientes_Exec.inc , line 4
1 - This code runs perfectly in my desktop PC running W2K (English Version);
2 - The same code raises the error in my Laptop running XP (Portuguese Version);
3 - PC had Access 2000, Laptop has Access 2003;
4 - I did not convert the database;
5 - Field IDCompra is autonumber;
6 - Field inputDataLimIniPublic is date;
7 - Remaining fields are logical;
Any clues? I would also appreciate a suggestion about a good ADODB reference on the web. Thanks
--------------------------
inputDataLimIniPublic = dateadd("yyyy",2,now())
sql="insert into tblN2PecasVDAnuncios(IDCom
sql=sql & inputIDCompra
sql=sql & ", #" & inputDataLimIniPublic & "#"
sql=sql & ", " & inputDestacados
sql=sql & ", " & inputComFoto
sql=sql & ")"
for anuncio=1 to inputNumAnuncios
%>
<!--#include file="ConexaoBDClientes_Ex
<%
oConn.close
set oConn = Nothing
next
%>
The include file is as follows:
--------------------------
<%
Set oConn = Server.CreateObject("ADODB
oConn.Open "DSN=Clientes"
set rs = Server.CreateObject("ADODB
oConn.execute sql,,129
%>
Now some facts:
0 - The browser shows:
Microsoft OLE DB Provider for ODBC Drivers (0x8004e10)
[Microsoft][Microsoft Access ODBC Driver] Few parameters, expected 1.
ConexaoBDClientes_Exec.inc
1 - This code runs perfectly in my desktop PC running W2K (English Version);
2 - The same code raises the error in my Laptop running XP (Portuguese Version);
3 - PC had Access 2000, Laptop has Access 2003;
4 - I did not convert the database;
5 - Field IDCompra is autonumber;
6 - Field inputDataLimIniPublic is date;
7 - Remaining fields are logical;
Any clues? I would also appreciate a suggestion about a good ADODB reference on the web. Thanks
ASKER
I'm sorry WMIF, but fact #5 is wrong, it should read:
5 - Field IDCompra is number;
There also should be a fact #8:
8 - The table has an autonumber field as its primary key;
5 - Field IDCompra is number;
There also should be a fact #8:
8 - The table has an autonumber field as its primary key;
ah. why are you using an include file for only 4 lines? your include is creating a recordset and you arent even using it. what i would suggest is to put the connection string into an include file so you can change that if you need to. then put that code into your page itself. you are opening a connection and closing it for each time through the loop.
the include file should be simple, like this:
<%
connstring = "DSN=Clientes"
%>
put the include file at the top of the page and open the connection like this:
oConn.open connstring
then your loop will look something like this:
<!--#include file="ConexaoBDClientes_Ex ec.inc"-->
<%
Set oConn = Server.CreateObject("ADODB .Connectio n")
oConn.Open connstring
for anuncio=1 to inputNumAnuncios
oConn.execute sql
next
oConn.close
set oConn = Nothing
also, what are you trying to do with the 129 on the execute line? i cant find that option on this page:
http://www.w3schools.com/ado/met_comm_execute.asp
the include file should be simple, like this:
<%
connstring = "DSN=Clientes"
%>
put the include file at the top of the page and open the connection like this:
oConn.open connstring
then your loop will look something like this:
<!--#include file="ConexaoBDClientes_Ex
<%
Set oConn = Server.CreateObject("ADODB
oConn.Open connstring
for anuncio=1 to inputNumAnuncios
oConn.execute sql
next
oConn.close
set oConn = Nothing
also, what are you trying to do with the 129 on the execute line? i cant find that option on this page:
http://www.w3schools.com/ado/met_comm_execute.asp
ASKER
First, the reason why my include file is so short:
- My website is hosted by brinkster, and my hosting plan only allows DSN-less connections;
- Over the years, I've been developing my website using local DSN connections;
- I have 2 different include files with the same name, one for development and other for the on-line site, that goes like this:
<%
Set oConn=Server.CreateObject( "ADODB.Con nection")
sConnString = "Provider=Microsoft.Jet.OL EDB.4.0; Data Source="
sMapPath = Server.MapPath("\")
sMapPath = Mid(sMapPath, 1, InStrRev(sMapPath,"\")-1) & "\database\Website.mdb;" & "Persist Security Info=False;"
sConnString = sConnString & sMapPath
oConn.Open(sConnString)
oConn.Execute sql,,129
%>
- With this (pre-historic?) technique I can write my programs in a way they will work in both platforms;
(I wonder if I could use DSNLess connections localy, could it be done? If so, the includes would simply disappear).
Second, why the 129? Please check http://www.aspfaq.com/show.asp?id=2191 for this chunk of code:
<%
sql = "INSERT INTO <table> (fields) VALUES (values)"
set conn = CreateObject("ADODB.Connec tion")
conn.open "<connection string>"
conn.execute sql, , 129
' 129 is the constant for adExecuteNoRecords + adCmdText
...
%>
- My website is hosted by brinkster, and my hosting plan only allows DSN-less connections;
- Over the years, I've been developing my website using local DSN connections;
- I have 2 different include files with the same name, one for development and other for the on-line site, that goes like this:
<%
Set oConn=Server.CreateObject(
sConnString = "Provider=Microsoft.Jet.OL
sMapPath = Server.MapPath("\")
sMapPath = Mid(sMapPath, 1, InStrRev(sMapPath,"\")-1) & "\database\Website.mdb;" & "Persist Security Info=False;"
sConnString = sConnString & sMapPath
oConn.Open(sConnString)
oConn.Execute sql,,129
%>
- With this (pre-historic?) technique I can write my programs in a way they will work in both platforms;
(I wonder if I could use DSNLess connections localy, could it be done? If so, the includes would simply disappear).
Second, why the 129? Please check http://www.aspfaq.com/show.asp?id=2191 for this chunk of code:
<%
sql = "INSERT INTO <table> (fields) VALUES (values)"
set conn = CreateObject("ADODB.Connec
conn.open "<connection string>"
conn.execute sql, , 129
' 129 is the constant for adExecuteNoRecords + adCmdText
...
%>
>>' 129 is the constant for adExecuteNoRecords + adCmdText
gotcha. i think i would like to try that line without the options at all.
conn.execute sql ', , 129
>>First, the reason why my include file is so short:
you can leave everything how you have it, you just need to change the connection string. i do the same thing you do, except i use only one include file with both strings. on the dev server i comment out the live string. on the live server, i comment out the dev string.
<%
' comment string not in use.
'connstring = "DSN=Clientes" ' development string
connstring = "brinkster_dsnless_string" ' production string
%>
>>I wonder if I could use DSNLess connections localy, could it be done?
this can be done also, but i wouldnt completely remove the include file. do it as i suggested above and it will work very smoothly. check out this site for example query strings.
www.connectionstrings.com
gotcha. i think i would like to try that line without the options at all.
conn.execute sql ', , 129
>>First, the reason why my include file is so short:
you can leave everything how you have it, you just need to change the connection string. i do the same thing you do, except i use only one include file with both strings. on the dev server i comment out the live string. on the live server, i comment out the dev string.
<%
' comment string not in use.
'connstring = "DSN=Clientes" ' development string
connstring = "brinkster_dsnless_string"
%>
>>I wonder if I could use DSNLess connections localy, could it be done?
this can be done also, but i wouldnt completely remove the include file. do it as i suggested above and it will work very smoothly. check out this site for example query strings.
www.connectionstrings.com
ASKER
I finally managed to find what was causing the error: the error was due to the 2 boolean fields. Please look at the way I had to build the sql statement - only this way I could make it work - is there a more adequate way to do this?
Btw, I followed your suggestions about the use of ADO, and it works fine, with or whithout
the 129 parameter.
<%
inputNumAnuncios = 5
inputIDCompra = 214
inputDataLimIniPublic = dateadd("yyyy",2,now())
inputDestacados = False
inputComFoto = True
sql="insert into tblN2PecasVDAnuncios(IDCom pra,DataLi mIniPublic ,Destacado ,ComFoto) values("
sql=sql & inputIDCompra
sql=sql & ", #" & inputDataLimIniPublic & "#"
// Can you believe this?
if inputDestacados = True then
sql = sql & ", True"
else
sql = sql & ", False"
end if
if inputComFoto = True then
sql = sql & ", True"
else
sql = sql & ", False"
end if
sql=sql & ")"
response.write(sql & "<br><br>")
%>
<!--#include file="ConBDClientes.inc"-- >
<%
set oConn = Server.CreateObject("ADODB .Connectio n")
oConn.Open ConnString
for anuncio=1 to inputNumAnuncios
oConn.execute sql
next
oConn.close
set oConn = Nothing
%>
Btw, I followed your suggestions about the use of ADO, and it works fine, with or whithout
the 129 parameter.
<%
inputNumAnuncios = 5
inputIDCompra = 214
inputDataLimIniPublic = dateadd("yyyy",2,now())
inputDestacados = False
inputComFoto = True
sql="insert into tblN2PecasVDAnuncios(IDCom
sql=sql & inputIDCompra
sql=sql & ", #" & inputDataLimIniPublic & "#"
// Can you believe this?
if inputDestacados = True then
sql = sql & ", True"
else
sql = sql & ", False"
end if
if inputComFoto = True then
sql = sql & ", True"
else
sql = sql & ", False"
end if
sql=sql & ")"
response.write(sql & "<br><br>")
%>
<!--#include file="ConBDClientes.inc"--
<%
set oConn = Server.CreateObject("ADODB
oConn.Open ConnString
for anuncio=1 to inputNumAnuncios
oConn.execute sql
next
oConn.close
set oConn = Nothing
%>
where are the inputDestacados and inputComFoto values coming from?
ASKER
The contents of these 2 variables come from 2 boolean fields of a table in the same DB. Please remember that this DB was created under the Portuguese version of Access 2000, although, in theory, this should not be a problem.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if the field is auto number, then you shouldnt include it in an insert statement. it should be skipped to let the db fill it in.