473,322 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Problem Adding new record to a database

I'm using VB .Net 2005 with Access 2003 for the database. The database has
two tables: tblContact (ContactNum (k), lastname, firstname, phone, email,
compNum) and tblCompany (CompNun (k), CompName, address, city, state, zip,
compNum)

There are a unumber of contact at each company. I have created a Windows
form where I can select a contact, all of the information about the contact
(tblContact), and the information about the (tblCompany) appears and can be
edited.

If I try to add a new contact, at a new company; the contact information is
saved to tblContact, but the company is not saved. Edit can be made to any
field for either table, but new records cannot be added to the company table.

The code is:

Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles TblContactsBindingNavigatorSaveItem.Click
'save, update records
Me.Validate()
Me.TblCompanyBindingSource.EndEdit()
Me.TblCompanyTableAdapter.Update(Me.DsContactData. tblCoAddress)

Me.TblContactBindingSource.EndEdit()
Me.TblContactTableAdapter.Update(Me.DsContactData. tblContact)
End Sub

When I run the program no errors appear, but nothing is saved to the
tblCompany table. Any help will be appreciated.

--
Tony
Sep 20 '06 #1
8 2329
Honestly I would not use Adapter.Update() to write your update, insert
and delete statements for you. In my opinion that's kind of a lazy way
to write programs.

My solution to this is to use the Oledb.Command object and write insert
and update statements yourself. When the user adds a new company, run
the insert, then let them add new contacts for that company once the
database has returned the Primary Key for the Company.

Izzy
Tony A. wrote:
I'm using VB .Net 2005 with Access 2003 for the database. The database has
two tables: tblContact (ContactNum (k), lastname, firstname, phone, email,
compNum) and tblCompany (CompNun (k), CompName, address, city, state, zip,
compNum)

There are a unumber of contact at each company. I have created a Windows
form where I can select a contact, all of the information about the contact
(tblContact), and the information about the (tblCompany) appears and can be
edited.

If I try to add a new contact, at a new company; the contact information is
saved to tblContact, but the company is not saved. Edit can be made to any
field for either table, but new records cannot be added to the company table.

The code is:

Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles TblContactsBindingNavigatorSaveItem.Click
'save, update records
Me.Validate()
Me.TblCompanyBindingSource.EndEdit()
Me.TblCompanyTableAdapter.Update(Me.DsContactData. tblCoAddress)

Me.TblContactBindingSource.EndEdit()
Me.TblContactTableAdapter.Update(Me.DsContactData. tblContact)
End Sub

When I run the program no errors appear, but nothing is saved to the
tblCompany table. Any help will be appreciated.

--
Tony
Sep 20 '06 #2
Thanks Izzy, I was looking for the lazy way out, but if it's not going to
work I'll have to do things the right way.
--
Tony
"Izzy" wrote:
Honestly I would not use Adapter.Update() to write your update, insert
and delete statements for you. In my opinion that's kind of a lazy way
to write programs.

My solution to this is to use the Oledb.Command object and write insert
and update statements yourself. When the user adds a new company, run
the insert, then let them add new contacts for that company once the
database has returned the Primary Key for the Company.

Izzy
Tony A. wrote:
I'm using VB .Net 2005 with Access 2003 for the database. The database has
two tables: tblContact (ContactNum (k), lastname, firstname, phone, email,
compNum) and tblCompany (CompNun (k), CompName, address, city, state, zip,
compNum)

There are a unumber of contact at each company. I have created a Windows
form where I can select a contact, all of the information about the contact
(tblContact), and the information about the (tblCompany) appears and can be
edited.

If I try to add a new contact, at a new company; the contact information is
saved to tblContact, but the company is not saved. Edit can be made to any
field for either table, but new records cannot be added to the company table.

The code is:

Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles TblContactsBindingNavigatorSaveItem.Click
'save, update records
Me.Validate()
Me.TblCompanyBindingSource.EndEdit()
Me.TblCompanyTableAdapter.Update(Me.DsContactData. tblCoAddress)

Me.TblContactBindingSource.EndEdit()
Me.TblContactTableAdapter.Update(Me.DsContactData. tblContact)
End Sub

When I run the program no errors appear, but nothing is saved to the
tblCompany table. Any help will be appreciated.

--
Tony

Sep 20 '06 #3
Tony,

