?
Solved

ASP Button to Run SQL Script and Fill Form Boxes

Posted on 2009-04-25
41
Medium Priority
?
967 Views
Last Modified: 2012-05-06
I have created a webpage ASP VBScript in dreamweaver which contains a form for our users to enter address details.

I want to make this process easier by putting a find address button on the side of the postcode field. So that they can fill in the house number and postcode, click FIND ADDRESS, and it will search our address database and fill in the Street Name, Town and City automatically.

Laid out like this.

House Number: [            ]
Postcode: [                ]    {FIND ADDRESS}
Street: [                                  ]
Town: [                              ]
City: [                              ]

How can this be done?

Database fields are:
BuildingNumber, StreetName1, AreaName1, AreaName2, Postcode

Database Name: Gazetteer
0
Comment
Question by:AVONFRS
  • 22
  • 19
41 Comments
 
LVL 1

Accepted Solution

by:
captainGrebo earned 2000 total points
ID: 24232064
The Address finder must acces the database, to do this you will need to reload the page
This is how

Create a hidden input within the form - <input type="Hidden" name="GetAdd" value = "0" />

create a javascript function :-

function SubmitAF()
{
  if (document.[yourformName].[yourPostCodeFieldName].length > 0)
  {
      document.[yourformName].GetAdd.value = 1;
      document.[yourformName].submit();
  }
  else
  {
      alert("You must enter a postcode to find an address");
  }
}


Attach your Address Finder button to the javascript function by adding :- onclick="SubmitAF()" to the button details


Now you have a way of reloading the form and a way of telling if you need to perform address search

At the top of the file (before the header statemnt) add the switch code for the address finder :-

<%
     StreetName1 = ""
     AreaName1 = ""
     AreaName2 = ""
     Postcode = ""

 
     if request("GetAdd") = 1 then
      set conn = [your database connection string]
      set rsFA = server.create("ADODB.recordset")
      strsql = "select distinct StreetName1, AreaName1, AreaName2, Postcode from [nameoftable] where Postcode = " & request("[YourPostcodefield]")
      rsFA.open conn,strsql
      if not rsFA.EOF then
            StreetName1 = rsFA("StreetName1")
            AreaName1 = rsFA("AreaName1")
            AreaName2 = rsFA("AreaName2")
            Postcode = rsFA("Postcode")
      end if
      rsFA.close
    end if
%>

Lastly

 Preset the values for your form inputs :-
 <input type="Text name = "[yourStreetnameFieldnmae]" value= "<%=StreetName1%>">

and for AreaName1 AreaName2 and Postcode


Things to think about:
The Postcode will have to match the format you have in the database. It is a good idea to do a format validation within the javascript function
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24232293
Does the javascript function have to be in its own javascript file?
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24232322
No You can put it in the header
but you must surround it with <script language="javascript"> before the function and </script> at the end of the function
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:AVONFRS
ID: 24232383
When i click the Find Address button i get a problem with the following line

<input name="FindAddress" type="button" class="TextMediumBlackNonCaps" id="Submit2" value="Find Address" onClick="SubmitAF()">

Object Expected error.

Any ideas?

0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24232438
Does this code seem correct for the javascript bit and the address finder bit?
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/Connections/HFSV.asp" -->
<SCRIPT RUNAT=SERVER LANGUAGE=JAVASCRIPT>					
function SubmitAF()
{
	if(document.form1.postcode.length > 0)
	{
		document.form1.GetAdd.value = 1	;
		document.form1.submit();
	}
	else
	{
		alert("You must enter a postcode and house number to find an address");
	}
}	
</script>			
	
<%
	street = ""
	town = ""
	city = ""
	postcode = ""
	dim originalpostcode = request.Form("postcode")
	if request("GetAdd") = 1 then
	set conn = Gaz_String
	set rsFA = server.CreateObject("ADODB.recordset")
	strsql = "SELECT DISTINCT STREETNAME1, AREANAME1, AREANAME2, POSTCODE FROM GAZETTEER WHERE POSTCODE = "& request.Form("postcode") & " AND BUILDINGNUMBER = "& request.Form("houseno")
	rsFA.open conn,strsql
	if not rsFA.EOF then
	street = rsFA("StreetName1")
	town = rsFA("AreaName1")
	city = rsFA("AreaName2")
	postcode = rsFA("Postcode")
	end if 
	rsFA.close
	end if
%>

Open in new window

