Form creating record but not adding data

Andrew1986 · #1 (**permalink**) Oct 17th, 2007, 21:34

ok i have a form for people to sign up which is this one:

<form method="POST" action="app.asp">
Fields marked (*) are required
<p><font color="#990000"><strong>UserName:*</strong></font><br>
<input type="text" name="UserName" value="UserName">
<p><font color="#990000"><strong>Password:*</strong></font><br>
<input type="text" name="Password" value="Password">
<p><font color="#990000"><strong>Email:*</strong></font> "Must be a Valid Email Address"<br>
<input type="text" name="Email" value="Email">
<p><input type="submit" name="submit" value="Submit">
</form>

and i have a bit of sql that is ment to write that data to a database but it doesnt here is the asp bit

Dim sConnection, objConn , objRS
strusername = UserName
sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=web78-uo; UID=xxxxxxx;PASSWORD=xxxxxxxx OPTION=3"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(sConnection)
Set objRS = objConn.Execute("INSERT INTO accounts (username,password,email ) VALUES(username,password,email)")

for some reason it will not write the data from that form to the database is there somthing wrong with my script if so can any one help me fix it

Monie · #2 (**permalink**) Oct 18th, 2007, 06:52

What is the error message that you get?

Andrew1986 · #3 (**permalink**) Oct 18th, 2007, 07:21

i dont get an error message thats whats confusing me its just writing blank feilds to the database

Monie · #4 (**permalink**) Oct 18th, 2007, 07:42

send me all your .asp page and your database as well in a zipped file.
I'll see what I can do.

Andrew1986 · #5 (**permalink**) Oct 18th, 2007, 08:12

i found the error

it was here

Set objRS = objConn.Execute("INSERT INTO accounts (username,password,email ) VALUES(username,password,email)")

changed to

Set objRS = objConn.Execute("INSERT INTO accounts (username,password,email ) VALUES ('" & username & "','" & password & "','" & email & "')")

and works fine but now if the username already exists it still adds it to the database how do i get it so if username already exist's it does a response.write saying that the username already exists in the databse

Monie · #6 (**permalink**) Oct 18th, 2007, 08:21

yeah, it was the string ' that you are trying to insert into the database. If it was a number, you will use "

Monie · #7 (**permalink**) Oct 18th, 2007, 08:22

Quote:

Originally Posted by Andrew1986

i found the error

it was here

Set objRS = objConn.Execute("INSERT INTO accounts (username,password,email ) VALUES(username,password,email)")

changed to

Set objRS = objConn.Execute("INSERT INTO accounts (username,password,email ) VALUES ('" & username & "','" & password & "','" & email & "')")

and works fine but now if the username already exists it still adds it to the database how do i get it so if username already exist's it does a response.write saying that the username already exists in the databse

You have to add some verification in your add.asp page to handle the error...

Andrew1986 · #8 (**permalink**) Oct 18th, 2007, 08:51

i have a bit of verification ill post my add.asp page here

<%@LANGUAGE="VBSCRIPT"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>AlternatePK :: Beta</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<link href="default.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="wrapper">
<div id="header">
<h1>Alternate-PK</h1>
<h2><% Response.Write currentday&" "&datenow&" "&monthnow&" "&year(now)%>
</h2>
</div>

<div id="menu">
<ul>
<li><a href="index.asp">Home</a></li>
<li><a href="rules.asp">Rules</a></li>
<li><a href="status.asp">Server Status</a></li>
<li><a href="downloads.asp">Downloads</a></li>
<li><a href="forum.asp">Forum</a></li>
<li><a href="signup.asp">Sign up</a></li>
<li><a href="staff.asp">Staff</a></li>
</ul>
</div>

<div id="page">
<div id="content">
<div id="welcome">
<h1><div style="border-bottom: 1px solid #1C1C1C;">Apply for an account!</div></h1>
<p><%
' declare variables
Dim UserName
Dim Password
Dim Email
' get posted data into variables
UserName = Trim(Request.Form("UserName"))
Password = Trim(Request.Form("Password"))
Email = Trim(Request.Form("Email"))
' validation
Dim validationOK
validationOK=true
If (Trim(UserName)="") Then validationOK=false
If (Trim(Password)="") Then validationOK=false
If (Trim(Email)="") Then validationOK=false
IF (Trim(UserName)="EOF" or Trim(Password)="EOF" or Trim(Email)="EOF") Then validationOK=false & response.write("If you have entered <strong>EOF</strong> in any of the fields then.<br>")
If (validationOK=false) Then
Response.write("Please go back fill in all ")
Response.write("the requied information<br>")
Response.write("<a href=""signup.asp"">Click here</a> to return to the form.")
'does account or email already exist
Dim sConnection, objConn , objRS
sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=xxx; UID=xxx;PASSWORD=xxx; OPTION=3"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(sConnection)
Set objRS = objConn.Execute("SELECT username, email FROM accounts")
IF username = Trim(UserName) then response.write("That username already exist Please go back and try a different usrname")
ELSE
'Add account to Database
sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=xxxxx; UID=xxxxx;PASSWORD=xxxxx; OPTION=3"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(sConnection)
Set objRS = objConn.Execute("INSERT INTO accounts (username,password,email ) VALUES ('" & username & "','" & password & "','" & email & "')")
' redirect to success page
'Response.Redirect("index.asp")
Response.write("<h2>Account Added!</h2>" & "<BR>")
Response.write("Account: " & UserName & "<BR>")
Response.write("Password: " & Password & "<BR>")
Response.write("Email: " & Email)
END IF
%></p>
</div>
</div>
<div id="sidebar">
<ul>
<li id="submenu">
<h2>Navigation</h2>
<ul>
<li><a href="index.asp">Home</a></li>
<li><a href="rules.asp">Rules</a></li>
<li><a href="status.asp">Server Status</a></li>
<li><a href="downloads.asp">Downloads</a></li>
<li><a href="forum.asp">Forum</a></li>
<li><a href="signup.asp">Sign up</a></li>
<li><a href="staff.asp">Staff</a></li>
</ul>
</li>
<li id="submenu">
<h2>Status</h2>
<ul>
<li>Staff:</li>
<li>Players:</li>
<li>Guilds:</li>
</ul>

