Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access ASP Using ASP with Microsoft Access databases. For Access questions not specific to ASP, please use the Access forum. For more ASP forums, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 4th, 2005, 12:57 PM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Error messages

Hi there,

I've created an Access database to hold 2 fields... 'UserName' and 'UserStyle'. I've made 'UserName' the Primary key as I don't want the same record, but i'm getting this error message;

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[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.

/default.asp, line 18

Any ideas?

Thanks/...

Also, how set data in the 'UserStyle' as a variable?

Reply With Quote
  #2 (permalink)  
Old June 5th, 2005, 03:35 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Do you get this error when you try to save the changes to the table in Access??

From the error message, it sounds that the table already contains duplicate records (including null or "" values). Remove those and then try to save again.

Not sure what you mean with your second question.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #3 (permalink)  
Old June 5th, 2005, 06:54 AM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Basically, I've created a database with just 2 field names, no data. They're titled 'UserName' and 'UserStyle', with 'UserName' as the primary key. I've got an internal website running on a Windows 2003 AD domain, which requires users to logon using their username/password from AD.

I've got a page on this website that allows them to change the stylesheet (there's 6 styles).

What I want to happen, is the following (if possible);


In default.asp (which is the main page)
---------------------------------------
If the user logs on for the first time, it creates a record in the 'UserName' field whith their username from AD, and creates a record in the 'UserStyle' field with the default style, which is blue.css. Any further visits will see that the user already has a record in the DB, so will not create a duplicate record.
---------------------------------------

In userEnv.asp (which is included in all other pages)
---------------------------------------
It looks in the DB to see if the user has a record, which they should do as it's the first thing that happens when they logon, and sets the record in 'UserStyle' (which is still blue.css) as a variable called strUserStyle. Each page then uses this variable to set the stylesheet.

on the update_style.asp page
---------------------------------------
When they select a new style, it *updates* the users record in the DB with the new style (silver.css for example), then each page uses the new style as the stylesheet.

Hope this makes sense!!

Thanks for any help/...

Reply With Quote
  #4 (permalink)  
Old June 5th, 2005, 07:12 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Yeah, it makes sense, and it is possible as well.

However, you shouldn't rely on the database preventing duplicate records. The procedure that tries to insert the first record, should first check whether there is already a record for the user. If there is no record yet, insert one, otherwise simply do nothing.

You can't have a variable in your code connected to the database directly. However, whenever the user chooses a new style sheet you should fire some update code that updates the style sheet for the user in the database.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #5 (permalink)  
Old June 5th, 2005, 09:53 AM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the speedy reply.

Could you point me in the right direction on accomplishing the above?

Thanks/...

Reply With Quote
  #6 (permalink)  
Old June 5th, 2005, 10:00 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Do you already have some code? And what is your level of knowledge in ASP and ADO?

It's a bit too much to code this for you, so you'll need to do some Googling and reading. For some very basic stuff about inserting records in a database, check here:

http://www.asp101.com/samples/form_to_db.asp or here: http://www.codetoad.com/directory_type193.asp or here: http://www.google.com/search?hl=en&l...s+database+ASP

If you already have some code, you could post it here and ask specific questions about it.

You could also take a look at the books Beginning Active Server Pages 3.0, Professional Active Server Pages 3.0 or Beginning Dreamweaver MX / MX 2004 if you're using Dreamweaver for your coding.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #7 (permalink)  
Old June 5th, 2005, 12:07 PM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is the code I have for the default.asp (first page);

Code:
<%
    Dim objConn, rsEA, strSQL, strUserName, strUserStyle, strDefaultData, strUserInfo, strURL

    strUserName = Request.ServerVariables("LOGON_USER")
    strDefaultStyle = "blue"

    Set objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open "DBQ=" & Server.MapPath("/database/ea.mdb") & ";DRIVER={Microsoft Access Driver (*.mdb)};"

    Set rsEA = Server.CreateObject("ADODB.Recordset")

    strSQL = "SELECT tblStyleSheet.UserName, tblStyleSheet.UserStyle FROM tblStyleSheet;"

        rsEA.CursorType = 2
        rsEA.LockType = 3
        rsEA.Open strSQL, objConn

***************************
        objConn.Execute "INSERT INTO [tblStyleSheet]" & " ([UserName], [UserStyle])" & " VALUES('" & strUserName & "','" & "blue" & "');"
***************************

    set strUserData = Server.CreateObject("ADSystemInfo")
    set strUserInfo = getObject ("LDAP://" & strUserData.Username)
    strURL = "/html/error.asp"
        For Each Group in strUserInfo.Groups
            If Group.cn = "AYL Administrators" Then
                strURL = "/admins/index.asp"
            End If
            If Group.cn = "AYL Support Staff" or Group.cn = "AYL Teachers" Then
                strURL = "/staff/index.asp"
            End If
