Dataset Default Values (1 Viewer)

[Matty]

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?

 

[WindSailor]

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.

 

[Matty]

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.

 

[WindSailor]

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.)

 

[Matty]

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.

 

[Matty]

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?

 

[selenau837]

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?

 

[Matty]

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.

 

[selenau837]

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.

 

[selenau837]

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

 

[Matty]

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

 

[selenau837]

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...

 

[Matty]

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...

 

[selenau837]

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.

 

[WindSailor]

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...

 

[WindSailor]

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.?

 

[WindSailor]

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.

 

[Matty]

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.EquipPool)

    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.EquipPool)
    End Sub

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

 

[skea]

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.

 

[Matty]

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.