Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
settinUpShop
Starting Member
28 Posts |
 Posted - 2003-10-13 : 20:50:35
|
| This is such a simple question, but I can't find an answer in Access's help guide, so here goes.I learned a valuable lesson today about the difference in empty strings in a database and null values. I have several optional fields which accept null values, and empty strings. So the empty strings were getting fed into the database. This became a problem when I pulled the data out and created an if statement checking to see if the value was null. If it was null, then I'd go to another field and display that value instead. Problem was that I had these empty strings in there so isnull (in asp) returned false and displayed the empty string. Not the desired result. My data is all in SQL server, but I use access to view it/edit it by linking tables. Null values aren't being displayed to me. So a null value and an empty string value look the same. In Enterprise Manager this is not the case. When trying to decypher the problem it wasn't until I looked at my data in Enterprise manager that i realized that I needed to be passing null values rather than empty strings.Now I"m wondering how I can get the null values to display in Access. If that is possible.Thanks for any help!!! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-13 : 21:09:04
|
| I don't think there is a way, and instead of trying to display bad data, add a constraint to the table that prevents it from being entered in the first place.Something like:ALTER TABLE myTable ADD CONSTRAINT CK_myColCheck CHECK(myCol IS NULL OR myCol>'')That way, if anyone tries to enter an empty string, they'll get an error. The other option is to reverse it and make the column NOT NULL, then use an empty string as a blank value. It's up to you as to which way you want to do it. Usually it's best to avoid nulls as much as possible so that you don't have to contend with three-valued logic (yes, no, and IS NULL). |
 |
|
|
settinUpShop
Starting Member
28 Posts |
Posted - 2003-10-14 : 09:52:24
|
I think I might follow your recommendation to just bag using nulls all together. I also appreciate the constraint suggestions. I just read about contrainsts for the first time reading articles on this site yesterday. So I'm still digesting the concept, although it seems straight forward. Where do you actually define the constraint? Do you put it in a stored procedure?I'm going to go look that up.Thanks for your help!  |
|
|
|
settinUpShop
Starting Member
28 Posts |
Posted - 2003-10-14 : 10:47:28
|
| robvolk,Are there any more indepth articles about the pros vs cons of allowing null values into a database. If I'm going to shift my code around so that nulls aren't allowed, even for optional data, and accept empty strings instead, then that's a pretty big task. I just want to be sure my reasons for doing this are solid.Thanks. |
|
|
|
settinUpShop
Starting Member
28 Posts |
Posted - 2003-10-14 : 12:32:43
|
| I'm also wondering if there is a way using tables linked in Access to a SQL Server database, to add empty values to a field? Nevermind. Found the answer!If not, does that mean you need to convert the null values that Access passes to empty strings in SQL Server?Thanks! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-14 : 22:33:45
|
| The best place to read up on constraints, and all other things SQL Server, is Books Online. Look under the entries for "CREATE TABLE" and "ALTER TABLE", and search for "constraints" too. There are many different kinds of constraints (check, foreign key, primary key, not null, default, etc.) and you'll almost certainly use all of them in a well-designed database. There are also plenty of articles here on SQL Team that cover the basics of table and database design. |
|
|
|
|
|
|
|
|
displaying nulls