Database

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

I'm using the Jet.Oledb to connect to an Access 2002
database. I have a form that is supposed to load
information from the database, and then return
information to it. It loads the info okay, but will not
update the database correctly. I get the error
message: "An unhandled exception of
type 'System.Data.OleDb.OleDbException' occurred in
system.data.dll." The code is as follows:

' conSchoolInfo = data connection
' adpSchoolInfo = data adapter
' "School Info" = table in database and dataset
' txt***.Text = Textboxes on form

Private Sub GetSchoolInfo() ' Subroutine
that runs when the form is loaded

conSchoolInfo.Open()
adpSchoolInfo.Fill(datSchoolInfo, "SchoolInfo")
conSchoolInfo.Close()

txtName.Text = datSchoolInfo.Tables
("SchoolInfo").Rows(0).Item("Name")
txtCustomerNumber.Text = datSchoolInfo.Tables
("SchoolInfo").Rows(0).Item("CustomerNo")

txtPostAddress1.Text = datSchoolInfo.Tables
("SchoolInfo").Rows(0).Item("PostAddress1")
txtPostAddress2.Text = datSchoolInfo.Tables
("SchoolInfo").Rows(0).Item("PostAddress2")
txtPostCity.Text = datSchoolInfo.Tables
("SchoolInfo").Rows(0).Item("PostCity")
txtPostState.Text = datSchoolInfo.Tables
("SchoolInfo").Rows(0).Item("PostState")
txtPostCountry.Text = datSchoolInfo.Tables
("SchoolInfo").Rows(0).Item("PostCountry")
txtPostZip.Text = datSchoolInfo.Tables
("SchoolInfo").Rows(0).Item("PostZip")
End Sub

Private Sub SaveSchoolInfo() ' Subroutine
that runs when the form is closed

adpSchoolInfo.Fill(datSchoolInfo, "SchoolInfo")
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item
("Name") = txtName.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item
("CustomerNo") = txtCustomerNumber.Text

datSchoolInfo.Tables("SchoolInfo").Rows(0).Item
("PostAddress1") = txtPostAddress1.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item
("PostAddress2") = txtPostAddress2.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item
("PostCity") = txtPostCity.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item
("PostState") = txtPostState.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item
("PostCountry") = txtPostCountry.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item
("PostZip") = txtPostZip.Text

conSchoolInfo.Open()
adpSchoolInfo.Update(datSchoolInfo)
conSchoolInfo.Close()
End Sub
 
Firstly you should wrap your routine with a try...catch block and have a
look at the underlying exception

Second - you will probably find that you are trying to insert badly
formatted information. Null values etc

Third have a look at the databinding concepts you will find that it will
save you lots of time

Cheers

Jody
MCSD.NET
 
Robert:

Try this:

Dim conSchoolInfo As OleDb.OleDbConnection = New OleDb.OleDbConnection
Dim adpSchoolInfo As New OleDb.OleDbDataAdapter
Dim datSchoolInfo As Data.DataSet

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

End Sub



Private Sub GetSchoolInfo() ' Subroutine that runs when the form is loaded

conSchoolInfo.Open()
adpSchoolInfo.Fill(datSchoolInfo, "SchoolInfo")
conSchoolInfo.Close()

TxtName.Text = datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("Name")
txtCustomerNumber.Text =
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("CustomerNo")
txtPostAddress1.Text =
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostAddress1")
txtPostAddress2.Text =
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostAddress2")
txtPostCity.Text =
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostCity")
txtPostState.Text =
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostState")
txtPostCountry.Text =
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostCountry")
txtPostZip.Text = datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostZip")

End Sub

Private Sub SaveSchoolInfo()

adpSchoolInfo.Fill(datSchoolInfo, "SchoolInfo")
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("Name") = TxtName.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("CustomerNo") =
txtCustomerNumber.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostAddress1") =
txtPostAddress1.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostAddress2") =
txtPostAddress2.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostCity") =
txtPostCity.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostState") =
txtPostState.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostCountry") =
txtPostCountry.Text
datSchoolInfo.Tables("SchoolInfo").Rows(0).Item("PostZip") = txtPostZip.Text


adpSchoolInfo.Update(datSchoolInfo)


End Sub
 
Back
Top