Database does not support updating, but I know it does 2

[Light777]

I am getting this error when I run code:

Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

The line that the error is in is the connection string which is:

rs.Open strSql, db, adOpenDynamic

When I try to do this:

rs.Open strSql, db, adOpenDynamic, adLockOptimistic

I get this error:

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Please help.

 

[Veep]

Are you including the file ADOVBS.inc? If not ADO doesn't know what adOpenDynamic and adLockOptimistic are. Also:
Don't use recordsets. You'll have much more success.
<%
set conn=server.createobject(&quot;ADODB.CONNECTION&quot
myConnString = &quot;YourConnectionStringHere&quot;
conn.open myConnString
strSql=&quot;SELECT Something FROM myTABLE WHERE SomeCriteria='&quot; & Whatever & &quot;'&quot;
set rs=conn.execute(strSql)
If not rs.eof then
do while not rs.eof
'do your thing here
rs.movenext
loop
End if
set rs=nothing
conn.close
set conn=nothing
%>

Try it. You'll like it!

 

[Light777]

It still doesn't work, I modified it to basically what you suggested, and I do like that way, it is much simpler. All I'm trying to do is to create a new user registration.

Here is the code:

<%@ Language=VBScript %>
<!-- #include file=&quot;includes/AccessConnect.asp&quot; -->

<%
if Request.Form(&quot;password&quot = &quot;&quot; or Request.Form(&quot;UserName&quot = &quot;&quot; or Request.Form(&quot;passwordconfirm&quot <> Request.Form(&quot;password&quot then
Response.Redirect &quot;../NewUserError.htm&quot;
end if

Dim e
Dim rs
strSql = &quot;SELECT * FROM Members WHERE UserName = '&quot; & Request.Form(&quot;UserName&quot & &quot;'&quot;
rs = db.execute(strSql)

If rs.EOF then
rs.AddNew
rs(&quot;UserName&quot = Request.Form(&quot;UserName&quot
rs(&quot;Password&quot = Request.Form(&quot;password&quot
rs.Update
rs.MoveFirst
Session(&quot;Account_ID&quot = rs(&quot;Account_ID&quot
Response.Redirect &quot;../NewUserRegistration.htm&quot;
else
Response.Redirect &quot;../UserNameTaken.htm&quot;
end if
%>

The includes file is just the connection string to the database. I still can't figure out what is wrong though.

 

[Veep]

You're sort of coding this like you are in a module of an Access database. Looks like DAO. That stuff goes out the window here.
<%
<%@ Language=VBScript %>
<!-- #include file=&quot;includes/AccessConnect.asp&quot; -->

<%
if Request.Form(&quot;password&quot = &quot;&quot; or Request.Form(&quot;UserName&quot = &quot;&quot; or Request.Form(&quot;passwordconfirm&quot <> Request.Form(&quot;password&quot then
Response.Redirect &quot;../NewUserError.htm&quot;
end if

'****** Many changes *******
strSql = &quot;SELECT * FROM Members WHERE UserName = '&quot; & Request.Form(&quot;UserName&quot & &quot;'&quot;
Set rs = db.execute(strSql)

If rs.EOF then
set rs=nothing
'Create insert statement
strSql=&quot;Insert INTO Members (UserName,Password) &quot; _
& &quot;Values('&quot; & Request.Form(&quot;UserName&quot & &quot;','&quot; & Request.Form(&quot;PassWord&quot & &quot;')&quot;
db.execute(strSql)
strSql=&quot;SELECT MAX(Account_ID) AS LastID FROM Members&quot;
set rsLastID=db.execute(strSql)
AccountID=rsLastID(&quot;Account_ID&quot
Session(&quot;Account_ID&quot = AccountID
set rsLastID=Nothing
db.close
set db=nothing
Response.Redirect &quot;../NewUserRegistration.htm&quot;
else
set rs=noting
db.close
set db=nothing
Response.Redirect &quot;../UserNameTaken.htm&quot;
end if
%>

Also, make sure that the IUSR_<MACHINE_NAME> account has read/write permissions on the folder that your database resides in.

 

[Light777]

Again I like your solution, and you are probably right, I am writing this a little wierd, I'm not used to ASP yet. However, I still get an error in the syntax of the INSERT INTO statement. I don't see anything wrong with it though.

 

[Veep]

OK. Let's try this. We'll take it from &quot;If rs.eof&quot;
<%
If rs.EOF then
set rs=nothing
'Create insert statement
'Assign the UserName & Password to variables
'We're assuming at this point that some values came over
UserName=request.form(&quot;UserName&quot
UserPassword=request.form(&quot;Password&quot
strSql=&quot;Insert INTO Members (UserName,Password) &quot; _
& &quot;Values('&quot; & UserName & &quot;','&quot; & UserPassword & &quot;')&quot;
%>
Now, if you're still getting an error because of the INSERT statement do this:
On the next line after the insert statement (but BEFORE db.execute(strSql) type this:
response.write(strSql)
response.end() 'this will stop execution of the page before the execute
This will show you whether or not there is a problem with the SQL syntax. It's pretty standard debugging practice that will display exactly what the interpreter and the database will see when you to the db.execute(strSql).

 

[Light777]

Ok, I didn't assign the Posted data to variables, because that shouldn't matter...

However, I did use the Response.Write(strSql) command, and it gave me this line:

INSERT INTO Members (UserName, Password) VALUES ('User', 'Password')

Again I see nothing wrong with the Syntax, could there be something else going on? This is an odd error.

 

[Veep]

Post the error. Make sure you have &quot;Show friendly HTTP error messages&quot; UNCHECKED. <Tools><Internet Options><Advanced><Browsing - towards the bottom>

 

[Light777]

Here is the error info:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/NewUser.asp, line 17


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)

Page:
POST 72 bytes to /NewUser.asp

POST Data:
UserName=User&password=Password&passwordconfirm=Password&NewUser=Sign-up

 

[Tarwn]

Ah, you have run into the wonderful problem of reserved words in Access. Certain words in Access are reserved words (ie internal methods, functions,variables that are exposed for the outside world to use) and when you have a field name that is both a reserved word and a field name you confuse access (when your sending it SQL, doesn't occur in the query editor).
Try putting square brackets around Username and Password.

I think you may also have another error, as the reserved word conflict generally doesn't throw an error, just does absolutely nothing and pretends it didn't get an SQL statement from you.
Try pasting the SQL string you print out straight into the query analyzer, it may be that you have a primary key violation or you need to enter data for another field that is marked as &quot;not null&quot; and is currently in your sql statement.

-Tarwn

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}

http://www.tiernok.com/

The never-completed website

 

[Light777]

Ok, here is the error that I get now...

Error Type:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/NewUser.asp, line 16

When I run the exact query in Access, it works though.

 

[Veep]

This error is almost always a permissions issue where the IUSR_MachineName or authenticated user doesn't have read/write access to the mdb file or the folder in which it resides.

Right-click the folder that the database resides in. Select <Properties> from the menu. Select the <Security> folder. Click <Add>. Double-click on the user: IUSR_YOURMACHINENAME. Click OK. Make sure that the IUSR_YourMachineName is highlighted. Make sure that READ and WRITE are CHECKED. Click <Apply>.

 

[Light777]

Where can I find the security folder, all I have under properties is General, Sharing, Web Sharing, and Customize?

 

[Veep]

I meant the Security Tab. What is your operating system? I guess it's not W2K Pro. It's been so long since I've used anything else that I'm not sure I can guide you through this one. When you select the Web Sharing tab, is there a grayed out button that says &quot;Permissions&quot;? If so, share the folder, and give read/write permissions to anonymous user, IUSR_machinename IWAM_machinename. If you don't have the permissions option, I'd start a new thread stating your problem along with your operating system, version of IIS (or PWS if that's what you're using). I remember using PWS on a Windows 98 machine and not having any problems with database access. I just can't remember what I did.