newbie problems with null values

S

Stefan

Hello,

Some probs here.

i'm new to programming the msde

all fields in dat_contacten have default value '' and null is not allowed

when i try to add a new one with addnew property of currencymanager

and then push btnbewaar.click

i always get an error column 'namecolumn' does not allow nulls.

When i change my table and allow nulls it works. but all my empty fields are
value null .

How can i fill my empty fields with default value ('') and not with null
values



thanx in advance for the helping people

Stefan



Private Sub frmContacten_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

'backcolor koppelen

Dim mycol As New clsControlBackcolor

mycol.doSet(Me)

command = New SqlCommand("select dat_contacten.* from dat_contacten", cn)

da = New SqlDataAdapter(command)

Try

da.Fill(ds, "gegevens")

da.FillSchema(ds, SchemaType.Source, "gegevens")

ds.Tables("gegevens").Columns("id").AutoIncrementSeed = -1

ds.Tables("gegevens").Columns("id").AutoIncrementStep = -1

dv = ds.Tables("gegevens").DefaultView

cbGegevens = New SqlCommandBuilder(da)

cmGegevens = CType(Me.BindingContext(dv), CurrencyManager)

Call koppel()

Catch ex As Exception

MessageBox.Show(ex.Message & vbCr & ex.Source)

End Try


End Sub



Private Sub koppel()

txtBedrijf.DataBindings.Add(New Binding("text", dv, "bedrijf"))

txtVoornaam.DataBindings.Add(New Binding("text", dv, "voornaam"))

txtNaam.DataBindings.Add(New Binding("text", dv, "naam"))

txtAdres.DataBindings.Add(New Binding("text", dv, "adres"))

txtpostcode.DataBindings.Add(New Binding("text", dv, "postcode"))

cboGemeente.DataBindings.Add(New Binding("selectedvalue", dv, "gemeente"))

txtTelPrive.DataBindings.Add(New Binding("text", dv, "tel_2"))

End Sub



Private Sub btnBewaren_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnBewaren.Click

Try

cmGegevens.EndCurrentEdit()

da.Update(ds.Tables("gegevens"))

Catch ex As Exception

MessageBox.Show(ex.Message & vbCrLf & ex.Source & vbCrLf & ex.StackTrace)

End Try

End Sub
 
K

Ken Tucker [MVP]

Hi,

Set the datacolumns default value.

ds.Tables("gegevens").Columns("namecolumn").DefaultValue = "'

http://msdn.microsoft.com/library/d...ystemDataDataColumnClassDefaultValueTopic.asp

Ken
-----------------
Hello,

Some probs here.

i'm new to programming the msde

all fields in dat_contacten have default value '' and null is not allowed

when i try to add a new one with addnew property of currencymanager

and then push btnbewaar.click

i always get an error column 'namecolumn' does not allow nulls.

When i change my table and allow nulls it works. but all my empty fields are
value null .

How can i fill my empty fields with default value ('') and not with null
values



thanx in advance for the helping people

Stefan



Private Sub frmContacten_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

'backcolor koppelen

Dim mycol As New clsControlBackcolor

mycol.doSet(Me)

command = New SqlCommand("select dat_contacten.* from dat_contacten", cn)

da = New SqlDataAdapter(command)

Try

da.Fill(ds, "gegevens")

da.FillSchema(ds, SchemaType.Source, "gegevens")

ds.Tables("gegevens").Columns("id").AutoIncrementSeed = -1

ds.Tables("gegevens").Columns("id").AutoIncrementStep = -1

dv = ds.Tables("gegevens").DefaultView

cbGegevens = New SqlCommandBuilder(da)

cmGegevens = CType(Me.BindingContext(dv), CurrencyManager)

Call koppel()

Catch ex As Exception

MessageBox.Show(ex.Message & vbCr & ex.Source)

End Try


End Sub



Private Sub koppel()

txtBedrijf.DataBindings.Add(New Binding("text", dv, "bedrijf"))