0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24232520
Try taking the javascript and put inbetween <head> and </Head>  section or at least after <HTML>
The call from your button probably fails because the function is not in scope which willll be the case if it is not within the <HTML> area
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24232546
Still not working.
scrn.jpg
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24232635
Can you send me the code please
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24232666

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/Connections/HFSV.asp" -->
			
	
<%
	street = ""
	town = ""
	city = ""
	postcode = ""
	dim originalpostcode
	originalpostcode = Replace(Request.Form("postcode")," ","")
	if request("GetAdd") = 1 then
	set conn = Gaz_String
	set rsFA = server.CreateObject("ADODB.recordset")
	strsql = "SELECT DISTINCT STREETNAME1, AREANAME1, AREANAME2, POSTCODE FROM GAZETTEER WHERE POSTCODE = "& request.Form("postcode") & " AND BUILDINGNUMBER = "& request.Form("houseno")
	rsFA.open conn,strsql
	if not rsFA.EOF then
	street = rsFA("StreetName1")
	town = rsFA("AreaName1")
	city = rsFA("AreaName2")
	postcode = rsFA("Postcode")
	end if 
	rsFA.close
	end if
%>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HFSV Management Tool</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="refresh" content="100" />
<link href="CSS/style.css" rel="stylesheet" type="text/css" />
<script src="SpryAssets/SpryTabbedPanels.js" type="text/javascript"></script>
<script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
<link href="SpryAssets/SpryTabbedPanels.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.style12 {
	font-size: 12px;
	color: #000000;
}
-->
</style>
<link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css">
<link href="CSS/style.css" rel="stylesheet" type="text/css">
<SCRIPT RUNAT=SERVER LANGUAGE=JAVASCRIPT>					
function SubmitAF()
{
	if(document.form1.postcode.length > 0)
	{
		document.form1.GetAdd.value = 1	;
		document.form1.submit();
	}
	else
	{
		alert("You must enter a postcode and house number to find an address");
	}
}	
</script>
</head>
 
<body bgcolor="#CCCCCC" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="762" border="0" align="center" cellpadding="2" cellspacing="2" class="BorderTemplate">
  <tr>
    <td width="771" height="100%" valign="top"><table width="750" border="0" align="center" cellpadding="0" cellspacing="1" class="HFSVTopBar">
      <tr>
        <td height="40" class="NavTextSmall"><table width="97%" border="0" cellpadding="0" cellspacing="0" class="TextSmallBlackNonCaps">
            <tr>
              <td>&nbsp;
              <!--#include file="includes/pageheadertitle.asp"-->                &nbsp;</td>
            </tr>
        </table></td>
      </tr>
    </table>
  <table width="750" border="0" align="center" cellpadding="0" cellspacing="0">
    <tr>
          <td height="31" background="Images/ToolBar.png">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
    </tr>
      </table>
    <table width="750" height="48%" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
      <tr>
        <td width="180" height="619" valign="top" background="Images/MenuRightShadow.png" bgcolor="#ECE9D8">&nbsp;
