Default values in a database?

[Yuliya]

I am working on a Windows form application where a user enters data which is saved in a database. There are several fields in a DataGridView that the user needs to fill out. I need to set a default value for some of the fields, so if the user doesn't specify the value, it would be set to the default.

So when I specified a table in the database, I put a default value in some of the columns (through Visual Studio db editor). I also set up those columns to not allow null values. But when I run my program, if I don't enter a value in those columns, I get an exception that null is not allowed, instead of setting it to the default value.

What am I doing wrong?

Thank you very much!

 

[giadich]

so if the user doesn't specify the value, it would be set to the default.

The key lies in that phrase. How are you updating the db?

Without further info, my guess is it would be best if you create a trigger to enforce default values. This should work regardless whether you supply the data for the column(s) or not when you insert data.

 

[jmcilhinney]

The problem is that null itself is a value, so if you specify null for a column then null will be inserted. It's only if you don't specify anything at all for that column that the default value will be used. For instance, if you do this:

INSERT INTO MyTable (ID, Name) VALUES (1, NULL)

then NULL will be inserted into the Name column, even if it has a default value. On the other hand if you did this:

INSERT INTO MyTable (ID) VALUES (1)

Then you haven't specified anything at all for the Name column so the default value will be used.

If you're using an UPDATE statement to insert every column from your VB code then you need to provide a value for every column. In that case you have to rely on your DataTable and its DataColumns to provide default values. You need to set your DataColumn's DefaultValue property to the same value as the corresponding column's default value in the database. That way, when you add a new row to your DataTable the default values will be added immediately and they will then be inserted into the database as well.

Note that if you generate a typed DataSet from your database in the IDE then the schema will be drawn from the database itself and the DefaultValue properties of your DataColumns will be set to the correct values automatically. If you're using an untyped DataSet or a lone DataTable then it's up to you to set them yourself.

 

[cjard]

(through Visual Studio db editor)

What is the visual studio db editor? Can you provide a screenshot, because I wasnt aware VS had one.

 

[JohnH]

Through DataBase Explorer you can edit database and table definitions. Through Solution Explorer/Data Sources you can edit the schema with Dataset editor.

 

[cjard]

I have a full install of VS2005 and I definitely dont have a window called DataBase Explorer... I think he's referring to the dataset editor, but I want confirmation so that we can start calling things proper names, and hammer home the understanding that DataSet <> database

 

[JohnH]

I have a full install of VS2005 and I definitely dont have a window called DataBase Explorer...

Found this:

In the Visual Studio Express products, this window is named Database Explorer. In all other Visual Studio products, this window is named Server Explorer.

 

[Arg81]

I forgot to reply to this earlier when I saw it.

Basically, as John says. Click View -> Server Explorer and you will have a new pane to the left....

 

[cjard]

Server Explorer is not a db editor. Oracle (a database) guys cannot use that window to edit a database..

cjard introduces the concept of a rhetorical question

 

[Arg81]

cjard introduces the concept of a rhetorical question

hmmm....is Oracle really regarded as a DBMS?

 

[jmcilhinney]

You can create an Oracle connection in the Server Explorer to view your database schema but you can't edit the data, or even view it I'd think. Even Access database don't support more than schema viewing in VS. As far as I'm aware it's only SQL Server that can actually be edited within VS. If the OP added columns in VS then they must be using SQL Server.

 

[cjard]

Guys.. STOP with the conjecture.. WAIT for the OP to answer the questions put to him. We'll go through all this chit chat over editing DBs in VS using Server Explorer or Oracle Developer Tools etc, and then he'll come back and say "Oh. I meant the DataSet Designer"

 

[Yuliya]

Thank you for all your replies and sorry for not replying. I am very new at this, and now I am confused about what I should be using to edit a database.

I have Express Edition of Visual Studio. I used the Database Explorer to create a database schema, in a separate project. Then I added this database as a data source to my existing project. First I set it to Copy if newer, and if I needed to change the original schema, I would go back to the other project. Then I thought I could be more efficient if I could just set it to Never copy, and then make changes in the db schema that's already in my project. So I added another table, through Database Explorer, in the existing project. Then I put a DataGridView of that table, through the Data Sources window, into one of my Windows Forms. Now when I run it, on loading this form, it throws an SQLException saying "An attempt to attach an auto-named database for file path" and then gives the path of my project\bin\Debug\my db.mdf. So what's the correct way for editing a database schema? And what is the difference between doing it in a separate project, vs. within a Windows Forms project, vs. in DataSet Designer, vs. with DataSet wizard?

 

[Yuliya]

How are you updating the db?

Through TableAdapter.Update() method.

 

[Yuliya]

The problem is that null itself is a value, so if you specify null for a column then null will be inserted. It's only if you don't specify anything at all for that column that the default value will be used.

So what happens with a DataGridView? In my Windows Form, I have a DataGridView, which I dragged and dropped from the Data Sources window, from a specific table in my db. It has several columns. If for a given row, the user fills out only some of the columns, are the rest of them set to NULL or to the default values for those columns that are specified in the db schema?