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 are these queries evaluted differently by sql server?

I'm looking for the minimum date of an entry into a history table. The table contains multiple entries for the customer and the item with an activation and deactivation date for each entry.

I could use the following:

select customerId, item, min(activationDate) from history group by customerId, item

or a sub query

select customerId, item, activationDate

from history h1

where activationDate=(select min(activationDate) from history h2 where h2.customerId=h1.customerId and h2.item=h1.item)

How are these two queries parsed differently by SQL.

They return a different number of results.

Thanks,

karen

I'm guessing that customerId and item do not uniquely define a record in the table, is this true? If so, that would explain the difference in the amount of rows between the two result sets.

-The first query finds the minimum activationDate for each unique customerId and item.

-The second query finds the minimum activationDate for each customerId and item.

-If you add a group by customerId, item to the second query you should have matching result sets.

The inner query of the correlated subquery is processed once per record of the outer query.

|||

If you have the following data in the table:

CustomerID - Item - ActivationDate

1 1 1/1/2007

1 1 1/1/2007

The first query will return one record while the second query will return two. This is because (as the above post indicates), that the first query is returning a true grouping while the second will return duplicate records if there is duplicate data in the table.

|||

Bcs there are duplicate entries available on your database..

You can force DISTINCT class to fix this..

Sample..

Create Table #samplehistory (

[customerId] int ,

[item] int ,

[activationDate] datetime

);

Insert Into #samplehistory Values('1','1','1/1/2007');

Insert Into #samplehistory Values('1','1','1/2/2006');

Insert Into #samplehistory Values('1','1','1/3/2006');

Insert Into #samplehistory Values('1','2','1/11/2007');

Insert Into #samplehistory Values('1','2','1/11/2003');

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate) activationDate

from

#samplehistory

group by

customerId, item

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

After duplicating one of the value.. You are result is correct but there are dupicate data in the result

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate)

from

#samplehistory

group by

customerId, item

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

122002-01-11 00:00:00.000

*/

The group by class force the First query to avoid the duplicates (already distincted values are return).

After Distinct on second query,

Code Snippet

select distinct

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

|||

Thank you very much for your help. I really appreciate the time.

Would you recommend a book for dealing with these kinds of sublties in SQL?

Karen

|||

Inside SQL Server 2005 T-SQL Querying by Itzik Ben-Gan is very good.

|||Ken Henderson's "The Guru's Guide to Transact-SQL" is also very good.

How are pictures stored in a SQL database ??

Are they actually broken down into a binary record entry, or is there a jpg
file sitting out there somewhere that I haven't yet found ?I believe that they are stored as a binary blob in the record's entry,
not as a file elsewhere.
R.
WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?|||What needs to be done to that binary blob to display a picture on a web
site?
================================================<slaught@.vault13.co.uk> wrote in message
news:1169046978.563378.93060@.11g2000cwr.googlegroups.com...
>I believe that they are stored as a binary blob in the record's entry,
> not as a file elsewhere.
> R.
> WANNABE wrote:
>> Are they actually broken down into a binary record entry, or is there a
>> jpg
>> file sitting out there somewhere that I haven't yet found ?
>|||WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?
>
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the suggestion. This database is a production canned database
application, and I am assuming that there is no way to modify their product
to store pictures in a different way. So, with the understanding that I
know less then I don't know, Is this product only used in the development
realm, or can it be implemented into a database system which is already
storing pictures in blobs? :-) lol thanks..
=============================================="Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE450B.7030409@.realsqlguy.com...
> WANNABE wrote:
>> Are they actually broken down into a binary record entry, or is there a
>> jpg file sitting out there somewhere that I haven't yet found ?
> http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Please take a look at this article : "HOW TO: Read and Write BLOB Data by
Using ADO.NET Through ASP.NET": http://support.microsoft.com/kb/326502
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

How are pictures stored in a SQL database ??

Are they actually broken down into a binary record entry, or is there a jpg
file sitting out there somewhere that I haven't yet found ?
I believe that they are stored as a binary blob in the record's entry,
not as a file elsewhere.
R.
WANNABE wrote:

> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?
|||What needs to be done to that binary blob to display a picture on a web
site?
================================================
<slaught@.vault13.co.uk> wrote in message
news:1169046978.563378.93060@.11g2000cwr.googlegrou ps.com...
>I believe that they are stored as a binary blob in the record's entry,
> not as a file elsewhere.
> R.
> WANNABE wrote:
>
|||WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?
>
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Please take a look at this article : "HOW TO: Read and Write BLOB Data by
Using ADO.NET Through ASP.NET": http://support.microsoft.com/kb/326502
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

How are pictures stored in a SQL database ??

Are they actually broken down into a binary record entry, or is there a jpg
file sitting out there somewhere that I haven't yet found ?I believe that they are stored as a binary blob in the record's entry,
not as a file elsewhere.
R.
WANNABE wrote:

> Are they actually broken down into a binary record entry, or is there a jp
g
> file sitting out there somewhere that I haven't yet found ?|||What needs to be done to that binary blob to display a picture on a web
site?
========================================
========
<slaught@.vault13.co.uk> wrote in message
news:1169046978.563378.93060@.11g2000cwr.googlegroups.com...
>I believe that they are stored as a binary blob in the record's entry,
> not as a file elsewhere.
> R.
> WANNABE wrote:
>
>|||WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jp
g
> file sitting out there somewhere that I haven't yet found ?
>
http://databases.aspfaq.com/databas...filesystem.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the suggestion. This database is a production canned database
application, and I am assuming that there is no way to modify their product
to store pictures in a different way. So, with the understanding that I
know less then I don't know, Is this product only used in the development
realm, or can it be implemented into a database system which is already
storing pictures in blobs? :-) lol thanks..
========================================
======
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE450B.7030409@.realsqlguy.com...
> WANNABE wrote:
> http://databases.aspfaq.com/databas...filesystem.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Please take a look at this article : "HOW TO: Read and Write BLOB Data by
Using ADO.NET Through ASP.NET": http://support.microsoft.com/kb/326502
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com