Update command OledbDataAdapter problem

[explode]

I made nova oledbdataadapter select update insert and delete command and
connection veza. dataset is Studenti1data, I made it by the new data source
wizard,and made datagridview and bindingsource draging Table1 to Form2. The
select command works fine, but when I change the data and call update
command I get a syntax error:

System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217900
Message="Syntax error in UPDATE statement."
Source="Microsoft JET Database Engine"
StackTrace:
at
System.Data.Common.DbDataAdapter.UpdatedRowStatusE rrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at
System.Data.Common.DbDataAdapter.UpdatedRowStatus( RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTab le(DataTable
dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at Diplomski1.Form1.Novo(String nova1, String nova2) in
D:\OdMomka\Diplomski\Diplomski1\Diplomski1\Form1.v b:line 272
at Diplomski1.Form2.Table1BindingNavigatorSaveItem_Cl ick(Object
sender, EventArgs e) in
D:\OdMomka\Diplomski\Diplomski1\Diplomski1\Form2.v b:line 8
at System.Windows.Forms.ToolStripItem.RaiseEvent(Obje ct key,
EventArgs e)
at System.Windows.Forms.ToolStripButton.OnClick(Event Args e)
at System.Windows.Forms.ToolStripItem.HandleClick(Eve ntArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(M ouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventIntera ctive(EventArgs
e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(Event Args e,
ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEven tArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons
button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Mes sage& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at
System.Windows.Forms.Control.ControlNativeWindow.O nMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message&
m)
at System.Windows.Forms.NativeWindow.DebuggableCallba ck(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchM essageW(MSG&
msg)
at
System.Windows.Forms.Application.ComponentManager. System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData)
at
System.Windows.Forms.Application.ThreadContext.Run MessageLoopInner(Int32
reason, ApplicationContext context)
at
System.Windows.Forms.Application.ThreadContext.Run MessageLoop(Int32 reason,
ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationCo ntext context)
at
Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.OnRun()
at
Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.DoApplicationModel()
at
Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.Run(String[]
commandLine)
at Diplomski1.My.MyApplication.Main(String[] Args) in
17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAss embly()
at
System.Runtime.Hosting.ApplicationActivator.Create Instance(ActivationContext
activationContext, String[] activationCustomData)
at
System.Runtime.Hosting.ApplicationActivator.Create Instance(ActivationContext
activationContext)
at
Microsoft.VisualStudio.HostingProcess.HostProc.Run UsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context( Object state)
at System.Threading.ExecutionContext.Run(ExecutionCon text
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

The procedure cod is this:

Public Sub Novo(ByVal nova1 As String, ByVal nova2 As String)
Dim i As Integer
Dim nova As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim veza As OleDb.OleDbConnection = New
OleDb.OleDbConnection(Global.Diplomski1.My.MySetti ngs.Default.Studenti1Connection)
Dim SelectCom As OleDb.OleDbCommand = New OleDb.OleDbCommand
Dim UpdateCom As OleDb.OleDbCommand = New OleDb.OleDbCommand
Dim InsertCom As OleDb.OleDbCommand = New OleDb.OleDbCommand
Dim DeleteCom As OleDb.OleDbCommand = New OleDb.OleDbCommand
Dim tableMapping As System.Data.Common.DataTableMapping = New
System.Data.Common.DataTableMapping("Table", ComboBox1.Text)

veza.Open()

For i = 0 To Form2.Studenti1Data.Tables.Item(0).Columns.Count - 1
tableMapping.ColumnMappings.Add(Form2.Studenti1Dat a.Tables.Item(0).Columns.Item(i).ColumnName,
Form2.Studenti1Data.Tables.Item(0).Columns.Item(i) .ColumnName)
Next i
nova.TableMappings.Add(tableMapping)

nova.MissingMappingAction = MissingMappingAction.Passthrough
nova.MissingSchemaAction = MissingSchemaAction.AddWithKey
nova.AcceptChangesDuringUpdate = True

SelectCom.Connection = veza
SelectCom.CommandType = CommandType.Text
SelectCom.CommandText = "SELECT * FROM " & nova1

UpdateCom.Connection = veza
UpdateCom.CommandType = CommandType.Text
UpdateCom.CommandText = "UPDATE " & nova1 & " SET Broj iksice=?,
Prezime=?, Ime=?, Broj indeksa=?, Broj pohadjanja=? " & _
"WHERE Broj iksice=?"
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("Broj iksice",
OleDb.OleDbType.Double, 0, System.Data.ParameterDirection.Input, False,
CType(20, Byte), CType(0, Byte), "Broj iksice", DataRowVersion.Current,
Nothing))
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("stari Broj
iksice", OleDb.OleDbType.Double, 0, System.Data.ParameterDirection.Input,
False, CType(20, Byte), CType(0, Byte), "Broj iksice",
DataRowVersion.Original, Nothing))
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("Prezime",
OleDb.OleDbType.WChar, 0, System.Data.ParameterDirection.Input, False,
CType(0, Byte), CType(0, Byte), "Prezime", DataRowVersion.Current, Nothing))
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("stari Prezime",
OleDb.OleDbType.WChar, 0, System.Data.ParameterDirection.Input, False,
CType(0, Byte), CType(0, Byte), "Prezime", DataRowVersion.Original,
Nothing))
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("Ime",
OleDb.OleDbType.WChar, 0, System.Data.ParameterDirection.Input, False,
CType(0, Byte), CType(0, Byte), "Ime", DataRowVersion.Current, Nothing))
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("stari Ime",
OleDb.OleDbType.WChar, 0, System.Data.ParameterDirection.Input, False,
CType(0, Byte), CType(0, Byte), "ime", DataRowVersion.Original, Nothing))
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("Broj indeksa",
OleDb.OleDbType.WChar, 0, System.Data.ParameterDirection.Input, True,
CType(0, Byte), CType(0, Byte), "Broj indeksa", DataRowVersion.Current,
Nothing))
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("stari Broj
indeksa", OleDb.OleDbType.WChar, 0, System.Data.ParameterDirection.Input,
True, CType(0, Byte), CType(0, Byte), "Broj indeksa",
DataRowVersion.Original, Nothing))
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("Broj pohadjanja",
OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, True,
CType(0, Byte), CType(0, Byte), "Broj pohadjanja", DataRowVersion.Current,
Nothing))
UpdateCom.Parameters.Add(New OleDb.OleDbParameter("stari Broj
pohadjanja", OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte),
"Broj pohadjanja", DataRowVersion.Original, Nothing))
For i = 5 To Form2.Studenti1Data.Tables.Item(0).Columns.Count - 1
UpdateCom.Parameters.Add(Form2.Studenti1Data.Table s.Item(0).Columns.Item(i).ColumnName,
_
OleDb.OleDbType.Integer, 0,
Form2.Studenti1Data.Tables.Item(0).Columns.Item(i) .ColumnName)
Next i

