View Full Version : forms and asp page

08-16-2005, 11:52 PM
Hi all, im fairly new to asp and playing about with asp and pulling out data from an access databases using forms...and have come across a problem. i have a simple form to prompt a user to enter a team name...


Please enter the Football Team Name:
<FORM METHOD="post" ACTION="SearchTeamResponse.asp">
<INPUT TYPE="text" NAME="FootballTeamInput" SIZE="20">
<P><INPUT TYPE="submit" VALUE="Search" NAME="searchbutton">
<INPUT TYPE="reset" VALUE="Reset" NAME="resetbutton">

This then looks at the following SearchTeamResponse.asp page, with which i am having the problem...

dim oRSft
set oRSft=Server.CreateObject("ADODB.recordset")
sqltext="SELECT * FROM Club_Data WHERE Club_Name=" & "'varFootballTeam'" & ";"
Response.Write sqltext
oRSft.Open sqltext, "DSN=Footy"
Response.Write "<TABLE BORDER=1>"

Do while NOT oRSft.EOF
Response.Write "<TR><TD>" & oRSft("Club_Name") & "&nbsp;</TD>"
Response.Write "<TD>" & oRSft("Club_Ground") & "&nbsp;</TD>"
Response.Write "<TD>" & oRSft("Club_Capacity") & "&nbsp;</TD>"
Response.Write "<TD>" & oRSft("Year_Formed") & "&nbsp;</TR></TD>"
Response.Write "</TABLE>"
set oRSft=nothing

when i execute this the code seems to stop at
Response.Write sqltext. the form seems to process ok, and moves to the next page with my sql statement written out...but below this the table doesnt appear to kick in for some reason and my sql statements (along html headers etc that i didnt include in this code) are the only things displaying on screen. Anyone have any ideas? Been staring at this for ages now and just cant figure out what im doing wrong.....wouldnt be surprised if something simple! Does anything stand out as being incorrect with this?

Anyhow if any one has any ideas then your help would be much appreciated!

08-17-2005, 12:03 AM
Just glancing at it quickly I see that you have this:

sqltext="SELECT * FROM Club_Data WHERE Club_Name=" & "'varFootballTeam'" & ";"

varFootballTeam, is a variable you defined, therefore if you put quotes around it like that, it's going to be treated as a literal, I'm assuming when you see youre response.write on your sql statement it has the literal varFootballTeam in the where clause? Assuming so, i'd change it to this and see what happends:

sqltext = "SELECT * FROM Club_Data WHERE Club_Name = '" & varFootballTeam & "'"

Good luck

08-17-2005, 12:14 AM
Brando...thats brilliant thanks for the prompt and accurate reply, that has sorted it! Now i just need to get my head around where the single and double quotes are going/doing. Any chance you could explain that line a little for me...

Again thanks alot for your help!

08-17-2005, 12:41 AM
You're welcome :)
I was assuming that your field `Club_Name` in your table was set as a string type field, when you're doing a comparision on strings, you need to enclose the string you're searching on, in quotes. Unlike other languges such as php for example, you can interpolate your variables, in asp you can't, meaning you can't enclose your variables in quotes:
valid in php:

$myvar = 'brandoe85';
echo "hey $myvar";

asp version:

Dim myvar
myvar = "brandoe85"
Response.Write "hey " & myvar

So what we did in your statement, is we put them in our string literal `sqltext` and then concatenated our `varFootballTeam` variable into our sql statement. Now even if your field wasn't a string, you would need to concatenate your variable name into your `sqltext` but omitting your single quotes in your statement.

08-17-2005, 03:33 AM
to expound on what Brandoe said, In asp any time you have a string assigned to a variable the 1st instance of a lone double quote " will close the string. Therefore in your case you have a variable named sqltext and try to assign the following string to it

sqltext="SELECT * FROM Club_Data WHERE Club_Name="
the actual value of sqltext is SELECT * FROM Club_Data WHERE Club_Name= then you had the concatenation operator (&) and then had another set of double quotes which asp then misunderstood and thought you were trying to concatenate two strings together into the following
SELECT * FROM Club_Data WHERE Club_Name='varFootballTeam' and then add the 3rd string to it to come up with
SELECT * FROM Club_Data WHERE Club_Name='varFootballTeam';

While this looks like it could be a proper sql statement the only way this would work is if there was actually a team named varFootballTeam. (going back on the fact that the set of double quotes around the variable converted it into a literal.)

