can't add new rows with default values in access

[dennist]

In ADO I had no trouble doing the following: My Access
tables would usually have autonumbering ID fields as the
key. Other values were required. Some had default
values. Using the dataforms, or grids or code, when I
added a row I wouldn't put in a value for those fields
with default values. ADO and visual basic 6 took care of
it. The row would update without a problem.

Now, using dataforms, I cannot add a row. Here is the
layout of the tables:

The five fields for DateType are:
ID autonumber key field
DateType text required no default value
CreateDate Date/Time GeneralDate required default value =
Now
ChangeDate Date/Time GeneralDate required default value =
Now
Active Yes/No required default value = Yes


If I use a grid with the dataform, fill in the field that
is required but which does not have a default value, the
autonumber works but when I click update I get the
following error:
grid: The field 'DateType.CreateDate' cannot contain a
Null value because the required property for this field
is set to true. enter a value in this field.

Individual text boxes: One thing I don't like is when you
press 'Add' button the values for the first row remain in
the text boxes. Second dislike: If I delete contents of
text boxes with changedate and createdate, the original
values just pop up again. The default values are now(),
but the values that pop up are the values for the first
row. When I click update I get the error message
Concurrency violation: The UpdateCommand affected 0
records.

How to I update a table with default values without
having to put in values for those columns. That's the
whole idea of default values. Second, with individual
text boxes, how can I get blank fields when I press adds?

I thought ado.net was supposed to make things easier for
us. But my experience so far is that things that I
learned to do in a few minutes in vb6 and ado I can't
learn to do at all in ado.net.

If I get this question answered, I have further
questions. If not, I'll just use ado and record sets.
It's not as if I haven't read. I bought a number of
books - visual basic.net for microsoft access databases,
ado.net step by step, ado.net core reference, vb.net step
by step, vb.net core reference and three more. I'm not a
programming wizard, but I'm reasonably bright and am
interested in the problems I have to solve.

Any help will be appreciated.

dennist

 

[Neil McKechnie]

Agreed, ADO.Net has taken some retrograde steps relative
to ADO2.x. There are a number of things which ADO does
without you asking for it which ADO.Net doesn't. In many
cases this makes it safer, as you don't get connections
created for you when you didn't want them, causing program
logic errors. However, in this case it makes things more
difficult.

The main problem here is that the default queries
generated for a data adaptor do not take into
consideration the fact that fields may have defaults
allocated for them and therefore the InsertCommand doesn't
need to bother about those fields. A way around this is
to change the commandtext for the InsertCommand so that it
only inserts the values which are not defaulted.

ADO.Net provides more flexibility than ADO by simplifying
the objects provided. You can do more with ADO.Net with
greater reliability, but it means that you have to write
more code in some cases.

Hope this helps,

Neil.

 

[dennist]

-----Original Message-----
Agreed, ADO.Net has taken some retrograde steps relative
to ADO2.x. There are a number of things which ADO does
without you asking for it which ADO.Net doesn't. In many
cases this makes it safer, as you don't get connections
created for you when you didn't want them, causing program
logic errors. However, in this case it makes things more
difficult.

The main problem here is that the default queries
generated for a data adaptor do not take into
consideration the fact that fields may have defaults
allocated for them and therefore the InsertCommand doesn't
need to bother about those fields. A way around this is
to change the commandtext for the InsertCommand so that it
only inserts the values which are not defaulted.

ADO.Net provides more flexibility than ADO by simplifying
the objects provided. You can do more with ADO.Net with
greater reliability, but it means that you have to write
more code in some cases.

Hope this helps,

Neil.


.

 

[Steven Bras [MS]]

This article should be of assistance:

815629 HOW TO: Retrieve the Identity Value While Inserting Records into
Access
http://support.microsoft.com/?id=815629

Hope it helps!

Steven Bras, MCSD
Microsoft Developer Support/Data Access Technologies

This posting is provided "AS IS" with no warranties, and confers no rights.

Microsoft Security Announcement: Have you installed the patch for Microsoft
Security Bulletin MS03-026?  If not Microsoft strongly advises you to
review the information at the following link regarding Microsoft Security
Bulletin MS03-026
http://www.microsoft.com/security/security_bulletins/ms03-026.asp and/or to
visit Windows Update at http://windowsupdate.microsoft.com to install the
patch. Running the SCAN program from the Windows Update site will help to
insure you are current with all security patches, not just MS03-026.

 

[Severin]

This is because when you call DataSet.Update() you are supplying values for
the columns that are defaulted in the Access database - in this case Nulls
are the values you are supplying.

This is because the dataset reflects the contents of your datagrid, which by
your error message stated below, must contain Null values for those fields.

You are using the autogenerated update/insert statements supplied by the
dataadapter object when you drag and drop from Server explorer, yes? I,
personally, have not once been able to use the autogenerated SQL even once
(the Select command yes, insert and update, no) so my dataadapters rarely
contain anything other than a select command.

