Library tutorials & articles
Creating a Members Area in ASP
By James Crowley, published on 16 Oct 2001
Preparation
For this example, we are going to use a MS SQL Server database. This doesn't mean you have to, as the SQL statements will work whatever the database; you'll just need to change the connection code.
Before we start writing any ASP code, create a new MS SQL Server database called testdb, and then create a new table in it called members. Add the following columns (we've included both the MS SQL Server, and the Access descriptions)
| Name | SQL Server Data Type |
Access Data Type |
Notes |
| id | int (AutoIncrement=True) | AutoNumber | Primary Key |
| username | varchar (20) | Text (FieldSize=20) | Unique |
| password | varchar (20) | Text (FieldSize=20) |
Now, we'll create a short ASP script that connects to the database. Save it as inc-dbconnection.asp.
|
inc-dbconnection.asp <%
|
James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audience ...
Related articles
Related discussion
-
VB6 Runtime error 381 subsript out of range Error
by Uncle (2 replies)
-
passing and reading parameters from using Shell
by jigartoliya (0 replies)
-
Convert C++ code to VB6
by mawcot (4 replies)
-
Help to Call ASP function from onclick event in HTML to pass an array
by vka (0 replies)
-
listbox scrollbar
by Dennijr (10 replies)
Related podcasts
-
Scott Guthrie
Scott catches up with Scott Guthrie in an interview covering Ajax, Asp 2.0, extender controls, CSS adapters and more.
Hello, thanks for the code it really works!
Actually I´m using SQL Server 2000 and it is possible to register the users with the same user name, and there is not any message that indicate the user exists in the date base. Could you please help me with this!!
Thanks!!
I want to with u a happy day .. And thank's once again for giving us the source code.. but unfortunately i just use your code for my following project but then i got some error said ---The include file 'inc-dbconnection.asp' was not found.![Sad [:(]](/emoticons/emotion-6.gif)
--, I try to look at your source code pages in the "Creating A Members Area IN ASP" ![Sad [:(]](/emoticons/emotion-6.gif)
but i couldnt find one? can you give me the link to copy the file?
i really need this script for my project!!
Thanks..
Regards From Shawne![Big Smile [:D]](/emoticons/emotion-2a.gif)
If I look at the page in Dreamweaver or similar I also see the code. However, when it is uploaded to my web server it appears without the code.
Are you sure your web server can run asp?
Hi there
I'm new to asp and thought i'd start applyinh what I've been reading by using this tutorial. When I attempt to use login.asp or register.asp I receive the following error:
Microsoft JET Database Engine error '80040e4d'
Not a valid password.
/inc-dbconnection.asp, line 12
I am using an MsAccess 2000 database.
Does anyone have any clues?
Nathan
Hi, I've never used ASP before and just want to create a members area where I can view the members and their attributes in a simple non-relational database, for my website that is being updated (freebets4all.com).
Can anyone tell me is this tutorial written for Microsoft Visual Web Developer Expresss Edition as it doesn't seem to follow.
Where do I write the first bit of ASP code on the preparation page. The closest new file I can create is an aspx file not asp file. Any help regarding this would be appreciated. To help you reply i am 2nd year uni computer science level.
I don't understand where the ASP code goes.
I really don't understand how to make the register form. Could someone send me their registration form?
My email is intellistar@gmail.com
thanks,
Gil
with the registration.asp there seems to be a problem some of the code appears on the page
"<%=Server.HTMLEncode(Request.Form("username"))%>"
this code appears on the text box area
can anyone help on why this is happening?
<%
If Session("loggedin") <> True Then Response.Redirect "login.asp"
%>
I didn't see anyone posted the problem I had, so I thought I could give some contribution.
Look at red highlighted word that I had to change in order for it to work:
'// create the SQL
strSQL = "SELECT id,password FROM members WHERE id='" & _
fixQuotes(Request.Form("username")) & "'"
Everything else seems to work good except I didn't use register.asp just yet.
Hello . . . I'm using your script, and it works, but only temporarily. Once logged in, I get sent back to my login page after a seemingly random sequence and number of clicks through my members only area. Everything will seem to work well and I can navigate the members area fine. Then all of a sudden the next click will send me back to the login page. There's no consistency as to when or where or what link I click on to cause this. It seems like the session info is being lost. Have you heard of this before? What can I do to squash this issue? Any help would be greatly appreciated! The code is really good except for this issue.
Thanks,
Justin
can anybody help me with the codes on a login/logout session in ASP to SQL database? I'm just a beginner on ASP so im having hard time tackling on the syntax but in logic i think I can handle.. pls....post it here or just e-mail me at karlmarx_37@yahoo.com
tanx everyone
Have you actually got a field in the database table called username?
Hi,
I'm been think or missing something! Can you please tell me where i create the ASP SCRIPT inc-dbconnection.asp? Is it frontpage or ms access? and what is the ms access script
Thank you
i have been at this for a few days now. at first i was getting:
That username is already in use. Please choose another
then i got smart and read-up on the forums and discovered that i needed to share the folder in which the testdb.mdb file was contained in.
now i get this new error:
- An error occured. -2147217900 : The INSERT INTO statement contains the following unknown field name: 'username'. Make sure you have typed the name correctly, and try the operation again
and this is what i have for that section in the register.asp:
'// create the SQL
strSQL = "INSERT INTO members ([username],[password]) VALUES " & _
"('" & fixQuotes(Request.Form("username")) & "','" & _
fixQuotes(Request.Form("password")) & "')"
'// run the SQL
objConn.Execute strSQL
'// check for an error
'// ATTENTION: this should be changed depending on the database provider
If Err.Number = -2147467259 Then
strError = "- That username is already in use. Please choose another<br>" & vbNewLine
ElseIf Err.Number <> 0 Then
strError = "- An error occured. " & Err.Number & " : " & _
Err.Description & "<br>" & vbNewLine
is there anyone else out there with the same problem or who can help.......
please help......
i used your "creating a members area in ASP" codes but i have a big problem its is that when a user registers in my site, even the username entered is used and alresdy exist in the database table it registers the user and add the username and password in the database. how can i correct this problem pls help me
an indian is looking for help pls help me in this matter
Hi, I get this screen when I submit the register form:
Method Not Allowed
The requested method POST is not allowed for the URL /register.asp.
Apache/1.3.31 Server at www.asesoresrh.com Port 80
Can anybody help me?
On the registration page, I'm getting this error too...
I'm using an access database and I know the connection is good becuase I entered an id and pw directly to the db and then used the login.asp page just fine. I was redirected to the welcome default.asp page. So I know the error is somewhere in the SQL statement for the register.asp page. The INSERT INTO statement appears to be having problems with ab Access 2000 database.
I commented out a portion of code so I could see the real error message behind the script and it says
- An error occured. -2147467259 : Operation must use an updateable query.
Any ideas how to fix this?
The error detection code doesn't give you the specific problem. The fix is this: Goto where you have the DB file and change the file permissions to either allow 'Everyone' write+modify, or full access. The safest bet is to add the 'IUSR_MACHINE' user to the permissions and give it read,write,modify access.
Works like a charm!
Can some1 plz just send me a download file 2 phil99999@hotmail.com and in the download file there must just be the database adn the login and the ahtors files i need plzplz and make it work so if i jsut put then on me site they work
i have the same prob how do i make me username uniqe
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/philip/zith/Bruger/login.asp, line 31
this is a msg when i try to log on whit a user
when i try to make a user in the register.asp i get the error msg username already exits why i have tryed more names like jalhgak and i get the same error msg
<% If Request("r") <> "r" then response.redirect("thesamepage.asp?r=r") %>
Basicly it looks for the Request("r") if its not there it reloads the page with it.
Good Luck
Robert
HDSS.COM
How do I integrate this script in a online book store ?
I want to create a sample of an online book store for my College project.
Do you know how I can create such a site ?
- validate users
- search for the products
- Charts
- simulation of payment through CC
I had the same problem when i was linking to a databse in windows using IIS the way i got round this was to share the folder the udatable query problem is usually becase it cannot write to the database this can be because of:
Read only Access on the database
(If using IIS) have the dbase in the root folder of your website
Or change the dir settings to be shared on the folder hope this helps
Kained
I'm using an access database and I know the connection is good becuase I entered an id and pw directly to the db and then used the login.asp page just fine. I was redirected to the welcome default.asp page. So I know the error is somewhere in the SQL statement for the register.asp page. The INSERT INTO statement appears to be having problems with ab Access 2000 database.
I commented out a portion of code so I could see the real error message behind the script and it says
- An error occured. -2147467259 : Operation must use an updateable query.
Any ideas how to fix this?
You'll want to add some additional code to the files in the private members area to ensure that the browser does not cache the page:
http://www.developerfusion.com/show/1010/14/
I have the same problem, when using the back button in the browser. Haven't been able to solve it, but then again, I ain't exactly Einstein at this stuff.
TIA
-Gate
It's working... I got it all figured out.
Thanks,
-M
As far as I can see, that code should work fine - I assume you're not getting any errors at all?
What error occurs, and when?
I have attempted to add additional fields but am not sure I am doing it correctly. I am not a Database guru or even an ASP guru for the matter. Can some one point me in the right track? My username and passwords are getting inserted into the DB but the email field is not.
Thanks,
-Michael Maxim
Here is what I have so far:
You can view my code in a text file at http://www.michaelmaxim.com/regtext.txt
i have tried this code , it work perfectly except for the part on the username. i tried entering same username to test the system but however, it didn't show the "the username is already in use" error message Please help. my codes are as follows. Thanks a lot
<%
Option Explicit
Dim strError, strSQL
'see if the form has been submitted
If Request.Form("action")="register" Then
'the form has been submitted
'// check if compulsory items has been submitted
If Request.Form("username") = "" Then _
strError = strError & "<font color=red>Username,</font>"
If Request.Form("password") = "" Then _
strError = strError & "<font color=red> Password,</font>"
'check if the passwords are the same... but don't display it if the password field is blank.
If Request.Form("password") <> Request.Form("password_confirm") _
And Request.Form("password") <> "" Then _
strError = strError & "<font color=red>Your passwords do not match<br></font>" & vbNewLine
If Request.Form("name") = "" Then _
strError = strError & "<font color=red> Name,</font>"
If Request.Form("address") = "" Then _
strError = strError & "<font color=red> Address,</font>"
If Request.Form("postcode") = "" Then _
strError = strError & "<font color=red> Postcode,</font>"
If Request.Form("city") = "" Then _
strError = strError & "<font color=red> City,</font>"
If Request.Form("state") = "" Then _
strError = strError & "<font color=red> State,</font>"
If Request.Form("phone") = "" Then _
strError = strError & "<font color=red> Phone,</font>"
If Request.Form("hphone") = "" Then _
strError = strError & "<font color=red> Hphone,</font>"
If Request.Form("email") = "" Then _
strError = strError & "<font color=red> Email address</font>"
'// check if an error has occured
If strError = "" Then
'continue
'include database connection code
%>
<!--#include file="connection.inc"-->
<%
On Error Resume Next
'// create the SQL
strSQL = "INSERT INTO customers ([username],[password],[name],[address],[postcode],[city],[state],[phone],[hphone],[email]) VALUES " & _
"('" & fixQuotes(Request.Form("username")) &"','"&_
fixQuotes(Request.Form("password")) & "','" & _
fixQuotes (Request.Form("name")) &"', '"&_
fixQuotes(Request.Form("address"))&"','" &_
fixQuotes(Request.Form("postcode")) & "','"&_
fixQuotes(Request.Form("city")) & "','" &_
fixQuotes(Request.Form("state"))&"','"&_
fixQuotes(Request.Form("phone")) & "','" &_
fixQuotes(Request.Form("hphone")) & "','" & _
fixQuotes(Request.Form("email"))&"')"
'// run the SQL
objConn.Execute strSQL
'// check for an error
'// ATTENTION: this should be changed depending on the database provider
If Err.Number = -2147467259 Then
strError = "- That username is already in use. Please choose another<br>" & vbNewLine
ElseIf Err.Number <> 0 Then
strError = "- An error occured. " & Err.Number & " : " & _
Err.Description & "<br>" & vbNewLine
Else
'record created... redirect
Response.Redirect "login1.asp"
Response.End
End If
'restore standard error handling
On Error Goto 0
End If
<%@LANGUAGE=VBScript%>
<% Option Explicit
Dim strError, strSQL
IF Request.Form("action")="register" THEN
' Form has been submitted
' Check username & password
IF Request.Form("uname")= " " THEN strError = strError & "Please enter a username<BR>" & vbNewLine
IF Request.Form("pword")= " " THEN strError = strError & "Please enter a password<BR>" & vbNewLine
IF Request.Form("pword") <> Request.Form("pword_confirm") AND Request.Form("pword") <> " " THEN _
strError = strError & "Your passwords don't match<BR>" & vbNewLine
IF strError = " " THEN
%>
<!-- #INCLUDE FILE="dbconn.asp" -->
<%
ON ERROR RESUME NEXT
strSQL = "INSERT INTO members ([uname],[pword]) VALUES " & _
"(' " & fixQuotes(Request.Form("uname")) & " ', ' " & fixQuotes(Request.Form("pword")) & " ')"
Conn.Execute strSQL
If Err.Number = -2147467259 Then
strError = "The username you entered is chosen. Please select another.<BR>" & vbNewLine
ElseIf Err.Number <> 0 Then
strError = "Error occured. " & Err.Number & " : " & Err.Description & "<BR>" & vbNewLine
Else
Response.Redirect "login.asp?msg=" & Server.URLEncode("Thank you for your registration with us")
Response.End
End If
On Error Goto 0
End If
If strError <> " " Then
strError = "<P><FONT COLOR=""#FFOOOO"">Errors occured:" & "</FONT><BR>" & vbNewLine & strError
End If
End If
Function fixQuotes(strData)
fixQuotes = Replace(strData, " ' ", " ' ' ")
End Function
%>
<HTML>
<HEAD>
<TITLE> Free online registration page</TITLE>
</HEAD>
<BODY>
<H1>Member Registration</H1)<BR>
<P>Please fill out the form to register, and gain access to our members are.</P>
'<%=strError%>
<FORM ACTION="register.asp" METHOD="POST">
<INPUT TYPE="hidden" NAME="action" VALUE="register">
<TABLE BORDER="1">
<TR>
<TD><B>Username</B></TD>
<TD><INPUT TYPE="text" MAXLENGTH=20 NAME="uname"
VALUE="<%Server.HTMLEncode(Request.Form("uname"))%>"></TD>
</TR>
<TR>
<TD><B>Password</B></TD>
<TD><INPUT TYPE="password" MAXLENGTH=20 NAME="pword"
VALUE="<%Server.HTMLEncode(Request.Form("pword"))%>"></TD>
</TR>
<TR>
<TD><B>Confirm your password</B></TD>
<TD><INPUT TYPE="password" MAXLENGTH=20 NAME="pwordconfirm"
VALUE="<%Server.HTMLEncode(Request.Form("pwordconfirm"))%>"></TD>
</TR>
<TR>
<TD></TD>
<TD><INPUT TYPE="submit" VALUE="Submit Now"></TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
I used some of the code in existing web pages of an admin I built. When I clicked the logout on the admin home page and then hit the browser back button it returned to my admin page showing all the selections. As far as I can tell the session was terminated because if I chose an action on the admin page, it would redirect to an access denied page because the session was false. Even though the session is terminated, I am still a little concerned since the browser back can return to the page. Should I not worry and "learn to love the bomb"
or is something I can do?
Thanks,
James
However in order for the "not logged in" redirection function to work correctly you must refresh the page.
I have also added a name call, the after the verifcation has occured, greets the user by username. BUT
once again in order for this to work correctly you must refresh the page.
Likewise the login page, in order for the user to be logged out, the page must be refreshed.
So the code is correct i would image, but the pages are not loaded from the server on each access of the page.
I have used meta tags to prevent caching on each page involved ie:
<META HTTP-EQUIV="PRAGMA" CONTENT="NO-CACHE">
I have also tried using this :
<%@ Language=VBScript %>
<%
response.expires = 0
response.expiresabsolute = Now() - 1
response.addHeader "pragma","no-cache"
response.addHeader "cache-control","private"
Response.CacheControl = "no-cache"
%>
I am using PWS on win98.
If anyone could help at all it would be most appreciated.. Thanks
Hi,
I have trying to try this code but I get alot of "your username has already been used" and my database is empty!
Can someone help me out?
Cheers,
Iona
Just like to say excellent and well thought out tutorial - I have tryed to modify the code so it picks up and stores an email address (normal text field) but have not successfully completed this - can you tel me what code needs to be modified please.
Best regards,
Scott Quinn
Check the security of your .mdb database. Make sure that it's not set to read only and that you have full rights assigned to it. That should do it.
Hope that Helps!
the_sleeper
Hope That Helps!
the_sleeper
The App runs fine, EXCEPT for two weird issues..
1.) When I EXCLUDE the following code, the MEMBERS PAGE loads fine. But, heck, that defeats the purpose of the whole article, eh? But when I INCLUDE the code. It STILL sends me back to login.asp!?!
<%
If Session("loggedin") <> True Then Response.Redirect "login.asp"
%>
2.) In members.asp there is a "response.redirect" issue. I cannot seem to redirect to another directory in the web OTHER than the root "/" directory
'redirect to members area
Response.Redirect ("/members/")
Check that you haven't inadvertantly placed any spaces/new line characters before the first <% sign... otherwise ASP will start outputting the page even though we want to redirect...
fixQuotes prevents any errors from occuring in our SQL if the user enters a ' in the input fields.
The crucial thing missing from your code is the minus sign:
If Err.Number = -2147217900 Then
'error...
i'm having the exact same problem. were you able to solve it somehow?
Does anyone have any idea why or what i can do.
How do you change the script so that the user just enters in a username and a password given to them it then gets verified and they are prompted to change there password only one. After all that is complete I would love for that user to view info about them in another accessdb where there records are stored.
Can Anyone Help Me.
Thanks
Please help i keep having this error when i submit register.asp.
what can i do????????????? help please
THIS IS THE ERROR
Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
/will77/reg/register.asp, line 36
I use windowsXP and access 2000
please help
regards
tony
Thanks,
Nathan
The following errors occured:
- An error occured. -2147217900 : [Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
and here is the code in the register.asp page:
'// check for an error
If Err.Number = 2147217900 Then
strError = "- That username is already in use. Please choose another<br>" & vbNewLine
ElseIf Err.Number <> 0 Then
strError = "- An error occured. " & Err.Number & " : " & _
Err.Description & "<br>" & vbNewLine
Else
'record created... redirect
Response.Redirect "Mlogin.asp?msg=" & Server.URLEncode("Thank you for registering")
Response.End
End If
Please help
you can set a session variable with the following code
Session("id") = UserID
Read the Session variable
Response.Write Session("id")
Note: some people have session variable/cookies disabled
Hope that helps, Jono
filtering is usually done using a QueryString these are the part in the url after the "?"
http://www.developerfusion.com/forums/post.aspx?tid=3460
^^^^^^^
This value can be used from the code
TopicID = Request.QueryString("tid")
You use Session Cookies with this code
Response.Write Session("userid")
Hope that helps
Jono
this sounds interesting. How do I make sure my cookie is populated and how do I reference it or use in other pages as a variable name? say for filtering the results of a query?? Thanks.
This is awesome! I actually got this to work! When I come back from the login.asp I send them to page where I want to display the results of a query against an Access database showing the products they have already bought. I want to pass in the populated username variable from the register.asp page and either run the query when the page loads or run it off a button click. How do I work with the username variable in this and other pages I might redirect them to?? Thanks so much! I have been laboring at this project for weeks before finding your tutorial.
I have been using just the login section of your code, and as I have been adding members to the Access database manually it works perfectly - members can log in without any trouble. I have decided to use the full code now so new members can add themselves, but I have been getting this error:
- An error occured. -2147467259 : Operation must use an updateable query.
I guess it's something to do with the Accsess database but I don't know much about it. I am using Access from Office XP. Can you please help?
Thanks : )
i have created members area from your instruction.
but my database(members) is receiving same usernames.
So i think 222 error function does not exist . please suggest me.
and check my script
222 error function
If Err.Number = 222 Then
strError = "- That username is already in use. Please choose another<br>" & vbNewLine
ElseIf Err.Number <> 0 Then
strError = "- An error occured. " & Err.Number & " : " & _
Err.Description & "<br>" & vbNewLine
Else
'record created... redirect
Response.Redirect "login1.asp?msg=" & Server.URLEncode("Thank you for registering")
Response.End
End If
'restore standard error handling
On Error Goto 0
reply me soon(smruti2002)
how the heck do you display details that are unique to each member eg: their email address, their last posts, points they may have acquired etc. sorry if it may sound like a dumb question but if you could let me know I'd really appreciate it. Thanks, Rich: richardgillian@hotmail.com
Mail me at aalokcool@yahoo.com
HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services
--------------------------------------------------------------------------------
Technical Information (for support personnel)
Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
/login.asp, line 31
Line 31 is:
Set objRS = objConn.Execute (strSQL)
So default.asp isn't displayed
What am i doing wrong?
Thnx in advance!
Microsoft VBScript compilation error '800a0400'
Expected statement
inc-dbconnection.asp, line 6
accessdb = this is the url i link to the database i put here
Now getting this above error.
I don't think the inc-dbconnection.asp is set-up right. I copied it straighr of the page and on to the site (didn't adjut it). I am using an Access 2000 database, which i also uploaded to the same place as the other .asp files.
ADODB.Connection.1 error '80004005'
SQLState: 01S00
Native Error Code: 0
SQLState: 08001
Native Error Code: 0
[MERANT][ODBC SQL Server Driver]Insufficient information to connect to the data source
[MERANT][ODBC SQL Server Driver]Invalid connection string attribute
inc-dbconnection.asp, line 14
I am getting this error after clicking the submit button on the register page. My datbase is saved as testdb.mdp fill. Is that right? Any ideas woul be great plz.
I have set up the members area but when you try to login you get this error
Response object error 'ASP 0156 : 80004005'
Header Error
/members/login.asp, line 44
The HTTP headers are already written to the client browser. Any HTTP header modifications must be made before writing page content.
What should i do?
I want my Window to be of a fixed size (and the text box is in a table). It seems because of this... the str.Err message won't appear.
Can I display the str.error message in another text box? something like.... if you had another text box next to the Subject text box on this screen.
Thanks,
Amy-
i didnt read all of what GeoRod said but basically the cut down for the problem is that the DATABASE permissions need ot be changed.
Why does it happen? Well when you open a MDB(Access Format) it creates a tmeporary file(try it your self and you'll see something like lld or something file appaering) so on your webserver you will need to allow the DB to 'create' that file.
What server is it running?(IIS i guess but never the less) just ask your admin/support team about changeing the permissions on the folder where the location of the DB is located to make sure its got write permission.
If you have it hosted on local system just right click on teh explorer's folder options and under Security and turn to the relavent field.
Hope that helps.
I believe this is more applicable....
PRB: ASP Error "The Query Is Not Updateable" When You Update Table Record (Q174640)
The information in this article applies to:
Microsoft Active Server Pages
Microsoft Visual InterDev, versions 1.0 , 6.0
ActiveX Data Objects (ADO), versions 2.0 , 2.1 SP2 , 2.5 , 2.6 , 2.7
Microsoft Internet Information Server versions 4.0 , 5.0
Microsoft Data Access Components versions 2.5 , 2.6 , 2.7
SYMPTOMS
One of the following errors occurs when you update a table record from an Active Server Pages (ASP) page through ADO's Recordset.update method:
Source: Microsoft OLE DB Provider for ODBC Drivers
Error Number: -2147467259
Description: The query is not updateable because the from clause is
not a single simple table name. This may be caused by an attempt to
update a non-primary table in a view.
-OR-
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'<a column from your table>'.
/<your asp file>.asp, line xxx
-OR-
Microsoft OLE DB Provider for SQL Server '80004005' Cannot insert or update columns from multiple tables.
-OR-
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
CAUSE
The following conditions cause this error to occur:
You have created a query that contains more than one table.
You are updating the records returned by this query and the update affects fields in more than one table.
Basically, if the query involves tables that have a one-to-many relationship, the query -as a whole- is not updateable.
RESOLUTION
Process one table's fields independently from another. Issue an Update after each table's fields have been modified. For example, in the code sample below, the "cmdTemp.CommandText" property holds the one-to-many query and the fields of parent table are updated independently of the child table.
<Object creation code removed for clarity>
.
.
.
cmdTemp.CommandText = "SELECT stores.state, sales.qty FROM sales INNER
JOIN stores ON sales.storid = stores.storid"
.
.
.
'update parent table first
Datacommand1("state")="WA"
Datacommand1.update
'now update child table
Datacommand1("qty")=4
Datacommand1.update
STATUS
This behavior is by design.
REFERENCES
For the latest Knowledge Base artices and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:
http://support.microsoft.com/support/vinterdev/
Published Oct 2 1997 7:11PM Issue Type kbprb
Last Modifed Aug 23 2001 8:24AM Additional Query Words
Keywords kberrmsg kbASP kbASPObj kbDatabase kbODBC kbOLEDB kbVisID kbVisID100 kbGrpDSASP kbGrpDSMDAC kbDSupport kbMDAC210SP2 kbMDAC250 kbiis400 kbiis500 kbGrpDSASPDB kbADO250 kbMDAC260 kbADO260 kbATM kbmdac270 kbado270
Look At :
http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q307640
SYMPTOMS
If you try to use a Microsoft Jet (Access) database from multiple instances of the same application either on the same computer or on different computers, you receive the following error message:
-2147467259 Error ODBC Microsoft Access Driver: The database has been placed in a state by an unknown user that prevents it from being opened or locked.
This error occurs with both the Microsoft ODBC Driver for Access and the OLE DB Provider for Jet.
CAUSE
To run an .mdb file by multiple instances, Jet makes use of a lock delay and retry interval. However, under high load conditions, you may exceed these intervals.
RESOLUTION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
http://www.microsoft.com/partner/referral/
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
The following workaround extends the retry delay for Jet so that you can use additional instances. However, it is not recommended that you use this workaround if a high number of updates are being formed; this workaround is intended only for reading databases.
You can add the following sample error handler to your code. Please note that this handler only works with ADO and uses the Sleep function, which you must declare in your general declarations section.
RetryHandler:
' Retry until MAXRETRIES are hit to increment your error count.
errorcount = errorcount + 1
If errorcount < MAXRETRIES Then
Randomize 0
' Sleep a random amount of time, and retry the same operation.
Sleep Int(MAXSLEEPINTERVAL * Rnd) + 1
Resume
Else
' Retries did not help. Show the error, and fall out.
MsgBox Err.Number & " " & Err.Description
Exit Sub
End If
STATUS
This behavior is by design.
MORE INFORMATION
The above-mentioned workaround is only for read-only mode. Microsoft does not support placing Jet .mdb files under high user load. Microsoft strongly recommends that you use Microsoft SQL Server or Microsoft Data Engine (MSDE) instead of Access when high user loads (that is, more than 15 instances) are required or anticipated, especially when updating is required.
REFERENCES
For more information regarding the Sleep function, refer to the MSDN Library documentation.
Published Oct 2 2001 8:12AM Issue Type kbprb
Last Modifed May 13 2002 6:53AM Additional Query Words -2147467259
Keywords kberrmsg kbGrpDSVBDB kbGrpDSMDAC kbDSupport
COMMENTS?
this is a long shot as i dont know nything bout asp, but variable undefined means ure using a
var = var1
or something similar, and u havnt statedwhat var is ie, to overcome this in vb i just put
Dim var
var = var1
probably totally out for asp but worth a shot lol
I am very blank in asp.
could somebody help me on this???
When i want to add a member to my website on the internet i get the following message,
The following errors occured:
- An error occured. -2147467259 : Operation must use an updateable query
It works nicely when I test it on my computer, but when I publish it to my URL, I get this error. What can be the problem.
Sipes
i also forgot to tell you some other things:
You will need to create the 'FullName' field in the database, and also you will need to insert the session assigning code somewhere in the following code:
If objRS("password")=Request.Form("password") Then
'username/password valid
'save session data
so after the 'Save Session Data' would be an ideal place.
hey,
simple. I just had a brief look at the Code.
Heres something you can do:
Insert a field onto the registration form to get the users name(referenced as here as: 'fullname') then in the register.asp(i think thats teh filename) add:
sSQL = "INSERT INTO members (username,password,fullname) VALUES " & _
"('" & fixQuotes(Request.Form("username")) & "','" & _
"('" & fixQuotes(Request.Form("password")) & "','" & _
fixQuotes(Request.Form("fullname")) & "')"
then load the fullname into a session variable like this:
Session("fullname")= objRS("fullname")
then you can add a simple ASP retrieve statement to retrieve the name inserted into the field anyhwere on the site like this:
Welcome <%= Session("fullname")%>
Hope that Helps!
Hi James,
I am not a programmer. I have been studying ASP for less than one year. I guess you are talking about the databank, whemn you mention colum constraining. I will look into that. Thanks a lot.
Some minutes later... I changed the properties of username column. I got it. I just had to change the erro number to show the right message. Instead of 222 I used the number that appeared on my screen: -2147467259 ( it looks more like an international phone number... anyway... I hope this isn't just that session number.)
Thanks again.
Thank you for all of your help! It works great.
I would like to add the users name to the Memebers (default asp) page. So it reads Welcome Who ever to the members area
How can I do that?
Thanks
The
If Err.Number = 222 Then
line.... it may not be error 222 for access .... just try to create a duplicate username and see what error # occurs
Are you talking about changing the acess error number Or the error number in the asp form?
How do I, in Access, change the error number that the code traps?
Or
Is there another way to set the unique constraint in access?
Sorry for all of the questions.
yep; no duplicates will be fine. In access, you may also have to change the error number that the code traps
??
Do I create this unique constraint in the Design view of the database? And,if so, will changing the "INDEXED" to Yes - no duplicates solve the problem?
Is that all I hvae to do or is there more to it than that?
Thanks
No, I didn't even think of that.
Thanks
have you created a unique constraint for the username column?
have you created a unique constraint on the username field? (in the database)
Same here. It works fine but it accepts and sends to db multiple passwords for the same user, even though it uses just the first password to give access to restricted area. The problem is that users don't even know why their password won't be valid (if they registered with a previously registered password).
Almost everything seems to work except the Err.Number 222
If I attempt to create a user name that already exists in the database, it is accepted It seems like the Err.Number 222 script is being skipped?
It is now working and I didn't change anything. I no longer receive the MS Jet error.
Thanks
no problem...
what's the exact error you are receiving?
Sorry about yesterday
I am getting an error after I hit submit. The error is in the inc-dbconnection.asp line 4 (Microsoft Jet error )
What have I done wrong and can you help me?
Thank you
Amy
have you created a constraint in the database on the username field that only allows unique usernames?
I am able to get a user with the same username. According to error 222, they are supposed to be prompted to enter a new one, this is not occuring. PLease help
I am able to get a user with the same username. According to error 222, they are supposed to be prompted to enter a new one, this is not occuring. PLease help
I am having a problem updating the database I get an error:
- An error occured. -2147217900 : Number of query values and destination fields are not the same.
what is the problem here?
strSQL = "INSERT INTO Personal ([username],[Password],[FName],[LName],[Street],[City],[State],[Zip],[Phone],[Birthdate]) VALUES " & _
"('" & fixQuotes(Request.Form("username")) & "','" & _
fixQuotes(Request.Form("Password")) & "','" & _
fixQuotes(Request.Form("Fname")) & "','" & _
fixQuotes(Request.Form("LName")) & "','" & _
fixQuotes(Request.Form("Street")) & "','" & _
fixQuotes(Request.Form("City")) & "','" & _
fixQuotes(Request.Form("State")) & "','" & _
fixQuotes(Request.Form("Zip")) & "','" & _
fixQuotes(Request.Form("Phone")) & "','" & _
fixQuotes(Request.Form("Birthdate")) & "','" & "')"
I am having a problem updating the database I get an error:
- An error occured. -2147217900 : Number of query values and destination fields are not the same.
what is the problem here?
strSQL = "INSERT INTO Personal ([username],[Password],[FName],[LName],[Street],[City],[State],[Zip],[Phone],[Birthdate]) VALUES " & _
"('" & fixQuotes(Request.Form("username")) & "','" & _
fixQuotes(Request.Form("Password")) & "','" & _
fixQuotes(Request.Form("Fname")) & "','" & _
fixQuotes(Request.Form("LName")) & "','" & _
fixQuotes(Request.Form("Street")) & "','" & _
fixQuotes(Request.Form("City")) & "','" & _
fixQuotes(Request.Form("State")) & "','" & _
fixQuotes(Request.Form("Zip")) & "','" & _
fixQuotes(Request.Form("Phone")) & "','" & _
fixQuotes(Request.Form("Birthdate")) & "','" & "')"
Sorry... only just got to your post. Can I just ask what the problem was? Was it a problem with the article, or something at your end?
I know that no 1 has replied any way but i would just like to inform every 1 that there is no need to reply to my above message as i have rectified the problem..
Thank You
I am unable to run the login code, i seem to retrieve an unusual error.
This occurs from the line of code
If Request.Form("username") = "" Then _strError = strError & "- Please enter a username<br>" & vbNewLine Microsoft VBScript compilation (0x800A0408)
Invalid character line 8
however i am sure that the problem comes from the line above with this line of code:
If Request.Form("action")="login" Then
i believe this because if a put a comment in place of the line of code that generates an error, and move that line down i will then receive the error from the commented code
PS could it be because i have a global asa, running in the same directory and that i have used IISManager to create an application oif that directory ??
i Too am using an access database.
I found that it is firstly better to connect using ODBC,
Then make sure the the name you are giving to connect is exactly the same as the ODBC driver name not neccesarly the database name
e.g of the code that works 4 me, this puts all resords and siplays in a table
Dim adoCon 'Holds the DB connection Object
Dim rsRecords 'Holds the recordSet for the records in the DB
Dim strSQL 'Holds the SQL query 2 query the DB
'Creating the ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Opening the connection to the DB using DSN connection
adoCon.Open "DSN=yr6021" ' THIS IS THE NAME OF MY DB, SOCHANGE IT TO YOURS
'Opening an ADO recordset object
Set rsRecords = Server.CreateObject ("ADODB.Recordset")
'Intitialising the strSQL var with an SQL statement
strSQL = "SELECT * FROM amanihmetest1;" 'amanihmetable1.UserID, amanihme.userName, amanihmetable1.password FROM amanihme;" 'AMANIHMETST1 IS THE NAME OF MY TABLE SO U MUST CHANGE
'now i can open the recordSet with what is in the the SQL variable
rsRecords.Open strSQL, adoCon
'producing the results
'loop
Response.Write "<TABLE BORDER=1 CELLSPACING=3 CELLPADDING=1 >" &chr(13)
Response.Write "<TR VALIGN=""top"" ALIGN=""left"">" &chr(13)
For Each TheField in rsRecords.Fields
Response.Write "<TD WIDTH=103><P><B>" & TheField.Name & "</B></TD>"
'&chr(13)
Next
Response.Write "</TR>"
Do Until rsRecords.EOF
Response.Write "<TR VALIGN=""top"" ALIGN=""left"">" &chr(13)
For i=0 to rsRecords.Fields.Count -1
Response.Write "<TD WIDTH=103><P>" & rsRecords.Fields(i) & "</TD>"
Next
Response.Write "</TR>"
rsRecords.MoveNext
Loop
ADODB.Connection.1 error '80004005'
SQLState: 01S00
Native Error Code: 0
SQLState: 08001
Native Error Code: 0
[MERANT][ODBC SQL Server Driver]Insufficient information to connect to the data source
[MERANT][ODBC SQL Server Driver]Invalid connection string attribute
I'm using a microsoft access database and I just copied the code from this line for line, rather than trying a customisation like I did with some of the other code samples I've seen and I still couldn't get a connection.
Does anyone know why this might be. I thought it might be because of the location of my files, but now they're all in the folder with the read/write access on my server and I still can't gain a connection.
Any help would be appreciated!!
1) Looks like it's Unix server, but from what I can gather I'm not sure they support asp(?).
2) You'll probably have gathered that I'm pretty new at this, how do I go about this?
cheers
2) You'll probably have gathered that I'm pretty new at this, how do I go about this?
cheers
for question 1) does the server a mysql database and active server pages? What OS are they running?
for question 2) To solve the
Data source name not found or no default driver specified
error, you need to create the ODBC DSN connection in the System tab rather than the User tab
for question 2) To solve the
Data source name not found or no default driver specified
error, you need to create the ODBC DSN connection in the System tab rather than the User tab
The only problem is that the server I am using (virtual server) doesn't support ODBC so I don't think there is any other way(?). I did try and use it on another site (different server, with MySQL ODBC) and I got the error:
Vbscript Runtime error 'ASP 0185 : -2147467259'
Data source name not found or no default driver specified
inc-dbconnection.asp,line 14
I used DRIVER={MySQL}, but I'm a bit confused as to what code I would need to change.
So 2 questions really 1) What do I do on the server that doesn't support ODBC and 2) What code do I change on the one that does?!
cheers
Vbscript Runtime error 'ASP 0185 : -2147467259'
Data source name not found or no default driver specified
inc-dbconnection.asp,line 14
I used DRIVER={MySQL}, but I'm a bit confused as to what code I would need to change.
So 2 questions really 1) What do I do on the server that doesn't support ODBC and 2) What code do I change on the one that does?!
cheers
Yes, it is. All you need to do is download the MySQL ODBC Driver from mysql.com. Then you can create an ODBC/DSN connection in windows, and your ASP script won't know the difference
(of course, your server still needs to be able to run asp files, however...)
(of course, your server still needs to be able to run asp files, however...)
This is the most useful members area tutorial I have found so far and I've searched some, however I'm not sure if I'm going to be able to use it.
I am using a mysql database, so is it possible to change the code to work with this. If so, what do i change (from/to). If not, does anyone know of a useful php3 tutorial, because I'm blowed if I can find one?
cheers
I am using a mysql database, so is it possible to change the code to work with this. If so, what do i change (from/to). If not, does anyone know of a useful php3 tutorial, because I'm blowed if I can find one?
cheers
Try going to your internet service manager console and restarting your IIS under the action tab with your server highlighted.
To me, that sounds like you have not commented out the bit to connect to an SQL server; so it's sitting there for a while to decide whether the server actually exists or not, and eventually times out. Please check that you have the SQL server/ other access db code lines commented out.
I too got this 500 error. I just copy all the code from the db-connection, and the access database name is testdb.
any suggestion?
Well, firstly the 500 error is a standard error code saying there is an error in an ASP script; it's not necessarily the same one. Also, you shouldn't copy all the code into db-connection... you need to comment out some of the connection code depending on the type of database you are using.....
Try going to your internet service manager console and restarting your IIS under the action tab with your server highlighted.
Cheers,
Eugene
the db-connection.asp, there were 3 lines... 1 for SQL Server, and 2 for different versions of Access:
'// Use this for SQL Server
objConn.Open "Driver={SQL Server};Server=sqlservername;" & _
"UID=username;PASSWORD=password;DATABASE=testdb;"
'// Use this for an Access 2000 database
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("accessdb")
'// Use this for an Access database (earlier than 2000)
objConn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & Server.MapPath("accessdb")
Hi, im using an access database, could someone tell me how to connect to it, instead of an SQL db? for, example, what is the equivalent for:
On Error Resume Next
sSQL = "INSERT INTO members (username,password) VALUES " & _
"('" & fixQuotes(Request.Form("username")) & "','" & _
fixQuotes(Request.Form("password")) & "')"
cConn.Execute sSQL
cheers, Greg
I have Personal Web Manager 5.0 and IIS version 5
I used to run ZoneAlarm but have had to remove for various reasons. I have a Pentium III with a fair chunk of RAM
I am using W2K Also, I am running the site exclusively for myself ie 1 user.
Heres what happened to me:
I had worked out how to serve asp
Things were cool, running some nice apps etc.
Suddenly, HTTP1.0 new application failed
I turned on "nice friendly errors" then
http 500 internal server error
NB I could now display only .html
.asp was incommunicado
I managed to get the server to display the asp files by going to the Internet Services Manager (off Administrative tools)and right-clicking on the computer name and then restarting IIS.
PWM seems to run as normal but the IIS part has chucked a wobbly
Please can someone send me the files I need for this because I don't understand.
I'm using access 2000 db for the database. No SQL here.
How do I add new field, like full name, email etc?
Thanks!
I too got this 500 error. I just copy all the code from the db-connection, and the access database name is testdb.
any suggestion?
You can set a cookie after the logging in of user. And you can display user name in all the pages. You can use hidden form fields also.
First you have to pic up the submit value of USERIG, like
Request.querystring("Textbox name")=of the Get method or
Request.Form("Textbox name")=of the Post method and then you can use it like
<%
dim userid
userid=Request.Form("Textbox name")
%>
<h1><% =userid %></h1>
I want to display the name a user has used to log in on my default page. I tried using <h1><% =userid %> but it doesnt work. Can anyone tell me how to achieve this.
James Crowley said:
It doesn't work because they don't support session variables for general membership.
Ok.
Finesse
Finesse
I just ran a query on the db and all the info was there. I am puttin in the right info, but it neveer takes me to the member page!!!!
Please help!!!
I really need this member section!!!
CREATE TABLE members
(id AUTOINCREMENT PRIMARY KEY,
[username] char(20),
[password] char(20))
This creates the table successfully.
Step one is fine!
Here are the problems I am experiencing:
1. Register.asp allows me to register duplicate usernames, with NO error messages.
2. Even after I register, and login.. i am not taken to the members page. No matter what I do, it says, invalid username/password!!
Please test it out!
Here is the address: http://www25.brinkster.com/urbanaffilntwrk/register.asp
Please help!
djfinesse_215@yahoo.com
If you are writing a program/dll in VB/C++ etc you can reference that type library which makes it easy to use Microsoft ADO etc....
In Active Server Pages, you create an ADO object by using Server.CreateObject("ADODB.Connection") or whatever..
Anyone knows what this file is or does (msado21.tlb). It's part of NT4 IIS4. All I know that it has something to do with Global.asa files and SQL databases.
C:\Program Files\Common Files\System\ADO\msado21.tlb
Thanks,
Mo
Thanks for pointing that out... as I work almost entirely with SQL Server, I forget about things like that... ;-) I have added the []'s around username and password
Hi,
I had the same problem and found out that it may a problem with reserved words, although I cannot find 'username' or 'password' in the reserved word list for 'MS Access' in my case. The solution is to place [ ] around the field names in the SQL statement.
I have been getting the following error:
The following errors occured:
- An error occured. -2147217900 : Syntax error in INSERT INTO statement.
This thread is for discussions of Creating a Members Area in ASP