<!--#include file="NavIncludes/StructureNav.asp" --></td>
        <td width="567" valign="top" class="TitleText"><p>Home Fire Safety Visit Referral Form<br>
            <br>
            <span class="TextSmallBlackNonCaps">Electronically Submit Home Fire Safety Referral Forms to Community Fire Safety</span></p>
          <form action="ReferralProcessor.asp" method="post" name="form1" id="form1">
            <table width="73%" border="0">
              <tr>
                <th width="65" valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left" class="TextSmallBlackNonCaps">
                    <div align="left">Occupier:</div>
                </div></th>
                <td width="264" valign="middle"><input name="occupier" type="text" class="TextMediumBlackNonCaps" id="occupier" size="40"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">House No:</div></th>
                <td valign="middle"><input name="houseno" type="text" class="TextMediumBlackNonCaps" id="houseno" size="20"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">Postcode:</div></th>
                <td valign="middle"><input name="postcode" type="text" class="TextMediumBlackNonCaps" id="postcode" size="10" value="<%=postcode%>">
                <input name="FindAddress" type="button" class="TextMediumBlackNonCaps" id="FindAddress" value="Find Address" onClick="SubmitAF()">
                <input name="GetAdd" type="hidden" id="GetAdd" value="0"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">Street:</div></th>
                <td valign="middle"><input name="street" type="text" class="TextMediumBlackNonCaps" id="street" size="30"value="<%=street%>"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">Town:</div></th>
                <td valign="middle"><input name="town" type="text" class="TextMediumBlackNonCaps" id="town" value="<%=town%>"></td>
              </tr>
              
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">City:</div></th>
                <td valign="middle"><input name="city" type="text" class="TextMediumBlackNonCaps" id="city" value="<%=city%>"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">Tel:</div></th>
                <td valign="middle"><input name="telephone" type="text" class="TextMediumBlackNonCaps" id="telephone" size="30"></td>
              </tr>
              <tr>
                <th height="90" valign="top" class="TextSmallBlackNonCaps" scope="row"><div align="left">Notes:</div></th>
                <td valign="middle"><textarea name="notes" cols="50" rows="5" class="TextMediumBlackNonCaps" id="notes"></textarea></td>
              </tr>
              <tr>
                <th height="30" valign="top" class="TextSmallBlackNonCaps" scope="row">&nbsp;</th>
                <td valign="middle"><label></label>                <input name="Submit" type="submit" class="TextMediumBlackNonCaps" id="Submit" value="Submit">
                <input name="Clear" type="reset" class="TextMediumBlackNonCaps" id="Clear" value="Clear">
                <input name="user" type="hidden" id="user" value="<%=Request.ServerVariables("LOGON_USER")%>"></td>
              </tr>
            </table>
            <p>&nbsp; </p>
          </form>
          <p><br>
            <br>
            <span class="TextSmallBlackNonCaps">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="TextSmallBlackNonCaps"><%=Request.ServerVariables("LOGON_USER")%></span><br>                       
            
            <br>
          </p>
      <p>&nbsp;      </tr>
</table>
 
  
  </script>
</body>
</html>

Open in new window

0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24232712
Hi AvonFRS try taking out the runat =server in the script header, this script runs on the client
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24232729
Thats great thanks. Im now getting the alert box though telling me i should put a postcode in even though i have.

Can you see why its doing that as well?
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24232938
Sorry
Its the javascript if statement not working properly
either create a javascript variable and load the postcode in it then you can check the length
or
change the if statement to -  if(document.form1.postcode != "")  
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24235159
Thats fixed that problem. But guess what there is another one.

The Javascript tells the form to submit, but if it submits it, it submits that form as if i was pressing submit at the bottom.

How can i get it so that it doesnt actually submit the form, but loads the address instead?
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24235234
You have to reload the form to get the info from the database.
In the function before the line document.form1.submit();

Put this line
document.form1.action = "[name of the current page]";

This will send it back to reload the current page and then you find out if the database bit works

Regards

0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24235256
     if(document.form1.postcode != "")
      {
            document.form1.GetAdd.value = 1      ;
            document.form1.Action = ("ReferralForm.asp");
      }

Doesnt do anything at all now when i press FindAddress
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24235269
Thats because you have deleted the line that submits the form
document.form1.submit();
put this after the action line
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24235287
Sorry.

Well. That just submits the form, and runs the process which i have specified as the form submit (which sends an email out).

I just need it to reload the text boxes if possible.
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24235346
Ok Avonfrs
My guess is that the database is not returning anything, so lets put some user feedback in
in the if statement that goes
if not rsFA.EOF then

Put an else
then
Prompt = "That Postcode was not found"

on the page at the top - I suggest just after the form statement

put something like this

<% if len(Prompt) then%><%=prompt%><%end if%>
This will only display the prompt if there is no postcode of that pattern found.
And this shows what fails but not why
and I have a hunch that it is because we have not put quotes around the Postcode in the sql statement

see the following
strsql = "SELECT DISTINCT STREETNAME1, AREANAME1, AREANAME2, POSTCODE FROM GAZETTEER WHERE POSTCODE = '"& request.Form("postcode") & "' AND BUILDINGNUMBER = "& request.Form("houseno")

I hope you can see the single quotes after where Postcode = and before and building number
If this does not fix it what I suggest is to put response.write statements in so you can see what the sql is
ie
response.write strsql
This line needs to be within <HTML> or it won't show on the page
when you have the sql as you want it delete the line
 
One more thing I have noticed is that you don't test that the person has entered a house number and you dont put it back when you reload the page
Let me know how you get on



 
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24235359
Ok ill try those.

But i have just one question.

If the Find Address button is submitting the page, then how is it going to return the values and reload the page, if i have set the form to submit to ReferrallFormProcessor.asp?
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24235398
Here is the updated code.

