Snitz Forums 2000 - Baffled but tired - form data into mysql db

Author

Topic

Webbo
Average Member

United Kingdom
982 Posts

Posted - 06 August 2014 : 17:41:38

I'm trying to add some form data into a MySql database and failing miserably partially due to long work hours, tiredness and little spare time to spend on it

Basically I have a form page where info can be submitted.
It then goes to a confirmation page which opens in the users browser displaying the info that has been submitted. (this bit works fine)

It is this info from the confirmation page that I need to be added to the database

Below is a copy of the confirmation page:


<%@ Language="VBscript" %>
<html>
<head>
<title>Submitted Postcode</title>
</head>

<body>
<%
'declare the variables that will receive the values
Dim fishery, street_address, town_city, county, postcode
'receive the values sent from the form and assign them to variables
fishery=Request.Form("fishery")
street_address=Request.Form("street_address")
town_city=Request.Form("town_city")
county=Request.Form("county")
postcode=Request.Form("postcode")

'print out the received values in the browser
Response.Write("Fishery: " & fishery & "<br>")
Response.Write("Street Address: " & street_address & "<br>")
Response.Write("Town/City: " & town_city & "<br>")
Response.Write("County: " & county & "<br>")
Response.Write("Postcode: " & postcode & "<br>")
%>
</body>
</html>

Could someone please advise how I can send the form data into a table in the database

The table is called: postcodes
The columns in the table are named: fishery, street_address, town_city, county, postcode

Many thanks in anticipation

Carefree
Advanced Member

Philippines
4207 Posts

Posted - 06 August 2014 : 20:28:19

This is basically all you need. Change the bit in red to match your database connectivity string.


<%
strConn = (your DB connection here)
set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open(strConn)
strFishery=chkString(Request.Form("fishery"))
strAddress=chkString(Request.Form("street_address"))
strTown=chkString(Request.Form("town_city"))
strCountry=chkString(Request.Form("country"))
strPost=chkString(Request.Form("postcode"))
strSql="INSERT INTO POSTCODES (FISHERY,STREET_ADDRESS,TOWN_CITY,COUNTRY,POSTCODE) VALUES (strFishery,strAddress,strTown,strCountry,strPost)"
my_Conn.Execute(strSql)
my_Conn.Close
Set my_Conn=Nothing
Response.Write "<p align=""center"">Complete</p>"
Response.End

