variables with spaces in sql statements

[Guest]

I am having problems getting this code below to work. I am not why this is
happening, I am a novice programmer and this is the first big project I've
done. So the problem is when I execute the following sql statement I get a
eof/bof error and it only occurs when the value of the column Team has a
space in it ie(The Team)

all relevant code: -
-------------------------------------------------------

strteam = Request.Form("form_team")
CheckTeamsSQL = "Select * from tblteams WHERE TeamMembers ='" & strusername
& "'"
Set rsCheckTeam = dbConn.Execute(CheckTeamsSQL)
If rsCheckTeam.eof Then
' this is when the error comes up. only when strteam contains a name with
spaces
TeamInfoSQL = "Select * from tblteams WHERE Captain = True and Team ='"&
strteam &"'"
set rsTeamInfo = dbConn.Execute(TeamInfoSQL)

strmotto = rsTeamInfo.fields("Motto")
rs.Fields("Motto") = strmotto

-------------------------------------------------
and the value from the form is a list menu generated dynamically so the
value is always whats in the database.

So I guess what I need help with is why I cant have spaces, I believe
that I have had fields with spaces before and it worked. and if I cant have
spaces how would you convert spaces into something else to put into the
database and then still display the correct names with spaces. Thanks for any
help

 

[Thomas A. Rowe]

You can have spaces, just not in field names and best not to have space in values that will used for
lookup.

I would assign a unique ID to each team, and then use the TeamID as the lookup value and to
associate members, etc.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

 

[Guest]

Thank you for the quick reply, but I was wondering if you could explainif
spaces in look up values are prohibited or just not recommended. At this
point I think it could possibly be easier to keep the spaces and modify sql
to allow spaces at least until I have time to rework code. Thank you again
for your help

 

[Thomas A. Rowe]

You can have spaces, but based on the issues you are having I would suggest avoiding them.

I think all you would really need to do is add a field to the database with a unique ID for each
team, and then use that as the look up value.
--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

 

[p c]

You can have space in values for text fields for SQL statements and it
should work. Are you sure the space in the text value is the problem? Is
there a quote within the text?

Add this line after you complete SQL assignmen for TeamInfoSQL

Response.write "<br>TeamInfo SQL: " &TeamInfoSQL

Then run the form for the one that gives you problem. and review outpu
SQL for proper SQL syntax.

If you want, you can test for proper SQL in in the Access DB. Open the
file, go to queries, create a query (doesn't matter what), go to the SQL
View, delete everything, paste the SQL from the browser, and click Run
(exclamation mark).

By the way if you use spaces in tables or field names in Access, enlse
them in SQL with squire brackets

E.G., Select [my field with space] FROM [my table with space]

But you are better off without them.


...PC

 

[Guest]

I did what you suggested and it displays everything up unitl the space then
cuts off.So if the team name is Worst Stars then the sql looks like
Team='Worst'
not sure how to fix this butI am implementing Thomas' suggestion for ease in
the future but would appreciate a solution. Thanks