How can I avoid lots of spaces being added to the end when saving contents of a textbox

Hi

I am using FormView, SQL 2005, VB 2005

When I save the contents of Title and Area entry fields , I have lots of spaces added to the end.

Title and Area are MultiLine Textboxes and database:varchar(100)

I thought I had solved the issue using;

TextBox Title = FormView1.FindControl("TitleTextBox")asTextBox;

TextBox Area = FormView1.FindControl("AreaTextBox")asTextBox;

TitleLenTrim = Title.Text.Trim().ToString();

if (TitleLenTrim.Length > 100)

{

TitleLenTrim = TitleLenTrim.Trim().Substring(0, 99);

}

string AreaLenTrim;

AreaLenTrim = Area.Text.Trim().ToString();

if (AreaLenTrim.Length > 100)

{

AreaLenTrim = AreaLenTrim.Trim().ToString();

}

string insertSQL;

insertSQL ="INSERT INTO Issue(";

insertSQL +="ProjectID, TypeofEntryID, PriorityID ,Title, Area)";

insertSQL +="VALUES ( '";

insertSQL += ProjectID.Text.ToString() +"', '";

insertSQL += EntryTypeID.Text.ToString() +"', '";

insertSQL += PriorityID.Text.ToString() +"', '";

insertSQL += TitleLenTrim.Trim().ToString() +"', '";

insertSQL += AreaLenTrim.Trim().ToString() +"', '";

Is there any other way I could remove spaces?

Thanks in advance.

My guess is that your columns are defined with data typechar instead ofvarchar. SQL Server will pad char columns with spaces.

Also, you should be using Parameters, not concatenating UI-supplied data directly to SQL statements. This is an insecure practice.

|||

Thankstmorton , you were right, I had declared the two columns as char.

Could you please explain more regarding using Parameters, not concatenating UI-supplied data directly to SQL statements?

Thanks in advance

|||

string insertSQL;

insertSQL ="INSERT INTO Issue(";

insertSQL +="ProjectID, TypeofEntryID, PriorityID ,Title, Area)";

insertSQL +="VALUES ( '";

insertSQL += ProjectID.Text.ToString() +"', '";

insertSQL += EntryTypeID.Text.ToString() +"', '";

insertSQL += PriorityID.Text.ToString() +"', '";

insertSQL += TitleLenTrim.Trim().ToString() +"', '";

insertSQL += AreaLenTrim.Trim().ToString() +"', '";

Becomes:

string insertSQL;

insertSQL ="INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@.ProjectID, @.TypeofEntryID, @.PriorityID ,@.Title, @.Area)";

cmdInsert SqlCommand;

cmdInsert=new SqlCommand(insertSQL,conn);

cmdInsert.Parameters.Add("@.ProjectID",SqlDbType.Varchar).Value=ProjectID.Text;

cmdInsert.Parameters.Add("@.EntryTypeID",SqlDbType.Varchar).Value=EntryTypeID.Text;

...

cmdInsert.ExecuteNonQuery;

(Sorry if my C# syntax isn't exact)

|||

Thanks Motley, It works fine until I try to insert a date.

Previous code was

insertSQL +="convert(datetime,'" +DateTime.Now.ToString("dd/MM/yy") +"',3), '";

I tried the code below but the record doesn't save?

Thanks in advance

string date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

How can I assign a value to a textbox programmatically in a report?

For example: If I want to display the date today, txtDate.Text = DateToday;Type
=System.DateTime.Now
into the Textbox and set the Format (right-click->properties) to dd.MM.yyyy or your corresponding date-format
|||that's design time, what i'm trying to do is at run-time I want to assign a value to a particular textbox in the report. ü|||There is no way other than assigning a expression to the Textbox. The "MS Access way" doesn't work anymore ;(
You can do something like:
=iif( some_condition = true, System.DateTime.Now, "No Date")

or create your own function:
Add
Public Function myFunction(val as Object)
if val is Nothing then return System.DateTime.Now
return "Empty"
End Funcion

to Report-Properties->Code
and call it by
=Code.myFunction(Fields!ConditionColumn.Value)

|||ok thanks, i'll try your solution later. sql

How about a Rich Text or HTML Text TextBox

Come one guys how about a rich text or HTML text box in RS2005, this is a
glaring omission. I know there are some workarounds, but for some items this
is just necessary. How hard can this be?
Lets consider an example. I have a report that I send out that is really
quite complex. To help understand the report I have a page of documentation
built right into the report. Only problem is it needs some text in bold,
some in color, other not, tables, etc. Well, lets try some workarounds.
First, I tried linking to an HTML document, but this is distributed all over
the world via e-mail and is expected to be able to be read offline. Next, I
have tried using a combination of all the toolbox items to make part of the
"report" that looks like my documentation but that didn't work out too well
as its a real pain to try to make formatted text out of many independently
formatted textboxes, tables, etc. The last thing I have been trying is to
take my HTML page and run it through a HTML to image conversion and then
insert the image into the report. This works for online browsing, etc, but
when I save the report off to excel I get hit with a max column height of
409. This I get about 3/4 of my image.
Anyway, I'm just venting but please, even given the late date, please
consider adding a rich text or html text box to RS 2005.
Thanks,Here is something I do which might solve at least some of your needs. Create
an html document somewhere (I just start it out in Word and save it as
html). Then do a right mouse click, add, add existing item. Go to the
directory that has the document and select it. It will not be part of your
project. You deploy it like normal. You can do a jump to report, etc. You
can not have it be a subreport (I just tried that).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"asnewbie+rs=argh" <asnewbiersargh@.discussions.microsoft.com> wrote in
message news:48EE359B-939F-4EB6-AB39-3C4400A05F9D@.microsoft.com...
> Come one guys how about a rich text or HTML text box in RS2005, this is a
> glaring omission. I know there are some workarounds, but for some items
> this
> is just necessary. How hard can this be?
> Lets consider an example. I have a report that I send out that is really
> quite complex. To help understand the report I have a page of
> documentation
> built right into the report. Only problem is it needs some text in bold,
> some in color, other not, tables, etc. Well, lets try some workarounds.
> First, I tried linking to an HTML document, but this is distributed all
> over
> the world via e-mail and is expected to be able to be read offline.
> Next, I
> have tried using a combination of all the toolbox items to make part of
> the
> "report" that looks like my documentation but that didn't work out too
> well
> as its a real pain to try to make formatted text out of many independently
> formatted textboxes, tables, etc. The last thing I have been trying is to
> take my HTML page and run it through a HTML to image conversion and then
> insert the image into the report. This works for online browsing, etc,
> but
> when I save the report off to excel I get hit with a max column height of
> 409. This I get about 3/4 of my image.
> Anyway, I'm just venting but please, even given the late date, please
> consider adding a rich text or html text box to RS 2005.
> Thanks,