//            If Group.cn = "AYL Key Stage 3" or Group.cn = "AYL  Key Stage 4" or Group.cn = "AYL  Key Stage 5" Then
//                strURL = "/students/index.asp"
//            End If
        Next
%>
As you can see, the code inside the *'s is the initial insertion of the record, but there's nothing there to check if it already exists.



Reply With Quote
  #8 (permalink)  
Old June 5th, 2005, 12:11 PM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

And this is the code for my userEnv.asp file:

Code:
<%
    Dim objConn, rsEA, strSQL, strHostSvr, strHttpRef, strUserName, strNewStyle, strUserStyle, strUserData, strUserInfo, strBaseDir

    strHostSvr = Request.ServerVariables("HTTP_HOST")
    strHttpRef = Request.ServerVariables("HTTP_REFERER")
    strUserName = Request.ServerVariables("LOGON_USER")

    Set objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open "DBQ=" & Server.MapPath("/database/ea.mdb") & ";DRIVER={Microsoft Access Driver (*.mdb)};"

    Set rsEA = Server.CreateObject("ADODB.Recordset")

    strSQL = "SELECT tblStyleSheet.UserName, tblStyleSheet.UserStyle FROM tblStyleSheet;"

        rsEA.CursorType = 2
        rsEA.LockType = 3
        rsEA.Open strSQL, objConn

*************************
    strNewStyle = Request.QueryString("UserStyle")
        If Len(strNewStyle) > 1 Then
            objConn.Execute "UPDATE [tblStyleSheet]" & " SET [UserStyle] = '" & strNewStyle & "'" & " WHERE [UserName] = '" & strUserName & "';"
        End If
*************************

    set strUserData = Server.CreateObject("ADSystemInfo")
    set strUserInfo = getObject ("LDAP://" & strUserData.Username)
        For Each Group in strUserInfo.Groups
            If Group.cn = "AYL Administrators" Then
                strBaseDir = "staff/administrators"
            End If
            If Group.cn = "AYL Support Staff" Then
                strBaseDir = "staff/support"
            End If
            If Group.cn = "AYL Teachers" Then
                strBaseDir = "staff/teachers"
            End If
        Next
%>
Again, the code inside the *'s is the updating code, which works ok as far as I can tell.

The only thing I need is to 'extract' the users stylesheet entry from the DB and set it as a variable to use on the pages.


Thanks/...

Reply With Quote
  #9 (permalink)  
Old June 5th, 2005, 12:17 PM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also, just so you know what's going on inside my individual pages, this is the code at the start of each page;

Code:
<HTML>
<HEAD>

        <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
            <LINK REL=stylesheet href="../styles/<%=strUserStyle%>.css" type="text/css" />
                <SCRIPT language="JavaScript" src="../scripts/disableFKeys.js"></SCRIPT>
                <SCRIPT language="JavaScript" src="../scripts/noRightClick.js"></SCRIPT>
</HEAD>

Reply With Quote
  #10 (permalink)  
Old June 5th, 2005, 12:40 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

To see if the user already has an account, try something like this:

strSQL = "SELECT tblStyleSheet.UserName, tblStyleSheet.UserStyle FROM tblStyleSheet WHERE UserName = '" & strUserName & "'";"
        rsEA.CursorType = 2
        rsEA.LockType = 3
        rsEA.Open strSQL, objConn
If rsEA.EOF Then ' EOF means End of File which means empty recordset

        objConn.Execute "INSERT INTO [tblStyleSheet]" & " ([UserName], [UserStyle])" & " VALUES('" & strUserName & "','" & "blue" & "');"

Else
 theStyleSheet = rsEA.Fields("UserStyle").Value

You could save the style sheet in a session variable so it's available to all pages. That way you don't have to hit the database each time.

Dim styleSheet
If Session("StyleSheet") = "" Then
  ' Get the style sheet from the database
Else
  styleSheet = Session("StyleSheet")
End If

Does this help?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Customised Error Messages denzil_cactus Perl 3 June 15th, 2007 10:27 AM
Lua error messages trav BOOK Beginning Lua Programming ISBN: 978-0-470-06917-2 1 June 3rd, 2007 01:21 PM
error messages strt_prg Struts 1 November 13th, 2006 07:05 AM
error messages timbal25 Classic ASP Databases 0 August 1st, 2006 02:56 PM



All times are GMT -4. The time now is 11:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.