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;