Still submitting the page to the ReferralFormProcessor.asp and completing without filling in the address.

And also no SQL String is showing
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/Connections/HFSV.asp" -->
			
	
<%
	street = ""
	town = ""
	city = ""
	postcode = ""
	dim originalpostcode
	originalpostcode = Replace(Request.Form("postcode")," ","")
	if request("GetAdd") = 1 then
	set conn = Gaz_String
	set rsFA = server.CreateObject("ADODB.recordset")
	strsql = "SELECT STREETNAME1, AREANAME1, AREANAME2, POSTCODE FROM GAZETTEER WHERE POSTCODE = '"& request.Form("postcode") & "' AND BUILDINGNUMBER = '"& request.Form("houseno")&"'"
	rsFA.open conn,strsql
	if not rsFA.EOF then
	street = rsFA("StreetName1")
	town = rsFA("AreaName1")
	city = rsFA("AreaName2")
	postcode = rsFA("Postcode")
 	end if
	if not rsFA.EOF	then
	prompt = "That postcode was not found"
	end if 
	rsFA.close
	end if
%>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HFSV Management Tool</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="refresh" content="100" />
<link href="CSS/style.css" rel="stylesheet" type="text/css" />
<script src="SpryAssets/SpryTabbedPanels.js" type="text/javascript"></script>
<script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
<link href="SpryAssets/SpryTabbedPanels.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.style12 {
	font-size: 12px;
	color: #000000;
}
-->
</style>
<link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css">
<link href="CSS/style.css" rel="stylesheet" type="text/css">
<SCRIPT LANGUAGE=JAVASCRIPT>					
function SubmitAF()
{
	if(document.form1.postcode != "")
	{
		document.form1.GetAdd.value = 1	;
		document.form1.Action = ("ReferralForm.asp");
		document.form1.submit("ReferralForm.asp");
	}
	else
	{
		alert("You must enter a postcode and house number to find an address");
	}
}	
</script>
</head>
 
<body bgcolor="#CCCCCC" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="762" border="0" align="center" cellpadding="2" cellspacing="2" class="BorderTemplate">
  <tr>
    <td width="771" height="100%" valign="top"><table width="750" border="0" align="center" cellpadding="0" cellspacing="1" class="HFSVTopBar">
      <tr>
        <td height="40" class="NavTextSmall"><table width="97%" border="0" cellpadding="0" cellspacing="0" class="TextSmallBlackNonCaps">
            <tr>
              <td>&nbsp;
              <!--#include file="includes/pageheadertitle.asp"-->                &nbsp;</td>
            </tr>
        </table></td>
      </tr>
    </table>
  <table width="750" border="0" align="center" cellpadding="0" cellspacing="0">
    <tr>
          <td height="31" background="Images/ToolBar.png">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
    </tr>
      </table>
    <table width="750" height="48%" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
      <tr>
        <td width="180" height="619" valign="top" background="Images/MenuRightShadow.png" bgcolor="#ECE9D8">&nbsp;
<!--#include file="NavIncludes/StructureNav.asp" --></td>
        <td width="567" valign="top" class="TitleText"><p>Home Fire Safety Visit Referral Form<br>
            <br>
            <span class="TextSmallBlackNonCaps">Electronically Submit Home Fire Safety Referral Forms to Community Fire Safety</span></p>
          <p>SQL STRING: <%=(Response.Write(strsql))%></p>
          <form action="ReferralProcessor.asp" method="post" name="form1" id="form1">
          <% if len(Prompt) then%><TR><TD class="[a red and bold class]" colspan="2"><%=prompt%></TD></TR><%end if%>
 
            <table width="73%" border="0">
              <tr>
                <th width="65" valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left" class="TextSmallBlackNonCaps">
                    <div align="left">Occupier:</div>
                </div></th>
                <td width="264" valign="middle"><input name="occupier" type="text" class="TextMediumBlackNonCaps" id="occupier" size="40"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">House No:</div></th>
                <td valign="middle"><input name="houseno" type="text" class="TextMediumBlackNonCaps" id="houseno" size="20"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">Postcode:</div></th>
                <td valign="middle"><input name="postcode" type="text" class="TextMediumBlackNonCaps" id="postcode" size="10" value="<%=postcode%>">
                <input name="FindAddress" type="button" class="TextMediumBlackNonCaps" id="FindAddress" value="Find Address" onClick="SubmitAF()">
                <input name="GetAdd" type="hidden" id="GetAdd" value="0"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">Street:</div></th>
                <td valign="middle"><input name="street" type="text" class="TextMediumBlackNonCaps" id="street" size="30"value="<%=street%>"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">Town:</div></th>
                <td valign="middle"><input name="town" type="text" class="TextMediumBlackNonCaps" id="town" value="<%=town%>"></td>
              </tr>
              
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">City:</div></th>
                <td valign="middle"><input name="city" type="text" class="TextMediumBlackNonCaps" id="city" value="<%=city%>"></td>
              </tr>
              <tr>
                <th valign="middle" class="TextSmallBlackNonCaps" scope="row"><div align="left">Tel:</div></th>
                <td valign="middle"><input name="telephone" type="text" class="TextMediumBlackNonCaps" id="telephone" size="30"></td>
              </tr>
              <tr>
                <th height="90" valign="top" class="TextSmallBlackNonCaps" scope="row"><div align="left">Notes:</div></th>
                <td valign="middle"><textarea name="notes" cols="50" rows="5" class="TextMediumBlackNonCaps" id="notes"></textarea></td>
              </tr>
              <tr>
                <th height="30" valign="top" class="TextSmallBlackNonCaps" scope="row"></th>
                <td valign="middle"><label></label>                <input name="Submit" type="submit" class="TextMediumBlackNonCaps" id="Submit" value="Submit">
                <input name="Clear" type="reset" class="TextMediumBlackNonCaps" id="Clear" value="Clear">
                <input name="user" type="hidden" id="user" value="<%=Request.ServerVariables("LOGON_USER")%>"></td>
              </tr>
            </table>
            <p>&nbsp; </p>
          </form>
          <p><br>
            <br>
            <span class="TextSmallBlackNonCaps">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="TextSmallBlackNonCaps"><%=Request.ServerVariables("LOGON_USER")%></span><br>                       
            
            <br>
          </p>
      <p>&nbsp;      </tr>
</table>
 
  
  </script>
</body>
</html>

Open in new window

0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24235430
Hi Avonfrs when the find address button is pressed the javascript changes the page that the submit will take you to, in fact it changes the page to reload the current page which I hope is "ReferralForm.asp"
I note that you have putthe page to submit to in the submit commandand that you have put barckets around the the pagename
try this
  document.form1.Action = "ReferralForm.asp";
  document.form1.submit();

On the sql string take all  the brackets from around strsql - the syntax is <%response.write strsql%>
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24235467
Ive made those changes. I really appreciate your help with this.

When i click the Find Address button, it submits the form (as in it submits to ReferralFormProcessor.asp as if im submitting the actual form). And the SQL String is still displays nothing.

I have attached the screenshots of what the pages look like.

The first one i have filled in the house number and postcode.

And the second one happens when i click Find Address - acts as though im clicking submit.
1sc.jpg
2scr.jpg
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24235544
Please can you tell me the name of the page that is 1st stage.

Things you can try are to put test alerts into the javascript
Try putting some alerts in the SubmitAF function
one before the if statement - alert(document.form1.Postcode.value);
if you don't get stopped each time you click find address that means the javascript form is not being called

Hang on I've just thought of something - I think the if statement in the function should refer to the value
document.form1.Postcode.value != "" try that
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24235563
Right.

I change to postcode.value and it gave me the prompt when i didnt enter anything into the poscode box, but once i had filled out the postcode and clicked find address it went to the second page - ReferralFormProcessor.asp

First form is the Referralform.asp
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24235654
Hi Avonfrs
can you send me what you have now please and I'll have a ponder.

0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24235932
Hi Avonfrs
I loaded the page onto my test server and played with it - I have found why it goes to the processor page. Good old Javascript case sensitivity
in the function change Action to action and it will post back to the current page
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24239418
I have changed it to action, now i am getting another error :)

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/ReferralForm.asp, line 15

Seems as though its the rsFA.open conn,strsql line
<%
	street = ""
	town = ""
	city = ""
	postcode = ""
	dim originalpostcode
	originalpostcode = Replace(Request.Form("postcode")," ","")
	if request("GetAdd") = 1 then
	set rsFA = server.CreateObject("ADODB.recordset")
	strsql = "SELECT STREETNAME1, AREANAME1, AREANAME2, POSTCODE FROM GAZETTEER WHERE POSTCODE = '"& request.Form("postcode") & "' AND BUILDINGNUMBER = '"& request.Form("houseno")&"'"
	rsFA.open conn,strsql
	if not rsFA.EOF then
	street = rsFA("StreetName1")
	town = rsFA("AreaName1")
	city = rsFA("AreaName2")
	postcode = rsFA("Postcode")
 	end if
	if not rsFA.EOF	then
	prompt = "That postcode was not found"
	end if 
	rsFA.close
	end if
