I'm making my first data-bound form in Visual Studio.NET 2005 and I have my main table made in SQL Server 2000. I have a bit field named Inactive in my table, set to not allow nulls and have a default value of zero. I've then made my dataset in VS, and the default value does not pull through to the dataset. When looking at that field in the dataset, it has its DefaultValue field set to <DBNull>.

Is there a way I can make the default values I set at the table level to automatically be reflected in the dataset?

Well I dont work with SQL Server 2000, but if I remember correctly a bit field is basically a True/False statement.

First I would check your row and see if your defualt value was automatically transposed into your new row. The dataset should be pulling that value what ever it is, if it has a 'null' value then that tells me the default value was not entered in the new row upon creation at the table level.

Also I have used 'False' for the default value for a bit field in the past.

Thanks for the reply.

I've tried running the form to see if the default value will pull through, and it doesn't (I get an error because I've set at table-level to not allow null Bit fields). When I'm in the raw table I can create a new record and the zero gets put into the Inactive field.

I've typed 0 into the DefaultValue property of the field while in Visual Studio's dataset designer and it changes it automatically to False. I then run the form and the default value shows up. I've tried setting the default value to False at table-level but it won't accept it.

If I put the default value in with the dataset designer it works, but it seems redundant. Isn't it good database design to set your defaults at table level? I'm doing it twice now -- at the table and at the dataset. :(

First instinct is that this is a SQL Server 2000 issue...

When you put in false for the default value at the table level it must be 'False' including the hyphens (it works that way in SQL Server 2005 Express - Just started working with it).
Truthfully I don't think it matters which way you do it...

Try allowing null values for that field, it should still pass the default value into that field upon row creation and see what happens. There might also be a property choice for the bit field - to allow True/False or True/False/Niether (forgot what the third one is right now... g...)

The other thing I do is after inserting data or updating the database is to clear the dataset and repopulate it to make sure I am actually seeing the data from the database and not from the dataset.

Sometimes if you have a column name etc. that is a reserved word it will not pull the data from the database, it will give you a 'DBNull' value instead... (which is what thows me because that should be the default system value for a null value for any given column.)

I've deleted that Inactive field and re-made it, calling it InactiveModel (in case Inactive is a reserved word). It allows nulls. I've re-created my dataset using this wizard in VS, and the DefaultValue is still <DBNull>. It seems like I'm missing something obvious, and that's possible since this is one of my first times using Visual Studio 2005. :confused:

Just to eliminate SQL Server as a cause, I've made a database in Access 2000 and put a table into it. It has a primary key field, a text field called ProductName and a Yes/No field called InactiveModel. The default for ProductName is "Hammer" and the default for InactiveModel is 0.

I made a new connection and dataset (using the New Data Source Wizard) for this Access 2000 and took a look at the dataset in Dataset Designer. Both of those default values were set to <DBNull>.

Is there possibly a global setting in Visual Studio that needs to be set to pull through default values?

Just to eliminate SQL Server as a cause, I've made a database in Access 2000 and put a table into it. It has a primary key field, a text field called ProductName and a Yes/No field called InactiveModel. The default for ProductName is "Hammer" and the default for InactiveModel is 0.

I made a new connection and dataset (using the New Data Source Wizard) for this Access 2000 and took a look at the dataset in Dataset Designer. Both of those default values were set to <DBNull>.

Is there possibly a global setting in Visual Studio that needs to be set to pull through default values?

Isn't the default value for a yes no field already false? Why would you have to set the default value?

Okay, I guess in Access it's already False (SQL Server's default is Null). That's why I put a default in the ProductName field as well. That one didn't come through either.

Okay, I guess in Access it's already False (SQL Server's default is Null). That's why I put a default in the ProductName field as well. That one didn't come through either.

Did you use the wizard to set up your dynaset? Did you test the SQL statment when you set it up?

Also, check out the advance features in that wizard and see if there is something there that will help you.

You also have the database in the app folder for that program don't you?

Did you use the wizard to set up your dynaset? Did you test the SQL statment when you set it up?

Also, check out the advance features in that wizard and see if there is something there that will help you.

By dynaset you mean what?

In my Data Sources tab on the right side, I chose Add New Data Source...

I then chose that I wanted to connect to a database, made my connection string by choosing Access ODBC and my .mdb file and then I chose the table I wanted to use. I've previewed my data and it shows the two sample records I put in there when I designed the table in Access. I couldn't find any advanced options anywhere, sorry.