txtVoornaam.DataBindings.Add(New Binding("text", dv, "voornaam"))

txtNaam.DataBindings.Add(New Binding("text", dv, "naam"))

txtAdres.DataBindings.Add(New Binding("text", dv, "adres"))

txtpostcode.DataBindings.Add(New Binding("text", dv, "postcode"))

cboGemeente.DataBindings.Add(New Binding("selectedvalue", dv, "gemeente"))

txtTelPrive.DataBindings.Add(New Binding("text", dv, "tel_2"))

End Sub



Private Sub btnBewaren_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnBewaren.Click

Try

cmGegevens.EndCurrentEdit()

da.Update(ds.Tables("gegevens"))

Catch ex As Exception

MessageBox.Show(ex.Message & vbCrLf & ex.Source & vbCrLf & ex.StackTrace)

End Try

End Sub
 
S

Stefan

Cor,
I tought this first line of code loads also the default value's into the
dataset scheme. (in my case '')
da.FillSchema(ds, SchemaType.Source, "gegevens")

and when saving(i looked in msdn) when no text provided in a textbox the
default value is taken(here '')
'' stands for an empty string right?
i'm confused
Ken provided me a codewise sollution
but it has to be possible to give a varchar in msde an empty string as
default value right?

thanx for putting your time in my 'stupid' problems

Stefan
 
G

Greg Burns

but it has to be possible to give a varchar in msde an empty string as
default value right?
Click to expand...

Sure...

CREATE TABLE [table1] (
[desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_table1_desc] DEFAULT ('')
)

What are you designing your MSDE tables with? Do you have SQL EM?

Greg
 
S

Stefan

Hello Greg,
i use the sql em
and i use table designer(right click on table>design table)
and my default values for varchar is set to ('')

greetings,

Stefan

Greg Burns said:
but it has to be possible to give a varchar in msde an empty string as
default value right?
Click to expand...

Sure...

CREATE TABLE [table1] (
[desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_table1_desc] DEFAULT ('')
)

What are you designing your MSDE tables with? Do you have SQL EM?

Greg

Stefan said:
Cor,
I tought this first line of code loads also the default value's into the
dataset scheme. (in my case '')
da.FillSchema(ds, SchemaType.Source, "gegevens")

and when saving(i looked in msdn) when no text provided in a textbox the
default value is taken(here '')
'' stands for an empty string right?
i'm confused
Ken provided me a codewise sollution
but it has to be possible to give a varchar in msde an empty string as
default value right?

thanx for putting your time in my 'stupid' problems

Stefan


than
you
http://msdn.microsoft.com/library/d...emdatadatacolumnclassdefaultvaluetopic.aspCor"
Click to expand...
Click to expand...
 
G

Greg Burns

Yes, that is how I would do it to.

That will work, you can test this in EM. Open up the table and create a new
row. You will see it doesn't say <NULL> for that column. You can try and
force it to be null with Ctrl-0 (it shouldn't allow you to).

Not sure I understand why you are asking "but it has to be possible to give
a varchar in msde an empty string a default value right?", if you know how
to do it. :^)

Greg


Stefan said:
Hello Greg,
i use the sql em
and i use table designer(right click on table>design table)
and my default values for varchar is set to ('')

greetings,

Stefan

Greg Burns said:
but it has to be possible to give a varchar in msde an empty string as
default value right?
Click to expand...

Sure...

CREATE TABLE [table1] (
[desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_table1_desc] DEFAULT ('')
)

What are you designing your MSDE tables with? Do you have SQL EM?

Greg

Stefan said:
Cor,
I tought this first line of code loads also the default value's into
the
dataset scheme. (in my case '')
da.FillSchema(ds, SchemaType.Source, "gegevens")

and when saving(i looked in msdn) when no text provided in a textbox the
default value is taken(here '')
'' stands for an empty string right?
i'm confused
Ken provided me a codewise sollution
but it has to be possible to give a varchar in msde an empty string as
default value right?

thanx for putting your time in my 'stupid' problems

