[varchar] (100) and empty space

Hello

I am creating the following table

CREATE TABLE [dbo].[myT] (
[name] [varchar] (100) NULL
)

if I do

INSERT INTO myT (name) VALUES ('A')

i get in the table a column with

A ...

the 100 char place is full even if there is a data with only 1 char

how is it possible to avoid it ?
I want 100 char maximum but not full with nothing

thank you for helpingWhat do you get when you run this?:

select len([Name]), '[' + [Name] + ']' from myT

Name is a reserved word, and so it is not a good label for a column, but I don't think this would cause the problem you are seeing.|||the 100 char place is full even if there is a data with only 1 charHow do you know?

Could this be a display issue of whatever client program you use to display the data?
Only CHAR columns are padded to the full length not VARCHAR columns|||I know it when I fill a formula with datas I am getting 99 empty spaces

blindman it is an exemple i have no column named [name]

if i run select datalength(name) from myT
i am getting 200 2 times (100)|||Strange I just ran all the scripts above & everything looks good to me

I get 1 from select datalength(name) from myT

anselme

take a deep breath reboot and start running these scripts again

If this does'nt work

Reply here and

stop using the word Char as in the 100 char place is full even if there is a data with only 1 char

explain what query editor you are using

explain what database you are using

run the script exactly as blindman suggests select len([Name]), '[' + [Name] + ']' from myT and tell us exactly the output

GW|||blindman it is an exemple i have no column named [name]You have some sort of typo, and if you expect any more help on this you need to post the actual code so we don't waste more of our time.|||I know it when I fill a formula with datas I am getting 99 empty spacesSQL Server does not have "formulas" to be "filled" (whatever that should mean).
What exactly are you doing?|||i agree with Gwilliy everything looks cool for me too....
varchar will only occupy the required number of space...
however if still problems persist you can always use LTRIM and RTRIM to get rid of the remaining whitespaces

so your query will be something like
select LTRIM(RTRIM([name])) from myT

thats the most we can get you....

However the fact that the 100 varchar place is full still mystifies me|||However the fact that the 100 varchar place is full still mystifies me
I suspect this is a front end issue.
What is this "formula" thing he is filling in?

[SQL] One parameter and few values.

Hello!

I have tables "forum_topics" and "forum_categories":

forum_topics:
-topic_id
-topic_title
-topic_cat_id
-topic_user_id
-topic_date

forum_categories:
-cat_id
-cat_name

For example: I have 5 categories in table forum_categories and by this query I can display topics from 2nd and 3rd category only. So I'm using this query:
SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (2;3)

Now I want to parametrized this query by one parameter, so:

SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (@.param)

And I want to input this parameter lik in the query first - so @.param = "2;3"

But I have a problem to make this works, can someone explain me, how to solve this problem ? Thanks.

Czesc Grzesiek,

The quick and dirty way of doing it is to convert your SQL to a string and execute it later, this is useful when you have optional parameters. Your stor proc would look something like that:

Declare @.sqlas varchar(1000)SET @.sql ='SELECT topic_title FROM forum_topics WHERE topic_cat_id IN ('''+ @.param +''')'exec(@.sql)

|||

you want @.param to be comma separated value is that you are asking for ?

well you could do it in c# , vb.net or any language, could you tell me wht are you doing at the moment for that.

its simple just declare one string variable say str

string str = "";

if (str.length == 0)

str = some_value;

else

str += str +", " + some_other_value;

does that makes sense

regards,

satish

|||

Hey Tetris - you're from Poland maybe ? :)

I have:

ALTER PROCEDURE ShowTopics

@.param varchar
AS

Declare @.sql as varchar(max)

SET @.sql='SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN ('''+@.param+'''))'
exec(@.sql)RETURN

All as you wrote. But when I write as @.param "1,2,3" I'm getting only topics (records) where cat_id is "1". If I choose "3,2,1" I will get only from 3rd category.

|||

I wanna add only that this query:

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN (1,2,3)

Returns me all topics from category 1st,2nd and 3rd. So my question is simply - how can I put (and how declare, or I don't know) in place 1,2,3 a parameter @.categories, and how make it works ?

|||

Oh God ...

My mistake was at the begin of stored procedure. There was:

ALTER PROCEDURE ShowTopics
@.param varchar()

Should be
ALTER PROCEDURE ShowTopics
@.param varchar(100)

(for example)

No it works !!!! Thanks Tetris ! Big thanks !

|||I'm glad you got it to work :) Yeh I'm polish but I live in Canada.|||

So I have one more question.

I have 5 checkboxes:
Checkbox1 (Category 1st),Checkbox2 (Category 2nd),Checkbox3 (Category 3rd),Checkbox4 (Category 4th),Checkbox5 (Category 5th).

And as cookie "Forum_kategorie" I have value = "1,3,5".

So I have topics from category 1,3 and 5. Do you know how can I easily checked checkbox 1,3 and 5 and unchecked second and fourth ?

Default value of "@.param" in this parametrized query above will be 1,2,3,4,5. And at first enter to site all five checkboxes will be checked. I will unchecked second and fourth and click the button, which will add/change cookie "Forum_kategorie" to value "1,3,5" and reload site. After that I will see topics from 1,3,5 category, but I have in some way to define which checkbox is un/checked.

Any ideas ?

Dzi?ki za pomoc :-) / Thanks for help :-)

|||If you are re-loading the same page, as long as you have EnableViewState = True for each checkbox, it should remember what it's last selection was when the site re-loads (on post back) Is this the case?|||

As you have a small rabge of values, take a look at using bitwise operators.

Your check box values would be bases on multiples of two: 1,2,4,8,16.

The value of the parameter that you pass into your stored procedure would be the sum of the values of the check boxes.

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id & @.Categories = forum_kategorie.forum_kat_id)

[MSSQL + VS.NET] Connection - sleeping

Hello
I've prepared a small aplication in VS.NET and I use SqlConnection class.
Everything is ok, but when I'm trying to disconnect from server using
if (cnSQL->State != ConnectionState::Closed) cnSQL->Close()
there is still connection do server present. I have to close connection
because I'm trying to drop this database.
Have you ever heard about this kind of problem??

Trociu

--
Plain-text over all!!!
/**********/
write me : trociu@.autonom ict pwr wroc pl
search me : gg: 1382729"Trociu" <trociu@.autonom.ict.pwr.wroc.pl> wrote in message
news:slrncmhvuv.14n.trociu@.autonom.ict.pwr.wroc.pl ...
> Hello
> I've prepared a small aplication in VS.NET and I use SqlConnection class.
> Everything is ok, but when I'm trying to disconnect from server using
> if (cnSQL->State != ConnectionState::Closed) cnSQL->Close()
> there is still connection do server present. I have to close connection
> because I'm trying to drop this database.
> Have you ever heard about this kind of problem??
> Trociu
> --
> Plain-text over all!!!
> /**********/
> write me : trociu@.autonom ict pwr wroc pl
> search me : gg: 1382729

I have no idea, unless perhaps you're using connection pooling, and the pool
is still active? You might get a better response in a VB or .NET group.

From the server side, you can use ALTER DATABASE to disconnect all users
from a database - see Books Online:

ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE

Simon