Hi All,



First of all I must say thank you to both Imar Spaanjaars and Roger 

Balliger who excellently answered my question re : 'Next Page' URL gives 

an SQL error message ! Thank Alot !



Can anyone help me with this one ?



I authenticate a users username and password against a small access 

database. Once they have logged in correctly, they can add records to 

another access database via a simple HTML form.



How do I only allow users to update/edit their own inputted data.

i.e. John can only edit the data he has entered and can not see or edit 

Paul's data. (All users enter data into the same access database).



Any ideas or pointers would be great.



Thanks Guys.



Regards,



Ken 

You can use cookies too but I would let the user

give his personal details.

Regards

Nikos



-----Original Message-----

From: Ken Dowling [mailto:ken.dowling@o...]

Sent: Tuesday, March 27, 2001 3:57 PM

To: ASP Databases

Subject: [asp_databases] Allow users to update only the data they have

entered ?





Hi All,



First of all I must say thank you to both Imar Spaanjaars and Roger

Balliger who excellently answered my question re : 'Next Page' URL gives

an SQL error message ! Thank Alot !



Can anyone help me with this one ?



I authenticate a users username and password against a small access

database. Once they have logged in correctly, they can add records to

another access database via a simple HTML form.



How do I only allow users to update/edit their own inputted data.

i.e. John can only edit the data he has entered and can not see or edit

Paul's data. (All users enter data into the same access database).



Any ideas or pointers would be great.



Thanks Guys.



Regards,



Ken

Store the user number or id with the data that is theirs. Then only allow

them to view (through a dynamically created select statement) only their

records. I would also probably do some precaution (just because I usually

overkill) and check when they update a record to make sure that the userid

in the db is the same as their userid.



Hope this gives you some ideas



Daniel



-----Original Message-----

From: Ken Dowling [mailto:ken.dowling@o...]

Sent: Tuesday, March 27, 2001 3:57 PM

To: ASP Databases

Subject: [asp_databases] Allow users to update only the data they have

entered ?





Hi All,



First of all I must say thank you to both Imar Spaanjaars and Roger

Balliger who excellently answered my question re : 'Next Page' URL gives

an SQL error message ! Thank Alot !



Can anyone help me with this one ?



I authenticate a users username and password against a small access

database. Once they have logged in correctly, they can add records to

another access database via a simple HTML form.



How do I only allow users to update/edit their own inputted data.

i.e. John can only edit the data he has entered and can not see or edit

Paul's data. (All users enter data into the same access database).



Any ideas or pointers would be great.



Thanks Guys.



Regards,



Ken

---

Hi there

what I would do

In the edit page the recordset to edit should be like



dim USERID

USERID= something depends on the way you post it here

request.queriestring(something) or request.form("somevalue")



set rs = Server.CreateObject("ADODB.Recordset")



myConnection.Open "Driver={Microsoft Access Driver (*.mdb)};" & _

      "Dbq=" & Server.Mappath("../yourdatabase.mdb")& ";" ,IUSR,""



  'check permitions here for the folder I used IUSR here



MyID= request.Form("ID")

Sql="SELECT FROM * MYDATATABLE WHERE USERID = " & SOMESTRING

'The string is numeric here



rs.open Sql ,myConnection,2,3



'now get data etc etc



set rs = nothing

set myConnection = nothing



hope it helps

Regards Nikos

-----Original Message-----

From: Ken Dowling [mailto:ken.dowling@o...]

Sent: Tuesday, March 27, 2001 3:57 PM

To: ASP Databases

Subject: [asp_databases] Allow users to update only the data they have

entered ?





Hi All,



First of all I must say thank you to both Imar Spaanjaars and Roger

Balliger who excellently answered my question re : 'Next Page' URL gives

an SQL error message ! Thank Alot !



Can anyone help me with this one ?



I authenticate a users username and password against a small access

database. Once they have logged in correctly, they can add records to

another access database via a simple HTML form.



How do I only allow users to update/edit their own inputted data.

i.e. John can only edit the data he has entered and can not see or edit

Paul's data. (All users enter data into the same access database).



Any ideas or pointers would be great.



Thanks Guys.



Regards,



Ken

Dear Ken,





I suppose you give each user a unique number (primary

key) in your database so that you can identify each

user without any problem.



So, each time a user adds a record in your Access

database, you store this number in this record.  In

this way, you know from each record which user has

added it.



I'll explain with a little example:



Suppose you have 2 users in your userdatabase:



Usernr: 1

Username: Ken



Usernr: 2

