分隔符，sql，join，AHHH！ [英] delimiters, sql, joins, AHHH!

问题描述

好的，几天前我在这里发布了关于文字中的撇号问题

字段，我尝试了一些建议，现在我看起来很深刮掉整件事的
因为现在我不知道从哪里开始。如果

修复它看起来需要的时间比开始时间长，那么我想我应该让这艘船下沉并游到岸边并重新组合。


这就是我开始的方式。不是最好的方式我很有信心。我是自学成才的，对于那些在大学里这样做的人来说，你已经知道了我将要经历的事情以及所有陷阱和我一直陷入困境。无论如何，

这里是我的烂摊子的总结。


我在编写一行代码之前开始将信息输入Access数据库。 2

表：演出和场所。我想保留地点与演出分开

继续防止db中的冗余信息。这种方式（我认为）这样就更容易了，所以放置就是这样。信息将始终在那里调用

而不是每次重写它，如果有新的东西提供和

机会让用户单独输入信息然后只是重复使用它它是b / b
。我打算欺骗并创建单独的记录集...

RS forgigs然后只为地点创建另一个。好吧，我们现在不是很好！
现在相当混乱！精氨酸。这里遇到的问题......


它始于分隔符问题。文本字段中的撇号

对我的数据调用造成严重破坏。我是邪恶的并使用SELECT *所以它

没有出现在sql字符串中还没有。当我试图将记录集中的记录与循环中生成的内容进行匹配时，问题出现了...就像在html中的select标记中循环

记录以生成所有选项一个Dropbox。

问题是撇号，当文件字段中的第一个撇号打到

时，记录会停止写入。所以我尝试使用替换把它们全部变成& acute;最终造成了一个更大的混乱（并且html保持

解释& acute;进入''所以我可以让它们进行网格化）并且在数据库中如此

更多我把糟糕的事情弄得一团糟......追逐火车......


我确实试图回去并将替换改为'''''而不是& acute;

（难以阅读，不是吗）引用2撇号引用。我没有在之前的帖子中了解到b
。到这个时候这个东西是一个混乱......


我搞砸了Access并尝试使用查找向导链接表格

但是，如你所知，这会产生数字ID，所以我们周围和周围我们

去...... AAHHHH！


那么我开始阅读JOIN'，内部'，外部'，左'，右，s。

它们似乎都不是我需要的（而且我只是在处理

sql）...当我看着这个

项目的时钟用完时，我的头在转动。我正在努力!!!


哦，顺便说一句，这就是网站。前端完成了。这是用户管理员的后端，这是造成所有问题的原因。 http://thehalftonhorns.com


所以这就是我该做什么...


1）。有2个表，演出和地方

2）。能够在记录集中呼叫和更新


如果你想指向我的方向，这里有我手头的书籍清单

你知道...

开始Active Server Pages 3.0（Wrox）

开始ASP数据库（Wrox）

开始Javascript（Wrox）

HTML完整

MSDN Library -Visual Studio 6.0（磁盘）

我真的希望有人可以提供帮助。我正在驾驶我的妻子和孩子坚果

锁定在这个房间里好几天变成怪物爸爸。


谢谢!!!!

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

推荐答案

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

来决定是否要分隔数据，查看

字段的数据类型 - 不是数据。

1.如果它是数字字段，则必须提供数字数据，其中

表示你​​不能通过在它周围加上引号来划分数据。

2.如果它是字符/文本字段，那么你必须通过<提供字符串数据br />
用单引号或双引号分隔数据。如果数据

包含文字引号，则必须通过加倍来转义它们。这意味着

如果你的字符串分隔符使用单引号（撇号），并且

数据包含撇号，那么你必须用撇号取代撇号
两个撇号，如下所示：

更新tbl set textfield =''O''''Malley''

在Access中，你可以使用双引号你的分隔符，所以这将工作

以及：

更新tbl set textfield =" O''Malley"

注意：你不要当你使用

双引号作为分隔符时，不得不逃避O''Malley的撇号。但是，在将此语句分配给变量时，您需要转义双

引号：

sSQL ="更新tbl set textfield ="" O'' Malley"""

所以大多数人会使用单引号并逃避撇号：

sName =" O''Malley"

sSQL ="更新tbl set textfield =''" &安培;替换（sName，"''"，"''''"）& "''"

response.write sSQL


数据库。由于您使用的是Access，因此必须使用

哈希标记（＃）来分隔数据。此外，您必须以美国格式提供数据

