delimiters, sql, joins, AHHH!

[John]

Ok, I posted in here a few days ago about a problem with apostrophes in text
fields and I tried a few of the suggestions and now I'm in so deep I looking
at scraping the whole thing because now I don't know where to begin. If
fixing it looks like it will take longer than starting over then I suppose I
should let this ship sink and swim to shore and regroup.

Here's the way I started. Not the best way I'm sure. I'm self taught so
for those of you that did this in college you have have an idea of what I'm
going through and all the pitfalls and chasms I keep falling into. Anyway,
here's the summary of my mess.

I started entering info into an Access db before I wrote a line of code. 2
tables: gigs and places. I wanted to keep "places" separate from "gigs" to
keep prevent redundant info in the db. This way (I thought) it would be
easier this way so the "places" info would always be there to call on
instead rewriting it everytime and if something new came up to provide and
opportunity for the user to enter the info separately then just reuse it as
it came up. I was going to "cheat" and just create separate recordsets...an
RS for "gigs" then just create another for "places". WELL don't we have
quite a mess now!! Arg. Here the problems I've run into...

It started with the delimiter problems. The apostrophes in text fields
wreaked havoc on my data calls. I've been evil and using "SELECT *" so it
hasn't come up in sql strings "yet". The problems came when I tried to
match records in recordsets to something generated in loops...like looping
records in a select tag in html to produce all the options in a dropbox.
The problem was with apostrophes the record would stop writing when it hit
that first apostrophe in the text field. SO I tried using "Replace" to turn
them all into ´ that ended up creating a bigger mess (and html kept
interpreting ´ into ' so I could get them to mesh) and in the DB so
the more I mess with the worse things got...chasing the train as it were...

I did try to go back and change the Replace to "''" rather than "´"
(hard to read, ain't it) thats quote 2 apostrophes quote. I didn't
understand that in prior posts until later. By this time this thing is a
mess......

I messed with Access and tried using the lookup wizard to link the tables
but, as you know, that produces numeric ID's so around and around we
go.....AAHHHH!

So then I start reading up on JOIN's, inner's, outer's, left's ,right,s.
None of them seem to be what I need (and I'm just getting a handle on
sql)...and my head is spinning while I watch the clock run out on this
project. I AM GOING NUTS!!!

Oh, and this is the site by the way. the front end is done. It's the
backend, the users admin side, that's causing all the problems.
http://thehalftonhorns.com

So this is what I what to do...

1). have 2 tables, "gigs" and "places"
2). be able to call on and update both in recordsets

Here's a list of books I have on hand if you want to point me in a direction
you know of...
Beginning Active Server Pages 3.0 (Wrox)
Beginning ASP Databases (Wrox)
Beginning Javascript (Wrox)
HTML Complete
MSDN Library -Visual Studio 6.0 (disk)

I really hope someone can help on this. I'm driving my wife and kids nuts
locked in this room for days turning into "monster daddy".

Thanks!!!!
John

 

[Bob Barrows [MVP]]

Ok, I posted in here a few days ago about a problem with apostrophes
in text fields and I tried a few of the suggestions and now I'm in so

To decide whether or not to delimit the data, look at the datatype of the
FIELD - NOT THE DATA.
1. If it's a numeric field, you must supply it with numeric data, which
means you MUST NOT delimit the data by putting quotes around it.
2. If it's a character/text field, then you must supply string data by
delimiting the data either with single or double quotes. If the data
contains literal quotes, you must escape them by doubling them. This means
that if you use single quotes (apostrophes) for your string delimiters, and
the data contains an apostrophe, then you must replace the apostrophe with
two apostrophes, like this:
Update tbl set textfield = 'O''Malley'
In Access, you can use double quotes for your delimiters, so this will work
as well:
Update tbl set textfield = "O'Malley"
Note: you don't have to escape the apostrophe in O'Malley when you use
double quotes as the delimiter. However, you will need to escape the double
quotes when assigning this statement to a variable:
sSQL = "Update tbl set textfield = ""O'Malley"""
So most people will use the single quotes and escape the apostrophe:
sName = "O'Malley"
sSQL = "Update tbl set textfield = '" & Replace(sName,"'","''") & "'"
response.write sSQL

