leading spaces

[Jerry]

I have a connection to SQL Server and have setup a customer profile page
where the customer can change their information. All the fields when the SQL
update statement runs adds leading spaces to the database. I have used the
Trim() function and it works in the textboxes but stores the extra space.
Also, I have an Address2 line which in some cases are null and receive a
error message. Your help would be greatly appreciated.

Sample Code:
Function fixQuotes(theString)
fixQuotes = Replace(theString, "'","''")
End Function
strSql = "Update Tenant Set " &_
"Tenant = ' " & fixQuotes(Trim(Request.form("Tenantname"))) & "', " &_
"Address1 = ' " & fixQuotes(Trim(Request.form("Address1"))) & "', " &_
"Address2 = ' " & fixQuotes(Trim(Request.form("Address2"))) & "', " &_
"City = ' " & fixQuotes(Trim(Request.form("City"))) & "', " &_
"State = ' " & fixQuotes(Trim(Request.form("State"))) & "', " &_
"Zip = ' " & fixQuotes(Trim(Request.form("Zip"))) & "', " &_
"PhoneNumber = ' " & fixQuotes(Trim(Request.form("PhoneNumber"))) & "', " &_
"Email = ' " & fixQuotes(Trim(Request.form("email"))) & "', " &_
"Password = ' " & Trim(fixQuotes( Pass )) & "'" &_
"WHERE tenant.Customerid = '" & Cstr(Request.form("customer")) & "' "

set conn=Server.createObject("ADODB.connection")
conn.Open "DSN=Falco; uid=sa; pwd=6stang7"
conn.Execute strSql
conn.close
set conn = nothing

%>

 

[Kevin Spencer]

Hi Jerry,

You're trimming alright, but adding a space back in. Example:

"Tenant = ' " & fixQuotes(Trim(Request.form("Tenantname"))) & "', "

Note the space after the single quote, right after "Tenant ="

As for nulls, you shouldn't be having any problems with that. An empty text
box has a value of "", which is an empty string, not null. If you're having
a problem there, it lies in something I don't see. You might want to check
your rules in the database.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
I'd rather be a hammer than a nail.

 

[Jerry]

Kevin
Thanks, I really appreciate your help.

Hi Jerry,

You're trimming alright, but adding a space back in. Example:

"Tenant = ' " & fixQuotes(Trim(Request.form("Tenantname"))) & "', "

Note the space after the single quote, right after "Tenant ="

As for nulls, you shouldn't be having any problems with that. An empty text
box has a value of "", which is an empty string, not null. If you're having
a problem there, it lies in something I don't see. You might want to check
your rules in the database.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
I'd rather be a hammer than a nail.

 

[Jerry]

Kevin, I checked the database and everything looks fine. This is the error
message I'm getting from the empty address field.

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'htmlEncode'
/profile.asp, line 55

 

[p c]

What is line 55?

For the field that the SQL statement works when it is not empty, check
out the field properties in the DB table. Make sure that the field is
set to Not required and Allow zero length string (I am using Access/Jet
terms, SQL server may be different).

For debugging purpose, response write the sql statement to the the
screen, review it to make sure it's correct. Add the following statement
before the connection object line:

response.write "<br>sql: " & strSql

To verify that the SQL is correct you can also copy it and run at the DB.

...PC

 

[Jerry]

Thanks for your response. When I use the server.HtmlEncode I get the error
but when I don't use it I dont get the error. The only problem is that the
database doesn't return all the data without the server.HtmlEncode.
Sample code:
<b> &nbsp <input name="Address2" size="20" type=text
value=<%=ors("address2")%>></b>

