VB 2003.net Syntax error in INSERT INTO statement

[hathan]

Hi, i am trying to add a new row into a microsoft access database and cant understand why it wont work. Can anyone please help me understand what this means? The error message says:

************** Exception Text **************
System.Data.Ole Db.OleDbExcepti on: Syntax error in INSERT INTO statement.
at System.Data.Com mon.DbDataAdapt er.Update(DataR ow[] dataRows, DataTableMappin g tableMapping)
at System.Data.Com mon.DbDataAdapt er.Update(DataT able dataTable)
at movie_rentals.e mployeeadd.Butt on2_Click(Objec t sender, EventArgs e) in C:\Users\Rik\De sktop\Riks work\VB\picture s for vb\DVDs r us\Employee Add.vb:line 409
at System.Windows. Forms.Control.O nClick(EventArg s e)
at System.Windows. Forms.Button.On Click(EventArgs e)
at System.Windows. Forms.Button.On MouseUp(MouseEv entArgs mevent)
at System.Windows. Forms.Control.W mMouseUp(Messag e& m, MouseButtons button, Int32 clicks)
at System.Windows. Forms.Control.W ndProc(Message& m)
at System.Windows. Forms.ButtonBas e.WndProc(Messa ge& m)
at System.Windows. Forms.Button.Wn dProc(Message& m)
at System.Windows. Forms.ControlNa tiveWindow.OnMe ssage(Message& m)
at System.Windows. Forms.ControlNa tiveWindow.WndP roc(Message& m)
at System.Windows. Forms.NativeWin dow.Callback(In tPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


************** Actual Code **************
Try

Dim connStr As String = "Provider = Microsoft.Jet.O LEDB.4.0;" & _
"Data Source = dvd.mdb"
Dim aDataTable As New DataTable
Dim squlStr As String = "SELECT * From Users"
Dim oleDbStaffAdapt er As New OleDb.OleDbData Adapter(squlStr , connStr)

aDataTable.Clea r()

oleDbStaffAdapt er.Fill(aDataTa ble)
Dim MyNewRow As DataRow = aDataTable.NewR ow

MyNewRow("numbe r") = staffnum.Text
MyNewRow("passw ord") = staffpassword.T ext
MyNewRow("acces s") = "1"
MyNewRow("first name") = firstname.Text
MyNewRow("last name") = lastname.Text
MyNewRow("date of birth") = dob.Text
MyNewRow("addre ss line 1") = address1.Text
MyNewRow("addre ss line 2") = address2.Text
MyNewRow("town" ) = town.Text
MyNewRow("count y") = county.Text
MyNewRow("post code") = postcode.Text

aDataTable.Rows .Add(MyNewRow)

Dim changes As Integer
Dim commandbuilder As New OleDb.OleDbComm andBuilder(oleD bStaffAdapter)
changes = oleDbStaffAdapt er.Update(aData Table)


Catch ex As InvalidOperatio nException
MsgBox(ex.Messa ge)
End Try

Thanks, Rik

[Shashi Sadasivan]

That errror is caused because you have not set an insert command to the adapter.

you need to set the OleDbDataAdapte r.InsertCommand Property so that the adapter know how to insert a new row if it cannot find it in the database

[hathan]

I dont understand how i would do that, i have read the link to setting an insert command to the adapter but i still dont get it. Someone please help.

[CyberSoftHari]

The problem is you are not aware of sql queries. The link given is all about queries in sql which is not used in your code.

Creating DataTable

[kenobewan]

I note the same code is coming up multiple question and that you are basically multiposting under the guise of different questions. I also find it difficult to believe that this is a university course, although some unis will offer anything if they can find a student.

Your questions appears to be a Homework Assignment and you should make a proper attempt at assignment/program. This question will now be closed and you will have to post a new question when you're ready. I strongly recommend reading the posting guidelines.

MODERATOR

You chose to ignore the above post. The initial warning was about your assignment, now you just keep posting similiar versions of the same code. This is a warning that continuing to ignore posting guidelines will mean a temporary ban. I am now closing the post. Thanks.

MODERATOR