You say it is not added, is there not any error?
(Because than there are most probably changes done in the row)

Cor

"Tony A." <To***@discussions.microsoft.comschreef in bericht
news:D1**********************************@microsof t.com...
Thanks Izzy, I was looking for the lazy way out, but if it's not going to
work I'll have to do things the right way.
--
Tony
"Izzy" wrote:
>Honestly I would not use Adapter.Update() to write your update, insert
and delete statements for you. In my opinion that's kind of a lazy way
to write programs.

My solution to this is to use the Oledb.Command object and write insert
and update statements yourself. When the user adds a new company, run
the insert, then let them add new contacts for that company once the
database has returned the Primary Key for the Company.

Izzy
Tony A. wrote:
I'm using VB .Net 2005 with Access 2003 for the database. The database
has
two tables: tblContact (ContactNum (k), lastname, firstname, phone,
email,
compNum) and tblCompany (CompNun (k), CompName, address, city, state,
zip,
compNum)

There are a unumber of contact at each company. I have created a
Windows
form where I can select a contact, all of the information about the
contact
(tblContact), and the information about the (tblCompany) appears and
can be
edited.

If I try to add a new contact, at a new company; the contact
information is
saved to tblContact, but the company is not saved. Edit can be made to
any
field for either table, but new records cannot be added to the company
table.

The code is:

Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles TblContactsBindingNavigatorSaveItem.Click
'save, update records
Me.Validate()
Me.TblCompanyBindingSource.EndEdit()
Me.TblCompanyTableAdapter.Update(Me.DsContactData. tblCoAddress)

Me.TblContactBindingSource.EndEdit()
Me.TblContactTableAdapter.Update(Me.DsContactData. tblContact)
End Sub

When I run the program no errors appear, but nothing is saved to the
tblCompany table. Any help will be appreciated.

--
Tony


Sep 21 '06 #4
On Wed, 20 Sep 2006 13:38:02 -0700, Tony A. <To***@discussions.microsoft.comwrote:
>I'm using VB .Net 2005 with Access 2003 for the database. The database has
two tables: tblContact (ContactNum (k), lastname, firstname, phone, email,
compNum) and tblCompany (CompNun (k), CompName, address, city, state, zip,
compNum)

There are a unumber of contact at each company. I have created a Windows
form where I can select a contact, all of the information about the contact
(tblContact), and the information about the (tblCompany) appears and can be
edited.

If I try to add a new contact, at a new company; the contact information is
saved to tblContact, but the company is not saved. Edit can be made to any
field for either table, but new records cannot be added to the company table.

The code is:

Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles TblContactsBindingNavigatorSaveItem.Click
'save, update records
Me.Validate()
Me.TblCompanyBindingSource.EndEdit()
Me.TblCompanyTableAdapter.Update(Me.DsContactData. tblCoAddress)

Me.TblContactBindingSource.EndEdit()
Me.TblContactTableAdapter.Update(Me.DsContactData. tblContact)
End Sub

When I run the program no errors appear, but nothing is saved to the
tblCompany table. Any help will be appreciated.
Build a release. Run the release executable, make some changes and then exit. Rerun
the release executable and see if the changes are shown.

Gene
Sep 21 '06 #5
You are correct, there are no error messages. When I open the database the
contact table has the new record added, but the company does not. I have
switched between having the company table updated first then the contact
table, and vise versa.
--
Tony
"Cor Ligthert [MVP]" wrote:
Tony,

You say it is not added, is there not any error?
(Because than there are most probably changes done in the row)

Cor

"Tony A." <To***@discussions.microsoft.comschreef in bericht
news:D1**********************************@microsof t.com...
Thanks Izzy, I was looking for the lazy way out, but if it's not going to
work I'll have to do things the right way.
--
Tony
"Izzy" wrote:
Honestly I would not use Adapter.Update() to write your update, insert
and delete statements for you. In my opinion that's kind of a lazy way
to write programs.

My solution to this is to use the Oledb.Command object and write insert
and update statements yourself. When the user adds a new company, run
the insert, then let them add new contacts for that company once the
database has returned the Primary Key for the Company.

Izzy
Tony A. wrote:
I'm using VB .Net 2005 with Access 2003 for the database. The database
has
two tables: tblContact (ContactNum (k), lastname, firstname, phone,
email,
compNum) and tblCompany (CompNun (k), CompName, address, city, state,
zip,
compNum)

There are a unumber of contact at each company. I have created a
Windows
form where I can select a contact, all of the information about the
contact
(tblContact), and the information about the (tblCompany) appears and
can be
edited.

If I try to add a new contact, at a new company; the contact
information is
saved to tblContact, but the company is not saved. Edit can be made to
any
field for either table, but new records cannot be added to the company
table.

The code is:

Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles TblContactsBindingNavigatorSaveItem.Click
'save, update records
Me.Validate()
Me.TblCompanyBindingSource.EndEdit()
Me.TblCompanyTableAdapter.Update(Me.DsContactData. tblCoAddress)

Me.TblContactBindingSource.EndEdit()
Me.TblContactTableAdapter.Update(Me.DsContactData. tblContact)
End Sub

When I run the program no errors appear, but nothing is saved to the
tblCompany table. Any help will be appreciated.

--
Tony



Sep 21 '06 #6
Tony,

Two ideas: First, is there any referential integrity defined in the
Access database on the foreign key CompNum? If so, I'd actually expect
the reverse (no new contact insert until the company was inserted), but
I have experienced Access get the integrity relationship backwards once
before.

When using table adapter update, I've been more successful with
extracting a changed dataset as in the following approach:
Dim dschg As DataSet
dschg = ds.GetChanges()

If Not dschg Is Nothing Then
da.Update(dschg, SqlTable)
End If

Actually, a third idea: make sure there are no nulls or type-illegal
data in the tblCompany rows.

Regards,
Keith

Tony A. wrote:
I'm using VB .Net 2005 with Access 2003 for the database. The database has
two tables: tblContact (ContactNum (k), lastname, firstname, phone, email,
compNum) and tblCompany (CompNun (k), CompName, address, city, state, zip,
compNum)

There are a unumber of contact at each company. I have created a Windows
form where I can select a contact, all of the information about the contact
(tblContact), and the information about the (tblCompany) appears and can be
edited.

If I try to add a new contact, at a new company; the contact information is
saved to tblContact, but the company is not saved. Edit can be made to any
field for either table, but new records cannot be added to the company table.

The code is:

Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles TblContactsBindingNavigatorSaveItem.Click
'save, update records
Me.Validate()
Me.TblCompanyBindingSource.EndEdit()
Me.TblCompanyTableAdapter.Update(Me.DsContactData. tblCoAddress)

Me.TblContactBindingSource.EndEdit()
Me.TblContactTableAdapter.Update(Me.DsContactData. tblContact)
End Sub

When I run the program no errors appear, but nothing is saved to the
tblCompany table. Any help will be appreciated.

--
Tony
Sep 21 '06 #7
Than you have to check if the rows have really changes before the update.

If dt.haschanges then

or whatever like that.

Cor

"Tony A." <To***@discussions.microsoft.comschreef in bericht
news:8B**********************************@microsof t.com...
You are correct, there are no error messages. When I open the database
the
contact table has the new record added, but the company does not. I have
switched between having the company table updated first then the contact
table, and vise versa.
--
Tony
"Cor Ligthert [MVP]" wrote:
>Tony,

You say it is not added, is there not any error?
(Because than there are most probably changes done in the row)

Cor

"Tony A." <To***@discussions.microsoft.comschreef in bericht
news:D1**********************************@microso ft.com...
Thanks Izzy, I was looking for the lazy way out, but if it's not going
to
work I'll have to do things the right way.
--
Tony
"Izzy" wrote:

Honestly I would not use Adapter.Update() to write your update, insert
and delete statements for you. In my opinion that's kind of a lazy way
to write programs.

My solution to this is to use the Oledb.Command object and write
insert
and update statements yourself. When the user adds a new company, run
the insert, then let them add new contacts for that company once the
database has returned the Primary Key for the Company.

Izzy
Tony A. wrote:
I'm using VB .Net 2005 with Access 2003 for the database. The
database
has
two tables: tblContact (ContactNum (k), lastname, firstname, phone,
email,
compNum) and tblCompany (CompNun (k), CompName, address, city,
state,
zip,
compNum)

There are a unumber of contact at each company. I have created a
Windows
form where I can select a contact, all of the information about the
contact
(tblContact), and the information about the (tblCompany) appears and
can be
edited.

