Number Converter 1

[micawber]

I would like to create a page to enable a user to type a choice of 3 freephone numbers (i.e. 0800 123 4567) and it then return a corresponding local number. (i.e 0121 123 4567)

I have a long list of the numbers in a excel document.

As I have some knowledge of asp, I presume this would be best handled with asp.

Can anyone describe how I should set this up please?

Any examples/code/links would be fantastic!

Thanks.

 

[Sheco]

One approach would be to use ADO to bring the data from your spreadsheet into ASP. Try using the ConnectionString from this site:

http://www.connectionstrings.com/?carrier=excel

This approach would allow you to approach the spreadsheet as if it were a SQL database. Specifically, it would allow you to make a query like this: [TT]
SELECT LocalNum FROM TheList WHERE FreeNum = 'xxxxxxx'[/tt]

Also remember that the user might not always type the FreeNum in the exact correct format so you might want some code to check the format if the query finds no results.

 

[micawber]

Thats great! Thanks Sheco!
Do you know of any tutorials or examples that could get me started on this?
Thanks again.

 

[Sheco]

what have you got so far?

 

[micawber]

I don't have anything at the moment.
I'm currently looking for anything that would start me off or give me a clue.
Thanks!

 

[Chopstik]

I would start with the Data Connectivity section of the FAQ's for this forum. You can find them in the FAQ's link above or here:

http://www.tek-tips.com/faq.cfm?pid=333.

This will give you an introduction of sorts to getting information from a database. You should be able to tweak it to get information from your Excel spreadsheet.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[damber]

It sounds like you could also do with having a browse of this site:

http://www.w3schools.com/asp/default.asp

It's a useful site for beginners that has a lot of the answers you're going to need during your project.

When sanitising your input data, look for functions that test for a variable being a number or not, and probably look at loops too - or if you're brave enough, look up regular expressions. Make sure your source data is sanitised ahead of time. And as Sheco notes, ADO will help with the connection to a datasource.

A smile is worth a thousand kind words. So smile, it's easy!

 

[micawber]

I'm still struggling with this one. So much so don't have any code worth posting.
I've looked for examples to kick start me on this but can't seem to find anything.
Does anyone know of any online?
Thanks.

 

[damber]

What exactly do you want ? The exact working code?

I suggest you try at least a little - you've been given several links to sites that will help you with the basics... and this is *very* basic, as well as hints as to how to approach this...

short of actually writing this for you, what more do you expect from us ?

Let's see... you want a web page with a form to enter the number you wish to lookup, then you want to sanitise that number using basic string functions, connect to a database with a select statement and post the results to a page.... seriously... this is fundamental stuff, if you really don't know how to do this in ASP then you are a beginner, in which case go to the w3schools link I gave above and learn what to do for each step.

In fact you have all the keywords you need in these posts to get some good google results.

So, try for yourself, or offer payment for someone to write it for you.



A smile is worth a thousand kind words. So smile, it's easy!

 

[micawber]

Thanks for your comments!
I appreciate your direction!
No I don't expect the exact working code! I just thought there might be similar examples online. Most of what I have come across in the past there has been.

I'm just having difficulty with the form aspect where I can input and get an output. This is my code i'm using to read from my excel document.

<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("book1.xls") & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM [sheet1$]"
rs.open strSQL, conn, 3,3

rs.MoveFirst
WHILE NOT rs.EOF
     Response.Write(rs("number") & "<br/>")
     Response.Write(rs("localphone") & "<br/>")
     Response.Write(rs("freephone") & "<br/><br/>")
rs.MoveNext
WEND

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing
%>

 

[damber]

Are you saying you don't know how to create a html form ?

look it up - there are about a million examples, in fact just look at the source of this page. then look up querystring and form properties of the Response object for ASP - again, both found on the w3schools.com site.

You are already creating output.

If you just have no idea what you want to do, I don't think anyone can help you. If you know what you want, but not how to do it, then start off by writing down each step of the process in simple to understand steps - e.g.: Step 1: create html form for user to enter data, step 2: submit form to server, etc... when you've done that then spend time looking at the w3schools.com site for how each stage is done.. and google the keywords. when you get stuck with something that's not easily found then ask a specific question here... otherwise we are basically just developing it for you.