Function chkString(fString)
	fString = Trim(fString)
	fString = Replace(fString, "'", "''")
	fString = Replace(fString, "\", "\\")
	fString = Replace(fString, ">", ">")
	fString = Replace(fString, "<", "<")
	chkString = fString
Exit Function
%>

Edited by - Carefree on 06 August 2014 20:29:28

Webbo
Average Member

United Kingdom
982 Posts

Posted - 07 August 2014 : 16:55:51

Hi Carefree, can you just check my code below as when I add my connection string I get an error 500 message:


<%@ Language="VBscript" %>
<html>
<head>
<title>Submitted Postcode</title>
</head>

<body>


<%
'declare the variables that will receive the values
Dim fishery, street_address, town_city, county, postcode
'receive the values sent from the form and assign them to variables
'note that request.form("name") will receive the value entered
'into the textfield called name
fishery=Request.Form("fishery")
street_address=Request.Form("street_address")
town_city=Request.Form("town_city")
county=Request.Form("county")
postcode=Request.Form("postcode")

'let's now print out the received values in the browser
Response.Write("Fishery: " & fishery & "<br>")
Response.Write("Street Address: " & street_address & "<br>")
Response.Write("Town/City: " & town_city & "<br>")
Response.Write("County: " & county & "<br>")
Response.Write("Postcode: " & postcode & "<br>")
%>
<%
strConn = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=dbname; " &_
"UID=user;PASSWORD=password; OPTION=3"
set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open(strConn)
strFishery=chkString(Request.Form("fishery"))
strAddress=chkString(Request.Form("street_address"))
strTown=chkString(Request.Form("town_city"))
strCounty=chkString(Request.Form("county"))
strPost=chkString(Request.Form("postcode"))
strSql="INSERT INTO POSTCODES (FISHERY,STREET_ADDRESS,TOWN_CITY,COUNTY,POSTCODE) VALUES (strFishery,strAddress,strTown,strCounty,strPost)"
my_Conn.Execute(strSql)
my_Conn.Close
Set my_Conn=Nothing
Response.Write "<p align=""center"">Complete</p>"
Response.End

Function chkString(fString)
   fString = Trim(fString)
   fString = Replace(fString, "'", "''")
   fString = Replace(fString, "\", "\\")
   fString = Replace(fString, ">", ">")
   fString = Replace(fString, "<", "<")
   chkString = fString
Exit Function
%>
</body>
</html>

Carefree
Advanced Member

Philippines
4207 Posts

Posted - 07 August 2014 : 20:06:17

Well, the obvious problem is that you don't specify "user". You don't use "UID" as the variable. Beyond that, though, your "option=3" limits what you can do while connected to a MySQL database. Also, there's no data checking for the content of the form fields. Try this:


<html>
	<head>
		<title>Address Submitted</title>
	</head>
	<body>
		<%
		dim strFishery, strAddress, strTown, strCounty, strPost, strConn, my_Conn, strErr
		strErr = ""
		If Request.Form("fishery")>"" Then
			strFishery=chkString(Request.Form("fishery"))
		Else
			strErr="[li]You must specify the fishery.[/li]"
		End If
		If Request.Form("street_address")>"" Then
			strAddress=chkString(Request.Form("street_address"))
		Else
			strErr=strErr & "[li]You must specify the street address.[/li]"
		End If
		If Request.Form("town_city")>"" Then
			strTown=chkString(Request.Form("town_city"))
		Else
			strErr=strErr & "[li]You must specify the town/city.[/li]"
		End If
		If Request.Form("county")>"" Then
			strCounty=chkString(Request.Form("county"))
		Else
			strErr=strErr & "[li]You must specify the county.[/li]"
		End If
		If Request.Form("postcode")>"" Then
			strPost=chkString(Request.Form("postcode"))
		Else
			strErr=strErr & "[li]You must specify the post code.[/li]"
		End If
		If strErr > "" Then
			Response.Write "<table align=""center"" width=""50%"" border=""0"">" & vbNewLine & _
				" <tr>" & vbNewLine & _
				"  <td align=""left"">" & vbNewLine & _
				"   <font face=""times new roman"" size=""5"" color=""black""><p><b><u>Missing Information</u></b></p><ul>" & strErr & "</ul>"
				"    Go <a href=""javascript:history.go(-1)"";>back</a> to correct." & vbNewLine & _
				"   </font>" & vbNewLine & _
				"  </td>" & vbNewLine & _
				" </tr>" & vbNewLine & _
				"</table>" & vbNewLine
			Response.End
		End If
		Response.Write "Fishery:	" & strFishery & "<br />Street Address:	" & strAddress & "<br />Town/City:	" & strTown & "<br />County:	" & strCounty & "<br />Post Code:	" & strPost & "<br />"
		strConn = "driver={MySQL ODBC 5.1 Driver};option=67108867;server=127.0.0.1;user=user;password=password;DATABASE=dbname;"
		set my_Conn = Server.CreateObject("ADODB.Connection")
		my_Conn.Open(strConn)
		strSql="INSERT INTO POSTCODES (FISHERY,STREET_ADDRESS,TOWN_CITY,COUNTY,POSTCODE) VALUES ('" & strFishery & "','" & strAddress & "','" & strTown & "','" & strCounty & "','" & strPost & "')"
		my_Conn.Execute(strSql)
		my_Conn.Close
		Set my_Conn=Nothing
		Response.Write "<p align=""center"">Complete</p>"
		Response.End
		Function chkString(fString)
			fString = Trim(fString)
			fString = Replace(fString, "'", "''")
			fString = Replace(fString, "\", "\\")
			fString = Replace(fString, ">", ">")
			fString = Replace(fString, "<", "<")
			chkString = fString
		End Function
		%>
	 </body>
</html>

Edited by - Carefree on 08 August 2014 07:17:20

Webbo
Average Member

United Kingdom
982 Posts

Posted - 08 August 2014 : 02:20:25

Nope, still giving a http500 error

I've check the database details and they're all correct

Carefree
Advanced Member

Philippines
4207 Posts

Posted - 08 August 2014 : 07:46:30

I tested this and it works fine. Just change the connection string. I called it "address.asp" (look at the action line in the form). I also corrected two errors in the above, so it may work for you without using this one.


<html>
	<head>
		<title>Address Submitted</title>
	</head>
	<body>
		<%
		Function chkString(fString)
			fString = Trim(fString)
			fString = Replace(fString, "'", "''")
			fString = Replace(fString, "\", "\\")
			fString = Replace(fString, ">", ">")
			fString = Replace(fString, "<", "<")
			chkString = fString
		End Function
		If Request.Form("sendme") > "" Then
			dim strFishery, strAddress, strTown, strCounty, strPost, strConn, my_Conn, strErr
			strErr = ""
			If Request.Form("fishery")>"" Then
				strFishery=chkString(Request.Form("fishery"))
			Else
				strErr="[li]You must specify the fishery.[/li]"
			End If
			If Request.Form("street_address")>"" Then
				strAddress=chkString(Request.Form("street_address"))
			Else
				strErr=strErr & "[li]You must specify the street address.[/li]"
			End If
			If Request.Form("town_city")>"" Then
				strTown=chkString(Request.Form("town_city"))
			Else
				strErr=strErr & "[li]You must specify the town/city.[/li]"
			End If
			If Request.Form("county")>"" Then
				strCounty=chkString(Request.Form("county"))
			Else
				strErr=strErr & "[li]You must specify the county.[/li]"
			End If
			If Request.Form("postcode")>"" Then
				strPost=chkString(Request.Form("postcode"))
			Else
				strErr=strErr & "[li]You must specify the post code.[/li]"
			End If
			If strErr > "" Then
				Response.Write "<table align=""center"" width=""50%"" border=""0"">" & vbNewLine & _
					" <tr>" & vbNewLine & _
					"  <td align=""left"">" & vbNewLine & _
					"   <font face=""times new roman"" size=""5"" color=""black""><p><b><u>Missing Information</u></b></p><ul>" & strErr & "</ul>" & vbNewLine & _
					"    Go <a href=""javascript:history.go(-1)"";>back</a> to correct." & vbNewLine & _
					"   </font>" & vbNewLine & _
					"  </td>" & vbNewLine & _
					" </tr>" & vbNewLine & _
					"</table>" & vbNewLine
				Response.End
			End If
			strSpace="          "
			Response.Write "<font face=""consolas"">Fishery:" & left(strSpace,54) & strFishery & "<br />Street Address:	" & left(strSpace,6) & strAddress & "<br />Town/City:	" & left(strSpace,35) & strTown & "<br />County:	" & left(strSpace,54) & strCounty & "<br />Post Code:	" & left(strSpace,35) & strPost & "<br /></p></font>"
			strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("test.mdb")     												'		strConn = "driver={MySQL ODBC 5.1 Driver};option=67108867;server=127.0.0.1;user=user;password=password;DATABASE=dbname;"
			set my_Conn = Server.CreateObject("ADODB.Connection")
			my_Conn.Open(strConn)
			strSql="INSERT INTO POSTCODES (FISHERY,STREET_ADDRESS,TOWN_CITY,COUNTY,POSTCODE) VALUES ('" & strFishery & "','" & strAddress & "','" & strTown & "','" & strCounty & "','" & strPost & "')"
			my_Conn.Execute(strSql)
			my_Conn.Close
			Set my_Conn=Nothing
			Response.Write "<p align=""center"">Complete</p>"
			Response.End
		End If
		Response.Write	"<form action=""address.asp"" method=""post"">" & vbNewLine & _
			"	<input type=""hidden"" name=""sendme"" value=""doit"" />" & vbNewLine & _
			"	<table align=""center"" width=""600px;"" border=""0"" cellpadding=""0"" cellspacing=""0"">" & vbNewLine & _
			"		<tr valign=""middle"">" & vbNewLine & _
			"			<td align=""center"" width=""100%"">" & vbNewLine & _
			"				<table align=""center"" width=""100%"" bgColor=""black"" border=""1"" style=""border-collapse:collapse;"" cellpadding=""4"" cellspacing=""1"">" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Fishery: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""fishery"" value=""" & Request.Form("fishery") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Street Address: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""street_address"" value=""" & Request.Form("street_address") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Town/City: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""town_city"" value=""" & Request.Form("town_city") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>County: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""county"" value=""" & Request.Form("county") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Post Code: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""postcode"" value=""" & Request.Form("postcode") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"				</table>" & vbNewLine & _
			"			</td>" & vbNewLine & _
			"		</tr>" & vbNewLine & _
			"		<tr height=""40px;"" valign=""bottom"">" & vbNewLine & _
			"			<td align=""center"" colspan=""2"" width=""100%"">" & vbNewLine & _
			"				<input style=""color:maroon; font-weight:bold; padding:5px 10px 5px 10px; border:1px solid black; text-shadow:0px 1px 1px #000; text-decoration none; border-radius:25px; -moz-border-radius:25px; -webkit-border-radius:25px; background:lightgrey;"" type=""Submit"" name=""Submit"" value=""Submit"" />" & vbNewLine & _
			"			</td>" & vbNewLine & _
			"		</tr>" & vbNewLine & _
			"	</table>" & vbNewLine & _
			"</form>" & vbNewLine
		%>
	 </body>
</html>

Edited by - Carefree on 08 August 2014 07:49:06

Webbo
Average Member

United Kingdom
982 Posts

Posted - 08 August 2014 : 12:31:38

The version above (the last one) works fine Carefree using the MySql connection string - many thanks

I've just got to add a check to ensure that the fishery does not already exist in the database which would require two checks: fishery name and postcode as some fisheries do have the same names,
Also, a password protection to stop others from using it peferably with a session cookie so that the password is not required every time the page is viewed

If you get the time to do the above it would be muchly appreciated

Edited by - Webbo on 08 August 2014 12:40:18

Carefree
Advanced Member

Philippines
4207 Posts

Posted - 08 August 2014 : 18:25:59

This is untested. Don't forget to change your connection string.


<html>
	<head>
		<title>Address Submitted</title>
	</head>
	<body>
		<!--#INCLUDE file="inc_sha256.asp"-->
		<%
		strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("test.mdb")     												'		strConn = "driver={MySQL ODBC 5.1 Driver};option=67108867;server=127.0.0.1;user=user;password=password;DATABASE=dbname;"
		set my_Conn = Server.CreateObject("ADODB.Connection")
		my_Conn.Open(strConn)
		strCookieURL = Left(Request.ServerVariables("Path_Info"), InstrRev(Request.ServerVariables("Path_Info"), "/"))
		strUniqueID = "FishAddy"
		Const intCookieDuration = 30
		Function chkString(fString)
			fString = Trim(fString)
			fString = Replace(fString, "'", "''")
			fString = Replace(fString, "\", "\\")
			fString = Replace(fString, ">", ">")
			fString = Replace(fString, "<", "<")
			chkString = fString
		End Function
		If not Session("UID") > 0 Then
			If Request.Form("Pass") > "" Then
				strUser = trim(Request.Form("User"))
				strPass = sha256(Request.Form("Pass"))
			Else
				strUser = Request.Cookies(strCookieURL & "User")
				strPass = Request.Cookies(strCookieURL & "Pass")
			End If
			If strUser > "" Then
				strSql = "SELECT USER_ID, USER, PASS FROM USERS WHERE USER='" & strUser & "' AND PASS='" & strPass & "'"
				Set rsID = my_Conn.Execute(strSql)
				If not rsID.EOF Then
					Session("UID") = rsID("USER_ID")
					Response.Cookies(strUniqueID & "User")("User") = strUser
					Response.Cookies(strUniqueID & "User")("Pass") = strPass
					Response.Cookies(strUniqueID & "User").Expires = dateAdd("d", intCookieDuration, Now())
					rsID.Close
				End If
				Set rsID = Nothing
			Else
				Response.Write	"<form action=""address.asp"" method=""post"">" & vbNewLine & _
					"	<table align=""center"" width=""600px;"" border=""0"" cellpadding=""0"" cellspacing=""0"">" & vbNewLine & _
					"		<tr valign=""middle"">" & vbNewLine & _
					"			<td align=""center"" width=""100%"">" & vbNewLine & _
					"				<table align=""center"" width=""100%"" bgColor=""black"" border=""1"" style=""border-collapse:collapse;"" cellpadding=""4"" cellspacing=""1"">" & vbNewLine & _
					"					<tr valign=""middle"">" & vbNewLine & _
					"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
					"							<font face=""times new roman"" size=""5"" color=""navy""><b>User: </b></font>" & vbNewLine & _
					"						</td>" & vbNewLine & _
					"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
					"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
					"								<input type=""text"" name=""User"" value=""" & Request.Form("User") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
					"							</font>" & vbNewLine & _
					"						</td>" & vbNewLine & _
					"					</tr>" & vbNewLine & _
					"					<tr valign=""middle"">" & vbNewLine & _
					"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
					"							<font face=""times new roman"" size=""5"" color=""navy""><b>Pass: </b></font>" & vbNewLine & _
					"						</td>" & vbNewLine & _
					"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
					"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
					"								<input type=""password"" name=""Pass"" value="""" style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
					"							</font>" & vbNewLine & _
					"						</td>" & vbNewLine & _
					"					</tr>" & vbNewLine & _
					"				</table>" & vbNewLine & _
					"			</td>" & vbNewLine & _
					"		</tr>" & vbNewLine & _
					"		<tr height=""40px;"" valign=""bottom"">" & vbNewLine & _
					"			<td align=""center"" colspan=""2"" width=""100%"">" & vbNewLine & _
					"				<input style=""color:maroon; font-weight:bold; padding:5px 10px 5px 10px; border:1px solid black; text-shadow:0px 1px 1px #000; text-decoration none; border-radius:25px; -moz-border-radius:25px; -webkit-border-radius:25px; background:lightgrey;"" type=""Submit"" name=""Submit"" value=""Submit"" />" & vbNewLine & _
					"			</td>" & vbNewLine & _
					"		</tr>" & vbNewLine & _
					"	</table>" & vbNewLine & _
					"</form>" & vbNewLine
				Response.End
			End If
		End If
		If Request.Form("sendme") > "" Then
			dim strFishery, strAddress, strTown, strCounty, strPost, strConn, my_Conn, strErr
			strErr = ""
			If Request.Form("fishery")>"" Then
				strFishery=chkString(Request.Form("fishery"))
			Else
				strErr="[li]You must specify the fishery.[/li]"
			End If
			If Request.Form("street_address")>"" Then
				strAddress=chkString(Request.Form("street_address"))
			Else
				strErr=strErr & "[li]You must specify the street address.[/li]"
			End If
			If Request.Form("town_city")>"" Then
				strTown=chkString(Request.Form("town_city"))
			Else
				strErr=strErr & "[li]You must specify the town/city.[/li]"
			End If
			If Request.Form("county")>"" Then
				strCounty=chkString(Request.Form("county"))
			Else
				strErr=strErr & "[li]You must specify the county.[/li]"
			End If
			If Request.Form("postcode")>"" Then
				strPost=chkString(Request.Form("postcode"))
			Else
				strErr=strErr & "[li]You must specify the post code.[/li]"
			End If
			If strErr > "" Then
				Response.Write "<table align=""center"" width=""50%"" border=""0"">" & vbNewLine & _
					" <tr>" & vbNewLine & _
					"  <td align=""left"">" & vbNewLine & _
					"   <font face=""times new roman"" size=""5"" color=""black""><p><b><u>Missing Information</u></b></p><ul>" & strErr & "</ul>" & vbNewLine & _
					"    Go <a href=""javascript:history.go(-1)"";>back</a> to correct." & vbNewLine & _
					"   </font>" & vbNewLine & _
					"  </td>" & vbNewLine & _
					" </tr>" & vbNewLine & _
					"</table>" & vbNewLine
				Response.End
			End If
			strSpace="          "
			Response.Write "<font face=""consolas"">Fishery:" & left(strSpace,54) & strFishery & "<br />Street Address:	" & left(strSpace,6) & strAddress & "<br />Town/City:	" & left(strSpace,35) & strTown & "<br />County:	" & left(strSpace,54) & strCounty & "<br />Post Code:	" & left(strSpace,35) & strPost & "<br /></p></font>"
			strSql="SELECT FISHERY, POSTCODE FROM POSTCODES WHERE FISHERY='" & strFishery & "' AND POSTCODE='" & strPost & "'"
			Set rsFish=my_Conn.Execute(strSql)
			If not rsFish.EOF Then
				rsFish.Close
				Response.Clear
				strErr = "[li]Already in database.[/li]"
				Response.Write "<table align=""center"" width=""50%"" border=""0"">" & vbNewLine & _
					" <tr>" & vbNewLine & _
					"  <td align=""left"">" & vbNewLine & _
					"   <font face=""times new roman"" size=""5"" color=""black""><p><b><u>Duplicate Information</u></b></p><ul>" & strErr & "</ul>" & vbNewLine & _
					"    Go <a href=""javascript:history.go(-1)"";>back</a> to correct." & vbNewLine & _
					"   </font>" & vbNewLine & _
					"  </td>" & vbNewLine & _
					" </tr>" & vbNewLine & _
					"</table>" & vbNewLine
				Set rsFish = Nothing
				Response.End
			Else
				Set rsFish = Nothing
			End If
			strSql="INSERT INTO POSTCODES (FISHERY,STREET_ADDRESS,TOWN_CITY,COUNTY,POSTCODE) VALUES ('" & strFishery & "','" & strAddress & "','" & strTown & "','" & strCounty & "','" & strPost & "')"
			my_Conn.Execute(strSql)
			my_Conn.Close
			Set my_Conn=Nothing
			Response.Write "<p align=""center"">Complete</p>"
			Response.End
		End If
		Response.Write	"<form action=""address.asp"" method=""post"">" & vbNewLine & _
			"	<input type=""hidden"" name=""sendme"" value=""doit"" />" & vbNewLine & _
			"	<table align=""center"" width=""600px;"" border=""0"" cellpadding=""0"" cellspacing=""0"">" & vbNewLine & _
			"		<tr valign=""middle"">" & vbNewLine & _
			"			<td align=""center"" width=""100%"">" & vbNewLine & _
			"				<table align=""center"" width=""100%"" bgColor=""black"" border=""1"" style=""border-collapse:collapse;"" cellpadding=""4"" cellspacing=""1"">" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Fishery: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""fishery"" value=""" & Request.Form("fishery") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Street Address: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""street_address"" value=""" & Request.Form("street_address") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Town/City: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""town_city"" value=""" & Request.Form("town_city") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>County: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""county"" value=""" & Request.Form("county") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Post Code: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""postcode"" value=""" & Request.Form("postcode") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"				</table>" & vbNewLine & _
			"			</td>" & vbNewLine & _
			"		</tr>" & vbNewLine & _
			"		<tr height=""40px;"" valign=""bottom"">" & vbNewLine & _
			"			<td align=""center"" colspan=""2"" width=""100%"">" & vbNewLine & _
			"				<input style=""color:maroon; font-weight:bold; padding:5px 10px 5px 10px; border:1px solid black; text-shadow:0px 1px 1px #000; text-decoration none; border-radius:25px; -moz-border-radius:25px; -webkit-border-radius:25px; background:lightgrey;"" type=""Submit"" name=""Submit"" value=""Submit"" />" & vbNewLine & _
			"			</td>" & vbNewLine & _
			"		</tr>" & vbNewLine & _
			"	</table>" & vbNewLine & _
			"</form>" & vbNewLine
		%>
	 </body>
</html>

Edited by - Carefree on 08 August 2014 18:30:11

Webbo
Average Member

United Kingdom
982 Posts

Posted - 10 August 2014 : 15:50:22

Hi Carefree, the above accepts any username and password combination and takes the user to the form. However the form does not send the information to the database regardless if the user and pass values are correct or not

If you would like I can email you a link to the page and a user and password, let me know.

Edited by - Webbo on 10 August 2014 15:50:58

Carefree
Advanced Member

Philippines
4207 Posts

Posted - 10 August 2014 : 20:23:33

I just discovered that myself and rewrote the whole thing. Make sure you change the connection string (in red). This uses sha256 for encryption of the password. I just appropriated the include file from Snitz.


<!--#INCLUDE file="inc_sha256.asp"-->
<%
strConn = "driver={MySQL ODBC 5.1 Driver};option=67108867;server=127.0.0.1;user=user;password=password;DATABASE=dbname;"
set my_Conn = Server.CreateObject("ADODB.Connection")
my_Conn.Open(strConn)
strCookieURL = Left(Request.ServerVariables("Path_Info"), InstrRev(Request.ServerVariables("Path_Info"), "/"))
Function chkString(fString)
	fString = Trim(fString)
	fString = Replace(fString, "'", "''")
	fString = Replace(fString, "\", "\\")
	fString = Replace(fString, ">", ">")
	fString = Replace(fString, "<", "<")
	chkString = fString
End Function
If Request.Form("Pass") > "" Then
	strUser = trim(Request.Form("User"))
	strPass = sha256(Request.Form("Pass"))
Else
	strUser = Request.Cookies("FishAddy" & "User")("User")
	strPass = Request.Cookies("FishAddy" & "User")("Pass")
End If
If strUser > "" Then
	strSql = "SELECT USER_ID, USER_NAME, USER_PASS FROM USERS WHERE USER_NAME='" & strUser & "' AND USER_PASS='" & strPass & "'"
	Set rsID = my_Conn.Execute(strSql)
	If not rsID.EOF Then
		Response.Cookies("FishAddy" & "User").Path = strCookieURL
		Response.Cookies("FishAddy")="FishAddy"
		Response.Cookies("FishAddy" & "User")("User") = strUser
		Response.Cookies("FishAddy" & "User")("Pass") = strPass
		Response.Cookies("FishAddy" & "User").Expires =  dateAdd("d", 30, Now())
		rsID.Close
	Else
		Set rsID = Nothing
		strUser=""
		Response.Write	"End of file reached.  <mark>No record found.</mark>  Go <a href=""javascript:history.go(-1);"">back</a> to try again."
		Response.End
	End If
	Set rsID = Nothing
End If
If Request.Form("sendme") > "" Then
	dim strFishery, strAddress, strTown, strCounty, strPost, strConn, my_Conn, strErr
	strErr = ""
	If Request.Form("fishery")>"" Then
		strFishery=chkString(Request.Form("fishery"))
	Else
		strErr="[li]You must specify the fishery.[/li]"
	End If
	If Request.Form("street_address")>"" Then
		strAddress=chkString(Request.Form("street_address"))
	Else
		strErr=strErr & "[li]You must specify the street address.[/li]"
	End If
	If Request.Form("town_city")>"" Then
		strTown=chkString(Request.Form("town_city"))
	Else
		strErr=strErr & "[li]You must specify the town/city.[/li]"
	End If
	If Request.Form("county")>"" Then
		strCounty=chkString(Request.Form("county"))
	Else
		strErr=strErr & "[li]You must specify the county.[/li]"
	End If
	If Request.Form("postcode")>"" Then
		strPost=chkString(Request.Form("postcode"))
	Else
		strErr=strErr & "[li]You must specify the post code.[/li]"
	End If
	If strErr > "" Then
		Response.Write "<table align=""center"" width=""50%"" border=""0"">" & vbNewLine & _
			" <tr>" & vbNewLine & _
			"  <td align=""left"">" & vbNewLine & _
			"   <font face=""times new roman"" size=""5"" color=""black""><p><b><u>Missing Information</u></b></p><ul>" & strErr & "</ul>" & vbNewLine & _
			"    Go <a href=""javascript:history.go(-1)"";>back</a> to correct." & vbNewLine & _
			"   </font>" & vbNewLine & _
			"  </td>" & vbNewLine & _
			" </tr>" & vbNewLine & _
			"</table>" & vbNewLine
		Response.End
	End If
	strSpace="               "
	Response.Write "<font face=""consolas"">Fishery:" & left(strSpace,9) & strFishery & "<br />Street Address:	" & left(strSpace,1) & strAddress & "<br />Town/City:	" & left(strSpace,7) & strTown & "<br />County:	" & left(strSpace,9) & strCounty & "<br />Post Code:	" & left(strSpace,7) & strPost & "<br /></p></font>"
	strSql="SELECT FISHERY, POSTCODE FROM POSTCODES WHERE FISHERY='" & strFishery & "' AND POSTCODE='" & strPost & "'"
	Set rsFish=my_Conn.Execute(strSql)
	If not rsFish.EOF Then
		rsFish.Close
		Response.Clear
		strErr = "[li]Already in database.[/li]"
		Response.Write "<table align=""center"" width=""50%"" border=""0"">" & vbNewLine & _
			" <tr>" & vbNewLine & _
			"  <td align=""left"">" & vbNewLine & _
			"   <font face=""times new roman"" size=""5"" color=""black""><p><b><u>Duplicate Information</u></b></p><ul>" & strErr & "</ul>" & vbNewLine & _
			"    Go <a href=""javascript:history.go(-1)"";>back</a> to correct." & vbNewLine & _
			"   </font>" & vbNewLine & _
			"  </td>" & vbNewLine & _
			" </tr>" & vbNewLine & _
			"</table>" & vbNewLine
		Set rsFish = Nothing
		Response.End
	Else
		Set rsFish = Nothing
	End If
	strSql="INSERT INTO POSTCODES (FISHERY,STREET_ADDRESS,TOWN_CITY,COUNTY,POSTCODE) VALUES ('" & strFishery & "','" & strAddress & "','" & strTown & "','" & strCounty & "','" & strPost & "')"
	my_Conn.Execute(strSql)
	my_Conn.Close
	Set my_Conn=Nothing
	Response.Write "<p align=""center"">Complete</p>"
	Response.End
End If
%>
<html>
	<head>
		<title>Address Submission</title>
	</head>
	<body>
		<%
		If strPass = "" Then
			Response.Write	"<form action=""address.asp"" method=""post"">" & vbNewLine & _
				"	<table align=""center"" width=""600px;"" border=""0"" cellpadding=""0"" cellspacing=""0"">" & vbNewLine & _
				"		<tr valign=""middle"">" & vbNewLine & _
				"			<td align=""center"" width=""100%"">" & vbNewLine & _
				"				<table align=""center"" width=""100%"" bgColor=""black"" border=""1"" style=""border-collapse:collapse;"" cellpadding=""4"" cellspacing=""1"">" & vbNewLine & _
				"					<tr valign=""middle"">" & vbNewLine & _
				"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
				"							<font face=""times new roman"" size=""5"" color=""navy""><b>User: </b></font>" & vbNewLine & _
				"						</td>" & vbNewLine & _
				"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
				"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
				"								<input type=""text"" name=""User"" value=""" & Request.Form("User") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
				"							</font>" & vbNewLine & _
				"						</td>" & vbNewLine & _
				"					</tr>" & vbNewLine & _
				"					<tr valign=""middle"">" & vbNewLine & _
				"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
				"							<font face=""times new roman"" size=""5"" color=""navy""><b>Pass: </b></font>" & vbNewLine & _
				"						</td>" & vbNewLine & _
				"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
				"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
				"								<input type=""password"" name=""Pass"" value="""" style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
				"							</font>" & vbNewLine & _
				"						</td>" & vbNewLine & _
				"					</tr>" & vbNewLine & _
				"				</table>" & vbNewLine & _
				"			</td>" & vbNewLine & _
				"		</tr>" & vbNewLine & _
				"		<tr height=""40px;"" valign=""bottom"">" & vbNewLine & _
				"			<td align=""center"" colspan=""2"" width=""100%"">" & vbNewLine & _
				"				<input style=""color:maroon; font-weight:bold; padding:5px 10px 5px 10px; border:1px solid black; text-shadow:0px 1px 1px #000; text-decoration none; border-radius:25px; -moz-border-radius:25px; -webkit-border-radius:25px; background:lightgrey;"" type=""Submit"" name=""Submit"" value=""Submit"" />" & vbNewLine & _
				"			</td>" & vbNewLine & _
				"		</tr>" & vbNewLine & _
				"	</table>" & vbNewLine & _
				"</form>" & vbNewLine
			Response.End
		End If
		Response.Write	"<form action=""address.asp"" method=""post"">" & vbNewLine & _
			"	<input type=""hidden"" name=""sendme"" value=""doit"" />" & vbNewLine & _
			"	<table align=""center"" width=""600px;"" border=""0"" cellpadding=""0"" cellspacing=""0"">" & vbNewLine & _
			"		<tr valign=""middle"">" & vbNewLine & _
			"			<td align=""center"" width=""100%"">" & vbNewLine & _
			"				<table align=""center"" width=""100%"" bgColor=""black"" border=""1"" style=""border-collapse:collapse;"" cellpadding=""4"" cellspacing=""1"">" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Fishery: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""fishery"" value=""" & Request.Form("fishery") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Street Address: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""street_address"" value=""" & Request.Form("street_address") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Town/City: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""town_city"" value=""" & Request.Form("town_city") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>County: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""county"" value=""" & Request.Form("county") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"					<tr valign=""middle"">" & vbNewLine & _
			"						<td align=""right"" bgColor=""lightblue"" width=""30%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy""><b>Post Code: </b></font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"						<td align=""center"" bgColor=""lightblue"" width=""70%"">" & vbNewLine & _
			"							<font face=""times new roman"" size=""5"" color=""navy"">" & vbNewLine & _
			"								<input type=""text"" name=""postcode"" value=""" & Request.Form("postcode") & """ style=""text-align:center; width:95%; background-color:lightgrey; font-weight:bold; color:maroon;"" />" & vbNewLine & _
			"							</font>" & vbNewLine & _
			"						</td>" & vbNewLine & _
			"					</tr>" & vbNewLine & _
			"				</table>" & vbNewLine & _
			"			</td>" & vbNewLine & _
			"		</tr>" & vbNewLine & _
			"		<tr height=""40px;"" valign=""bottom"">" & vbNewLine & _
			"			<td align=""center"" colspan=""2"" width=""100%"">" & vbNewLine & _
			"				<input style=""color:maroon; font-weight:bold; padding:5px 10px 5px 10px; border:1px solid black; text-shadow:0px 1px 1px #000; text-decoration none; border-radius:25px; -moz-border-radius:25px; -webkit-border-radius:25px; background:lightgrey;"" type=""Submit"" name=""Submit"" value=""Submit"" />" & vbNewLine & _
			"			</td>" & vbNewLine & _
			"		</tr>" & vbNewLine & _
			"	</table>" & vbNewLine & _
			"</form>" & vbNewLine
		%>
	</body>
</html>

Edited by - Carefree on 11 August 2014 18:46:31

Webbo
Average Member

United Kingdom
982 Posts

Posted - 11 August 2014 : 16:21:41

This one doesn't accept the user name or password, displaying the message: No record found. Go back to try again

In the database I have a table named: users
and columns in the table: User_ID , User_name , User_pass

Carefree
Advanced Member

Philippines
4207 Posts

Posted - 11 August 2014 : 18:01:41

It works here, does exactly what you asked, cookies and all. Was your password encrypted with sha256? That's what I wrote it to check. If the password is plain text or uses some other form of encryption, line 18 (below) will have to be changed accordingly.

For sha256 (currently used):

		strPass = sha256(Request.Form("Pass"))

For plain text passwords:

		strPass = Request.Form("Pass")

Any other encryption should follow the sha256 scheme, but you'll have to provide your own "include" file and function to call it.

Edited by - Carefree on 11 August 2014 18:03:40

Webbo
Average Member

United Kingdom
982 Posts

Posted - 11 August 2014 : 21:30:51

^^ That was it Carefree, I changed line 18 and it works fine

Many thanks

Carefree
Advanced Member

Philippines
4207 Posts

Posted - 11 August 2014 : 23:56:00

You're welcome.

Topic