3. If it's a date/Time field, then the delimiters depend on the type of
database. Since you are using Access, then you must delimit the data with
hash marks (#). Additionally, you must supply the data in US format
(m/d/yyyy) or in ISO (yyyy-mm-dd), with the latter being the more
recommended.

4.Lastly, if you are using LIKE, you need to be aware that you must use %
and _ as the wildcards, not * and ?. This is true no matter what database
you are using

And then, when you think you have it right and it still does not work,
response.write it to see the result of your concatenation. If you've done it
correctly, you will have a statement that you can copy and paste from the
browser window into the SQL View of an Access Query Builder and run without
modification (unless you need to replace the wildcards with the Jet
wildcards).

Here's more:

http://groups.google.com/groups?hl=...=1&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

http://www.google.com/[email protected]&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

Bob Barrows

 

[John]

I'm doing this but I still can't get the recordset to match what is being
edited. I'm just not seeing it??

<%
Set rsGigPlace = Server.CreateObject("ADODB.Recordset")
gigPlaceSQL = "SELECT * FROM places ORDER BY placeName ASC;"
rsGigPlace.CursorType = 3
rsGigPlace.Open gigPlaceSQL, gigsCon

Response.write _
"<select name='location'>"
While Not rsGigPlace.EOF
Response.write _
"<option value='" & rsGigPlace("placeName") & "'"
If Replace(rsGigPlace("placeName"),"'","''") =
Replace(rsGigEdit("gigPlace"),"'","''") Then Response.write "selected" End
If
Response.write _
">" & rsGigPlace("placeName") & "</option>"
rsGigPlace.MoveNext
Wend
rsGigPlace.Close
Set rsGigPlace = nothing
Response.write "</select>"
%>

(rsGigEdit is from another recordset still open)

the html being produced....
<td>
<select name='location'><option value='Benefit Concert'>Benefit
Concert</option><option value='Fat Moe's'>Fat Moe's</option><option
value='Froggy's'>Froggy's</option><option value='George Eastman
House'>George Eastman House</option><option
value='Georgio's'>Georgio's</option><option value='TBA'>TBA</option><option
value='The Panorama'>The Panorama</option></select>
</td>

 

[larrybud2002]

2. If it's a character/text field, then you must supply string data
by

delimiting the data either with single or double quotes. If the data
contains literal quotes, you must escape them by doubling them. This means
that if you use single quotes (apostrophes) for your string delimiters, and
the data contains an apostrophe, then you must replace the apostrophe with
two apostrophes, like this:
Update tbl set textfield = 'O''Malley'
In Access, you can use double quotes for your delimiters, so this will work
as well:
Update tbl set textfield = "O'Malley"

I think it would be recommended that you use single quotes, so that
if/when you move this from Access to SQL, you won't have to go in and
rewrite so much code.

 

[Bob Barrows [MVP]]

I'm doing this but I still can't get the recordset to match what is
being edited. I'm just not seeing it??

Oh you're problem is not in creating the query? It's displaying the results?

<%
Set rsGigPlace = Server.CreateObject("ADODB.Recordset")
gigPlaceSQL = "SELECT * FROM places ORDER BY placeName ASC;"

Don't use selstar: http://www.aspfaq.com/show.asp?id=2096

rsGigPlace.CursorType = 3
rsGigPlace.Open gigPlaceSQL, gigsCon

Response.write _
"<select name='location'>"
While Not rsGigPlace.EOF
Response.write _
"<option value='" & rsGigPlace("placeName") & "'"
If Replace(rsGigPlace("placeName"),"'","''") =
Replace(rsGigEdit("gigPlace"),"'","''")

This is not necessary. Escaping the apostrophes is only necessary when
creating sql statements. All you need to do is:

If rsGigPlace("placeName") = rsGigEdit("gigPlace") Then
Response.write " selected"
End If

If this does not work, then we need to see the values being compared.

the html being produced....
<td>
<select name='location'><option value='Benefit Concert'>Benefit
Concert</option><option value='Fat Moe's'>Fat Moe's</option>

I would not use single-quote delimiters for the attributes. I would do this
(doubling the quote marks):

Response.write "<select name=""location"">"
Do Until rsGigPlace.EOF
Response.write "<option value=""" & _
rsGigPlace("placeName") & """"
If rsGigPlace("placeName") = rsGigEdit("gigPlace") Then
Response.write " selected"
End If
Response.write ">" & rsGigPlace("placeName") & "</option>"
rsGigPlace.MoveNext
Loop
rsGigPlace.Close
Set rsGigPlace = nothing
Response.write "</select>"


Bob Barrows

 

[John]

It didn't work. What you wrote produced this...
<select name="location"><option value="Benefit Concert">Benefit
Concert</option><option value="Fat Moe's">Fat Moe's</option><option
value="Froggy's">Froggy's</option><option value="George Eastman
House">George Eastman House</option><option
value="Georgio's">Georgio's</option><option value="TBA">TBA</option><option
value="The Panorama">The Panorama</option></select>

I should add that this is how the form data is processed. Is there
something here that's adding to my problems?

gigDateMerge = Request("month") & "/" & Request("day") & "/" &
Request("year")
gigTimeMerge = Request("Time_hour") & ":" & Request("Time_minute") &
Request("Time_AMPM")
gigTitle = Replace(Request("gigTitle"),"'","''")
gigLocation = Replace(Request("location"),"'","''")
gigComments = Replace(Request("comments"),"'","''")

Set rsGigUpdate = Server.CreateObject("ADODB.Recordset")
gigUpdateSQL = "SELECT * FROM gigs WHERE gigID=" & Request("gigID") & ";"
rsGigUpdate.Open gigUpdateSQL, gigConn, adOpenDynamic, adLockOptimistic,
adCmdText

rsGigUpdate("gigTitle") = gigTitle
rsGigUpdate("gigDate") = gigDateMerge
rsGigUpdate("gigTime") = gigTimeMerge
rsGigUpdate("gigPlace") = gigLocation
rsGigUpdate("gigComments") = gigComments
rsGigUpdate.Update
Set rsGigUpdate = nothing

 

[Bob Barrows [MVP]]

It didn't work. What you wrote produced this...

<select name="location">
<option value="Benefit Concert">Benefit Concert</option>
<option value="Fat Moe's">Fat Moe's</option>
<option value="Froggy's">Froggy's</option>
<option value="George Eastman House">
George Eastman House</option>
<option value="Georgio's">Georgio's</option>
<option value="TBA">TBA</option>

Uuuummmm - what's wrong with this output?

Bob Barrows

 

[John]

None of the values are matching anything in the recordset which "should"
produce a "selected". I don't know why because these exact values are going
"in" to the database as far as I can see. That's why I included the the
processing end to see if I'm doing something wrong there. what's weird is
when I change a record to a place "without" an apostrophe in it it works.
it's driving me crazy! I just can't see why?

Thanks Bob!! You're my last hope of getting this done in time! 8-D

 

[Bob Barrows [MVP]]

None of the values are matching anything in the recordset which
"should" produce a "selected". I don't know why because these exact
values are going "in" to the database as far as I can see. That's
why I included the the processing end to see if I'm doing something
wrong there. what's weird is when I change a record to a place
"without" an apostrophe in it it works. it's driving me crazy! I
just can't see why?

Thanks Bob!! You're my last hope of getting this done in time! 8-D

I can't help without seeing the data in the recordsets. Do this:

response.write rsGigPlace.getstring(2,," | ", "<BR>")
response.write rsGigEdit.getstring(2,," | ", "<BR>")
response.end

And show the results, explaining which fields are relevant.

Bob Barrows

 

[John]

This is what you requested produced...

5 | Benefit Concert | | Lyndonville | |
1 | Fat Moe's | 4423 Dewey Ave | Rochester | NY | 663-1860
3 | Froggy's | 1129 Empire Blvd | Webster | NY | 288-1080
6 | George Eastman House | 900 East Ave | Rochester | NY | 271-3361
2 | Georgio's | | Niagara Falls | NY |
7 | TBA | | | |
8 | The Panorama | | Gates | NY |
19 | Test gig | 1/1/2005 | 7:00:00 AM | Froggy''s | woo ha

The record I'm after is just the title, or the field right after the ID.

 

[Bob Barrows [MVP]]

This is what you requested produced...

5 | Benefit Concert | | Lyndonville | |
1 | Fat Moe's | 4423 Dewey Ave | Rochester | NY | 663-1860
3 | Froggy's | 1129 Empire Blvd | Webster | NY | 288-1080
6 | George Eastman House | 900 East Ave | Rochester | NY | 271-3361
2 | Georgio's | | Niagara Falls | NY |
7 | TBA | | | |
8 | The Panorama | | Gates | NY |
19 | Test gig | 1/1/2005 | 7:00:00 AM | Froggy''s | woo ha

The record I'm after is just the title, or the field right after the
ID.

Where does the rsGigEdit recordset start?

Oh wait! Why does "Froggy''s" have two apostrophes? Oh, I see! It goes back
to this:

gigTitle = Replace(Request("gigTitle"),"'","''")
....
rsGigUpdate("gigTitle") = gigTitle


The only reason to escape apostrophes by doubling them is if you are
planning to use the value in a concatenated sql statement. That is not the
case here.

Do I understand correctly that you are opening two recordsets on the same
page? There is no need to do this. Do this instead (I'm guessin at the
datatypes of your fields, particularly the date and time fields. If they are
not Date/Time fields, then get rid of the #'s - see my first reply):

gigDateMerge = Request("year") & "-" & Request("month") & "-" &
Request("day")
gigTimeMerge = Request("Time_hour") & ":" & Request("Time_minute") &
Request("Time_AMPM")
gigTitle = Replace(Request("gigTitle"),"'","''")
gigLocation = Replace(Request("location"),"'","''")
gigComments = Replace(Request("comments"),"'","''")

sql = "UPDATE gigs SET gigTitle='" & gigTitle & "'" & _
", gigDate=#" & gigDateMerge & "#" & _
", gigTime=#" & gigTimeMerge & "#" & _
", gigLocation ='" & gigLocation & "'" & _
", gigComments ='" & gigComments & "'" & _
" WHERE gigID=" & Request("gigID") & ";"
'Response.Write sql
gigConn.Execute sql,,129

'better yet, read about saved parameter queries in the links
'in my first reply

gigPlaceSQL = "SELECT gigTitle FROM places " & _
" ORDER BY placeName ASC;"
set rs=gigConn.Execute(gigPlaceSQL,,1)
dim arData, i
if not rs.eof then arData=rs.getRows
rs.close:set rs=nothing
gigConn.close:set gigConn = nothing
if isarray(arData) then
for i=0 to ubound(arData,2)
Response.write _
"<option value='" & arData(0,i) & "'"
If arData(0,i) = Request("gigTitle") Then
Response.write " selected"
End If
Response.write ">" & arData(0,i) & "</option>"
next
end if


HTH,
Bob Barrows