My connection string looks like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\vstest.mdb

By dynaset you mean what?

In my Data Sources tab on the right side, I chose Add New Data Source...

I then chose that I wanted to connect to a database, made my connection string by choosing Access ODBC and my .mdb file and then I chose the table I wanted to use. I've previewed my data and it shows the two sample records I put in there when I designed the table in Access. I couldn't find any advanced options anywhere, sorry.

My connection string looks like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\vstest.mdb

*shurgs*
Never mind then, I have no clue honey....sorry...

It's okay, thanks for trying. I think if making datasources from different types of databases is giving the same result, the problem must be in Visual Studio. I'm still digging to see if there's some setting in VS that isn't set properly...

It's okay, thanks for trying. I think if making datasources from different types of databases is giving the same result, the problem must be in Visual Studio. I'm still digging to see if there's some setting in VS that isn't set properly...

Good luck, and lemme know.

How are you creating your new row in your dataset?
Have you tried it on a DataGrid and see if the results are the same (just bind the Datagrid to your table and create a new row)?

I am not totally up on VS 2005 but for VS 2003 I used the currency manager for creating new rows. (.add new row)

Using VistaDB and using the currency manager I can pull default values for a bit column... (2003).

Interesting. I am a little busy right now but I will look into it more later today.

---------Edit--------
Check your new row after inserting it in your table through your dataset, make sure your new row is in fact being inserted in your table from your dataset... at the table level through your database tools... this should be a database engine issue with nothing to do with a dataset because you are handling it at the database table/row level... dont know right now... I will fire up VS 2005 later tonight

Giving it a default value may be just part of the procedure, maybe you have to enable it somewhere in the column properties...?

Unless you are looking at your new row in your dataset and not from your database... after creating your new row, save it back to your database and then repopulate your dataset and see if your default values are in your new dataset and your column...