<li id="submenu">
<h2>Vote For Us</h2>
<ul>
<li></li>
</ul>
</li>
</ul>
</div>
<div style="clear: both; height: 1px"></div>
</div>
<div id="footer">
<p id="legal">Copyright © 2007 Alternate-PK.</p>
<p id="links"><a href="status.asp">Shard Status</a> | <a href="signup.asp">Join Us!</a></p>
</div>
</div>
</body>
</html>

Monie · #9 (**permalink**) Oct 18th, 2007, 09:15

Thats a very long page....
I'll have a look at it Andrew.

AdRock · #10 (**permalink**) Oct 18th, 2007, 09:20

Shouldn't you have to query the database first to see if the uersame already exists in the database and then return the error message?

Andrew1986 · #11 (**permalink**) Oct 18th, 2007, 11:33

Quote:

Originally Posted by AdRock

Shouldn't you have to query the database first to see if the uersame already exists in the database and then return the error message?

i think you do but i just started to use asp and not quite 100% with it, if some one could help me write the query it would be VERY much appreciated

AdRock · #12 (**permalink**) Oct 18th, 2007, 13:27

I know nothing about asp but here is how i do it in php

PHP: Select all


if( mysql_num_rows(mysql_query("SELECT username FROM users WHERE username = '$username'")) ) {
//error

Andrew1986 · #13 (**permalink**) Oct 18th, 2007, 13:57

ok im currently at work ill be home in a few hours ill try it and ill post the response i get

Andrew1986 · #14 (**permalink**) Oct 18th, 2007, 17:33

nah that didnt work

where should i of put that line in?

AdRock · #15 (**permalink**) Oct 18th, 2007, 17:46

Like I said...I know nothing about ASP but it looks like you are nearly all the way there

Code: Select all

Set objRS = objConn.Execute("SELECT username, email FROM accounts")
IF username = Trim(UserName) then response.write("That username already exist Please go back and try a different usrname")

You need to put a WHERE clause in yourt SELECT query so I don't know if this is right or how you insert variables into a sql query but a guess might be

The code is here, it's just hidden...just click select all

Code: Select all

Set objRS = objConn.Execute("SELECT username, email FROM accounts WHERE username=' & username & '")

This is only a guess as I have no idea how to put variables into a sql query

Andrew1986 · #16 (**permalink**) Oct 18th, 2007, 18:10

nope lol still does not want to work lol

AdRock · #17 (**permalink**) Oct 18th, 2007, 19:16

You may have more luck posting in the ASP forum.

I have no more ideas

Did you try using a WHERE clause?

You baiscally need to connect to the database, check to see if a record already exists and if it does display the error message. How you perform the query using ASP is beyond me

Andrew1986 · #18 (**permalink**) Oct 18th, 2007, 19:31

yea ill post in the asp forum now lol

Monie · #19 (**permalink**) Oct 19th, 2007, 02:03

Code: Select all

Set objRS = objConn.Execute("SELECT username, email FROM accounts WHERE username = '" & UserName & "'")

That should do it..!

AdRock · #20 (**permalink**) Oct 19th, 2007, 08:33

Looks like I was nearly there except i missed 2 sets of double quotes

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
E-mailing a report based on current record on form	easydoesit	Databases	0	Aug 27th, 2007 23:07
Add/Delete/Modify Database Record form	David Blake	Starting Out	6	May 1st, 2007 16:22
Record insertion form problem	The Hick Man	PHP Forum	3	Feb 27th, 2006 01:00
Creating A new Data BAse from .txt file	JamieH	Databases	3	Jan 28th, 2006 23:26

		Webforumz.com > Main Forums > Program Your Website > Databases
Form creating record but not adding data