Can't set 'real' data type to NULL 3

[Snaggs]

I have a table that has a field called "Discount". The data type of this field is a "real" in SQL 2000. The box at the end is checked to Allow Nulls and the length shows 4, which can't be changed.

When I enter a new row, the Discount is Null. If I set the value, save the row, then come back to the row and delete the value by hightlighting it and pressing the delete key, then try to save it again, I get the following error:

SQL Server Enterprise Manager reports:

"The value you entered is not consistent with the data type or length of the column."

I would think that it would allow nulls since it's checked. I'm not entering a space in it, as the cursor as as far as it can go to the left in column when I move off the record to try and save it. I just click on another column to save the current row.

I don't have any constraints on the table either and the row is not a Primary Key.

Any ideas?
Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[jhall156]

I don't think that's a null when you delete but rather an empty value which would be illegal. If you need to put a null back in there type <ctl>0



JHall

 

[Snaggs]

JHall,

Thanks for the response. Well it worked using the Ctrl-0 on it. It did come back as <Null> after I did what you suggested. Now I have another question.

The Field is bound to a control in Visual BASIC. How can I put a NULL in the table when the user blanks out textbox on the form since it's bound to the field? I don't want to have the user press Ctrl-0 if it's supposed to be Null.

I know this might be beyond the scope of this discussion, but if you have any ideas, I'm open for them.

Thanks, Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[jhall156]

Is there a reason why you can't let it be zero when there is no value? JHall

 

[Snaggs]

I agree with you, however there are instances when we need it to be null or empty or blank, basiclly something other than 0.

Some customers get a discount, others don't. My arguement was if they don't get a discount, then the value should be 0. However, since I'm not the one paying for the system, it needs to accecpt a null or blank, etc.

Thanks again in advance,
Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[jhall156]

I'm gonna ping Terry about this real quick. While I'm doing that, how committed are you to having the real type? You could store the info as a varchar and use conversion in your calculations (you would also have to provide front end validation to prevent alphabetics and such) JHall

 

[Snaggs]

That'll work. I wasn't sure if I would lose any significance by using that type, but I know I can blank out the VarChar.

Thanks again!!! Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[tlbroadbent]

There are two different interfaces involved here. The first is Enterprise Manager which has many deficiencies in my opnion. It is especially deficient when used for editing tables. I rarely use it fot that purpose.

In VB, you can develop a very good interface. While I personally dislike bound controls, VB should be able to properly handle the deletion of the data in the Real column. That is, when the user deletes a discount, the column will become Null rather than Empty String as in Enterprise Manager. I've not tested this but believe it will work properly.

I question the use of Real or Float as a data type in this application. These are approximate data types. Numbers are not stored precisely or exactly as entered. I would think the discount should be stored in an exact numeric data type such as Decimal or Numeric. With these you specify the precision and scale. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Snaggs]

Terry,

Thanks for the information. It's been a LONG time since I've worked with SQL Server. I started the database in Access and then when we got SQL Server for a different application I did an import of the Access Database. SQL Server assigned the 'real' data type based on the schema from the Access database. I guess I assumed too much by figuring that SQL Server would pick the right data type. Evidently it picks something that's 'close'.

Thanks for clarifiying the difference and the approximation thing. I really appreaciate it. I'll try some of your suggestions on Monday when I'm back at the office.

Thanks again!
Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.