InsertCom.Connection = veza
InsertCom.CommandType = CommandType.Text
InsertCom.CommandText = "INSERT INTO " & nova1 & " (Broj iksice,
Prezime, Ime, Broj indeksa, " & _
"Broj pohadjanja) VALUES (?,?,?,?,?)"
InsertCom.Parameters.Add("Broj iksice", OleDb.OleDbType.Double, 0,
"Broj iksice")
InsertCom.Parameters.Add("Prezime", OleDb.OleDbType.WChar, 0,
"Prezime")
InsertCom.Parameters.Add("Ime", OleDb.OleDbType.WChar, 0, "Ime")
InsertCom.Parameters.Add("Broj indeksa", OleDb.OleDbType.WChar, 0,
"Broj indeksa")
InsertCom.Parameters.Add("Broj pohadjanja", OleDb.OleDbType.Integer,
0, "Broj pohadjanja")
For i = 5 To Form2.Studenti1Data.Tables.Item(0).Columns.Count - 1
InsertCom.Parameters.Add(Form2.Studenti1Data.Table s.Item(0).Columns.Item(i).ColumnName,
_
OleDb.OleDbType.Integer, 0,
Form2.Studenti1Data.Tables.Item(0).Columns.Item(i) .ColumnName)
Next i

DeleteCom.Connection = veza
DeleteCom.CommandType = CommandType.Text
DeleteCom.CommandText = "UPDATE " & nova1 & " SET Broj iksice=?,
Prezime=?, Ime=?, Broj indeksa=?, Broj pohadjanja=? " & _
"WHERE Broj iksice=?"
DeleteCom.Parameters.Add("Broj iksice", OleDb.OleDbType.Double, 0,
"Broj iksice")
DeleteCom.Parameters.Add("Prezime", OleDb.OleDbType.WChar, 0,
"Prezime")
DeleteCom.Parameters.Add("Ime", OleDb.OleDbType.WChar, 0, "Ime")
DeleteCom.Parameters.Add("Broj indeksa", OleDb.OleDbType.WChar, 0,
"Broj indeksa")
DeleteCom.Parameters.Add("Broj pohadjanja", OleDb.OleDbType.Integer,
0, "Broj pohadjanja")
For i = 5 To Form2.Studenti1Data.Tables.Item(0).Columns.Count - 1
DeleteCom.Parameters.Add(Form2.Studenti1Data.Table s.Item(0).Columns.Item(i).ColumnName,
_
OleDb.OleDbType.Integer, 0,
Form2.Studenti1Data.Tables.Item(0).Columns.Item(i) .ColumnName)
Next i

nova.SelectCommand = SelectCom
nova.UpdateCommand = UpdateCom
nova.DeleteCommand = DeleteCom
nova.InsertCommand = InsertCom

If nova2 = "Fill" Then

nova.Fill(Form2.Studenti1Data.Tables.Item(Index(Co mboBox1.Text)))

End If

If nova2 = "Update" Then
nova.Update(Form2.Studenti1Data.Tables.Item(Index( ComboBox1.Text)))
End If
veza.Close()

End Sub

Index(tablename) is a function that returns index of the table with
specified tablename.
Program stops executing on green marked text and I get a syntax error.
Where is the syntax error?
Please help....!!!!

[Armin Zingler]

"explode" <ur**@mzos.hrschrieb

I made nova oledbdataadapter select update insert and delete command and
connection veza. dataset is Studenti1data, I made it by the new data
source wizard,and made datagridview and bindingsource draging Table1 to
Form2. The select command works fine, but when I change the data and call
update command I get a syntax error:

In the SQL, put field names that contain blanks into brackets:
"...[Broj iksice]..."

It also seems that you are setting many more parameters for the
UpdateCommand than there are available. I count six "?" in the SQL but you
set some more.
Armin

[explode]

Thank you! this was helpfull!