Username: Steven



Every time when a users adds information you get a

record in your database like this:



field 1: ....

field 2: ....

usernr: 1



field 1: ...

field2: ...

usernr: 2



........



Now you know for example that the first record is

entered by user 1 and the second one by user 2.

When user 1 has access to your website, you only show

the records with usernr 1 in it ...





Success,



Steven







--- Ken Dowling <ken.dowling@o...> wrote:

> Hi All,

> 

> First of all I must say thank you to both Imar

> Spaanjaars and Roger 

> Balliger who excellently answered my question re :

> 'Next Page' URL gives 

> an SQL error message ! Thank Alot !

> 

> Can anyone help me with this one ?

> 

> I authenticate a users username and password against

> a small access 

> database. Once they have logged in correctly, they

> can add records to 

> another access database via a simple HTML form.

> 

> How do I only allow users to update/edit their own

> inputted data.

> i.e. John can only edit the data he has entered and

> can not see or edit 

> Paul's data. (All users enter data into the same

> access database).

> 

> Any ideas or pointers would be great.

> 

> Thanks Guys.

> 

> Regards,

> 

> Ken 

Hi Ken,



One way to do this, is store the "author's" ID in the database. Give your 

User table a unique ID (authorID) and log that when you insert an article.



Database structure:



tblUser

     UserID    int, autonumber

     UserName

     UserPassWord



tblContent

     ContentID int, autonumber

     Title

     BodyText

     ..... etc

     AuthorID, int (the ID from table tblUser).



If you can / want to use sessions, store the user's ID in the session 

object when he logs in. Whenever you insert an article, pass the UserID to 

the table tblContent and fill it in the column AuthorID.



When users need to update stuff, you can select records from the database 

where their ID matches the column AuthorID. Also, when updating, add a 

WHERE clause to the update statement. Inside the WHERE clause, check for 

the AuthorID again. This may be necessary to avoid a security-leak. For 

example, if you open a record for editing with this URL: 

/editArticle.asp?ID=123 a user can easily change the 123 to another ID, 

which may not be his. When you use the WHERE AuthorID = Session("AUTHORID") 

(or any other way to retrieve the ID of the current user) you can avoid 

people from updating the wrong content.



Let me know if you'll need more ideas / info on this.



Imar







At 03:56 PM 3/27/2001 +0000, you wrote:

>Hi All,

>

>First of all I must say thank you to both Imar Spaanjaars and Roger

>Balliger who excellently answered my question re : 'Next Page' URL gives

>an SQL error message ! Thank Alot !

>

>Can anyone help me with this one ?

>

>I authenticate a users username and password against a small access

>database. Once they have logged in correctly, they can add records to

>another access database via a simple HTML form.

>

>How do I only allow users to update/edit their own inputted data.

>i.e. John can only edit the data he has entered and can not see or edit

>Paul's data. (All users enter data into the same access database).

>

>Any ideas or pointers would be great.

>

>Thanks Guys.

>

>Regards,

>

>Ken

You could put their user id's in the tables and linit the data with a 

criteria based upon their user id.



Regards,

Wally



>From: "Ken Dowling" <ken.dowling@o...>

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] Allow users to update only the data they have 

>entered ?

>Date: Tue, 27 Mar 2001 15:56:43

>

>Hi All,

>

>First of all I must say thank you to both Imar Spaanjaars and Roger

>Balliger who excellently answered my question re : 'Next Page' URL gives

>an SQL error message ! Thank Alot !

>

>Can anyone help me with this one ?

>

>I authenticate a users username and password against a small access

>database. Once they have logged in correctly, they can add records to

>another access database via a simple HTML form.

>

>How do I only allow users to update/edit their own inputted data.

>i.e. John can only edit the data he has entered and can not see or edit

>Paul's data. (All users enter data into the same access database).

>

>Any ideas or pointers would be great.

>

>Thanks Guys.

>

>Regards,

>

>Ken

Hi Imar,



Thanks for your swift reply. I was thinking the same. However, I am big on 

ideas and short on ablity ... but learning every day.



You wrote ... One way to do this, is store the "author's" ID in the 

database. Give your User table a unique ID (authorID) and log that when 

you insert an article.



In my case the user sees a simple HTML form and enters a user name and 

password. If these match what's in the Access DB a response.redirect 

brings then to another data entry form. 



How do I log the user's ID from the database when the user is 

authenticated and how do I get the output into a session variable and how 

do I pass this back into the DB when records are entered.  



i.e. The user logs in, is authenticated, is given his/her unique user ID 