（m / d / yyyy）或ISO（yyyy-mm-dd），后者更多

推荐。


4.最后，如果你使用LIKE，你需要注意你必须使用％

和_作为通配符，而不是*和？这是真的无论你使用什么数据库




然后，当你认为你做对了它仍然无效时，

response.write它来查看连接的结果。如果你已经正确地完成了它，那么你将有一个声明，你可以从

浏览器窗口复制并粘贴到Access Query Builder的SQL视图中运行没有
修改（除非你需要用Jet

通配符替换通配符）。


这里有更多：

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

http://groups.google.com/groups?hl=e...TNGP11.phx.gbl

< a rel =nofollowhref =http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gbl&oe=UTF-8&output=gplaintarget =_ blank> http ：？//www.google.com/groups塞尔姆= EE .. 。& output = gplain
http://www.google.com/groups?hl=en&l...TNGP12。 phx.gbl


Bob Barrows


-

Microsoft MVP - ASP / ASP .NET

请回复新闻组。我的From

标题中列出的电子邮件帐户是我的垃圾邮件陷阱，因此我不经常检查它。通过发布到新闻组，您将获得更快的回复。

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=e...tngp13.phx.gbl

http://groups.google.com/groups?hl=e...TNGP11.phx.gbl

http://www.google.com/groups?selm=eE...&output=gplain

http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.

我这样做但我仍然无法得到记录集匹配正在编辑的
。我只是没有看到它？


<％

设置rsGigPlace = Server.CreateObject（" ADODB.Recordset"）

gigPlaceSQL =" SELECT * FROM ORDER BY placeName ASC;"

rsGigPlace.CursorType = 3

rsGigPlace.Open gigPlaceSQL，gigsCon


Response.write _

"< select name =''location''>"

while rsGigPlace.EOF

Response.write _

"< option value =''" &安培; rsGigPlace（placeName）& "''"

如果替换（rsGigPlace（" placeName"）），"''"，'''''''）=

替换（rsGigEdit（" gigPlace"），"''"，''''''"）然后Response.write" selected"结束

如果

Response.write _

">" &安培; rsGigPlace（placeName）& "< / option>"

rsGigPlace.MoveNext

Wend

rsGigPlace.Close

设置rsGigPlace =没有

Response.write"< / select>"

％>


（rsGigEdit来自另一个记录集仍然开放）


正在制作的HTML ....

< td>

< select name =' 'location''>< option value =''Benefit Concert''> Benefit

Concert< / option>< option value =''Fat Moe'''> Fat Moe''s< / option>< option

value =''Froggy'''> Froggy'< / option>< option value =''George Eastman

House''> George Eastman House< / option>< option

value =''Georgio'''> Georgio'< / option>< ;选项值=''TBA''> TBA< /选项><选项

value =''全景''>全景< /选项>< / select>

< / td>

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>

2.如果它是一个字符/文本字段，那么您必须通过单引号或双引号分隔数据来提供字符串数据。如果数据
包含文字引号，则必须通过加倍来转义它们。这意味着如果你使用单引号（撇号）作为你的字符串分隔符，
并且数据包含一个撇号，那么你必须用撇号替换撇号两个撇号，如下所示：
更新tbl set textfield =''''''''Malley''
在Access中，你可以使用双引号作为分隔符，所以这也是
的工作原理：
更新tbl set textfield =" O''Malley"
注意：当你使用双引号作为分隔符时，你不必逃避O''Malley的撇号。但是，在将此语句赋给变量时，您需要转义
双引号：
sSQL =" Update tbl set textfield ="" O''Malley""" sName =" O''Malley"
sSQL =" Update tbl set textfield =''" &安培;替换（sName，"''"，"''''"）& "''"
response.write sSQL

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

> 2.如果它是一个字符/文本字段，那么你必须通过

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

用单引号或双引号分隔数据来提供字符串数据

。如果数据
包含文字引号，则必须通过加倍来转义它们。这个
意味着如果你为字符串
分隔符使用单引号（撇号），并且数据包含撇号，那么你必须用两个撇号替换撇号
，如下所示：
更新tbl set textfield =''''''''Malley''
在Access中，你可以使用双引号作为分隔符，所以这个
也可以使用：
更新tbl set textfield =" O''Malley"

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"

我认为建议您使用单引号，这样

如果/当你将它从Access移动到SQL时，你不必进入并且

重写这么多代码。

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.

这篇关于分隔符，sql，join，AHHH！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！