Insert into msAccess problems
in [ASP]
|
Prev: window based authentication with members defined in a distribution list.
Next: POP3 Toolkit for 64bit server
From: chumley on 24 Feb 2010 19:14 I'm using Access dbase for a simple insertion, when i post from my form page into my insertion script, i get error at the StrConnz.Execute (StrSql) command line of the query needs to be updateable type: Microsoft JET Database Engine error '80004005' Operation must use an updateable query. /providerform/fo-add.asp, line 25 ''''''''''''''''' <%@ LANGUAGE="VBSCRIPT"%> <%Response.Buffer = True%> <% dim dsn,StrConnz dsn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("fo1.mdb") Set StrConnz = Server.CreateObject("ADODB.Connection") StrConnz.Open dsn dim strSql strSql = "insert into tblMain (LastName,FirstName,fund,Industry,Company) values ('" strSql = StrSql & replace(Request.Form("LastName"),"'","''") & "', '" strSql = StrSql & Request.Form("FirstName") & "', '" strSql = StrSql & Request.Form("fund") & "','" strSql = StrSql & Request.Form("Industry") & "', '" strSql = StrSql & Request.Form("Company") & "')" StrConnz.Execute (StrSql) %> not sure why as i am connecting properly to dbase ??? chumley
From: Bob Barrows on 24 Feb 2010 20:13 chumley wrote: > I'm using Access dbase for a simple insertion, when i post from my > form page into my insertion script, i get error at the > StrConnz.Execute (StrSql) command line of the query needs to be > updateable type: > Microsoft JET Database Engine error '80004005' > Operation must use an updateable query. > /providerform/fo-add.asp, line 25 > > ''''''''''''''''' > <%@ LANGUAGE="VBSCRIPT"%> > <%Response.Buffer = True%> > <% > dim dsn,StrConnz > dsn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & > Server.MapPath("fo1.mdb") > Set StrConnz = Server.CreateObject("ADODB.Connection") > StrConnz.Open dsn > > dim strSql > strSql = "insert into tblMain > (LastName,FirstName,fund,Industry,Company) values ('" > strSql = StrSql & replace(Request.Form("LastName"),"'","''") & "', '" > strSql = StrSql & Request.Form("FirstName") & "', '" > strSql = StrSql & Request.Form("fund") & "','" > strSql = StrSql & Request.Form("Industry") & "', '" > strSql = StrSql & Request.Form("Company") & "')" > StrConnz.Execute (StrSql) %> > http://www.aspfaq.com/show.asp?id=2062 Further points to consider: Your use of dynamic sql is leaving you vulnerable to hackers using sql injection: http://mvp.unixwiz.net/techtips/sql-injection.html http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23 See here for a better, more secure way to execute your queries by using parameter markers: http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e Personally, I prefer using stored procedures, or saved parameter queries as they are known in Access: Access: http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
From: Dan on 25 Feb 2010 05:25
"chumley" <blueberryz99(a)yahoo.com> wrote in message news:27439f5d-9992-4ff6-a5aa-d276e2980aaf(a)t9g2000prh.googlegroups.com... > I'm using Access dbase for a simple insertion, when i post from my > form page into my insertion script, i get error at the > StrConnz.Execute (StrSql) command line of the query needs to be > updateable type: > Microsoft JET Database Engine error '80004005' > Operation must use an updateable query. > /providerform/fo-add.asp, line 25 > > ''''''''''''''''' > <%@ LANGUAGE="VBSCRIPT"%> > <%Response.Buffer = True%> > <% > dim dsn,StrConnz > dsn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & > Server.MapPath("fo1.mdb") > Set StrConnz = Server.CreateObject("ADODB.Connection") > StrConnz.Open dsn > > dim strSql > strSql = "insert into tblMain > (LastName,FirstName,fund,Industry,Company) values ('" > strSql = StrSql & replace(Request.Form("LastName"),"'","''") & "', '" > strSql = StrSql & Request.Form("FirstName") & "', '" > strSql = StrSql & Request.Form("fund") & "','" > strSql = StrSql & Request.Form("Industry") & "', '" > strSql = StrSql & Request.Form("Company") & "')" > StrConnz.Execute (StrSql) %> > > not sure why as i am connecting properly to dbase > > ??? > chumley As well as Bob's recommendation of using parameterised queries, also check that the ASP application has write permission to the folder that the mdb file is located in (if not then the database is opened as read only. Is tblMain a table, or a query? If it's a query, it will be unupdateable if any of the tables involved don't have a primary key, or if you are using any joins other than INNER. -- Dan |