vb form and access update

[scotttt]

Hi,

Im moving over from VB6 to .net 2003 but things are not going to well

I have an access db and have designed a VB form with about 16 text boxes to fill from the db.

My connection is ok and I can fill the textboxes ok and move through the db rows ok. My problem happens when I try and update the db. When I do this I get the following error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

The following line of code is highlighted: Code:
studentadapter.Update(studentcontacts)

Below is some of my code:
Declaring connection Variables:
Public Class Form1
Inherits System.Windows.Forms.Form
Private studcon As New OleDb.OleDbConnection
Private studentadapter As New OleDb.OleDbDataAdapter
Private studentbuilder As OleDb.OleDbCommandBuilder
Private studentcontacts As New DataTable
Private studid As Integer
Private position As Integer = 0

Assigning the connection variables at form load:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
studcon.ConnectionString = "provider=Microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\Scott\My Documents\Visual Studio Projects\test db\frisby's.mdb"
studcon.Open()
studentadapter = New OleDb.OleDbDataAdapter("SELECT * FROM studenttable", studcon)
studentbuilder = New OleDb.OleDbCommandBuilder(studentadapter)
studentadapter.Fill(studentcontacts)

OleDbDataAdapter1.SelectCommand.Parameters("studentid").Value = studid
Me.showcurrentrecords()

End Sub


My code to update that doesn't work:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
If studentcontacts.Rows.Count <> 0 Then
studentcontacts.Rows(position)("city") = tbcity.Text
studentcontacts.Rows(position)("country") = tbcountry.Text
studentcontacts.Rows(position)("date of birth") = tbdob.Text
studentcontacts.Rows(position)("email") = tbemail.Text
studentcontacts.Rows(position)("emergnum") = tbemernum.Text
studentcontacts.Rows(position)("emergcont") = tbemrgcontactname.Text
studentcontacts.Rows(position)("First Name") = tbfirstname.Text
studentcontacts.Rows(position)("telephone") = tbhomephone.Text
studentcontacts.Rows(position)("studentid") = tbid.Text
studentcontacts.Rows(position)("last Name") = tblastname.Text
studentcontacts.Rows(position)("marketing") = tbmarketing.Text
studentcontacts.Rows(position)("mobile no") = tbmobilephone.Text
studentcontacts.Rows(position)("nationality") = tbnationality.Text
studentcontacts.Rows(position)("notes") = tbnotes.Text
studentcontacts.Rows(position)("postcode") = tbpostcode.Text
studentcontacts.Rows(position)("street") = tbstreet.Text
studentcontacts.Rows(position)("title") = tbtitle.Text
studentcontacts.Rows(position)("town") = tbtown.Text

studentadapter.Update(studentcontacts)


Can anyone help or see where im going wrong? ive been on this all day now. My 2 books VB.net1.1 databases and teach yourself vb.net 2003 are not being to helpfull!!

Thanks

Scott

 

[jmcilhinney]

Thread moved to more appropriate forum.

 

[jmcilhinney]

Put the line that throws the exception into a Try...Catch block and see if you get any more information:

Try
	[size=2] studentadapter.Update(studentcontacts)
Catch ex As Exception
	MessageBox.Show(ex.ToString())
End Try[/size]

 

[scotttt]

Thanks for the responce,

This is the error message i get from the message box.

Syntax error (missing operator) in query expression '((studentID=? AND ((?=1 
AND First Name is NILL) OR (First Name = ?)) AND((?=1 AND Last Name IS NULL) OR (Last Name = ?)) AND((?=1 AND Title is Null OR(Title=?))AND((?=1 AND date 
of birth IS NULL) OR (Date of Birth = ?)) AND((?=1AND'

I have now taken all the spaces out of my db headings and code to see if that helped but it didn't.

Regards

Scott

 

[jmcilhinney]

Can I take it that this SQL was generated by the Data Adapter Configuration Wizard? If so, why don't you try running the wizard again.

 

[scotttt]

I dont think so.

Is that when you drag the conection,adapter etc to the area under the form designer?
If so no i didn't do this I just coded all the connections etc.

Sorry If im on the wrong track here but im trying to move from VB6 to .net and its not going to well so far.

Regards

Scott

 

[jmcilhinney]

Hmmm... that's a very complex SQL statement to hand-code. Obviously it's not impossible, but it would be very error-prone. Given we can't see the whole thing from the error message, we can't really say where exactly the error might be. I don't fancy trying to decipher it anyway, frankly. Perhaps you might want to think about using the wizard I mentioned. If you add a DataAdapter to the form in the designer it will stratup automatically, but you can cancel it. You can then run it any time from the bottom of the Properties window. You might as well use the tools available to you, and the visual designer is the most time-saving of all.

 

[scotttt]

Thank you for your reply,

I do try to not use quick tools as I like to (try to) understand what is happening but I think your right that its the best way to go.

Thats got it!!!!!

I put data adapter in and used the query to build the code.
It should just be a SELECT * FROM studenttable so i dont know where the problem come from.

I am still unure on this dataadapter as on the last page of the configureation where its gives you the wizard results it gives me a warning on the UPDATE and DELETE statements yet i can update ok.

Oh well im sure the code will die soon and then I will know. lol

Thanks for your help!!!

Scott