Stefan


"Cor Ligthert" <[email protected]> schreef in bericht
Stefan,

What is the problem when your fields are db.null in your datarow?
When you have set that in your database that nulls are not allowed, than
you
can use the datacolumn.defaultvalue (it is not taken from your database)


http://msdn.microsoft.com/library/d...emdatadatacolumnclassdefaultvaluetopic.aspCor"
Click to expand...
Click to expand...
Click to expand...
 
S

Stefan

Hello again Greg,
The problem is that i get an error when i try to add a new record with some
empty textboxes
you can see the code in my first tread
"column 'columnname' does not allow nulls
when i 'put' allow nulls on in my database , i can add new records,but my
default value is not given to my empty fields,but null instead
still confused,
greetz,

Stefan


Greg Burns said:
Yes, that is how I would do it to.

That will work, you can test this in EM. Open up the table and create a new
row. You will see it doesn't say <NULL> for that column. You can try and
force it to be null with Ctrl-0 (it shouldn't allow you to).

Not sure I understand why you are asking "but it has to be possible to give
a varchar in msde an empty string a default value right?", if you know how
to do it. :^)

Greg


Stefan said:
Hello Greg,
i use the sql em
and i use table designer(right click on table>design table)
and my default values for varchar is set to ('')

greetings,

Stefan

Greg Burns said:
but it has to be possible to give a varchar in msde an empty string as
default value right?

Sure...

CREATE TABLE [table1] (
[desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_table1_desc] DEFAULT ('')
)

What are you designing your MSDE tables with? Do you have SQL EM?

Greg

Cor,
I tought this first line of code loads also the default value's into
the
dataset scheme. (in my case '')
da.FillSchema(ds, SchemaType.Source, "gegevens")

and when saving(i looked in msdn) when no text provided in a textbox the
default value is taken(here '')
'' stands for an empty string right?
i'm confused
Ken provided me a codewise sollution
but it has to be possible to give a varchar in msde an empty string as
default value right?

thanx for putting your time in my 'stupid' problems

Stefan


"Cor Ligthert" <[email protected]> schreef in bericht
Stefan,

What is the problem when your fields are db.null in your datarow?
When you have set that in your database that nulls are not allowed, than
you
can use the datacolumn.defaultvalue (it is not taken from your database)
Click to expand...
http://msdn.microsoft.com/library/d...emdatadatacolumnclassdefaultvaluetopic.aspCor"
Click to expand...
Click to expand...
 
G

Greg Burns

I've been looking at the FillSchema method of the DataAapter. I don't think
it will automatically set the DefaultValue property for your DataColumns
based on the database.

It appears it only handles these properties:

AllowDBNull
AutoIncrement
MaxLength
ReadOnly
Unique

Looks like you have to do this manually as Ken suggested.

This has been one of those nagging issues about ADO.NET. It makes you
'design' your database both at the database and locally. When one changes,
you gotta change the other.

Greg


Stefan said:
Hello again Greg,
The problem is that i get an error when i try to add a new record with
some
empty textboxes
you can see the code in my first tread
"column 'columnname' does not allow nulls
when i 'put' allow nulls on in my database , i can add new records,but my
default value is not given to my empty fields,but null instead
still confused,
greetz,

Stefan


Greg Burns said:
Yes, that is how I would do it to.

That will work, you can test this in EM. Open up the table and create a new
row. You will see it doesn't say <NULL> for that column. You can try
and
force it to be null with Ctrl-0 (it shouldn't allow you to).

Not sure I understand why you are asking "but it has to be possible to give
a varchar in msde an empty string a default value right?", if you know
how
to do it. :^)

Greg


Stefan said:
Hello Greg,
i use the sql em
and i use table designer(right click on table>design table)
and my default values for varchar is set to ('')

greetings,

Stefan

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> schreef in bericht
but it has to be possible to give a varchar in msde an empty string as
default value right?

Sure...

CREATE TABLE [table1] (
[desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_table1_desc] DEFAULT ('')
)

