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.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at movie_rentals.employeeadd.Button2_Click(Object sender, EventArgs e) in C:\Users\Rik\Desktop\Riks work\VB\pictures for vb\DVDs r us\Employee Add.vb:line 409
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventAr gs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage (Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(M essage& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


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

Dim connStr As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = dvd.mdb"
Dim aDataTable As New DataTable
Dim squlStr As String = "SELECT * From Users"
Dim oleDbStaffAdapter As New OleDb.OleDbDataAdapter(squlStr, connStr)

aDataTable.Clear()

oleDbStaffAdapter.Fill(aDataTable)
Dim MyNewRow As DataRow = aDataTable.NewRow

MyNewRow("number") = staffnum.Text
MyNewRow("password") = staffpassword.Text
MyNewRow("access") = "1"
MyNewRow("first name") = firstname.Text
MyNewRow("last name") = lastname.Text
MyNewRow("date of birth") = dob.Text
MyNewRow("address line 1") = address1.Text
MyNewRow("address line 2") = address2.Text
MyNewRow("town") = town.Text
MyNewRow("county") = county.Text
MyNewRow("post code") = postcode.Text

aDataTable.Rows.Add(MyNewRow)

Dim changes As Integer
Dim commandbuilder As New OleDb.OleDbCommandBuilder(oleDbStaffAdapter)
changes = oleDbStaffAdapter.Update(aDataTable)


Catch ex As InvalidOperationException
MsgBox(ex.Message)
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 OleDbDataAdapter.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