%>

Open in new window

0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24239542
Hi Avonfrs
Its progress - the form is being reloaded

now is the time to get the sql and make sure the string has been formed correctly
My first hunch is that Building Number is not a string

If this doesn't work
 why don't you try
commenting out the lines that access the recordset and let the sql be shown, then you can cut and paste it into a query window on the database and see if it works
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24239560
How do i do the last bit. Sorry, im new to VBScript
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24239596
to commnet out a line in vbscript you type '

These lines will do it I think
      'rsFA.open conn,strsql
      'if not rsFA.EOF then
      'street = rsFA("StreetName1")
      'town = rsFA("AreaName1")
      'city = rsFA("AreaName2")
      'postcode = rsFA("Postcode")
       'end if
      'if not rsFA.EOF      then
      'prompt = "That postcode was not found"
      'end if
      'rsFA.close
      'end if


Don't forget to remove the comments when you think you have fixed it
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24239631
SELECT STREETNAME1, AREANAME1, AREANAME2, POSTCODE FROM GAZETTEER WHERE POSTCODE = 'BS44AU' AND BUILDINGNUMBER = '25'

This is the SQL string. I have put that into SQL and it gave me the result i was expecting
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24239669
The error that you had was pointing at the recordset .open line
This means that the system sent an ilegal sql string to the database. In web programming the web server uses an interface to join the asp to the Database. This interface may subtly alter the sql.

The values can only be ilegal if you have specified values , so the problem is either Postcode being BS44AU or BiuldingNumber being '10'

Humour me and tell me the table field type for BuildingNumber and Postcode also - what databse are you using? Is it by any chance Access?
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24239681
The database is SQL Server 2005
BuildingNumber (nvarchar)
Postcode (nvarchar)
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24239718
Look at this page

http://www.4guysfromrolla.com/aspfaqs/ShowFAQ.asp?FAQID=160

It could be the way you are forming your connection string or the name you are using, for instance is it con or conn
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24239813
Ive just had a thought. How does it know what to connect. I havent specified anywhere to connect using the GAZ_STRING in the include file. And conn isnt defined anywhere

<%
      street = ""
      town = ""
      city = ""
      postcode = ""
      dim originalpostcode
      originalpostcode = Replace(Request.Form("postcode")," ","")
      if request("GetAdd") = 1 then
      set rsFA = server.CreateObject("ADODB.recordset")
      strsql = "SELECT STREETNAME1, AREANAME1, AREANAME2, POSTCODE FROM GAZETTEER WHERE POSTCODE = '"& Request.form("postcode") & "' AND BUILDINGNUMBER = '"& Request.form("houseno")&"'"
      rsFA.open conn, strsql
      if not rsFA.EOF then
      street = rsFA("StreetName1")
      town = rsFA("AreaName1")
      city = rsFA("AreaName2")
      postcode = rsFA("Postcode")
       end if
      if not rsFA.EOF      then
      prompt = "That postcode was not found"
      end if
      rsFA.close
      end if

%>
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24239866
Very good point

have you got something like?
conn.open "driver={sql Server}; server=[yourservername]; database=gazetteer;uid=yourusername];password=[yourPassword];"
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24239896
This is the connection bit in the connection include file

Dim Gaz_String
Gaz_String  = "Provider=SQLOLEDB;SERVER=SQL1;DATABASE=Gazetteer;User ID=HFSVAdmin;PassWORD=hfsv;Trusted_Connection=True;LANGUAGE=British English"
%>

0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24239904
Its working.

Ill let you know what the final result was
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24239988
Its working great :)

However, when i click the clear button, it clears all of the boxes but not the boxes which have been filled in automatically
0
 
LVL 1

Expert Comment

by:captainGrebo
ID: 24240030
Probably the easiet thing is make the clear button call a javascript function that individually clears down each field
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 24240132
Ok. Its not urgent so ill work on that in my own time.

Thank you very much for you help over the past few days.

If i could give you 1000 points i would do so.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question