| Author |
 Topic  |
|
|
BillyWardrop2345
Starting Member
9 Posts |
 Posted - 09/02/2005 : 09:40:26
|
Im developing a web application in ASP.NET. When a user inserts a record from a form, I get a lot additional space characters. I've checked the form and there is no fixed column widths on the controls.
Is there a setting I can add to stop this happening?
Many Thanks
Billy |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 09/02/2005 : 09:42:44
|
Use Trim function(or its equivalent) in ASP.NET
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 09/02/2005 : 09:44:06
|
also:
what is the datatype of the sql column storing these values? char(n) or varchar(n)? Are the spaces showing up at the beginning, end, or thoughout the value? beginning or end you can take care of with rTrim and lTrim functions.
Be One with the Optimizer
TG |
|
|
|
BillyWardrop2345
Starting Member
9 Posts |
Posted - 09/02/2005 : 09:46:02
|
| The spaces appear at the end of the value. Where would I use trim? I am creating a drop down with three values using SQL Views. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 09/02/2005 : 09:48:56
|
Something like this
Insert into yourtable values( '" & trim(dropdown.Text) & "',.....)
If the datatype is char and the length of value inserted is less than char length then you will have trailing spaces
Madhivanan
Failing to plan is Planning to fail |
Edited by - madhivanan on 09/02/2005 09:49:35 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/02/2005 : 09:51:32
|
Or put a trigger on the table to post-RTrim the offending columns on INSERT/UPDATE
Kristen |
|
|
|
BillyWardrop2345
Starting Member
9 Posts |
Posted - 09/02/2005 : 09:54:24
|
Unfortunately Im using dreamweaver which isnt the best when it comes to ASP.NET.
How could I post a trigger rTRIM onto the tabel? |
|
|
|
BillyWardrop2345
Starting Member
9 Posts |
Posted - 09/02/2005 : 09:58:42
|
The datatype is nvarchar. Here is the dreamweaver insert command:
<MM:Insert
runat="server"
CommandText='<%# "INSERT INTO dbo.ISAsset (AssetRef, Location, AssetType, AssignedTo, AssignedBy, DateIssued, status, PCPatchNo, Networked, AssetNo) VALUES (@AssetRef, @Location, @AssetType, @AssignedTo, @AssignedBy, @DateIssued, @status, @PCPatchNo, @Networked, @AssetNo)" %>'
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_HelpdeskConnection") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_HelpdeskConnection") %>'
Expression='<%# Request.Form("MM_insert") = "form1" %>'
CreateDataSet="false"
SuccessURL='<%# "confirmadd.aspx" %>'
FailureURL='<%# "addisasset.aspx" %>'
Debug="true"
><Parameters>
<Parameter Name="@AssetRef" Value='<%# IIf((Request.Form("HardwareRefDropDownList") <> Nothing), Request.Form("HardwareRefDropDownList"), "") %>' Type="Int" />
<Parameter Name="@Location" Value='<%# IIf((Request.Form("LocationDropDown") <> Nothing), Request.Form("LocationDropDown"), "") %>' Type="NChar" />
<Parameter Name="@AssetType" Value='<%# IIf((Request.Form("AssignedByDropDownList") <> Nothing), Request.Form("AssignedByDropDownList"), "") %>' Type="Int" />
<Parameter Name="@AssignedTo" Value='<%# IIf((Request.Form("AssignedToDropDownList") <> Nothing), Request.Form("AssignedToDropDownList"), "") %>' Type="Int" />
<Parameter Name="@AssignedBy" Value='<%# IIf((Request.Form("AssignedByDropDownList") <> Nothing), Request.Form("AssignedByDropDownList"), "") %>' Type="Int" />
<Parameter Name="@DateIssued" Value='<%# IIf((Request.Form("DateIssued") <> Nothing), Request.Form("DateIssued"), "") %>' Type="DateTime" />
<Parameter Name="@status" Value='<%# IIf((Request.Form("Status") <> Nothing), Request.Form("Status"), "") %>' Type="NChar" />
<Parameter Name="@PCPatchNo" Value='<%# IIf((Request.Form("PCPatchNo") <> Nothing), Request.Form("PCPatchNo"), "") %>' Type="NChar" />
<Parameter Name="@Networked" Value='<%# IIf((Request.Form("Networked") <> Nothing), Request.Form("Networked"), "") %>' Type="NChar" />
<Parameter Name="@AssetNo" Value='<%# IIf((Request.Form("AssetNo") <> Nothing), Request.Form("AssetNo"), "") %>' Type="NChar" />
</Parameters>
</MM:Insert> |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 09/02/2005 : 10:01:55
|
If it is a nvarchar field then you need to trim it in the dreamweaver scripts
See if there is any function like trim, rtrim or ltrim
You can use for example
Trim(Request.Form("Status"))
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 09/02/2005 : 10:03:57
|
quote:
Originally posted by Kristen
Or put a trigger on the table to post-RTrim the offending columns on INSERT/UPDATE
Kristen
Kris, is this normal approach?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/02/2005 : 10:08:27
|
"How could I post a trigger rTRIM onto the tabel?"
CREATE TRIGGER MyTrigger
ON dbo.ISAsset
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
UPDATE U
SET AssetRef = RTrim(I.AssetRef),
Location = RTrim(I.Location),
...
FROM inserted I
JOIN dbo.ISAsset U
ON U.AssetRef = I.AssetRef -- Is this the PK??
GO
Kristen |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/02/2005 : 10:10:16
|
"Kris, is this normal approach?"
Definitely not! However, if data is coming in from sources which do not provide it as "clean", and IF the applications cannot call an SProc, then I think its not a bad idea.
We use triggers to force all [maybe not ALL, but certainly MOST] "empty" text columns to NULL
Kristen |
|
|
|
BillyWardrop2345
Starting Member
9 Posts |
Posted - 09/02/2005 : 10:16:02
|
I tried inserting trim into the dreamweaver code but it didnt work.
My SQL Server knowledge is very limited. Where would I setup this trigger?
Thanks again |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 09/02/2005 : 10:17:13
|
Well. But I like updation than using trigger whenever needed
Update myTable Set textcol=Null where textcol=''
Will this make any impact on large tables?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 09/02/2005 : 10:22:32
|
>>I tried inserting trim into the dreamweaver code but it didnt work.
Why didnot it work?
How do you know the value is inserted with trailing spaces?
See what is the result when usig this query?
Select column+'test' from yourTable
where column is the column which you think having spaces
Madhivanan
Failing to plan is Planning to fail |
|
|
|
BillyWardrop2345
Starting Member
9 Posts |
Posted - 09/02/2005 : 10:35:22
|
When I run that query I get
IT154 test
Thanks |
|
|
|
BillyWardrop2345
Starting Member
9 Posts |
Posted - 09/02/2005 : 10:38:01
|
Sorry that was supposed to be
IT154----------------------------------------------test
'-' are spaces
Thanks again |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 09/02/2005 : 11:02:36
|
I think you need to trim that in Dreamweaver
See what is equivalent trim function in dreamweaver
Otherwise use this update command
Update yourtable Set column=rtrim(column)
where column is the column which you think having spaces
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/02/2005 : 11:43:47
|
Is
Type="NChar"
fixed length, or variable length in DreamWeaver parlance?
Kristen |
|
|
| |
Topic |
|
Unwanted Spaces in SQL field
