AVONFRS
asked on
ASP Button to Run SQL Script and Fill Form Boxes
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
but you must surround it with <script language="javascript"> before the function and </script> at the end of the function
ASKER
When i click the Find Address button i get a problem with the following line
<input name="FindAddress" type="button" class="TextMediumBlackNonC
Object Expected error.
Any ideas?
<input name="FindAddress" type="button" class="TextMediumBlackNonC
Object Expected error.
Any ideas?
ASKER
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
%>
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
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
ASKER
Still not working.
scrn.jpg
scrn.jpg
Can you send me the code please
ASKER
<%@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>
<!--#include file="includes/pageheadertitle.asp"--> </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"> </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">
<!--#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"> </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> </p>
</form>
<p><br>
<br>
<span class="TextSmallBlackNonCaps"> </span><span class="TextSmallBlackNonCaps"><%=Request.ServerVariables("LOGON_USER")%></span><br>
<br>
</p>
<p> </tr>
</table>
</script>
</body>
</html>
Hi AvonFRS try taking out the runat =server in the script header, this script runs on the client
ASKER
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?
Can you see why its doing that as well?
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
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
ASKER
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?
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?
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
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
ASKER
if(document.form1.postcode
{
document.form1.GetAdd.valu
document.form1.Action = ("ReferralForm.asp");
}
Doesnt do anything at all now when i press FindAddress
{
document.form1.GetAdd.valu
document.form1.Action = ("ReferralForm.asp");
}
Doesnt do anything at all now when i press FindAddress
Thats because you have deleted the line that submits the form
document.form1.submit();
put this after the action line
document.form1.submit();
put this after the action line
ASKER
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.
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.
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
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
ASKER
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
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
ASKER
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
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>
<!--#include file="includes/pageheadertitle.asp"--> </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"> </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">
<!--#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> </p>
</form>
<p><br>
<br>
<span class="TextSmallBlackNonCaps"> </span><span class="TextSmallBlackNonCaps"><%=Request.ServerVariables("LOGON_USER")%></span><br>
<br>
</p>
<p> </tr>
</table>
</script>
</body>
</html>
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%>
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%>
ASKER
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
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
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.Postc
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.va
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.Postc
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.va
ASKER
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
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
Hi Avonfrs
can you send me what you have now please and I'll have a ponder.
can you send me what you have now please and I'll have a ponder.
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
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
ASKER
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
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
%>
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
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
ASKER
How do i do the last bit. Sorry, im new to VBScript
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
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
ASKER
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
This is the SQL string. I have put that into SQL and it gave me the result i was expecting
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?
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?
ASKER
The database is SQL Server 2005
BuildingNumber (nvarchar)
Postcode (nvarchar)
BuildingNumber (nvarchar)
Postcode (nvarchar)
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
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
ASKER
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("post
if request("GetAdd") = 1 then
set rsFA = server.CreateObject("ADODB
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
%>
<%
street = ""
town = ""
city = ""
postcode = ""
dim originalpostcode
originalpostcode = Replace(Request.Form("post
if request("GetAdd") = 1 then
set rsFA = server.CreateObject("ADODB
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
%>
Very good point
have you got something like?
conn.open "driver={sql Server}; server=[yourservername]; database=gazetteer;uid=you
have you got something like?
conn.open "driver={sql Server}; server=[yourservername]; database=gazetteer;uid=you
ASKER
This is the connection bit in the connection include file
Dim Gaz_String
Gaz_String = "Provider=SQLOLEDB;SERVER=
%>
Dim Gaz_String
Gaz_String = "Provider=SQLOLEDB;SERVER=
%>
ASKER
Its working.
Ill let you know what the final result was
Ill let you know what the final result was
ASKER
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
However, when i click the clear button, it clears all of the boxes but not the boxes which have been filled in automatically
Probably the easiet thing is make the clear button call a javascript function that individually clears down each field
ASKER
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.
Thank you very much for you help over the past few days.
If i could give you 1000 points i would do so.
ASKER