ASP Trim() Function Error

[Dominic]

Here is the general problem:

Information is taken from a form (consisting of text boxes, drop down
lists et cetera) and submitted to an Access database. Before the data
is inserted into the database, trailing and leading spaces are removed
using a call to the Trim()function.

This works the first time data is inserted into the database. However,
if the data is modified (via web forms) and then resubmitted (after
calling the Trim() function) an extra leading space (occasionally two)
is randomly inserted.

For example,

I enter info for the following variables:

NAME: ' Bill'
AGE: ' 20 '

The Access database looks like this:

NAME: 'Bill'
AGE: '20'

I then edit the record in the database, changing the name to 'Peter'
and age to '30':

NAME: ' Peter '
AGE: ' 30 '

The Access database then looks like this:

NAME: ' Peter'
Age: ' 30'

OR

sometimes like this:

NAME: ' Peter'
AGE: '30'

It appears to be total random, anyone have any suggestions?

 

[Ray at]

Need to see code that handles the updating of your records.

Ray at work

 

[Dominic Marsat]

Thanks for the reply.

I'll provide code for, say, a text box to keep things simple (the
project I'm working on consists of hundreds of variables spread over
many pages).

Data is entered using a standard form and stored in session variables
(I'm not too pleased about having to use session variables but nothing
else will do the job), so...

Session("PA_strPostCode") = Request.Form("PA_txtPostCode")

spaces are then removed using the Trim() function:

Session("PA_strPostCode") = Trim(Session("PA_strPostCode"))

a connection to the database is opened using:

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" &

dbPath

where the variable dbPath contains the location of the database.

data is inserted into the database using:

strSQL= ""
strSQL = strSQL & "INSERT INTO ProjectAddress "
strSQL = strSQL & "(PA_strPostCode) "
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & Session("PA_strPostCode") & "'"
strSQL = strSQL & ");"
Connection.Execute strSQL, lngRecsAffected, adCmdText Or >

adExecuteNoRecords

The editing code is identical apart from the data is retrieved using a
recordset, modified via a web form and then inserted back into the
database using an identical expression to the INSERT statement except
with UPDATE.

Note that before data in reinserted back into the database calls to the
request.form and trim functions are made.

 

[Ray at]

The editing code is identical apart from the data is retrieved using a
recordset, modified via a web form and then inserted back into the
database using an identical expression to the INSERT statement except
with UPDATE.

You said that when the data is INSERTed, it is fine. And it's only not fine
when you UPDATE it. So, please post the UPDATE code, not the INSERT code!

Also, please see www.aspfaq.com/5007.

Ray at home

 

[Dominic Marsat]

Here is the UPDATE code. I've sent the whole function instead of editing
bits out. I've noticed that I haven't set the strSQL string to " " as I have
done in the INSERT function, however this shouldn't matter because the first
line of the function effectively does this. Thanks, Dom

' Update the project address database records
Function UPDATE_PA()
strSQL= "UPDATE ProjectAddress SET PA_strOrderNumber= ' " &
Session("PA_strOrderNumber") & " ', "
strSQL=strSQL & "PA_strQuotationRef= ' " & Session("PA_strQuotationRef") &
" ', "
strSQL=strSQL & "PA_strProjectStatus= ' " &
Session("PA_strProjectStatus") & " ', "
strSQL=strSQL & "PA_strAddress1= '" & Session("PA_strAddress1") & "', "
strSQL=strSQL & "PA_strAddress2='" & Session("PA_strAddress2") & "', "
strSQL=strSQL & "PA_strAddress3= '" & Session("PA_strAddress3") & "', "
strSQL=strSQL & "PA_strAddress4= '" & Session("PA_strAddress4") & "', "
strSQL=strSQL & "PA_strCity= '" & Session("PA_strCity") & "', "
strSQL=strSQL & "PA_strCounty= '" & Session("PA_strCounty") & "', "
strSQL=strSQL & "PA_strPostCode= '" & Session("PA_strPostCode") & "' "
strSQL=strSQL & "WHERE ID=" & Session("ID")
Connection.Execute strSQL, lngRecsAffected, adCmdText Or adExecuteNoRecords
End Function

 

[Ray at]

You have leading spaces.

strSQL= "UPDATE ProjectAddress SET PA_strOrderNumber= ' " &
Session("PA_strOrderNumber") & " ', "
--------------------------------------------------------^

strSQL=strSQL & "PA_strQuotationRef= ' " & Session("PA_strQuotationRef") &
" ', "
---------------------------------------^

Ray at home

 

[Dominic Marsat]

Thanks.

You have leading spaces.

strSQL= "UPDATE ProjectAddress SET PA_strOrderNumber= ' " &
Session("PA_strOrderNumber") & " ', "
--------------------------------------------------------^

strSQL=strSQL & "PA_strQuotationRef= ' " & Session("PA_strQuotationRef") &
" ', "
---------------------------------------^

Ray at home





Session("PA_strQuotationRef")