If I try to add a new contact, at a new company; the contact
information is
saved to tblContact, but the company is not saved. Edit can be made
to
any
field for either table, but new records cannot be added to the
company
table.

The code is:

Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles TblContactsBindingNavigatorSaveItem.Click
'save, update records
Me.Validate()
Me.TblCompanyBindingSource.EndEdit()

Me.TblCompanyTableAdapter.Update(Me.DsContactData. tblCoAddress)

Me.TblContactBindingSource.EndEdit()

Me.TblContactTableAdapter.Update(Me.DsContactData. tblContact)
End Sub

When I run the program no errors appear, but nothing is saved to the
tblCompany table. Any help will be appreciated.

--
Tony




Sep 22 '06 #8
K,

Be aware that in this approach the acceptchanges should be used on the
original table. The getchanges gives a copy. I tell that because it was a
mistake that I made the first time I was using this.

Cor

"kgerritsen" <ki***@drexel.eduschreef in bericht
news:11**********************@m7g2000cwm.googlegro ups.com...
Tony,

Two ideas: First, is there any referential integrity defined in the
Access database on the foreign key CompNum? If so, I'd actually expect
the reverse (no new contact insert until the company was inserted), but
I have experienced Access get the integrity relationship backwards once
before.

When using table adapter update, I've been more successful with
extracting a changed dataset as in the following approach:
Dim dschg As DataSet
dschg = ds.GetChanges()

If Not dschg Is Nothing Then
da.Update(dschg, SqlTable)
End If

Actually, a third idea: make sure there are no nulls or type-illegal
data in the tblCompany rows.

Regards,
Keith

Tony A. wrote:
>I'm using VB .Net 2005 with Access 2003 for the database. The database
has
two tables: tblContact (ContactNum (k), lastname, firstname, phone,
email,
compNum) and tblCompany (CompNun (k), CompName, address, city, state,
zip,
compNum)

There are a unumber of contact at each company. I have created a Windows
form where I can select a contact, all of the information about the
contact
(tblContact), and the information about the (tblCompany) appears and can
be
edited.

If I try to add a new contact, at a new company; the contact information
is
saved to tblContact, but the company is not saved. Edit can be made to
any
field for either table, but new records cannot be added to the company
table.

The code is:

Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles TblContactsBindingNavigatorSaveItem.Click
'save, update records
Me.Validate()
Me.TblCompanyBindingSource.EndEdit()
Me.TblCompanyTableAdapter.Update(Me.DsContactData. tblCoAddress)

Me.TblContactBindingSource.EndEdit()
Me.TblContactTableAdapter.Update(Me.DsContactData. tblContact)
End Sub

When I run the program no errors appear, but nothing is saved to the
tblCompany table. Any help will be appreciated.

--
Tony

Sep 22 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: VIJAY KUMAR | last post by:
Hi pals, I am using 2 web forms (pages). In first page, i have Datagrid control and on second page i have a hyper link control to the first page and Add value to the data grid/Database. ...
2
by: Douglas Buchanan | last post by:
This is a mystery! Why does the boolean value of a record change without my input. What follows describes the situation and the behavior. Table1: Field1: Text Field2: Yes/No Form1 has two...
7
by: RC | last post by:
When I am adding records to a database and each record is unique by the Serial Number. I would like to step through my code adding records unless the Serial Number already exists in the database. ...
1
by: Terry | last post by:
Hello, Has anyone experienced the following problem following an Upsize from Access 97 to SQL 2000 using the MS Upsize Wizard? Or can anyone see what the problem might be. Before Upsize...
6
by: Ammar | last post by:
Dear All, I'm facing a small problem. I have a portal web site, that contains articles, for each article, the end user can send a comment about the article. The problem is: I the comment length...
9
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
1
by: raaaz | last post by:
Hi dears, I am adding a record in Access database using AddNew method of recordset. here is the code of the page .. <% Set RsLibBuild = Server.CreateObject ("ADODB.Recordset")...
10
by: AdrianGawrys | last post by:
Hi guys, I recently rewrote onClick procedure for Calendar on my form (frmRota). It opens record from tblRotas where field rDate is equal the one selected on Calendar. If such a record doesnt exist...
1
by: Kyosuke18 | last post by:
Hi everyone, I have a problem in deleting a data that is connected on the database.. I tried this code but it shows me an error: Run-time error '-2147217900(80040e14)': Syntax error in string in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.