vs:
<b>&nbsp;&nbsp <input name="Address2" size="20" type=text
value=<%=server.HtmlEncode(ors("address2")%>></b>

 

[Stefan B Rusynko]

A workaround is to make sure you never have a null to contend with by check for it first

<%
If IsNull(ors("address2")) Or ors("address2")="" Then
straddress2= ""
Else
straddress2= server.HtmlEncode(ors("address2"))
End If
%>
<input name="Address2" size="20" type=text value=<%=straddress2%>>


_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________


| Thanks for your response. When I use the server.HtmlEncode I get the error
| but when I don't use it I dont get the error. The only problem is that the
| database doesn't return all the data without the server.HtmlEncode.
| Sample code:
| <b> &nbsp <input name="Address2" size="20" type=text
| value=<%=ors("address2")%>></b>
|
| vs:
| <b>&nbsp;&nbsp <input name="Address2" size="20" type=text
| value=<%=server.HtmlEncode(ors("address2")%>></b>
|
|
| "p c" wrote:
|
| > What is line 55?
| >
| > For the field that the SQL statement works when it is not empty, check
| > out the field properties in the DB table. Make sure that the field is
| > set to Not required and Allow zero length string (I am using Access/Jet
| > terms, SQL server may be different).
| >
| > For debugging purpose, response write the sql statement to the the
| > screen, review it to make sure it's correct. Add the following statement
| > before the connection object line:
| >
| > response.write "<br>sql: " & strSql
| >
| > To verify that the SQL is correct you can also copy it and run at the DB.
| >
| > ...PC
| >
| > Jerry wrote:
| > > Kevin, I checked the database and everything looks fine. This is the error
| > > message I'm getting from the empty address field.
| > >
| > > Microsoft VBScript runtime error '800a000d'
| > > Type mismatch: 'htmlEncode'
| > > /profile.asp, line 55
| > >
| > >
| > > "Kevin Spencer" wrote:
| > >
| > >
| > >>Hi Jerry,
| > >>
| > >>You're trimming alright, but adding a space back in. Example:
| > >>
| > >>"Tenant = ' " & fixQuotes(Trim(Request.form("Tenantname"))) & "', "
| > >>
| > >>Note the space after the single quote, right after "Tenant ="
| > >>
| > >>As for nulls, you shouldn't be having any problems with that. An empty text
| > >>box has a value of "", which is an empty string, not null. If you're having
| > >>a problem there, it lies in something I don't see. You might want to check
| > >>your rules in the database.
| > >>
| > >>--
| > >>HTH,
| > >>
| > >>Kevin Spencer
| > >>Microsoft MVP
| > >>..Net Developer
| > >>I'd rather be a hammer than a nail.
| > >>
| > >>| > >>
| > >>>I have a connection to SQL Server and have setup a customer profile page
| > >>>where the customer can change their information. All the fields when the
| > >>>SQL
| > >>>update statement runs adds leading spaces to the database. I have used the
| > >>>Trim() function and it works in the textboxes but stores the extra space.
| > >>>Also, I have an Address2 line which in some cases are null and receive a
| > >>>error message. Your help would be greatly appreciated.
| > >>>
| > >>>Sample Code:
| > >>>Function fixQuotes(theString)
| > >>>fixQuotes = Replace(theString, "'","''")
| > >>>End Function
| > >>>strSql = "Update Tenant Set " &_
| > >>>"Tenant = ' " & fixQuotes(Trim(Request.form("Tenantname"))) & "', " &_
| > >>>"Address1 = ' " & fixQuotes(Trim(Request.form("Address1"))) & "', " &_
| > >>>"Address2 = ' " & fixQuotes(Trim(Request.form("Address2"))) & "', " &_
| > >>>"City = ' " & fixQuotes(Trim(Request.form("City"))) & "', " &_
| > >>>"State = ' " & fixQuotes(Trim(Request.form("State"))) & "', " &_
| > >>>"Zip = ' " & fixQuotes(Trim(Request.form("Zip"))) & "', " &_
| > >>>"PhoneNumber = ' " & fixQuotes(Trim(Request.form("PhoneNumber"))) & "', "
| > >>>&_
| > >>>"Email = ' " & fixQuotes(Trim(Request.form("email"))) & "', " &_
| > >>>"Password = ' " & Trim(fixQuotes( Pass )) & "'" &_
| > >>>"WHERE tenant.Customerid = '" & Cstr(Request.form("customer")) & "' "
| > >>>
| > >>>set conn=Server.createObject("ADODB.connection")
| > >>>conn.Open "DSN=Falco; uid=sa; pwd=6stang7"
| > >>>conn.Execute strSql
| > >>>conn.close
| > >>>set conn = nothing
| > >>>
| > >>>%>
| > >>>
| > >>
| > >>
| >

 

[Jerry]

Thank you for your help

A workaround is to make sure you never have a null to contend with by check for it first

<%
If IsNull(ors("address2")) Or ors("address2")="" Then
straddress2= ""
Else
straddress2= server.HtmlEncode(ors("address2"))
End If
%>
<input name="Address2" size="20" type=text value=<%=straddress2%>>


_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________


| Thanks for your response. When I use the server.HtmlEncode I get the error
| but when I don't use it I dont get the error. The only problem is that the
| database doesn't return all the data without the server.HtmlEncode.
| Sample code:
| <b> <input name="Address2" size="20" type=text
| value=<%=ors("address2")%>></b>
|
| vs:
| <b> <input name="Address2" size="20" type=text
| value=<%=server.HtmlEncode(ors("address2")%>></b>
|
|
| "p c" wrote:
|
| > What is line 55?
| >
| > For the field that the SQL statement works when it is not empty, check
| > out the field properties in the DB table. Make sure that the field is
| > set to Not required and Allow zero length string (I am using Access/Jet
| > terms, SQL server may be different).
| >
| > For debugging purpose, response write the sql statement to the the
| > screen, review it to make sure it's correct. Add the following statement
| > before the connection object line:
| >
| > response.write "<br>sql: " & strSql
| >
| > To verify that the SQL is correct you can also copy it and run at the DB.
| >
| > ...PC
| >
| > Jerry wrote:
| > > Kevin, I checked the database and everything looks fine. This is the error
| > > message I'm getting from the empty address field.
| > >
| > > Microsoft VBScript runtime error '800a000d'
| > > Type mismatch: 'htmlEncode'
| > > /profile.asp, line 55
| > >
| > >
| > > "Kevin Spencer" wrote:
| > >
| > >
| > >>Hi Jerry,
| > >>
| > >>You're trimming alright, but adding a space back in. Example:
| > >>
| > >>"Tenant = ' " & fixQuotes(Trim(Request.form("Tenantname"))) & "', "
| > >>
| > >>Note the space after the single quote, right after "Tenant ="
| > >>
| > >>As for nulls, you shouldn't be having any problems with that. An empty text
| > >>box has a value of "", which is an empty string, not null. If you're having
| > >>a problem there, it lies in something I don't see. You might want to check
| > >>your rules in the database.
| > >>
| > >>--
| > >>HTH,
| > >>
| > >>Kevin Spencer
| > >>Microsoft MVP
| > >>..Net Developer
| > >>I'd rather be a hammer than a nail.
| > >>
| > >>| > >>
| > >>>I have a connection to SQL Server and have setup a customer profile page
| > >>>where the customer can change their information. All the fields when the
| > >>>SQL
| > >>>update statement runs adds leading spaces to the database. I have used the
| > >>>Trim() function and it works in the textboxes but stores the extra space.
| > >>>Also, I have an Address2 line which in some cases are null and receive a
| > >>>error message. Your help would be greatly appreciated.
| > >>>
| > >>>Sample Code:
| > >>>Function fixQuotes(theString)
| > >>>fixQuotes = Replace(theString, "'","''")
| > >>>End Function
| > >>>strSql = "Update Tenant Set " &_
| > >>>"Tenant = ' " & fixQuotes(Trim(Request.form("Tenantname"))) & "', " &_
| > >>>"Address1 = ' " & fixQuotes(Trim(Request.form("Address1"))) & "', " &_
| > >>>"Address2 = ' " & fixQuotes(Trim(Request.form("Address2"))) & "', " &_
| > >>>"City = ' " & fixQuotes(Trim(Request.form("City"))) & "', " &_
| > >>>"State = ' " & fixQuotes(Trim(Request.form("State"))) & "', " &_
| > >>>"Zip = ' " & fixQuotes(Trim(Request.form("Zip"))) & "', " &_
| > >>>"PhoneNumber = ' " & fixQuotes(Trim(Request.form("PhoneNumber"))) & "', "
| > >>>&_
| > >>>"Email = ' " & fixQuotes(Trim(Request.form("email"))) & "', " &_
| > >>>"Password = ' " & Trim(fixQuotes( Pass )) & "'" &_
| > >>>"WHERE tenant.Customerid = '" & Cstr(Request.form("customer")) & "' "
| > >>>
| > >>>set conn=Server.createObject("ADODB.connection")
| > >>>conn.Open "DSN=Falco; uid=sa; pwd=6stang7"
| > >>>conn.Execute strSql
| > >>>conn.close
| > >>>set conn = nothing
| > >>>
| > >>>%>
| > >>>
| > >>
| > >>
| >

 

[p c]

The problem is that you don't have the quotes for the VALUE atribute.
Try it like this

<b> &nbsp <input name="Address2" size="20" type=text
value="<%=ors("address2")%>"></b>

When you assign values to input fields you need to enclose it betwen
quotes. If you dont, the assigned value will be whatever is after the =
to the first space.

value="This this my value"
will result in "This is my value"
value=This this my value
will result in "This"
value= This this my value
will result in ""

...PC

 

[Kevin Spencer]

Here is an even simpler technique, which works, because everything in
VBScript is a variant:

<input name="Address2" size="20" type=text value=<%=straddress2 & ""%>>

By concatenating a blank string to the value, if it is null (Nothing) it
will become an empty string. If it is not null, it doesn't add anything to
it (it adds an empty string).

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
I'd rather be a hammer than a nail.

A workaround is to make sure you never have a null to contend with by check
for it first

<%
If IsNull(ors("address2")) Or ors("address2")="" Then
straddress2= ""
Else
straddress2= server.HtmlEncode(ors("address2"))
End If
%>
<input name="Address2" size="20" type=text value=<%=straddress2%>>


_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________


| Thanks for your response. When I use the server.HtmlEncode I get the
error
| but when I don't use it I dont get the error. The only problem is that
the
| database doesn't return all the data without the server.HtmlEncode.
| Sample code:
| <b> &nbsp <input name="Address2" size="20" type=text
| value=<%=ors("address2")%>></b>
|
| vs:
| <b>&nbsp;&nbsp <input name="Address2" size="20" type=text
| value=<%=server.HtmlEncode(ors("address2")%>></b>
|
|
| "p c" wrote:
|
| > What is line 55?
| >
| > For the field that the SQL statement works when it is not empty, check
| > out the field properties in the DB table. Make sure that the field is
| > set to Not required and Allow zero length string (I am using
Access/Jet
| > terms, SQL server may be different).
| >
| > For debugging purpose, response write the sql statement to the the
| > screen, review it to make sure it's correct. Add the following
statement
| > before the connection object line:
| >
| > response.write "<br>sql: " & strSql
| >
| > To verify that the SQL is correct you can also copy it and run at the
DB.
| >
| > ...PC
| >
| > Jerry wrote:
| > > Kevin, I checked the database and everything looks fine. This is the
error
| > > message I'm getting from the empty address field.
| > >
| > > Microsoft VBScript runtime error '800a000d'
| > > Type mismatch: 'htmlEncode'
| > > /profile.asp, line 55
| > >
| > >
| > > "Kevin Spencer" wrote:
| > >
| > >
| > >>Hi Jerry,
| > >>
| > >>You're trimming alright, but adding a space back in. Example:
| > >>
| > >>"Tenant = ' " & fixQuotes(Trim(Request.form("Tenantname"))) & "', "
| > >>
| > >>Note the space after the single quote, right after "Tenant ="
| > >>
| > >>As for nulls, you shouldn't be having any problems with that. An
empty text
| > >>box has a value of "", which is an empty string, not null. If you're
having
| > >>a problem there, it lies in something I don't see. You might want to
check
| > >>your rules in the database.
| > >>
| > >>--
| > >>HTH,
| > >>
| > >>Kevin Spencer
| > >>Microsoft MVP
| > >>..Net Developer
| > >>I'd rather be a hammer than a nail.
| > >>
| > >>| > >>
| > >>>I have a connection to SQL Server and have setup a customer profile
page
| > >>>where the customer can change their information. All the fields
when the
| > >>>SQL
| > >>>update statement runs adds leading spaces to the database. I have
used the
| > >>>Trim() function and it works in the textboxes but stores the extra
space.
| > >>>Also, I have an Address2 line which in some cases are null and
receive a
| > >>>error message. Your help would be greatly appreciated.
| > >>>
| > >>>Sample Code:
| > >>>Function fixQuotes(theString)
| > >>>fixQuotes = Replace(theString, "'","''")
| > >>>End Function
| > >>>strSql = "Update Tenant Set " &_
| > >>>"Tenant = ' " & fixQuotes(Trim(Request.form("Tenantname"))) & "', "
&_
| > >>>"Address1 = ' " & fixQuotes(Trim(Request.form("Address1"))) & "', "
&_
| > >>>"Address2 = ' " & fixQuotes(Trim(Request.form("Address2"))) & "', "
&_
| > >>>"City = ' " & fixQuotes(Trim(Request.form("City"))) & "', " &_
| > >>>"State = ' " & fixQuotes(Trim(Request.form("State"))) & "', " &_
| > >>>"Zip = ' " & fixQuotes(Trim(Request.form("Zip"))) & "', " &_
| > >>>"PhoneNumber = ' " & fixQuotes(Trim(Request.form("PhoneNumber"))) &
"', "
| > >>>&_
| > >>>"Email = ' " & fixQuotes(Trim(Request.form("email"))) & "', " &_
| > >>>"Password = ' " & Trim(fixQuotes( Pass )) & "'" &_
| > >>>"WHERE tenant.Customerid = '" & Cstr(Request.form("customer")) & "'
"
| > >>>
| > >>>set conn=Server.createObject("ADODB.connection")
| > >>>conn.Open "DSN=Falco; uid=sa; pwd=6stang7"
| > >>>conn.Execute strSql
| > >>>conn.close
| > >>>set conn = nothing
| > >>>
| > >>>%>
| > >>>
| > >>
| > >>
| >

 

[p c]

That's correct.

In code for string that can be empty I also use that trick to force the
strVariable to get assigned a string value. I do the same when I get
value from the DB. It's important if you need to compare values later on
to make certain decisions. Checking for empty or "" does not yield the
same answer if the value is null, and vice versa.

http://www.evolt.org/article/Empty_Nothing_and_Null_How_do_you_feel_today/17/346/

Acess DB uses the term Zero length string for value of "" (empty string).

The CString function can also be used to convert variant type data to
string type. CInt coverts text to integer. It's important when comparing
variables to make sure they are the same data type otherwise you may end
up with "unrpredictable" results.


PC..