What are you designing your MSDE tables with? Do you have SQL EM?

Greg

Cor,
I tought this first line of code loads also the default value's into
the
dataset scheme. (in my case '')
da.FillSchema(ds, SchemaType.Source, "gegevens")

and when saving(i looked in msdn) when no text provided in a
textbox
the
default value is taken(here '')
'' stands for an empty string right?
i'm confused
Ken provided me a codewise sollution
but it has to be possible to give a varchar in msde an empty string as
default value right?

thanx for putting your time in my 'stupid' problems

Stefan


"Cor Ligthert" <[email protected]> schreef in bericht
Stefan,

What is the problem when your fields are db.null in your datarow?
When you have set that in your database that nulls are not allowed,
than
you
can use the datacolumn.defaultvalue (it is not taken from your
database)



http://msdn.microsoft.com/library/d...emdatadatacolumnclassdefaultvaluetopic.aspCor"
Click to expand...
Click to expand...
Click to expand...
 
S

Stefan

thanx for your answers

kind regards,

Stefan

Greg Burns said:
I've been looking at the FillSchema method of the DataAapter. I don't think
it will automatically set the DefaultValue property for your DataColumns
based on the database.

It appears it only handles these properties:

AllowDBNull
AutoIncrement
MaxLength
ReadOnly
Unique

Looks like you have to do this manually as Ken suggested.

This has been one of those nagging issues about ADO.NET. It makes you
'design' your database both at the database and locally. When one changes,
you gotta change the other.

Greg


Stefan said:
Hello again Greg,
The problem is that i get an error when i try to add a new record with
some
empty textboxes
you can see the code in my first tread
"column 'columnname' does not allow nulls
when i 'put' allow nulls on in my database , i can add new records,but my
default value is not given to my empty fields,but null instead
still confused,
greetz,

Stefan


Greg Burns said:
Yes, that is how I would do it to.

That will work, you can test this in EM. Open up the table and create
Click to expand...
a
new
row. You will see it doesn't say <NULL> for that column. You can try
and
force it to be null with Ctrl-0 (it shouldn't allow you to).

Not sure I understand why you are asking "but it has to be possible to give
a varchar in msde an empty string a default value right?", if you know
how
to do it. :^)

Greg


Hello Greg,
i use the sql em
and i use table designer(right click on table>design table)
and my default values for varchar is set to ('')

greetings,

Stefan

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> schreef in bericht
but it has to be possible to give a varchar in msde an empty
Click to expand...
string
as
default value right?

Sure...

CREATE TABLE [table1] (
[desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_table1_desc] DEFAULT ('')
)

What are you designing your MSDE tables with? Do you have SQL EM?

Greg

Cor,
I tought this first line of code loads also the default value's into
the
dataset scheme. (in my case '')
da.FillSchema(ds, SchemaType.Source, "gegevens")

and when saving(i looked in msdn) when no text provided in a
textbox
the
default value is taken(here '')
'' stands for an empty string right?
i'm confused
Ken provided me a codewise sollution
but it has to be possible to give a varchar in msde an empty
Click to expand...
string
as
default value right?

thanx for putting your time in my 'stupid' problems

Stefan


"Cor Ligthert" <[email protected]> schreef in bericht
Stefan,

What is the problem when your fields are db.null in your datarow?
When you have set that in your database that nulls are not allowed,
than
you
can use the datacolumn.defaultvalue (it is not taken from your
database)
Click to expand...
http://msdn.microsoft.com/library/d...emdatadatacolumnclassdefaultvaluetopic.aspCor"
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding a second table to this DataSet. 1
Problem on Combobox textchange event VB.NET 2
MsAccess Database Updation using Dataset 16
How do you start an assembly dynamically with constructors 2
Adding record with controls bound in code 6
Big problems with data binding 3
problems with parsing & format in ConvertEventArgs when field value is DBNULL 1
BUG Different Behavior 1.0 / 1.1 - combobox datasource 1

Top