update everything except one field

[razstec]

Hi guys, strange thing append this works perfectly except the email update, it makes all verifications and update all fields except the email. Can you see why?? the field name and format are correct and it doesnt give any error, it just dont change the email.

<%
set conn = CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.Oledb.4.0"
conn.Open Server.MapPath("db/12345678.mdb")
set rs=server.createobject("adodb.recordset")

	if request.form("insform.x")>0 then
	addusers
	end if
		
	sub addusers()

	if 	request.form("Password")<>request.form("Password2") then
	Response.Redirect("updatuser.asp?msg=Your password fields dont match")
	else
	
	if request.form("email")="" or request.form("Name")="" or request.form("LastName")="" or request.form("Institution")=""or request.form("Password")="" or request.form("Study")="" or request.form("Study")="Choose" or request.form("Date_Birth_day")="" or request.form("Date_Birth_day")="Day" or request.form("Date_Birth_month")="" or request.form("Date_Birth_month")="Month" or request.form("Date_Birth_year")="" or request.form("Date_Birth_year")="Year" or request.form("safeans")="" or request.form("safquest")="" then 	
		Response.Redirect("updatuser.asp?msg=Please fill all fields mark with (*). ")
	else
	
		
	instotal = "select * from users WHERE email = '" & Request.Form("email") & "'"
	rs.open instotal,conn,2,2
		
	 If not rs.EOF And session("user")<> rs.fields("username") Then 
			
		Response.Redirect("updatuser.asp?msg=That e-mail is already in use by another user")
			
 	Else

	strpassword = request.form("Username") + request.form("password")
		
	rs.update
	rs.fields("email")=request.form("email")
    rs.fields("nome")=request.form("Name")
   	rs.fields("last_name")=request.form("LastName")
	rs.fields("password")=MD5(strpassword)	
	rs.fields("center")=request.form("Institution")
	rs.fields("study")=request.form("Study")
	rs.fields("website")=request.form("Website")
	rs.fields("data_nasc")=request.form("Date_Birth_day")&"-"&request.form("Date_Birth_month")&"-"&request.form("Date_Birth_year")
	rs.fields("morada")=request.form("Address")
	rs.fields("cidade")=request.form("City")
	rs.fields("pais")=request.form("Country")
	rs.fields("position")=request.form("Team_Position")
	rs.fields("telefone")=request.form("Telephone")
	rs.fields("datdia")=request.form("Date_Birth_day")
	rs.fields("datmes")=request.form("Date_Birth_month")
	rs.fields("datano")=request.form("Date_Birth_year")
	rs.fields("pergunta")=request.form("safquest")
	rs.fields("resposta")=request.form("safeans")
	rs.fields("user_status")= "2"
	
	rs.update
	rs.close
	Response.Redirect("updatuser.asp?msg=User updated successfully.")	
 
	end if
end if
end if
   conn.close
	end sub

[yamaharuss]

Have you debugged?

What does this produce:

response.write "email = " & request.form("email")

[razstec]

Thats working fine, the problem is when he check if theres another email with the same name and that dont bellong to the same user.

[razstec]

the problem is here although i can seem to understand what

instotal1 = "select * from users WHERE email = " & Request.Form("E-mail") & "'"
	rs2.open instotal1,conn,2,2
	
		 If not rs2.EOF And request.form("username")<> session("user") Then 
			
			Response.Redirect("reguser.asp?msg=That e-mail is already in use")
 		Else

if i remove this it work great but dont check if anyone already have the email

[yamaharuss]

Why would you update the email address if the email address is what you're using to find the user? It's always going to exist unless he changes it. Then if he changes it, how do you know which record to update?

Your logic is completely wrong there.

[yamaharuss]

You need to pass another variable to the update page, like a UserID or another key.

Then instead of querying based on email address, you query based on the key like this.

select * from users where UserID = "& request.form("UserID") &"

You'll have to run another query to make sure the email addy is not duplicated

select * from users where UserID <> "& request.form("UserID") &" and email = '"& request.form("email") &"'

Put your stop check on that query.

[razstec]

i was thinking like this:

'call all fields where the field email is  equal to request.form ("email")

instotal1 = "select * from users WHERE email = '" & Request.Form("email") & "'"
	rs2.open instotal1,conn,2,2
	

          'if it returns any value and the rs.field username is diferent from the session user he dont allow it
		 If not rs2.EOF And rs.fields("username")<> session("user") Then 
			
			Response.Redirect("reguser.asp?msg=That e-mail is already in use")
 		Else

is this too wrong? how can i do this?

thanks

[yamaharuss]

Once you get it working, don't forget to clean your incoming requests for sql injection before moving to production.

[razstec]

perfect.

thanks, its FINALLY working wright


maybe you can help with something that i just can seem to find a way to do it.

I have a table with the fields user, date, study and I need a button that allows me to enter more than one study,i was thinking to increase a field named study1 and so on.

something like this:

<&#37;
if study00 = "" then
study00  = 1 
else
study00 = "study00" + 1
end if

ALTER TABLE ADD studys "'& & study00'" memo
%>

But then when you do the same thing with another user I check all the columns to see which is empty and insert it?

Any idea the best way to do this?

thanks

[yamaharuss]

Nooooo. That is terribly poor table design.

What you need to do is add another table, call it Studies, make it many to many

Your Studies table would have a UserID int field and a Study varchar field

[razstec]

i already have a table just for studys that saves the study_name, user_that_created_it and id.

What do you mean with "make it many to many"??


But i need that a user be allowed to have more than one study.

I can just add the username to the study, but again theres more than one user

[yamaharuss]

Adding a username would certainly work, but it's not really a normalized schema.

Your layout should look something like this

Users
UID | Name
1 Joe
2 Sally
3 Bob

Studies
StudyID | UID | StudyName
1 1 Math with Mr. Woods
2 1 Biology in room 214
3 1 Economics part 2
4 2 Woods Math shared with Joe
5 2 My free art class
etc..

Now..

In the event the study names are pre-defined (Math, Biology, etc..), you should use a third table for those names and make the Studies field an ID key to the name instead.

[razstec]

I see i connect the table thru the userid.

when ever i create a new user i would have a list of all users with checkboxes and after press save it would save the userid in the study table.

But whe are talking of something like 20 to 30 users per study, that the triky part...

[yamaharuss]

What's so tricky about it? That's why it's called a many-to-many relationship.

Joe can have as many studies as he wants and there's no limit to how many users study Math

[razstec]

Im sorry, im a little lost

I still cant see how to do this :s