Had you written the SQL yourself, and only inserted those fields that
contain useful info, disregarding null values in the SQL completely, then
the db default values would be used instead.

ADO.NET is really a great improvement, you have greater control and
flexibility in data manipulation - but that does come with a price, more
code writing mostly -- but, you can reuse code much easier, so if you get it
right once, it's done for good!!

Severin

 

[dennist]

Severin,

thank you for your answer. Can you help me get it right
once, for the table I described in my inquiry. I'd guess
the code would take you only a couple minutes to write
and it'd boost my development tremendously.

Here's hoping.

dennist

 

[Severin]

Yes I can (sorry for delay, been away from office)

From what I see you have this in the table

[ID] - autogenerated primary Key
[SelectedDate] - user input date value
[CreateDate] - a field that is only ever entered ONCE for the date record
created - default Now()
[ChangeDate] - a field that is changed every time record is edited - default
Now()
[Active] - a boolean field - default Yes (I suggest in Access changing this
to True/False rather than Yes/No)

When someone Creates a record, there is only ONE field you are interested
in, correct?
The user input field [SelectedDate], every other field has default values
that you want to use.

When Someone edits a record there are THREE fields you are interested in
The user input [SelectedDate], the [ChangeDate], and [Active].
You will never change the [ID] (can't) nor change the [CreateDate]
(shouldn't).

You should drag three DataBase Command objects to your form, and set their
connection properties to the connection that uses the database you are
working with -- name the commands something like:
dbInsert
dbUpdate
dbDelete

You already have the dbSelect command built into the DataAdapter

For this, the first thing I suggest is to use the Datagrid, not as an
editing platform, but only as a viewing platform - making the datagrid a
readonly control.

Since there is only one needed field during creation, you can use a
datepicker control (winforms) or a calendar control (webforms) use the user
selection for the creation of a record --

' Once user selects a date they push a button to enter that date
Private Sub Button1_click(sender as object, e as eventargs)
Try
me.dbConnection.Open()
me.dbInsert.CommandText = "INSERT INTO

([SelectedDate])
VALUES(#" & me.DatePicker1.Value &"#);"
me.dbInsert.ExecuteNonQuery
Catch ex as Exception
' Depending on Winform or Webform how to deal with an exception
Finally
me.dbConnection.Close()
End Try
End Sub

If you are using webforms, at this point you must re-retreive the data and
rebind the datagrid to show the newly entered data, winforms should
immediately reflect the new data.

For editing you would need to know which record the user has selected on the
datagrid (readonly still allows users to select a row, and you can use that
selection to determine which record has focus)

I rarely use datagrids, but there is an index of which row is selected, this
index corresponds to the index on the records in the database... Since most
times you would hide the column containing the [ID] field from view, I
retreive the dataset, and set a datarow object that references the index of
the datagrid to get the [ID] field value.

Private Sub EditButton_Click(sender as object, e as eventargs)
Dim pk as Integer
Dim dr as DataRow
Try
dr = dataset.Tables(0).Rows(me.datagrid.SelectedRowIndex)
pk = Cint(dr("ID"))
me.dbConnection.Open()
me.dbUdate.CommandText = "UPDATE

SET "
me.dbUpdate.CommandText &="[SelectedDate]=#" & me.DatePicker1.Value
&"#, "
me.dbUpdate.CommandText &="[ChangeDate]=#" & Now() &"#, "
me.dbUpdate.CommandText &="[Active]=Yes "
me.dbUpdate.CommandText &="WHERE [ID]=" & pk &";"
me.dbUpdate.ExecuteNonQuery
Catch ex as Exception
' Depending on Winform or Webform how to deal with an exception
Finally
me.dbConnection.Close()
End Try
End Sub

Now, you could wrap these code snippets in Functions that return the number
of rows effected like this, this is where code reuse comes in handy, if
there is more than one place you could change this information in your
application, then you only ever have to call this function to make the
changes

Public Function UpdateTable(ByVal selecteddate as Date, ByVal active as
Boolean, ByVal pk as Integer) as Integer
Try
me.dbConnection.Open()
me.dbUdate.CommandText = "UPDATE

SET "
me.dbUpdate.CommandText &="[SelectedDate]=#" & selecteddate &"#, "
me.dbUpdate.CommandText &="[ChangeDate]=#" & Now() &"#, "
me.dbUpdate.CommandText &="[Active]=" & active & " "
me.dbUpdate.CommandText &="WHERE [ID]=" & pk &";"

' Return value is the number of rows effected by ExecuteNonQuery
Return me.dbUpdate.ExecuteNonQuery

Catch ex as Exception
Throw ex
Finally
me.dbConnection.Close()
End Try
End Function

This Function would return a 0 if the update failed, and a 1 if the update
was successful, you test for that like this

If UpdateTable(selecteddate, active, pk) <> 0 Then
' Update successful, go onto next task
Else
' Update unsuccessful, throw an exception
End If

Severin