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...

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

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:

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!

Brandoe85

08-16-2005, 11:41 PM

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.

miranda

08-17-2005, 02: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

sliwo

08-17-2005, 11:54 PM

Thanks Brando and Miranda...that totally helps, better exlanation than the book infact!

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!

Brandoe85

08-18-2005, 12:15 AM

Hey sliwo,

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

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

Good luck

sliwo

08-20-2005, 11:46 AM

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.

ghell

08-20-2005, 11:23 PM

www.connectionstrings.com

heres a dsn and dsnless connection to mssql

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