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" <dj******@hotma il.com> wrote in message
news:e2******** *************** ***@posting.goo gle.com...

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?

[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...

on submission the text box variable is retrieved using:

Session("PA_str PostCode") = Request.Form("P A_txtPostCode")
spaces are then removed using the Trim() function:
Session("PA_str PostCode") = Trim(Session("P A_strPostCode") )
a connection to the database is opened using:
Set Connection = Server.CreateOb ject("ADODB.Con nection")
Connection.Open "PROVIDER=MICRO SOFT.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_strPostCod e) "
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & Session("PA_str PostCode") & "'"
strSQL = strSQL & ");"
Connection.Exec ute strSQL, lngRecsAffected , adCmdText Or >

adExecuteNoReco rds

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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ray at]

"Dominic Marsat" <dj******@hotma il.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[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_strOrderNumb er= ' " &
Session("PA_str OrderNumber") & " ', "
strSQL=strSQL & "PA_strQuotatio nRef= ' " & Session("PA_str QuotationRef") &
" ', "
strSQL=strSQL & "PA_strProjectS tatus= ' " &
Session("PA_str ProjectStatus") & " ', "
strSQL=strSQL & "PA_strAddress1 = '" & Session("PA_str Address1") & "', "
strSQL=strSQL & "PA_strAddress2 ='" & Session("PA_str Address2") & "', "
strSQL=strSQL & "PA_strAddress3 = '" & Session("PA_str Address3") & "', "
strSQL=strSQL & "PA_strAddress4 = '" & Session("PA_str Address4") & "', "
strSQL=strSQL & "PA_strCity = '" & Session("PA_str City") & "', "
strSQL=strSQL & "PA_strCoun ty= '" & Session("PA_str County") & "', "
strSQL=strSQL & "PA_strPostCode = '" & Session("PA_str PostCode") & "' "
strSQL=strSQL & "WHERE ID=" & Session("ID")
Connection.Exec ute strSQL, lngRecsAffected , adCmdText Or adExecuteNoReco rds
End Function
"Ray at <%=sLocation% > [MVP]" <myfirstname at lane34 dot com> wrote in
message news:eN******** *****@TK2MSFTNG P10.phx.gbl...

"Dominic Marsat" <dj******@hotma il.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ray at]

You have leading spaces.

strSQL= "UPDATE ProjectAddress SET PA_strOrderNumb er= ' " &
Session("PA_str OrderNumber") & " ', "
--------------------------------------------------------^

strSQL=strSQL & "PA_strQuotatio nRef= ' " & Session("PA_str QuotationRef") &
" ', "
---------------------------------------^

Ray at home

"Dominic Marsat" <djmarsatAThotm ail.com> wrote in message
news:um******** ******@TK2MSFTN GP11.phx.gbl...

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_strOrderNumb er= ' " &
Session("PA_str OrderNumber") & " ', "
strSQL=strSQL & "PA_strQuotatio nRef= ' " & Session("PA_str QuotationRef") & " ', "
strSQL=strSQL & "PA_strProjectS tatus= ' " &
Session("PA_str ProjectStatus") & " ', "
strSQL=strSQL & "PA_strAddress1 = '" & Session("PA_str Address1") & "', "
strSQL=strSQL & "PA_strAddress2 ='" & Session("PA_str Address2") & "', "
strSQL=strSQL & "PA_strAddress3 = '" & Session("PA_str Address3") & "', "
strSQL=strSQL & "PA_strAddress4 = '" & Session("PA_str Address4") & "', "
strSQL=strSQL & "PA_strCity = '" & Session("PA_str City") & "', "
strSQL=strSQL & "PA_strCoun ty= '" & Session("PA_str County") & "', "
strSQL=strSQL & "PA_strPostCode = '" & Session("PA_str PostCode") & "' "
strSQL=strSQL & "WHERE ID=" & Session("ID")
Connection.Exec ute strSQL, lngRecsAffected , adCmdText Or adExecuteNoReco rds End Function
"Ray at <%=sLocation% > [MVP]" <myfirstname at lane34 dot com> wrote in
message news:eN******** *****@TK2MSFTNG P10.phx.gbl...

"Dominic Marsat" <dj******@hotma il.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dominic Marsat]

Thanks.

"Ray at <%=sLocation% > [MVP]" <myfirstname at lane34 dot com> wrote in
message news:uW******** ******@TK2MSFTN GP11.phx.gbl...

You have leading spaces.

strSQL= "UPDATE ProjectAddress SET PA_strOrderNumb er= ' " &
Session("PA_str OrderNumber") & " ', "
--------------------------------------------------------^

strSQL=strSQL & "PA_strQuotatio nRef= ' " & Session("PA_str QuotationRef") & " ', "
---------------------------------------^

Ray at home

"Dominic Marsat" <djmarsatAThotm ail.com> wrote in message
news:um******** ******@TK2MSFTN GP11.phx.gbl...

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_strOrderNumb er= ' " &
Session("PA_str OrderNumber") & " ', "
strSQL=strSQL & "PA_strQuotatio nRef= ' " & Session("PA_str QuotationRef") &

" ', "
strSQL=strSQL & "PA_strProjectS tatus= ' " &
Session("PA_str ProjectStatus") & " ', "
strSQL=strSQL & "PA_strAddress1 = '" & Session("PA_str Address1") & "', "
strSQL=strSQL & "PA_strAddress2 ='" & Session("PA_str Address2") & "', "
strSQL=strSQL & "PA_strAddress3 = '" & Session("PA_str Address3") & "', "
strSQL=strSQL & "PA_strAddress4 = '" & Session("PA_str Address4") & "', "
strSQL=strSQL & "PA_strCity = '" & Session("PA_str City") & "', "
strSQL=strSQL & "PA_strCoun ty= '" & Session("PA_str County") & "', "
strSQL=strSQL & "PA_strPostCode = '" & Session("PA_str PostCode") & "' "
strSQL=strSQL & "WHERE ID=" & Session("ID")
Connection.Exec ute strSQL, lngRecsAffected , adCmdText Or

adExecuteNoReco rds

End Function
"Ray at <%=sLocation% > [MVP]" <myfirstname at lane34 dot com> wrote in
message news:eN******** *****@TK2MSFTNG P10.phx.gbl...

"Dominic Marsat" <dj******@hotma il.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

> 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

>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!