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 ThanksBilly |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-02 : 09:42:44
|
Use Trim function(or its equivalent) in ASP.NETMadhivananFailing 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 OptimizerTG |
|
|
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 thisInsert 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 spacesMadhivananFailing 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/UPDATEKristen |
|
|
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:Insertrunat="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 scriptsSee if there is any function like trim, rtrim or ltrimYou can use for exampleTrim(Request.Form("Status"))MadhivananFailing 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/UPDATEKristen
Kris, is this normal approach?MadhivananFailing 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 MyTriggerON dbo.ISAssetAFTER INSERT, UPDATEASSET 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 NULLKristen |
|
|
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 neededUpdate myTable Set textcol=Null where textcol=''Will this make any impact on large tables?MadhivananFailing 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 spacesMadhivananFailing to plan is Planning to fail |
|
|
BillyWardrop2345
Starting Member
9 Posts |
Posted - 2005-09-02 : 10:35:22
|
When I run that query I getIT154 testThanks |
|
|
BillyWardrop2345
Starting Member
9 Posts |
Posted - 2005-09-02 : 10:38:01
|
Sorry that was supposed to be IT154----------------------------------------------test'-' are spacesThanks again |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-02 : 11:02:36
|
I think you need to trim that in DreamweaverSee what is equivalent trim function in dreamweaverOtherwise use this update command Update yourtable Set column=rtrim(column)where column is the column which you think having spacesMadhivananFailing to plan is Planning to fail |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-02 : 11:43:47
|
IsType="NChar"fixed length, or variable length in DreamWeaver parlance?Kristen |
|
|
|