Inserting a Decimal - HELP

Im trying to insert a decimal into SQL 2000 using a stored procedure. I can successfully insert it using a direct T-SQL statement inside the code, but I cant insert it correctly when calling for a stored procedure.

For instance 12.5 will insert as 12.

Here is my SQL Parameter code:
[code]
MyCommand.Parameters.Add(New SqlParameter("@.Num", SqlDbType.Decimal))
MyCommand.Parameters("@.Num").Precision = 5
MyCommand.Parameters("@.Num").Scale = 2
MyCommand.Parameters("@.Num").Value = Convert.ToDecimal(TextBox1.Text)
[/code]

I also declared @.Num to be a decimal in my Stored Procedure.

As you can see I give the Parameter more than enough detail about the data type as well as convert the textbox value to a decimal, but it still rounds the value to an integer like data.

Again using a direct T-SQL statement inside the code works OK, but the stored procedure will not.
ANyone got any ideas why this is happening.Try running Profiler and tracing the transaction. Then you can determine if the precision is being lost when .NET sends the command to SQL Server, or when SQL Server calls the stored procedure. Have you tried not being so specific? Can you post the source code for your stored procedure?

MyCommand.Parameters.Add("@.Num", Convert.ToDecimal(TextBox1.Text))

Insert statement for datetime column fails

I ve a simple table with a column of type datetime. I ve successfully inserted the following values in it,

2006-09-13 18:00:10
2006-09-14 18:00:10
2006-09-15 18:00:10

however, it fails when i try to insert the value 0000-00-00 00:00:00. ie., the following insert statement fails

INSERT INTO TEST VALUES('0000-00-00 00:00:00')

The error thrown is,

Server Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.Obviously SQL Server thinks that '0000-00-00 00:00:00' is not a valid date and I couldn't agree more.
You should use NULL to "mark" a column's value as absent, not some strange (invalid) value|||hi shammat,

thanks for the reply.. whats the least possible valid day i could enter for a datetime column..|||What's wrong with NULL?

Edit:

The lowest value is documented in the manual:
http://msdn2.microsoft.com/en-us/library/ms187819.aspx|||Hi shammat,

I ve migrated an existing table structure and its data from MySQL to SQLServer, in MySQL the column is Not NULL and one of the row has the value 0000-00-00 00:00:00. When i tried to create the same in SQLServer i faced such errors..|||This is something I have seen a lot recently.
Why would anybody declare a column as NOT NULL and then put a totally meaningless value in there, just to comply with the NOT NULL constraint.

That sure does not make any sense to me

I do understand that this was not your decision, I'm just wondering why people do such stupid things|||shammat, very nicely stated, i totally agree

the fact that mysql allows a "zero date" sure detracts from its reputation

the fact that mysql programmers would actually utilize it detracts from them even more|||To be fair against the MySQL users:
I have seen this in an Oracle environment as well, they simply used 1970-01-01 instead...

But then - I have seen it only once with Oracle, whereas I tend to see it more often in the MySQL area|||I've always been in favor of using a NULL when possible to mark data with an unknown or an unknowable value, but many systems can't cope with that due to the programming language being used... Many COBOL variants just don't cope with NULL very gracefully, and a number of "4GL" wannabes have the same problems, although they are dressed up in newer clothes.

In defense of the SQL Server choice for minimum date, that wasn't truly their choice... They had to deal with calendar reformations, and simply picked the earliest date that wasn't likely to have problems for most users. The Julian to Gregorian conversion was messy, it wasn't implemented the same way in many places, and wasn't implemented at the same time everywhere... We take it for granted that only timezones need to be considered to determine when 2006-11-01 will occur because the world has only had a couple of calendars since 1800, and all of those calendars conveniently convert to the Gregorian. This has not been the case throughout history.

-PatP|||And a number of "4GL" wannabes have the same problems, although they are dressed up in newer clothes.Understable, but still not nice :)

In defense of the SQL Server choice for minimum date, that wasn't truly their choice...I find the minimum date to be perfectly fine, as a matter of fact a lot better than allowing 0000-00-00.|||the minimum date is not "perfectly fine"

it may be practicable, but it does introduce another form of "three-valued logic"

for instance, if you assign the minimum date to a date_of_birth column in those instances when you do not know the person's date_of_birth, you cannot simply go blithely ahead and calculate the person's current age

well, technically speaking, you could, but it would be wrong

so using the minimum date is far from "perfect"|||the minimum date is not "perfectly fine"
I didn't mean that the usage of it was fine. I totally agree with you that it is nonsens to use special values for this purpose

I meant the restriction for a minimum date value is acceptable. A valid date as the minimum date is "perfectly fine" compate to 0000-00-00|||I just wonder how much code relies of that date "not being there" as 0000-00-00....|||I just wonder how much code relies of that date "not being there" as 0000-00-00....
exactly

and if you should ever need to move the app to another database platform? code changes!!

nothing more fun than coming in to the office on a sunny saturday afternoon to find all occurrences of 0000-00-00 and replace them with 1970-01-01

whereas if you had used NULL in the first place...

:)