from the DB, which is passed to a session variable. When the user adds a 

DB record their user ID is passed to and entered into the DB.



Any help on this would be great.



I think I'm OK wiht the WHERE clause etc.



Thanks Again,



Ken 

Hi Ken,

(Since I am in a coding mood, and having a nice ice-cold beer standing next 

to me, I'll make this a comprehensive reply ;-)   )



Here's what I would do.

(Assumptions:

         1. you use Access

         2. Session variables are allowed

)





1. Create two tables in Access:

         1. tblUsers

                 ID

                 LoginCode

                 Password

                 FirstName

                 LastName

                 .... more fields

         2. tblArticles

                 ID

                 Title

                 Body

                 AuthorID

Relate the AuthorID of tblContent to the ID of tblUsers.





2. On the page where your users log in, do this (pseudo code):

         SELECT ID, Password FROM tblUsers WHERE LoginCode = _

                 Request.Form("login") and Password = Request.Form("password")

         Recordset.Open

         if not Recordset.EOF then

                 ' User exists

                 ' Do a case sensitive compare here if necessary because 

access doesn't care about case

                 Session("USERID") = Recordset.Fields.Value("ID")

                 Response.Redirect to the page where they can enter content.

         else

                 Response.write("Wrong userID and/or password")

         end if





3. The page where they enter content.

Add a form, two textfields / areas and a submit button.

Add this code near the top of the page:



If UCaseRequest.Form("BTNSUBMIT")) = "SUBMIT" then

         ' This assumes you have a button called btnSubmit with a value of 

submit

         ' This will be true when the user pressed the submit button.

         Dim iUserID

         Dim sTitle

         Dim sBody

         Dim sSQL

         Dim connInsert

         sTitle = Request.Form("txtTitle")

         sBody = Request.Form("txtBody")

         iUserID = Session("USERID")

         ' Next line may wrap)

         sSQL = "INSERT INTO tblContent (Title, Body, AuthorID) VALUES(" & 

iUserID & ", '" & sTitle & "', '" & sBody & "'"

         Set connInsert = Server.CreateObject(ADODB.Connection")

         connInsert.Open sMyConnectionStringToAccess

         conn.Execute(sSQL)

         Set connInsert = Nothing

end if



If all went well, you have just inserted an article in the tblContent 

table. In my example I only added a title and some content, but you can of 

course add as much fields as you want (more or less).

I also passed the ID of the user which is stored in the AuthorID column. 

The the Content / Articles has been related to a specific user.



4. Now, on a page where a user is able to select only his own articles (for 

viewing or editing), you could do something like this:



Dim sSQL

sSQL = "Select Title, Body FROM tblContent WHERE authorID = " & 

Session("USERID")



This will limit the resultset to just the articles that are written by the 

currently logged in user.



You may have to check if the user has actually logged in. Suppose they 

bookmark the add page, and return to it without logging in, 

Session("USERID") is empty, so all your SQL statements will fail.



One way to do this is, like this:



         1. Create a page called CheckLoggedIn.asp (for example).

         2. Add the following code to that page:

         <%

                 if Session("USERID") = "" then

                         Response.redirect("/login.asp")

                         Response.end

                 end if

         %>

         3. Include this page on every "sensitive" page. If the 

Session("USERID") does not exist,

         the user is automatically redirected to the login page. If it does 

exist, nothing happens



I think this should be enough to give you some ideas and to get you going. 

If not, let us know.



Please note: I created this code in my mail program: I haven't run anything 

or tested it. There might be some typos or errors in it......



Imar









At 06:31 PM 3/27/2001 +0000, you wrote:

>Hi Imar,

>

>Thanks for your swift reply. I was thinking the same. However, I am big on

>ideas and short on ablity ... but learning every day.

>

>You wrote ... One way to do this, is store the "author's" ID in the

>database. Give your User table a unique ID (authorID) and log that when

>you insert an article.

>

>In my case the user sees a simple HTML form and enters a user name and

>password. If these match what's in the Access DB a response.redirect

>brings then to another data entry form.

>

>How do I log the user's ID from the database when the user is

>authenticated and how do I get the output into a session variable and how

>do I pass this back into the DB when records are entered.

>

>i.e. The user logs in, is authenticated, is given his/her unique user ID

>from the DB, which is passed to a session variable. When the user adds a

>DB record their user ID is passed to and entered into the DB.

>

>Any help on this would be great.

>

>I think I'm OK wiht the WHERE clause etc.

>

>Thanks Again,

>

>Ken