So whenever you write a sql statement in VB you need to have your first single quote inside the first string right before you have your closing double quote, then you have the concatenation operator followed by the variable then another concatenation operator and another string designated by a double quote followed by a single quote followed bythe semi colon and lastly another double quote to end the string. Optionally you can omit the semicolon in some databases.

Had you tried to omit the double quotes around 'varFootballTeam' you would have had the following string
SELECT * FROM Club_Data WHERE Club_Name=
with the following comment 'varFootballTeam' & ";"
this is because VB and it's subsets see a single quote outside of a string as marking a comment.

I hope that helped

08-18-2005, 12:54 AM
Thanks Brando and Miranda...that totally helps, better exlanation than the book infact!

ok i was hoping you could help me out with the following...


dim oRSft
dim icount
icount = 0
set oRSft=Server.CreateObject("ADODB.recordset")
sqltext="SELECT cd.Club_Name, pd.First_Name, pd.Second_Name, pd.Playing_Position, pd.Date_Of_Birth, pd.Nationality "
sqltext=sqltext & " FROM Player_Data pd, Club_Data cd "
sqltext=sqltext & " WHERE cd.Club_Name = '" & varFootballTeam & "'"
oRSft.Open sqltext, "DSN=footy"
Response.Write sqltext
Response.Write "<TABLE BORDER='1'>"
Do While NOT oRSft.EOF
icount = icount + 1
Response.Write "<TR><TD>" & icount & "</TD>"
Response.Write "<TD>" & oRSft("Club_Name") & "</TD>"
Response.Write "<TD>" & oRSft("First_Name") & "</TD>"
Response.Write "<TD>" & oRSft("Second_Name") & "</TD>"
Response.Write "<TD>" & oRSft("Playing_Position") & "</TD>"
Response.Write "<TD>" & oRSft("Date_Of_Birth") & "</TD>"
Response.Write "<TD>" & oRSft("Nationality") & "</TD></TR>"
Response.Write "</TABLE>"
set oRSft=nothing

Something is not right with the above sql statement. I have two tables, club_data and player_data. on the form page when i enter the club name it is supposed to return all players set up under that club name, however what it actually does is return all players in the player_data table and gives them the club_name of the club that was entered on the form.

the club_name only exists in the club_data table and the club_name and player_data is linked by a field called Club_ID. For example if i ran the above but changed the where clause to club_data.club_id = player_data.club_id then i would get a list off all players with their corresponding club name. but how do i amend this to return all players against the club_name eneterd by the user of the form page?

Any ideas?

ps...your help is great on this site by the ways guys!

08-18-2005, 01:15 AM
Hey sliwo,

You'll have to join up the two tables by their linking fields, try this out:

sqltext = "SELECT cd.Club_Name, pd.First_Name, pd.Second_Name, pd.Playing_Position, pd.Date_Of_Birth, pd.Nationality"
sqltext = sqltext & " FROM Player_Data pd INNER JOIN Club_Data cd ON pd.Club_ID = cd.Club_ID"
sqltext = sqltext & " WHERE cd.Club_Name = '" & varFootballTeam & "'"

You can take a look at the JOIN syntax if you'd like:
JOINS (http://www.w3schools.com/sql/sql_join.asp)

Good luck

08-20-2005, 12:46 PM
Thats great thanks for your help Brando.

Ok doing all that was from an access db for which i had a DSN set up for. How would i go about running that same code for an sql db? I have set up a DSN for the sql db. Any pointers you could give me in terms of the code to open a connection to an sql db??


08-21-2005, 12:23 AM

heres a dsn and dsnless connection to mssql

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "driver={sql server};server=;database=somedb;uid=someuser;pwd=somepw;"
'objConn.Open "dsn=somedsn;uid=someuser;pwd=somepw;"
Set objConn = Nothing
and then theres trusted connections on top but they confuse me

the single quote thing is simple.. i got lost in those explanations so ill write 1

the sql has to be "SELECT * FROM Club_Data WHERE Club_Name = 'SomeTeamName' "
you need 's around strings in sql. when you concatenate in a variable it needs to end up with a string like that but showing the value of the variable. so "SELECT * FROM Club_Data WHERE Club_Name = '" & somevariable & "' "
if you are unsure about what it should come out like then get it to Response.Write the sql and see if it is the correct sql line. also you do not need the trailing ;, this just separates multiple sql statements.

in ms sql server some keywords are different from access but thats really the only difference. mainly this occurs in datatypes and theres some extra functionality