...

View Full Version : data loss



mivec
08-21-2005, 08:14 PM
hi all,
i am baffled with this problem...i dun see anything wrong and i hv tried printing it out but there doesn't seem to be any thing there. here is the code:

here is the value taking from DB to the textarea:


<tr>
<td width=30% class="row2">Event Sysnopsis :</td>
<td width=70% class=news><textarea name="txtaSysnopsis" cols="78" rows="4"><%=DBRecordset("Synopsis")%></textarea></td>
</tr>


here is the place where i am retrieving from the previous page where the textarea is:


<%
Dim SQLTxt

SQLTxt = "UPDATE tblEvents SET Title = '" &request.form("txtEvTitle")& "',"&_
"Venue = '" &request.form("txtEvVenue")& "',"&_
"Speaker = '" &request.form("txtEvSpeaker")& "',"&_
"evDate = '" &request.form("txtEvDate")& "',"&_
"Synopsis = '" &request.form("txtaSynopsis")& "',"&_
"SignUp = '" &request.form("txtEvSignUp")& "',"&_
"WebLink = '" &request.form("txtEvWeblink")& "',"&_
"borderColor = '" & Request.form("txtCol") & "' " &_
"WHERE eventID LIKE '" & Request.form("id") & "'"


OpenMyDB dbfile, SQLTxt

%>


when i upload it i get this error:


Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression ''FTMS-De Montfort University Campus MalaysiaLevel 5, University Hall24-30 Jalan Hang Kasturi50000 Kuala Lumpur(Next to Central Market, same row as Guardian Pharmacy and Watson's)',Speaker = 'Dr Peter Shepard',evDate = '2004-04-23',Synopsis = '',SignUp = ''.

pls help....i dun see anything wrong with it.... :(

mivec
08-21-2005, 09:14 PM
hi all,
i am trying to make the selected date(means year, month and day) from the either the textbox or welect menu and i want it to be combined into one whole string....it doesn't seem to work. here is my code:



<tr>
<td width="30%" class="row1">Event Date : </td>
<td width="60%" class="row2">
<input type="text" name="txtYear" size="10">
Year
<select name="selMonth">
<option>Select A Month</option>
<option>January</option>
<option>February</option>
<option>March</option>
<option>April</option>
<option>May</option>
<option>June</option>
<option>July</option>
<option>August</option>
<option>September</option>
<option>October</option>
<option>November</option>
<option>December</option>
</select> &nbsp;Month
<select name="selDay">
<option>Day</option>
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>
<option>6</option>
<option>7</option>
<option>8</option>
<option>9</option>
<option>10</option>
<option>11</option>
<option>12</option>
<option>13</option>
<option>14</option>
<option>15</option>
<option>16</option>
<option>17</option>
<option>18</option>
<option>19</option>
<option>20</option>
<option>21</option>
<option>22</option>
<option>23</option>
<option>24</option>
<option>25</option>
<option>26</option>
<option>27</option>
<option>28</option>
<option>29</option>
<option>30</option>
<option>31</option>
</select>&nbsp;Day
<strong><font color="#FF0000"> **</font></strong></td>
</tr>




<% Dim evDate

evDate = request.form("txtYear")&"-"&request.form("selMonth")&"-"&request.form("selDay")
%>


but when i use the variable name evDate..i get the error message that i handled which says "sorry, u didn't enter the event date"

so....is my syntax for combining them correct? :(

Bullschmidt
08-22-2005, 02:42 AM
Well with an Access database you can actually use the DateSerial(year, month, dayof month) function right in a SQL statement.

Example setting a date range based on posted fields:

strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & Request.Form("MinDate") & "#) AND (TheDateField <= #" & Request.Form("MaxDate") & "#)"

Example setting a date range to be the current and previous months:

strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & DateSerial(Year(Date()), Month(Date()) + 1, 0) & "#) AND (TheDateField <= #" & DateSerial(Year(Date()), Month(Date()) - 1, 1) & "#)"

Cyberlord
08-22-2005, 12:50 PM
Hello,

you must transform special characters into html-code.

<%
Function chk_html(html)
html = replace(html,"<","&lt;")
html = replace(html,">","&gt;")
html = replace(html,"\","&#92;")
html = replace(html,"""","&quot;")
chk_html = replace(html,"'","&#39;")
End Function

SQLTxt = "UPDATE tblEvents SET Title = '" & chk_html(request.form("txtEvTitle")) & ...
%>

glenngv
08-22-2005, 01:14 PM
Hello,

you must transform special characters into html-code.

<%
Function chk_html(html)
html = replace(html,"<","&lt;")
html = replace(html,">","&gt;")
html = replace(html,"\","\")
html = replace(html,"""","&quot;")
chk_html = replace(html,"'","'")
End Function

SQLTxt = "UPDATE tblEvents SET Title = '" & chk_html(request.form("txtEvTitle")) & ...
%>
There's already a built-in method for that.

Server.HTMLEncode(request.form("txtEvTitle"))

But that's not the real solution.
The solution is to escape the single quotes in the input by doubling them.

SQLTxt = "UPDATE tblEvents SET Title = '" & replace(request.form("txtEvTitle"), "'", "''") & ...

See this sticky thread Single Quotes give me a syntax error! (http://www.codingforums.com/showthread.php?t=9843) for more info.

Cyberlord
08-22-2005, 02:03 PM
Hello,

Server.HTMLEncode is not sufficiently.

My function works very well in my applications.
The forum just transformed it wrong.

I hope it works now:



Function chk_html(html)
html = replace(html,"<","&lt;")
html = replace(html,">","&gt;")
html = replace(html,"\","& #92;")
html = replace(html,"""","&quot;")
chk_html = replace(html,"'","& #39;")
End Function


Just remove the space after the amp (&).

ghell
08-22-2005, 03:26 PM
glen as ever is correct, you only need to replace ' with '' to get it to work but if you do intend using that replace method i would suggest this:

Replace(Server.HTMLEncode(str), "'", "''")

there should be no need to replace / \ or " anyway...i would infact insert it with Replace(str, "'", "''")

and then read it out of the database with Server.HTMLEncode

Cyberlord
08-22-2005, 03:47 PM
I think you need to replace "\".

Try this to store in your database:

update tablename set title = 'test \ test' where id = 1

Maybe its not needed for access.

glenngv
08-23-2005, 05:46 AM
I think you need to replace "\".

Try this to store in your database:

update tablename set title = 'test \ test' where id = 1

That depends on the database server used. If \ means something to the db server, then I think you escape it with \\.

But for me, I would save the data in the database with <, >, ", ', \ exactly as it is (of course, you should escape some symbols to avoid syntax error, loss of data and sql injection attacks (http://www.unixwiz.net/techtips/sql-injection.html)). Then when the data is displayed in the web page, that's the time you HTML-encode it.

Saving:

Function chk_html(txt)
chk_html = replace(txt, "'", "''")
'...and others measures such as preventing sql injection attacks

'or (depends on db server type)
'chk_html = replace(txt, "\", "\\")
End Function

SQLTxt = "UPDATE tblEvents SET Title = '" & chk_html(request.form("txtEvTitle")) & "'"

Displaying:

<%=Server.HTMLEncode(rs.Fields("Title").Value)%>

neocool00
08-23-2005, 02:51 PM
I think you need to replace "\".

Try this to store in your database:

update tablename set title = 'test \ test' where id = 1

Maybe its not needed for access.
I would think the \ would only be harmful if it was not encapsulated by apostrophes.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum