OleDb Syntaxfehler in UPDATE

Status
Nicht offen für weitere Antworten.

tomylee

Grünschnabel
Hallo,

ich habe aus einem Buch zum Thema OleDb Connection ein Programm ausprobiert. Es funktioniert alles soweit aber bei UPDATE kommt ein Syntaxfehler. Nun habe ich das ganze in mein Programm eingebaut und wieder das selbe. Was ich im Netz gefunden habe passt soweit alles mit dem überein was ich habe.

Hier das Fehlerprotokoll:

System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>MDB-DB_Test1.exe</AppDomain><Exception><ExceptionType>System.Data.OleDb.OleDbException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Syntaxfehler in UPDATE-Anweisung.</Message><StackTrace> at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(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.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at MDB_DB_Test1.MainForm.BtnSaveClick(Object sender, EventArgs e) in c:\Dokumente und Einstellungen\xxx\Eigene Dateien\SharpDevelop Projects\MDB-DB_Test1\MainForm.cs:line 175
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp; m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp; msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at MDB_DB_Test1.MainForm.Main(String[] args) in c:\Dokumente und Einstellungen\xxx\Eigene Dateien\SharpDevelop Projects\MDB-DB_Test1\MainForm.cs:line 36</StackTrace><ExceptionString>System.Data.OleDb.OleDbException: Syntaxfehler in UPDATE-Anweisung.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(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.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at MDB_DB_Test1.MainForm.BtnSaveClick(Object sender, EventArgs e) in c:\Dokumente und Einstellungen\xxx\Eigene Dateien\SharpDevelop Projects\MDB-DB_Test1\MainForm.cs:line 175
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp; m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp; msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at MDB_DB_Test1.MainForm.Main(String[] args) in c:\Dokumente und Einstellungen\xxx\Eigene Dateien\SharpDevelop Projects\MDB-DB_Test1\MainForm.cs:line 36</ExceptionString></Exception></TraceRecord>

Hier der Code von dem Bsp. aus dem Buch:

Code:
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

namespace MDB_DB_Test1
{
	/// <summary>
	/// Description of MainForm.
	/// </summary>
	public partial class MainForm : Form
	{
		private OleDbConnection conn;
		private OleDbDataAdapter dataAdapter;
		private DataTable dataTable;
		private DataSet ds;
		private int currRec = 0;
		private int totalRec = 0;
		private bool insertSelected;
		
		[STAThread]
		public static void Main(string[] args)
		{
			Application.EnableVisualStyles();
			Application.SetCompatibleTextRenderingDefault(false);
			Application.Run(new MainForm());
		}
		
		public MainForm()
		{
			//
			// The InitializeComponent() call is required for 
			// Windows Forms designer support.
			InitializeComponent();
			
			//
			// TODO: Add constructor code after the 
			//		 InitializeComponent() call.
		}
				
		private void BtnLoadTableClick(object sender, EventArgs e)
		{
			this.Cursor = Cursors.WaitCursor;
			
			string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;"+
								"data source = C:\\programmersheaven.mdb";
			
			conn = new OleDbConnection(connectionString);
			string commandString = 	"SElECT * from article";
			dataAdapter = new OleDbDataAdapter(commandString, conn);
			InitializeCommands();
			ds = new DataSet();
			dataAdapter.Fill(ds, "article");
			
			dataTable = ds.Tables["article"];
			currRec = 0;
			totalRec = dataTable.Rows.Count;
				
			FillControls();
			ToggleControls(true);
			
			this.Cursor = Cursors.Default;
		}
		
		private void FillControls()
		{
			txtArticleID.Text = dataTable.Rows[currRec]["artID"].ToString();
			txtArticleTitle.Text = dataTable.Rows[currRec]["title"].ToString();
			txtArticleTopic.Text = dataTable.Rows[currRec]["topic"].ToString();
			txtAuthorID.Text = dataTable.Rows[currRec]["authorID"].ToString();
			txtAuthorName.Text = dataTable.Rows[currRec]["authorName"].ToString();
			txtNumOfLines.Text = dataTable.Rows[currRec]["lines"].ToString();
			txtDateOfPublishing.Text = dataTable.Rows[currRec]["dateOfPublishing"].ToString();
		}
		
		private void InitializeCommands()
		{			
			//Preparing Select Command
			dataAdapter.SelectCommand = conn.CreateCommand();
			dataAdapter.SelectCommand.CommandText = 
				"SELECT * FROM article";
			
			//Preparing Insert Command
			dataAdapter.InsertCommand = conn.CreateCommand();
			dataAdapter.InsertCommand.CommandText = 
				"INSERT INTO article(artID, title, topic, authorID, authorName, lines, dateOfPublishing)"+
				"VALUES(@artID, @title, @topic, @authorID, @authorName, @lines, @dateOfPublishing)";
				AddParams(dataAdapter.InsertCommand, "artID", "title", "topic", "authorID", "authorName", "lines", "dateOfPublishing");
			
			//Preparing Update Command
			dataAdapter.UpdateCommand = conn.CreateCommand();
			dataAdapter.UpdateCommand.CommandText = "UPDATE article SET " +
													"title = @title, " +
													"topic = @topic, " +
													"authorID = @authorID,"+
													"authorName = @authorName,"+
													"lines = @lines, " +
													"dateOfPublishing = @dateOfPublishing,"+
													"WHERE artID = @artID";
				AddParams(dataAdapter.UpdateCommand, "title", "topic", "authorID", "authorName", "lines", "dateOfPublishing", "artID");
				
			//Preparing Delete Command	
			dataAdapter.DeleteCommand = conn.CreateCommand();
			dataAdapter.DeleteCommand.CommandText = "DELETE FROM article WHERE artID = @artID";
			AddParams(dataAdapter.DeleteCommand, "artID");
		}
		
		private void AddParams(OleDbCommand commandString, params string[] cols)
		{
			//Adding Hectice parameters in OleDb Commands
			foreach(string col in cols)
			{
				commandString.Parameters.Add("@" + col, OleDbType.Char, 0, col);
			}
		}
		
		private void ToggleControls(bool val)
		{
			txtArticleTitle.ReadOnly = val;
			txtArticleTopic.ReadOnly = val;
			txtAuthorID.ReadOnly = val;
			txtNumOfLines.ReadOnly = val;
			txtDateOfPublishing.ReadOnly = val;
			
			btnLoadTable.Enabled = val;
			btnNext.Enabled = val;
			btnPrevious.Enabled = val;
			btnEditRecord.Enabled = val;
			btnDeleteRecord.Enabled = val;
			btnInsertRecord.Enabled = val;
			
			btnSave.Enabled = !val;
			btnCancel.Enabled = !val;
		}
		
		void BtnNextClick(object sender, EventArgs e)
		{
			currRec++;
			if(currRec>=totalRec)
				currRec=0;
			FillControls();
		}
		
		void BtnPreviosClick(object sender, EventArgs e)
		{
			currRec--;
			if(currRec<0)
				currRec=totalRec-1;
			FillControls();
		}
		
		void BtnEditRecordClick(object sender, EventArgs e)
		{
			ToggleControls(false);
		}
		
		void BtnSaveClick(object sender, EventArgs e)
		{
			lblLabel.Text = "Saving Changes...";
			this.Cursor = Cursors.WaitCursor;
			DataRow row = dataTable.Rows[currRec];
			row.BeginEdit();
			row["title"] = txtArticleTitle.Text;
			row["topic"] = txtArticleTopic.Text;
			row["authorID"] = txtAuthorID.Text;
			row["authorName"] = txtAuthorName.Text;
			row["lines"] = txtNumOfLines.Text;
			row["dateOfPublishing"] = txtDateOfPublishing.Text;
			row.EndEdit();
			dataAdapter.Update(ds, "article");
			ds.AcceptChanges();
			
			ToggleControls(true);
			insertSelected = false;
			this.Cursor = Cursors.Default;
			lblLabel.Text = "Changes Saved";
		}
		
		void BtnCancelClick(object sender, EventArgs e)
		{
			if(insertSelected)
			{
				BtnDeleteRecordClick(null, null);
				insertSelected = false;
			}
			
			FillControls();
			ToggleControls(true);
		}
		
		void BtnDeleteRecordClick(object sender, EventArgs e)
		{
			DialogResult res = MessageBox.Show(
								"Are you sure you want to delet the current record?",
								"Confirm Record Deletion", MessageBoxButtons.YesNo);
			if(res == DialogResult.Yes)
			{
				DataRow row = dataTable.Rows[currRec];
				row.Delete();
				dataAdapter.Update(ds, "article");
				ds.AcceptChanges();
				lblLabel.Text = "Record Deleted";
				totalRec--;
				currRec = totalRec-1;
				FillControls();
			}
		}
		
		void BtnInsertRecordClick(object sender, EventArgs e)
		{
			insertSelected = true;
			DataRow row = dataTable.NewRow();
			dataTable.Rows.Add(row);
			totalRec = dataTable.Rows.Count;
			currRec = totalRec-1;
			row["artID"] = totalRec;
			
			txtArticleID.Text = totalRec.ToString();
			txtArticleTitle.Text = "";
			txtArticleTopic.Text = "";
			txtAuthorID.Text = "";
			txtAuthorName.Text = "";
			txtNumOfLines.Text = "";
			txtDateOfPublishing.Text = DateTime.Now.Date.ToString();
			
			ToggleControls(false);
		}
	}

Würde mich freuen wenn mir jemand sagen könnte an was es liegt. Eine Anwendung ohne Update ist etwas dumm ;)

Vielen Dank
 
Zuletzt bearbeitet:

ichhalt123

Grünschnabel
habs jetzt nicht getestet aber bei
Code:
dataAdapter.UpdateCommand = conn.CreateCommand();
			dataAdapter.UpdateCommand.CommandText = 
				"UPDATE article SET title = @title, topic = @topic, 
authorID = AuthorID,"+
				"lines = @lines, dateOfPublishing = @dateOfPublishing,"+
				"WHERE artID = @artID";
				AddParams(dataAdapter.UpdateCommand, "artID", "title", "topic", "authorID", "lines", "dateOfPublishing");
AuthorID is irgendwie kein @ davor
 

tomylee

Grünschnabel
emmmmm, hat damit nix zu tun. ist jetzt drin aber hat noch selben fehler.

ich habe oben den Code mal aktuell gemacht. fehler weiterhin.
 

tomylee

Grünschnabel
Fehler gefunden.

Code:
"dateOfPublishing = @dateOfPublishing,"+

Code:
"dateOfPublishing = @dateOfPublishing "+

ein komma zuviel :rolleyes:
 
Status
Nicht offen für weitere Antworten.

Neue Beiträge