SQL Fiends - whats wrong here ?

[lockstock2sb]

Hi All,

I am in the process of learning how to use SQL on a website to access an access databse primarily and then an SQL db later on once i am better at it. I have maded a simple form page that i enter a f1 team name into and then the 2nd page that loads in the string from the previous page and searches the table for what i have typed. It appears at the moment no matter what i type its saying it cant find the team i specified. Anyone know whats wrong below ? :


<%@ Language=VBScript %>
<% Option Explicit %>

<%

'define the variables
Dim SQL,myteams,SubmitText1,conn


'Get the variable from the querystring.
Request.Querystring("SubmitText1" )



'Build the SQL statement, making sure to surround the text field "SubmitText1" with single quotes

SQL="SELECT TeamName FROM Tbl_Teams "

SQL=SQL & "WHERE TeamName='"& SubmitText1 &"'"



'Create connection named "conn" + Load the resulting recordset into the variable myteams

set conn = server.createobject("ADODB.Connection" )

conn.open "f1db_access"

set myteams=conn.execute(SQL)



'Test to see if there are any matching records. If not, give a message.

If myteams.bof and myteams.eof then

Response.Write ("Team not found. Please try again." )


'Otherwise give another message.

Else

Response.Write ("Welcome back!" & myteams(0) & " " & "You are listed in the database." )

End if

conn.close

%>


>> Edited by lockstock2sb on Wednesday 28th December 16:10

[grumbledoak]

Are you sure you are setting 'SubmitText1' before you
set up the SQL statement ?

I can see the Dim, but no assignment.

[lockstock2sb]

Errrr.......

All i have done so far is the 1st page is a form and i have named the field SubmitText1. When You hit submit it loads the 2nd page and then i call in the field from page 1.

Its passing the value as the url shows it on the 2nd page : http://localhost/default2.asp?SubmitText1=Ferrari

Cheers

Steve



>> Edited by lockstock2sb on Wednesday 28th December 16:24

[grumbledoak]

Been a while since I did asp, but looks to me like you
have more than one 'SubmitText1' here !


Requesting blah.asp?SubmitText1=Ferrari
will set Request.Querystring( "SubmitText1" ) to "Ferrari"


But you also Dim a variable SubmitText1, and use that one
when creating the SQL:

SQL="SELECT TeamName FROM Tbl_Teams "
SQL=SQL & "WHERE TeamName='"& SubmitText1 &"'"


So presumably you meant:

SubmitText1=Request.Querystring( "SubmitText1" )

SQL="SELECT TeamName FROM Tbl_Teams "
SQL=SQL & "WHERE TeamName='" & SubmitText1" & "'"

Better explanation ?



>> Edited by grumbledoak on Wednesday 28th December 16:34

[lockstock2sb]

Right on the money ! Thats what i missed

Its been ages since i looked at asp also so i am rusty - hence missing out 'obvious' things like that

Cheers mate !

Steve