Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    forms and asp page

    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">
    </FORM>
    </BODY>
    </HTML>

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

    ...
    <%
    varFootballTeam=Request.Form("FootballTeamInput")
    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>"
    oRSft.MoveNext
    Loop
    Response.Write "</TABLE>"
    oRSft.Close
    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!
    Thanks

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Just glancing at it quickly I see that you have this:
    Code:
     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:
    Code:
     sqltext = "SELECT * FROM Club_Data WHERE Club_Name = '" & varFootballTeam & "'"
    Good luck
    Last edited by Brandoe85; 08-16-2005 at 11:31 PM.

  • #3
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  • #4
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    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:
    PHP Code:
    $myvar 'brandoe85';
    echo 
    "hey $myvar"
    asp version:
    Code:
    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.

  • #5
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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
    Code:
    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

  • #6
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

    ....

    <%
    varFootballTeam=Request.Form("FootballTeamInput")
    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>"
    oRSeof.MoveNext
    Loop
    Response.Write "</TABLE>"
    oRSft.Close
    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!

  • #7
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Hey sliwo,

    You'll have to join up the two tables by their linking fields, try this out:
    Code:
    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

    Good luck

  • #8
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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??

    cheers.

  • #9
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    www.connectionstrings.com

    heres a dsn and dsnless connection to mssql
    Code:
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "driver={sql server};server=127.0.0.1;database=somedb;uid=someuser;pwd=somepw;"
    'objConn.Open "dsn=somedsn;uid=someuser;pwd=somepw;"
    ....
    objConn.Close
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •