Unwanted Spaces in SQL field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Unwanted Spaces in SQL field

Author

Topic

BillyWardrop2345
Starting Member

9 Posts

Posted - 2005-09-02 : 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

22864 Posts

Posted - 2005-09-02 : 09:42:44

Use Trim function(or its equivalent) in ASP.NET

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-02 : 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 - 2005-09-02 : 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

22864 Posts

Posted - 2005-09-02 : 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

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 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 - 2005-09-02 : 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 - 2005-09-02 : 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

22864 Posts

Posted - 2005-09-02 : 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

22864 Posts

Posted - 2005-09-02 : 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

22859 Posts

Posted - 2005-09-02 : 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

22859 Posts

Posted - 2005-09-02 : 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 - 2005-09-02 : 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

22864 Posts

Posted - 2005-09-02 : 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

22864 Posts

Posted - 2005-09-02 : 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 - 2005-09-02 : 10:35:22

When I run that query I get

IT154 test

Thanks

BillyWardrop2345
Starting Member

9 Posts

Posted - 2005-09-02 : 10:38:01

Sorry that was supposed to be

IT154----------------------------------------------test

'-' are spaces

Thanks again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-02 : 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

22859 Posts

Posted - 2005-09-02 : 11:43:47

Is

Type="NChar"

fixed length, or variable length in DreamWeaver parlance?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources