Defining sort order using parameter fields.

Hi!

This is my first post at this forum.

I have a parameterfield wich the users can use to enter sortorder to the report. I have then created a formula with this code in it.

if {?sortid} = "1" then ({DMD_VIEW_ERRAND.er_control_date} AND {DMD_VIEW_ERRAND.er_id})
else
if {?sortid} = "2" then ({DMD_VIEW_ERRAND.er_activity_desc} AND {DMD_ERRAND.ER_HANDLERDATA} AND {DMD_VIEW_ERRAND.er_id})


This formula is then added into the record sorting expert.

The problem is there is an error in the formula. I want to order by multiple columns. Is it possible?
If it is, what's wrong with my code?

I'm using crystal XI
Best regards
HeleniusI've solved it.

Here's the code

if {?sortid} = "1" then (CStr({DMD_VIEW_ERRAND.er_control_date_fmt}) + "AND" + {DMD_VIEW_ERRAND.er_id})
else
if {?sortid} = "2" then ({DMD_VIEW_ERRAND.er_activity_desc} + "AND" + {DMD_ERRAND.ER_HANDLERDATA} + "AND" + CStr({DMD_VIEW_ERRAND.er_control_date_fmt}) + "AND" + {DMD_VIEW_ERRAND.er_id})

I had a little convertion problem...

/Helenius|||Noooo, it doesn't seem to be solved after all. The report only sort based by the first column. Any suggestions??

default value on quantity and price field

should I set to "0" as a default value on a quantity and a price field
or set to "null"?
if a user enter nothing on the quantity or price field on a web
browser, should I treat it as null or "0"? I am so confused about this
concept. please advise me. thank you.HandersonVA wrote:
> should I set to "0" as a default value on a quantity and a price field
> or set to "null"?
> if a user enter nothing on the quantity or price field on a web
> browser, should I treat it as null or "0"? I am so confused about this
> concept. please advise me. thank you.

I would do the following :
1) Set quantity and price to "not null"
2) Put 0 when there is no value, instead of NULL.

This way,
a) if you use aggrate function like AVG, thoses functions will use
everyrow
b) non unique index on thoses fields will contained every rows
c) if you query thoses field (where quantit=0) rows will come out. IF
quantity is null and you query thoses rows, youll need (where
quantity=0 or quantity is null)...
etc.|||francois.bourdages@.harfan.com wrote:
> HandersonVA wrote:
> > should I set to "0" as a default value on a quantity and a price field
> > or set to "null"?
> > if a user enter nothing on the quantity or price field on a web
> > browser, should I treat it as null or "0"? I am so confused about this
> > concept. please advise me. thank you.

Here is a link to an oracle document (outch), that explain some
problems with null...
http://www.oracle.com/technology/or...jul/o45sql.html|||HandersonVA (handersonva@.hotmail.com) writes:
> should I set to "0" as a default value on a quantity and a price field
> or set to "null"?
> if a user enter nothing on the quantity or price field on a web
> browser, should I treat it as null or "0"? I am so confused about this
> concept. please advise me. thank you.

That depends on the business rules. If not entering any price, means
"this item is for free", yes the 0 is the right thing. If not entering
any price means "I don't know", you should use NULL.

The same goes for Quantity. If this is an inventory 0 probably means "out
of stock". Which could be a good default. Then again, it's probably better
to have the user to state that explicitly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 1 Jun 2006 09:41:52 -0700, HandersonVA wrote:

>should I set to "0" as a default value on a quantity and a price field
>or set to "null"?
>if a user enter nothing on the quantity or price field on a web
>browser, should I treat it as null or "0"? I am so confused about this
>concept. please advise me. thank you.

Hi HandersonVA,

There's a very big difference between 0 and NULL.

Suppose you're in a restaurant and the menu lists the price for some
dishes as $0.00. You'd probably order it right away, and tell all your
friend that yoou've foound a place with free food. But I'm pretty sure
that you wouldn't do the same is the price for some (or all) dishes was
simply omitted from the list.

In a database, NULL represents the price on a menu without price list.
NULL is specifically designed to represent the fact that no data is
available for a specific column in a specific row in the table.

Alowing data to be missing in a database introduces some interesting
problems. I won't describe them here (but I skimmed the article Francois
linked to, and on first glance it appears to be a good start on the
subject), but they are the reason that the common advise is to avoid
missing data whenever possible (or, to state it very shortly: avoid
NULLS). Of course, the real world sometimes confronts us with situations
where part of the data IS missing, so we can't always avoid it.

Unfortunately, some people have taken the "avoid NULLS" advise way too
litteraly - I have seen people avoiding NULL, but using some other
"magic value" to represent missing data. And believe me - that only
results in more problems, not in less!!
(Quick example - the average of {8.5, 9.5, NULL} would be calculated as
9.0 - not exactly right [since one of the input values is missing, the
only correct answer would be that it's impossible to calculate the
average], but it is at least the real average of the values that are
present in the database. But use 0 as "magic value" to represent missing
data, and you're calculating the average of {8.5, 9.5, 0}, which yields
6.0 - and that''s just plain silly!)

After this explanation about NULLs, let's get back to your question. A
default should be either a commonly used value (eg countrycode USA when
doing business in the USA), or a "safe" value (eg no automatic unlimited
bidding on an auctioning site).

For quantity, the value 1 is quite common in many industries. It's also
safe. So I'd recommend setting the default quantity to 1. Unless you're
in an industry that usually orders large quantities at once - in that
case, either choose a better default that fits the business or don't use
a default at all. I would definitely NOT use 0 as default quantity, as
an order for a quantity of 0 items is pointless (and shouldd in fact be
rejected by a CHECK constraint).

For price, there is no goood default value in most industries, so I
would not create a default for the column at all. Define the column as
NOT NULL (to force the user to enter a price) or, if the business has to
deal with orders before a price is known, allow NULLs and don't set up a
default - the price will remain NULL until one is explicitly entered.
Make sure you handle the missing information adequately and set up
constraints to ensure that the price must be known once the order passes
the stage where the prices should be known according to the industry's
business rules.

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> Suppose you're in a restaurant and the menu lists the price for some
> dishes as $0.00. You'd probably order it right away, and tell all your
> friend that yoou've foound a place with free food. But I'm pretty sure
> that you wouldn't do the same is the price for some (or all) dishes was
> simply omitted from the list.

That usually means that it's very expensive!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

default value in datetime column

hi, i was wondering how to set default value in the datetime column of the database so that it will enter current date and time if one is not provided when a row is populated. is there a store procedure to do this? or built-in function?

mp

If you go to Modify view of the table in either Enterprise Manager or SQL Server Management Studio and select that database field, there should be a property where you can specify the default value. Enter GETDATE() as the default value.|||

There are 2 ways to add default value as Today's Date

1) Using QueryAnalyzer

alter table <TableName>
add <ColumnName> DateTime default getdate()

//This will add one column with Default value as Today's date. If you want to add to existing column...2)

2) go to Enterprise Manager, ... , select your table, Right click, go to Design Table and open...

place the mouse cusor in which column you want to put default value, When you place the mouse cursor bottom of this you will see Columns Tab-->Default value TextBox put getdate() and Save . Now insert any row it will automatically enters to days date

|||

thanks, just what i needed!

Happy New Year!!!

mp

default value for todays date and time

I have a column 'date and time', of type datetime.
I'm using MS VB Express - and am trying to enter a default value for
the column of today's date and time.
I cant find anything to enter in the 'default' property of the column
that will return todays date and time.
please help.
gary.
try either the function getdate() or CURRENT_TIMESTAMP
<garyusenet@.myway.com> wrote in message
news:1130144997.367325.64910@.f14g2000cwb.googlegro ups.com...
>I have a column 'date and time', of type datetime.
>
> I'm using MS VB Express - and am trying to enter a default value for
> the column of today's date and time.
>
> I cant find anything to enter in the 'default' property of the column
> that will return todays date and time.
>
> please help.
>
> gary.
>

default value for todays date and time

I have a column 'date and time', of type datetime.
I'm using MS VB Express - and am trying to enter a default value for
the column of today's date and time.
I cant find anything to enter in the 'default' property of the column
that will return todays date and time.
please help.
gary.try either the function getdate() or CURRENT_TIMESTAMP
<garyusenet@.myway.com> wrote in message
news:1130144997.367325.64910@.f14g2000cwb.googlegroups.com...
>I have a column 'date and time', of type datetime.
>
> I'm using MS VB Express - and am trying to enter a default value for
> the column of today's date and time.
>
> I cant find anything to enter in the 'default' property of the column
> that will return todays date and time.
>
> please help.
>
> gary.
>

default value for todays date and time

I have a column 'date and time', of type datetime.
I'm using MS VB Express - and am trying to enter a default value for
the column of today's date and time.
I cant find anything to enter in the 'default' property of the column
that will return todays date and time.
please help.
gary.try either the function getdate() or CURRENT_TIMESTAMP
<garyusenet@.myway.com> wrote in message
news:1130144997.367325.64910@.f14g2000cwb.googlegroups.com...
>I have a column 'date and time', of type datetime.
>
> I'm using MS VB Express - and am trying to enter a default value for
> the column of today's date and time.
>
> I cant find anything to enter in the 'default' property of the column
> that will return todays date and time.
>
> please help.
>
> gary.
>

Default Value

Hi!
I have the following sp:
ALTER PROCEDURE dbo.[Buscar Clientes]
(
@.Begin As DateTime = Null,
@.End As DateTime = Null
)
In VS 2003, If i dont enter any value in the parameters, shouldnt both be
Null?
If i dont enter any value for the parameters i get the following error when
i use the Fill method: "String was not recognized as a valid datetime"
When i look into the Parameters in my DataAdpter, the value field is empty.
Can anyboy help me out?
Thanks,
BRuno N> In VS 2003, If i dont enter any value in the parameters, shouldnt both be
> Null?
YEs, they are NULL as far as T-SQL is concerned.

> If i dont enter any value for the parameters i get the following error
when
> i use the Fill method: "String was not recognized as a valid datetime"
I assume this stored procedure then returns a resultset, and this is what
you are using for Fill. Your C# or VB.Net code needs to deal with DBNull...
or, you need to change your SELECT statement so that IT deals with the NULL
values appropriately.
In other words, your error is not because your date is NULL within SQL
Server, your error is because your .NET code does not deal with it
correctly.|||Thank you!
I will look into my code
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:%23Z5g3pkLFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I have the following sp:
> ALTER PROCEDURE dbo.[Buscar Clientes]
> (
> @.Begin As DateTime = Null,
> @.End As DateTime = Null
> )
> In VS 2003, If i dont enter any value in the parameters, shouldnt both be
> Null?
> If i dont enter any value for the parameters i get the following error
> when i use the Fill method: "String was not recognized as a valid
> datetime"
> When i look into the Parameters in my DataAdpter, the value field is
> empty.
>
> Can anyboy help me out?
> Thanks,
> BRuno N
>|||Bruno,
You can assign DBNull.Value to the parameter value. You can also not append
the parameters to the collection at all, ADO.NET use named parameters when
using sqlClient data provider.
AMB
"Bruno N" wrote:

> Thank you!
> I will look into my code
> "Bruno N" <nylren@.hotmail.com> escreveu na mensagem
> news:%23Z5g3pkLFHA.2604@.TK2MSFTNGP10.phx.gbl...
>
>

default sql server name and password

i want to know how to get default sql server name and password. os that i dont had to ask the user to enter his sql server name and passwordWhat do you mean by default server name and password ? There is no default password as you define it during the installation process. The servername of SQL Server is usally if you don′t specify any named instance, the servername itself.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

i think you did not picked my question.i want that name of sql server used in the connection string like

user id=sa; password=;inittialcatalog=; datasource="local" or etc,

i want to know this name used in the datasource property whether it is instance name or default(instace name not specified).

|||

Hi,

this either the name of the server or the instance name (depending if you installed a default instance, then its only the server name or a named instance, then its of the following syntax: MachineName\InstanceName). If you have choosen another port than 1433 you either have to start SQL Server Browser to have this service redirect the request to the non default port, or specify it with your connection string that you have the following syntax: MachineName\InstanceName,Port OR MachineName,Port.

Sample: DATA Source=MyServer\SomeNamedInstance

For more informations pick a connectionstring from www.connectionstrings.com

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de