Hallo,
ich habe aus einem Buch zum Thema OleDb Connection ein Programm ausprobiert. Es geht 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 hat 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>
> </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; m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&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; m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&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\Thomas Mann\Eigene Dateien\SharpDevelop Projects\MDB-DB_Test1\MainForm.cs:line 36</ExceptionString></Exception></TraceRecord>
Hier der Code von dem Bsp. aus dem Buch:
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
