Add a new record to access database that has default values

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 1031 Views - Last Post: 12 March 2014 - 11:51 AM Rate Topic: -----

#1 JD_FAN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-January 14

Add a new record to access database that has default values

Posted 21 January 2014 - 10:44 PM

I have a access database table with 22 fields in it. Of the 22 fields all but 3 have default values.

Using the OledbAdapter and Connection I want to add a new record to the table by specifing the values for the 3 fields that do not have default values and then only the other fields that I need to change.

But when I run the code as shown here I get an error

"The MyFieldName can not contain a null value because the required property for this field is set to True".

I have double checked the tabke to make sure the fields has a defaultproperty but it appears that VB.Net does not recgonize it.

Any Help would be appreciated.

	Dim m_cnToolCrib As New OleDb.OleDbConnection
	Dim m_daToolCrib As OleDb.OleDbDataAdapter = Nothing
	Dim nID As Long
	Dim m_cbToolCrib As OleDb.OleDbCommandBuilder
	Dim m_dtToolCrib As New DataTable
	Dim sql As String
	Dim ds As New DataSet
	Dim drNewRow As DataRow = m_dtToolCrib.NewRow()
        
        
	sql = "Select [Tool Crib].* From [Tool Crib]"

	m_cnToolCrib.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PathCMDB()

	m_cnToolCrib.Open()

	m_daToolCrib = New OleDb.OleDbDataAdapter(sql, m_cnToolCrib)

	m_cbToolCrib = New OleDb.OleDbCommandBuilder(m_daToolCrib)

	m_cbToolCrib.QuotePrefix = "["

	m_cbToolCrib.QuoteSuffix = "]"

	m_daToolCrib.Fill(m_dtToolCrib)

	drNewRow("Type ID") = (TryCast(cboToolSetupAvailTools.EditValue, myItemData)).ItemData.ToString()
	
	drNewRow("Color") = System.Drawing.Color.Red
	
	drNewRow("Description") = "New Description"
	
	m_dtToolCrib.Rows.Add(drNewRow)
	m_daToolCrib.Update(m_dtToolCrib)

	m_dtToolCrib.Dispose()
	m_daToolCrib.Dispose()
	
	m_cnToolCrib.Close()



Is This A Good Question/Topic? 0
  • +

Replies To: Add a new record to access database that has default values

#2 Minimalist  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 142
  • Joined: 13-November 13

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 05:54 AM

You need to set the field property to false.

"The MyFieldName can not contain a null value because the required property for this field is set to True".

Hier is an example where I create a table and set the atrtributes property of the description column to allow Nothing to be input

 tbl1.Name = "Classes"
            tbl1.Columns.Append("Form", ADOX.DataTypeEnum.adVarWChar, 3)

            With col
                .Name = "Description"
                .Type = ADOX.DataTypeEnum.adVarWChar
                .Attributes = ColumnAttributesEnum.adColNullable
            End With
            tbl1.Columns.Append(col)

Was This Post Helpful? 0
  • +
  • -

#3 JD_FAN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-January 14

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 06:43 AM

The table is already create. I do not create the tables or the fields.
This all worked in VB6 using DAO but I am trying to convert code to VB.NET.

The table and the fields are already created and when I look at the database in Acess all the fields exxept 3 have the default value set.
Was This Post Helpful? 0
  • +
  • -

#4 Minimalist  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 142
  • Joined: 13-November 13

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 08:25 AM

Well open the database in access, open the table you need, click on the column header and find in the ribbon the place where you see type and format you need to change the format to not reqired - it is a tick box.

This post has been edited by Minimalist: 22 January 2014 - 08:26 AM

Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9579
  • View blog
  • Posts: 36,298
  • Joined: 12-June 08

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 08:36 AM

Side note - you need to INSERT a row before you can SELECT it from the database.. all I see there, in your code, is SELECT.
Was This Post Helpful? 0
  • +
  • -

#6 JD_FAN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-January 14

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 12:56 PM

I do not have control over the database it is used by customers. I surelycan not ask every customer to change the database.

