Database Help

[kmercer46]

I am working with multiple forms using an Access Database, VB.Net,
DataSet and OLEDB. Bacically the Add Member form is form 2, not the
opening form which is the Menu. If the Add Member form was the opening
form then I am able to add a new record, but for some reason I am
having trouble with the usage of mulitple forms and databases.
Imports System.Data

Public Class frmAddMember
Inherits System.Windows.Forms.Form

Dim inc As Integer
Dim MaxRows As Integer
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Private Sub frmAddMember_Load(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles MyBase.Load

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source = C:\TravelAgents.mdb"
con.Open()
sql = "SELECT * FROM Members"

da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "TravelAgents")

con.Close()

End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
If inc <> -1 Then

Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

dsNewRow = ds.Tables("TravelAgents").NewRow()

dsNewRow.Item("Member ID") = txtID.Text
dsNewRow.Item("Gender") = cmbGender.SelectedItem
dsNewRow.Item("Forename") = txtForename.Text
dsNewRow.Item("Surname") = txtSurname.Text
dsNewRow.Item("City") = cmbCity.SelectedItem
dsNewRow.Item("County") = cmbCounty.SelectedItem
dsNewRow.Item("Date of Birth") = txtDateOfBirth.Text
dsNewRow.Item("Phone Number") = txtPhoneNumber.Text

ds.Tables("TravelAgents").Rows.Add(dsNewRow)

da.Update(ds, "TravelAgents")

MsgBox("Sucess!!")

End If
End Sub
End Class

The only code on form1(Menu) is the navigational coding for the Add
button, am I supposed to put some code in when form1 is loading in
regards to the above. The File path is correct and the same code has
been used on another project which loads this form as the opening form
and it works fine, but this is no good to me. Any suggestions would be
very welcome and appreciated. Cheers.

BTW this is the error

An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll
highlighting da.Update(ds, "TravelAgents")

[Lloyd Sheen]

I am not sure what the problem is but you should put a Try ... Catch around
the da.Update(ds, "TravelAgents"). Then in the exeception msgbox the
"Message" property which should give details. If this is not enough check
if there is an InnerMessage. This is sometimes the real problem.

That should give you a hint.

LLoyd Sheen
<km*******@yahoo.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I am working with multiple forms using an Access Database, VB.Net,
DataSet and OLEDB. Bacically the Add Member form is form 2, not the
opening form which is the Menu. If the Add Member form was the opening
form then I am able to add a new record, but for some reason I am
having trouble with the usage of mulitple forms and databases.
Imports System.Data

Public Class frmAddMember
Inherits System.Windows.Forms.Form

Dim inc As Integer
Dim MaxRows As Integer
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Private Sub frmAddMember_Load(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles MyBase.Load

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source = C:\TravelAgents.mdb"
con.Open()
sql = "SELECT * FROM Members"

da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "TravelAgents")

con.Close()

End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
If inc <> -1 Then

Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

dsNewRow = ds.Tables("TravelAgents").NewRow()

dsNewRow.Item("Member ID") = txtID.Text
dsNewRow.Item("Gender") = cmbGender.SelectedItem
dsNewRow.Item("Forename") = txtForename.Text
dsNewRow.Item("Surname") = txtSurname.Text
dsNewRow.Item("City") = cmbCity.SelectedItem
dsNewRow.Item("County") = cmbCounty.SelectedItem
dsNewRow.Item("Date of Birth") = txtDateOfBirth.Text
dsNewRow.Item("Phone Number") = txtPhoneNumber.Text

ds.Tables("TravelAgents").Rows.Add(dsNewRow)

da.Update(ds, "TravelAgents")

MsgBox("Sucess!!")

End If
End Sub
End Class

The only code on form1(Menu) is the navigational coding for the Add
button, am I supposed to put some code in when form1 is loading in
regards to the above. The File path is correct and the same code has
been used on another project which loads this form as the opening form
and it works fine, but this is no good to me. Any suggestions would be
very welcome and appreciated. Cheers.

BTW this is the error

An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll
highlighting da.Update(ds, "TravelAgents")

[Kerry Moorman]

kmercer46,

Does your dataset's Insert command have square brackets around all the
column names that contain spaces, such as [Date of Birth]?

Column names that contain spaces must be enclosed in square brackets, and I
am not sure that the command builder includes them.

Kerry Moorman
"km*******@yahoo.com" wrote:

I am working with multiple forms using an Access Database, VB.Net,
DataSet and OLEDB. Bacically the Add Member form is form 2, not the
opening form which is the Menu. If the Add Member form was the opening
form then I am able to add a new record, but for some reason I am
having trouble with the usage of mulitple forms and databases.
Imports System.Data

Public Class frmAddMember
Inherits System.Windows.Forms.Form

Dim inc As Integer
Dim MaxRows As Integer
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Private Sub frmAddMember_Load(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles MyBase.Load

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source = C:\TravelAgents.mdb"
con.Open()
sql = "SELECT * FROM Members"

da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "TravelAgents")

con.Close()

End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
If inc <> -1 Then

Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

dsNewRow = ds.Tables("TravelAgents").NewRow()

dsNewRow.Item("Member ID") = txtID.Text
dsNewRow.Item("Gender") = cmbGender.SelectedItem
dsNewRow.Item("Forename") = txtForename.Text
dsNewRow.Item("Surname") = txtSurname.Text
dsNewRow.Item("City") = cmbCity.SelectedItem
dsNewRow.Item("County") = cmbCounty.SelectedItem
dsNewRow.Item("Date of Birth") = txtDateOfBirth.Text
dsNewRow.Item("Phone Number") = txtPhoneNumber.Text

ds.Tables("TravelAgents").Rows.Add(dsNewRow)

da.Update(ds, "TravelAgents")

MsgBox("Sucess!!")

End If
End Sub
End Class

The only code on form1(Menu) is the navigational coding for the Add
button, am I supposed to put some code in when form1 is loading in
regards to the above. The File path is correct and the same code has
been used on another project which loads this form as the opening form
and it works fine, but this is no good to me. Any suggestions would be
very welcome and appreciated. Cheers.

BTW this is the error

An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll
highlighting da.Update(ds, "TravelAgents")