Retrieve data value in asp based on form Value

[yogeshwarv]

Hi all,

I need your help to retrieve data value from my MS Access database based on the criteria.

I have a database named distance in which i have made a table called cities and made 3 fields naming..City_fr om, City_To and Distance_KM

Now I want asp to find distance value from the datatable based on information given in City From and City To field

If value not found returned to "No Record Found"

Please help how can I do so...

I will be very very grateful for your prompt response.

Regards
Yogeshwar Vats

[jeffstl]

select Distance_KM from distance where City_from = '" & txtCityFrom.Tex t & "' and City_To = '" & txtCityTo.Text & "'

That is the sql you want. You will need to set up a form with those names to accept criteria values.

[yogeshwarv]

Hi jeffstl, thanks it working perfectly

how if i wish to put my result in front of find button

here is the code

<form name="dist" method="POST">
<table>
<tr><td><font size="2" face="Tahoma">C ity From: </font> </td>
<td><input type="text" name="City_From " size="25"></td></tr>
<tr><td><font face="Tahoma" size="2">City To: </font> </td>
<td><input type="text" name="City_To" size="25"></td></tr>
<tr><td>
<font face="Tahoma">
<input type="submit" name="Find" value="Find"></font></td>
<td></td>
</table>
</FORM>

<%
' Declaring variables
Dim RS, count, City_From, City_To, Distance_KM1, data_source, con, KM, check,string

' A Function to check if some field entered by user is empty
Function ChkString(strin g)
If string = "" Then string = ""
ChkString = Replace(string, "'", "''")
End Function

' Receiving values from Form
City_From = ChkString(Reque st.Form("City_F rom"))
City_To = ChkString(Reque st.Form("City_T o"))

If city_from="" OR city_to=False Then

Response.write "<br>"

Else

data_source = "Provider=Micro soft.Jet.OLEDB. 4.0; Data Source=" & _
Server.MapPath( "distance.m db")

' Creating Connection Object and opening the database
Set con = Server.CreateOb ject("ADODB.Con nection")
con.Open data_source

check = "select count(*) as [count] from cities where City_From ='"& Request.Form("C ity_From") & "' AND City_To ='"& Request.Form("C ity_To") & "'"

set RS = Con.Execute(che ck)

if RS("Count") = 0 then
Response.Write "<b>Records Not Found </b>"

Else

KM = "SELECT distance_KM FROM cities " _
& " WHERE city_from = '" & Replace( city_from, "'", "''" ) & "' " _
& " AND city_to = '" & Replace( city_to, "'", "''" ) & "' "

set RS = con.execute(KM)
distance_KM1 = RS("distance_KM ")
RS.close

Con.close

Response.Write "Dist from " & city_from & " to " & city_to & " is " & distance_km1 & " KMs"

end if
end if
%>


pls help

[jeffstl]

I am not sure exactly what you mean. If you mean you are wanting to do the city_From and to you just need to select those values out as well with the distance.

KM = "SELECT distance_KM,city_from,city_to FROM cities " _
& " WHERE city_from = '" & Replace( city_from, "'", "''" ) & "' " _
& " AND city_to = '" & Replace( city_to, "'", "''" ) & "' "

set RS = con.execute(KM)
distance_KM1 = RS("distance_KM")
city_from = RS("city_from")
city_to = RS("city_to")
RS.close

[yogeshwarv]

The script is is running succesfully but i want to detemine the placing of result value into form.

I want to put result value in front of the Find Button. would appreciate your great help

Regards
Yogeshwar