SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Unwanted Spaces in SQL field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BillyWardrop2345
Starting Member

9 Posts

Posted - 09/02/2005 :  09:40:26  Show Profile  Reply with Quote
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
22769 Posts

Posted - 09/02/2005 :  09:42:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Use Trim function(or its equivalent) in ASP.NET

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/02/2005 :  09:44:06  Show Profile  Reply with Quote
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
Go to Top of Page

BillyWardrop2345
Starting Member

9 Posts

Posted - 09/02/2005 :  09:46:02  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 09/02/2005 :  09:48:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/02/2005 :  09:51:32  Show Profile  Reply with Quote
Or put a trigger on the table to post-RTrim the offending columns on INSERT/UPDATE

Kristen
Go to Top of Page

BillyWardrop2345
Starting Member

9 Posts

Posted - 09/02/2005 :  09:54:24  Show Profile  Reply with Quote
Unfortunately Im using dreamweaver which isnt the best when it comes to ASP.NET.

How could I post a trigger rTRIM onto the tabel?
Go to Top of Page

BillyWardrop2345
Starting Member

9 Posts

Posted - 09/02/2005 :  09:58:42  Show Profile  Reply with Quote
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>
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 09/02/2005 :  10:01:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 09/02/2005 :  10:03:57  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/02/2005 :  10:08:27  Show Profile  Reply with Quote
"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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/02/2005 :  10:10:16  Show Profile  Reply with Quote
"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
Go to Top of Page

BillyWardrop2345
Starting Member

9 Posts

Posted - 09/02/2005 :  10:16:02  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 09/02/2005 :  10:17:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 09/02/2005 :  10:22:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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
Go to Top of Page

BillyWardrop2345
Starting Member

9 Posts

Posted - 09/02/2005 :  10:35:22  Show Profile  Reply with Quote
When I run that query I get

IT154 test

Thanks
Go to Top of Page

BillyWardrop2345
Starting Member

9 Posts

Posted - 09/02/2005 :  10:38:01  Show Profile  Reply with Quote
Sorry that was supposed to be

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

'-' are spaces

Thanks again
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 09/02/2005 :  11:02:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/02/2005 :  11:43:47  Show Profile  Reply with Quote
Is

Type="NChar"

fixed length, or variable length in DreamWeaver parlance?

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000