A smile is worth a thousand kind words. So smile, it's easy!

 

[micawber]

Thanks for your comments damber.
Yes I know how to create a html form. That is no problem. Yeah the form on my previous post was creating an output but what I need is to get an output based on the input.
That is what I'm trying to achieve.
Here is my attempt:

<%
Option Explicit
Dim strConnection, conn, rs, strSQL, freephone

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("book1.xls") & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM [sheet1$]"
rs.open strSQL, conn, 3,3

%> 

<form method="post">Telephone Number :
 <input type="text" >		
 <input type="submit" value="Submit" />
</form>

<%
if freephone  <> "" then
   strSQL="SELECT * FROM [sheet1$] WHERE freephone='"&freephone&"'"
   Set rs=Server.CreateObject("ADODB.Recordset")
   rs.Open sql,conn, 3,3
%>
   <table width="100%" cellspacing="0" cellpadding="2" border="1">
   <tr>
     <th>number</th>
     <th>localphone</th>
     <th>freephone</th>
   </tr>
<%
do until rs.EOF
   response.write("<tr>")
   response.write("<td>" & rs("number") & "</td>")
   response.write("<td>" & rs("localphone") & "</td>")
   response.write("<td>" & rs("freephone") & "</td>")
   response.write("</tr>")
   rs.MoveNext
loop
rs.close
conn.Close
set rs=Nothing
set conn=Nothing%>
</table>
<%  end if %>

</body>
</html>

 

[damber]

start here:

http://www.w3schools.com/sql/sql_where.asp

and reread my first post about sanitising your input data and the source data too - they must be the same,..
e.g:
+44 1234 123456
is not the same as
0044 1234 123456
or
01234 123456
or
01234 123 456
or
(01234) 123456
etc, etc..

that is why you need to sanitise the data. first though, make sure the source data is a literal string of numbers.. so: 01234123456 or is you want international codes: 00441234123456 - that way you can format the input number to look the same...

One thing you will want to do is validate the user entry for different formats.. e.g. with area code or without, with international code or without etc..

There are two ways to do this sanitisation - string manipulation and loops or regular expressions.. however regular expressions are complicated for the novice, so assume for now that the input data is exactly the format you need, get it working that way, then add the input validation/sanitisation afterwards.

A smile is worth a thousand kind words. So smile, it's easy!

 

[micawber]

many thanks damber! i appreciate your time.
i'm trying to get the search and output working first then look at validation of the data.
i have altered the following just to handle numerical values based on the w3 site:

strSQL="SELECT * FROM [sheet1$] WHERE freephone=freephone"

can anyone help me on how i search and find, is it possible to search and find one record or row in excel?

thanks!

 

[tsuji]

[1]><input type="text" >
[tt]<input type="text" [red]name="freephone"[/red]>[/tt]
[2]
[tt]<%
[red]dim freephone
freephone=request.form("freephone")[/red]
if freephone <> "" then
strSQL="SELECT * FROM [sheet1$] WHERE freephone='"&freephone&"'"
[blue]'etc etc[/blue]
[/tt]

 

[micawber]

Thanks tsuji.
That works exactly how i want it to when the values in the excel document all contain a letter.
i.e. '123a'
my problem is the values in my column need to be numbers when this is the case I get an error.
'Data type mismatch in criteria expression.'

   strSQL="SELECT * FROM [sheet1$] WHERE freephone='"&freephone&"'"
rs.Open strSQL, conn, 3,3

i have tried changing the line to:

   strSQL="SELECT * FROM [sheet1$] WHERE freephone=freephone"

but it does not work..it just returns everything in the excel document.

Any ideas?
Many thanks.

 

[tsuji]

Change your connection string adding IMEX=1 forcing text return rather than relying on the caprice of its guessing the schema.

>strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("book1.xls") & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
[tt]strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("book1.xls") & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;[red]IMEX=1[/red]"""[/tt]

 

[micawber]

Thanks tsuji.
That works great!