When I'm in the raw table I can create a new record and the zero gets put into the Inactive field.
:(

Ok, (I should have caught this earlier... sorry) this tells me that your default values are being inserted into your new row when you work with your database tools, right?

When you create a new row in your dataset do you then update your database, clear and repopulate your dataset?

Have you worked with datasets in Visual Studio before?

Edit-----

The reason I asked because if you create your new row(s) in your dataset, it will never show your default values given in your database because it is disconnected data (you are working with a snapshot taken of your database in a local *cached* memory). It will only show the default values after you update your database with the new rows from your *cached memory* dataset and then you can repopulate your dataset with the new rows and new information (default values).

You open the connection and fill your dataset etc.?

Matty... I am going to have to sincerely apologize!

This would be easy with VS2003, but I am having the same problem with VS2005 with bit fields...
My immediate problem is that VS2005 is not generating the update commands for me on my dataset. In VS2003 you could go back through the InitializeComponent Sub and check the update commands... it seems that I too am missing something obvious.:o

Rick,

Thanks for all the help. I basically haven't done any extra coding -- I'm just using what the wizards are giving me. This is my first time using Datasets in VS, so I'm trying to learn as I go along. Here's what I have:


Private Sub EquipPool_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'DsEquipPool.EquipPool' table. You can move, or remove it, as needed.
Me.EquipPoolTableAdapter.Fill(Me.DsEquipPool.Equip Pool)

End Sub

Private Sub imgSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles imgSave.Click
Me.Validate()
Me.EquipPoolBindingSource.EndEdit()
Me.EquipPoolTableAdapter.Update(Me.DsEquipPool.Equ ipPool)
End Sub


EquipPool is my form name. It has an image called imgSave that I'm using to save the record.

If you perform a query and you get null values returned then it imples that those fields do not contain anything.
I do hardcode my datasets,and i test for null values like this
If TypeOf myDataRow("ColumnName") Is DBNull Then
'myDataRow.ColumnName=0

But,remember that your database column has to allow null values in able for you to insert one.

But shouldn't the default values that I set in my database table pull through to my dataset? I shouldn't have to check for nulls on that field if it defaults to zero/false/unchecked/whatever. The only way a field can be null is if it's null when the record is created. My problem is that the default values aren't being used and eliminating the nulls for me.

Ok... briefly.
I can get my default values pulled in and show in the dataset.
Just by inserting a DataGridView on the form and adding a datasource through the wizard. This will pull all of my default values WHEN I create new rows 'in the raw' using my database tools on my table. So I am not creating a new row on the form.

But... the bit fields that have the default value of 'False', do show False (from what I can tell, the check/boxes are blank) also give an error with something to do with background color in the bit fields. If I elilminate the bit fields I dont get the error and the rest of my default values come through OK. I will have to do some research on this.

I got this (VS 2005 Pro) last week from Amazon, and thought I could just jump in and go... well... silly me :o

Did you get it to go?

Make sure your table has a primary key, or it will not work.
In fact make sure all of your tables have primary keys.

You should be able to make it work by doing the following...

Create new Data Source (go through the wizards - to bind a datasource to your DataGridView etc.)
Right click on your dataset (at the bottom of the page) and choose 'Edit in Dataset Designer'
Click on your TableAdapter (in the middle of the screen - to give it focus), then right click on it again and choose 'Configure'
And follow the prompts.

For the code on your image to save it, try something like this...


Private Sub imgSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles imgSave.Click

Try
With Me
.Validate()
.EquipPoolBindingSource.EndEdit()
.EquipPoolTableAdapter.Update(Me.DsEquipPool.Equip Pool)
.DsEquipPool.Clear
.EquipPoolTableAdapter.Fill(Me.DsEquipPool.EquipPo ol)
End With
Catch ex As Exception
Dim x101 As String
x101 = ex.ToString
MsgBox(x101, MsgBoxStyle.Information)
End Try

End Sub


See what you end up with...

Edit---------

This is a lot different than VS2003...
By default there is 780 lines of code to generate a dataset with update, delete, and insert statements in VS2005 (for a two column table - go figure)...

Rick,

I just tried this, and my default value still doesn't fill in. Maybe I'm missing something:

- All my tables have primary keys
- After re-creating my dataset I configured the table adapter and everything was checked (generate update/delete statements, etc). Was there anything special I needed to do in that configure step?
- I added the Clear and Fill statements to my save event. But when I make a new record on the form (before I click imgSave), my checkbox is still Null.

Just for future reference, why would I need to clear and then re-fill my dataset after each save?

I purchased an ADO.net 2.0 book over the weekend (Microsoft ADO.NET 2.0 Step By Step, Microsoft Press). I've learned a lot from it so far, but there's really nothing in there about doing anything special to get default values into VS (unless you want to create Data Columns in code at run-time). I really appreciate all your help, though. It's just weird that my keys/relationships pull through to VS, but the default values do not. :o

I really dont think the dataset (your form) has ever been designed to pull or store database/table/column default value properties for creating new rows (by design)… so you will have to do it in the run-time.

I was thinking you weren't pulling your default values from the database after inserting a new row... :o I am still doing some research on this (SQL Server Express).

I looked at the properties of the new DataGridView and couldn’t find an option to set default values for any column for a new row… to bad I think it would be a neat feature.

Check this site out; it has a lot of great information, including setting default values in a DataGrid:
http://www.syncfusion.com/FAQ/WindowsForms/Default.aspx

Also I clear and re-fill my dataset after an update just so I know I am viewing current data from the database and not data from my cached memory. And I make every effort to use the ‘Try Catch EndTry’ phrase to trap my errors.

Ok... after throwing this around a little, I *believe* I understand why Microsoft doesn't pull table level default values into the dataset.

It is so a 'Null' value can still be entered into the database instead of the assigned default value for that column at the table level. At least they left that option open so you could still do that and in case you wanted to test for nulls down the road...

Okay, I guess that makes sense. I just wanted to be sure I wasn't missing something (it being my first time using datasets and all). Thanks for all your help/research! :D

No problem...always learning. And sometimes I get to revisit them more than once. <g>

I didn't know I could assign the default values in the datasets designer in VS2005... Thanks!

With VistaDB and TurboDB I could automatically insert my default table values (inserting a new row in the table, not dataset) when the inserted value was *nothing* or null, it simply tested for null values and inserted the default value. I took that for granted.

SQL Server Express doesn't do it that way, and it threw me.

Evidently they see it as a design option (inserting nulls or dbnull) to over-ride the table level default value.

Other ways I could get the 'table level default values' to be inserted correctly into the table...

If I didn't include the column in the dataset (SQL statement - or no insert statement with a DBNull value for the column)
Use a trigger to assign an ID value for related fields in other tables (inserting a new row)

Or simply... the default value assigned at the table level only works if there is absolutely no value inserted into that column when the row is created, otherwise it is simply over-written. <g>

Thanks Matty, you made me look at something I took for granted, and thanks for helping. :)

dsMain.Tables["tbl_Customer_Master"].Columns["iCompanyYearId"].DefaultValue=2007;