Also you are mistaken on the insert staement. The record is addded just fine if I set each of the fields. The reason for having default values is so you only need to change the ones with new values.

Thanks for trying to help
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3838
  • View blog
  • Posts: 13,589
  • Joined: 12-December 12

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 01:13 PM

The DataAdapter.Update method uses DataAdapter.InsertCommand.CommandText to update the database. If you examine this command-text it will include lines like:
YourFieldName = @SomeParameter

probably for all/most fields, including the ones that have defaults. This will use DBNull if no value is supplied, which ignores the default value and causes the error.

You need to remove the three lines (for the fields that already have defaults) from this command-text. You can either use a CommandBuilder to do this or create your own InsertCommand.

OleDbDataAdapter.InsertCommand :MSDN
Was This Post Helpful? 0
  • +
  • -

#8 JD_FAN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-January 14

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 01:22 PM

I think there is one statement you made that I do not understand. You say to remove three lines but in reality I have 3 lines that need to be chnaged and 19 that I want the default values set.

So would I have just the 3 lines I want to update in the command?

This post has been edited by andrewsw: 22 January 2014 - 01:23 PM
Reason for edit:: Removed previous quote

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3838
  • View blog
  • Posts: 13,589
  • Joined: 12-December 12

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 01:24 PM

Yes.. the opposite ;)
Was This Post Helpful? 0
  • +
  • -

#10 JD_FAN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-January 14

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 01:28 PM

Well that makes more sense. I knew there had to be a way to use the default values. Escpecially since I was able to do it in VB^ with DAO.

So I geuess I need to read up and how to use the Update and Insert commands.

Thank You

This post has been edited by andrewsw: 22 January 2014 - 08:35 PM
Reason for edit:: Removed self quote

Was This Post Helpful? 0
  • +
  • -

#11 JD_FAN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-January 14

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 04:59 PM

This is the code I have now. But when I run it I get an error.

Posted Image

        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & PathCMDB() & "';"

        connection = New OleDbConnection(connetionString)

        sql = "insert into [Tool Crib] values('Color','Type ID','Description')"

        Try

            connection.Open()

            oledbAdapter.InsertCommand = New OleDbCommand(sql, connection)

            oledbAdapter.InsertCommand.ExecuteNonQuery()

            MsgBox("Row(s) Inserted !! ")

        Catch ex As Exception

            MsgBox(ex.ToString)

        End Try


I must be missing something. I know the fields do not match because I am only trying to set 3 of them the rest have default values.

Any suggestion from anyone
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3838
  • View blog
  • Posts: 13,589
  • Joined: 12-December 12

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 08:34 PM

sql = "insert into [Tool Crib] values('Color','Type ID','Description')"

This is not a valid insert statement for Access. You need to list the field-names, which I suspect are the values that you have typed. BTW I make every effort not to have spaces in field-names.

INSERT INTO Statement (Microsoft Access SQL)

I suggest that you print-out the current, default, insert statement and use it as a guide. In particular, it will most likely make use of parameters.
Was This Post Helpful? 0
  • +
  • -

#13 JD_FAN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-January 14

Re: Add a new record to access database that has default values

Posted 22 January 2014 - 10:31 PM

Yes I understand about not using spaces in the field names. I wish I had control over the database but I don't.

I will read up some more on the insert statement
Was This Post Helpful? 0
  • +
  • -

#14 JD_FAN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-January 14

Re: Add a new record to access database that has default values

Posted 23 January 2014 - 06:52 AM

andrewsw

Thank you for pointing me in the right direction.

All is well in my worls now.

I manged to implemant the corret sytax for the INSERT and my code now works.

Thanks Again.
Was This Post Helpful? 0
  • +
  • -

#15 JD_FAN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-January 14

Re: Add a new record to access database that has default values

Posted 23 January 2014 - 08:36 AM

ok 1 last thing.

Now that I have the INSERT INTO working with hardcoded values. I want to change it to where I have a grid.

In each row of the grid I store the field name and its value.

What would you suggest? Just looping through the Grid and write Code to create the INSERT INTO string or is there a better way?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2