Problemer med C# program til Access database.
Jeg har et problem med en kode jeg har arbejdet med.Programmet henter data fra en Access database og ligger det ind i en form. Man kan herefter oprette og hente de forskellige linjer.
Men så kommer problemet opdatering! Programmet er skrevt til kun at opdatere et felt. Når jeg tilføjer flere felter så får jeg en fejl ved opdatering. Opret og vis virker helt fint.
Fejlen opstår her efter hvad Visual Studios 2008 siger:
----------------------------------------------------------------
// Opdatere databasen med ændringer
dataAdapter.Update(dsChanges, "FakturaDatabase");
// Skriv til brugeren
MessageBox.Show("Databasen har nu opdateret faktura nr. " + selectedRow["fakturaID"]);
Application.DoEvents();
// Opdatere databasen og opdatere "listbox"
dataSet.AcceptChanges();
Fill_lb();
}
else // Hvis der er fejl bliver ændringerne ikke gemt
dataSet.RejectChanges();
}
-------------------------------------------------------------
Her er hele koden:
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
namespace ADONetTutorial
{
/// <summary>
/// Summary beskrivelse af from1
/// </summary>
public class Mainform : System.Windows.Forms.Form
{
public static string connectionString =
"provider=Microsoft.JET.OLEDB.4.0; "
+ "data source = " + Application.StartupPath + "\\ADONETTutorialDb.mdb";
private OleDbDataAdapter dataAdapter;
private DataSet dataSet;
private DataTable dataTable;
// *** End ADO.NET objects ***
// Database objects
private static string fakturaID;
private static string type;
private static string dato;
private static string belobUdenMoms;
private static string belobMedMoms;
private static string moms;
private static string information;
private static string virksomhed;
// *** End database objects
private System.Windows.Forms.ListBox listBox1;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Button btnExit;
private System.Windows.Forms.Button btnNew;
private System.Windows.Forms.TextBox txtVirksomhed;
private System.Windows.Forms.TextBox txtFakturaID;
private System.Windows.Forms.Button btnChange;
private System.Windows.Forms.Button btnDelete;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.TextBox txtInformation;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.Label label6;
private System.Windows.Forms.Label label7;
private System.Windows.Forms.Label label8;
private System.Windows.Forms.TextBox txtDato;
private System.Windows.Forms.TextBox txtBelobUdenMoms;
private System.Windows.Forms.TextBox txtMoms;
private System.Windows.Forms.TextBox txtBelobMedMoms;
private System.Windows.Forms.ComboBox ComboType;
/// <summary>
/// Designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Mainform()
{
InitializeComponent();
// FakturaDatabase table
string commandstring = "select * from FakturaDatabase";
// Link mellem SQL og databasen
dataAdapter = new OleDbDataAdapter(commandstring, connectionString);
// Definere "insert" & "update" & "delete" SQL commandoer
BuildCommands();
// Fyld indholdet fra databasen i RAM
dataSet = new DataSet();
dataSet.CaseSensitive = true;
dataAdapter.Fill(dataSet, "FakturaDatabase");
// Vis alle Linjer i Listboxen
Fill_lb();
} // *** Mainform ***
// Ryd op!
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
private void InitializeComponent()
{
this.listBox1 = new System.Windows.Forms.ListBox();
this.txtVirksomhed = new System.Windows.Forms.TextBox();
this.txtFakturaID = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.btnNew = new System.Windows.Forms.Button();
this.btnChange = new System.Windows.Forms.Button();
this.btnDelete = new System.Windows.Forms.Button();
this.btnExit = new System.Windows.Forms.Button();
this.label3 = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.txtInformation = new System.Windows.Forms.TextBox();
this.label5 = new System.Windows.Forms.Label();
this.label6 = new System.Windows.Forms.Label();
this.label7 = new System.Windows.Forms.Label();
this.label8 = new System.Windows.Forms.Label();
this.txtDato = new System.Windows.Forms.TextBox();
this.txtBelobUdenMoms = new System.Windows.Forms.TextBox();
this.txtMoms = new System.Windows.Forms.TextBox();
this.txtBelobMedMoms = new System.Windows.Forms.TextBox();
this.ComboType = new System.Windows.Forms.ComboBox();
this.SuspendLayout();
//
// listBox1
//
this.listBox1.Location = new System.Drawing.Point(8, 141);
this.listBox1.Name = "listBox1";
this.listBox1.Size = new System.Drawing.Size(915, 420);
this.listBox1.TabIndex = 13;
this.listBox1.SelectedIndexChanged += new System.EventHandler(this.listBox1_SelectedIndexChanged);
//
// txtVirksomhed
//
this.txtVirksomhed.Location = new System.Drawing.Point(178, 48);
this.txtVirksomhed.MaxLength = 17;
this.txtVirksomhed.Name = "txtVirksomhed";
this.txtVirksomhed.Size = new System.Drawing.Size(104, 20);
this.txtVirksomhed.TabIndex = 2;
//
// txtFakturaID
//
this.txtFakturaID.Location = new System.Drawing.Point(178, 18);
this.txtFakturaID.Name = "txtFakturaID";
this.txtFakturaID.Size = new System.Drawing.Size(104, 20);
this.txtFakturaID.TabIndex = 1;
//
// label1
//
this.label1.Location = new System.Drawing.Point(104, 50);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(68, 24);
this.label1.TabIndex = 3;
this.label1.Text = "Virksomhed:";
//
// label2
//
this.label2.Location = new System.Drawing.Point(104, 19);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(68, 23);
this.label2.TabIndex = 4;
this.label2.Text = "Faktura ID#:";
//
// btnNew
//
this.btnNew.Location = new System.Drawing.Point(8, 16);
this.btnNew.Name = "btnNew";
this.btnNew.Size = new System.Drawing.Size(75, 23);
this.btnNew.TabIndex = 9;
this.btnNew.Text = "&New";
this.btnNew.Click += new System.EventHandler(this.btnNew_Click);
//
// btnChange
//
this.btnChange.Location = new System.Drawing.Point(8, 48);
this.btnChange.Name = "btnChange";
this.btnChange.Size = new System.Drawing.Size(75, 23);
this.btnChange.TabIndex = 10;
this.btnChange.Text = "&Change";
this.btnChange.Click += new System.EventHandler(this.btnChange_Click);
//
// btnDelete
//
this.btnDelete.Location = new System.Drawing.Point(8, 79);
this.btnDelete.Name = "btnDelete";
this.btnDelete.Size = new System.Drawing.Size(75, 23);
this.btnDelete.TabIndex = 11;
this.btnDelete.Text = "&Delete";
this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
//
// btnExit
//
this.btnExit.Location = new System.Drawing.Point(8, 112);
this.btnExit.Name = "btnExit";
this.btnExit.Size = new System.Drawing.Size(75, 23);
this.btnExit.TabIndex = 12;
this.btnExit.Text = "E&xit";
this.btnExit.Click += new System.EventHandler(this.btnExit_Click);
//
// label3
//
this.label3.Location = new System.Drawing.Point(104, 80);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(68, 24);
this.label3.TabIndex = 8;
this.label3.Text = "Type:";
this.label3.Click += new System.EventHandler(this.label3_Click);
//
// label4
//
this.label4.Location = new System.Drawing.Point(311, 108);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(68, 24);
this.label4.TabIndex = 10;
this.label4.Text = "Information:";
//
// txtInformation
//
this.txtInformation.Location = new System.Drawing.Point(438, 108);
this.txtInformation.Name = "txtInformation";
this.txtInformation.Size = new System.Drawing.Size(485, 20);
this.txtInformation.TabIndex = 8;
//
// label5
//
this.label5.Location = new System.Drawing.Point(311, 19);
this.label5.Name = "label5";
this.label5.Size = new System.Drawing.Size(108, 23);
this.label5.TabIndex = 12;
this.label5.Text = "Beløb uden moms:";
//
// label6
//
this.label6.Location = new System.Drawing.Point(311, 50);
this.label6.Name = "label6";
this.label6.Size = new System.Drawing.Size(108, 23);
this.label6.TabIndex = 13;
this.label6.Text = "Beløb med moms:";
//
// label7
//
this.label7.Location = new System.Drawing.Point(311, 80);
this.label7.Name = "label7";
this.label7.Size = new System.Drawing.Size(108, 23);
this.label7.TabIndex = 14;
this.label7.Text = "Moms af beløb:";
//
// label8
//
this.label8.Location = new System.Drawing.Point(104, 108);
this.label8.Name = "label8";
this.label8.Size = new System.Drawing.Size(68, 24);
this.label8.TabIndex = 15;
this.label8.Text = "Dato:";
//
// txtDato
//
this.txtDato.Location = new System.Drawing.Point(178, 108);
this.txtDato.MaxLength = 10;
this.txtDato.Name = "txtDato";
this.txtDato.Size = new System.Drawing.Size(104, 20);
this.txtDato.TabIndex = 4;
//
// txtBelobUdenMoms
//
this.txtBelobUdenMoms.Location = new System.Drawing.Point(438, 18);
this.txtBelobUdenMoms.Name = "txtBelobUdenMoms";
this.txtBelobUdenMoms.Size = new System.Drawing.Size(136, 20);
this.txtBelobUdenMoms.TabIndex = 5;
//
// txtMoms
//
this.txtMoms.Location = new System.Drawing.Point(438, 79);
this.txtMoms.Name = "txtMoms";
this.txtMoms.Size = new System.Drawing.Size(136, 20);
this.txtMoms.TabIndex = 7;
//
// txtBelobMedMoms
//
this.txtBelobMedMoms.Location = new System.Drawing.Point(438, 48);
this.txtBelobMedMoms.Name = "txtBelobMedMoms";
this.txtBelobMedMoms.Size = new System.Drawing.Size(136, 20);
this.txtBelobMedMoms.TabIndex = 6;
//
// ComboType
//
this.ComboType.FormattingEnabled = true;
this.ComboType.Items.AddRange(new object[] {
"Omsætning",
"Omkostning"});
this.ComboType.Location = new System.Drawing.Point(178, 79);
this.ComboType.MaxDropDownItems = 2;
this.ComboType.Name = "ComboType";
this.ComboType.Size = new System.Drawing.Size(104, 21);
this.ComboType.TabIndex = 3;
//
// Mainform
//
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.None;
this.ClientSize = new System.Drawing.Size(938, 571);
this.Controls.Add(this.ComboType);
this.Controls.Add(this.txtBelobMedMoms);
this.Controls.Add(this.txtMoms);
this.Controls.Add(this.txtBelobUdenMoms);
this.Controls.Add(this.txtDato);
this.Controls.Add(this.label8);
this.Controls.Add(this.label7);
this.Controls.Add(this.label6);
this.Controls.Add(this.label5);
this.Controls.Add(this.txtInformation);
this.Controls.Add(this.label4);
this.Controls.Add(this.label3);
this.Controls.Add(this.btnExit);
this.Controls.Add(this.btnDelete);
this.Controls.Add(this.btnChange);
this.Controls.Add(this.btnNew);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.txtFakturaID);
this.Controls.Add(this.txtVirksomhed);
this.Controls.Add(this.listBox1);
this.MaximizeBox = false;
this.MaximumSize = new System.Drawing.Size(954, 607);
this.MinimizeBox = false;
this.MinimumSize = new System.Drawing.Size(954, 607);
this.Name = "Mainform";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "Remy\'s Faktura Program";
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
/// Main aplikation.
[STAThread]
static void Main()
{
Application.Run(new Mainform());
} // *** Main ***
// Vis alle informationer i "listbox"
private void Fill_lb()
{
dataTable = dataSet.Tables[0];
listBox1.Items.Clear();
foreach (DataRow dataRow in dataTable.Rows)
{
LoadBuffers(dataRow);
int stringSize;
stringSize = virksomhed.Length;
if (stringSize < 8)
{
listBox1.Items.Add(fakturaID + "\t\t" + virksomhed + "\t\t\t" + type + "\t\t" + dato + "\t\t" + belobUdenMoms + "\t\t" + belobMedMoms + "\t\t" + moms + "\t\t" + information);
}
else
{
listBox1.Items.Add(fakturaID + "\t\t" + virksomhed + "\t\t" + type + "\t\t" + dato + "\t\t" + belobUdenMoms + "\t\t" + belobMedMoms + "\t\t" + moms + "\t\t" + information);
}
}
} // *** Fill_lb ***
// "Load" global strings fra column værdier i "datarow"
private void LoadBuffers(DataRow prow)
{
fakturaID = prow["fakturaID"].ToString().Trim();
virksomhed = prow["virksomhed"].ToString().Trim();
dato = prow["dato"].ToString().Trim();
type = prow["type"].ToString().Trim();
belobUdenMoms = prow["belobUdenMoms"].ToString().Trim();
belobMedMoms = prow["belobMedMoms"].ToString().Trim();
moms = prow["moms"].ToString().Trim();
information = prow["information"].ToString().Trim();
} //*** LoadBuffers ***
private void BuildCommands()
{
// Brug forbindelsen igen
OleDbConnection connection =
(OleDbConnection) dataAdapter.SelectCommand.Connection;
// Deklarere en genbruglig "insert command" med parameter
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText =
"insert into FakturaDatabase " +
"(fakturaID, virksomhed, dato, type, belobUdenMoms, belobMedMoms, moms, information) " +
"values " +
"(?, ?, ?, ?, ?, ?, ?, ?)";
dataAdapter.InsertCommand.Parameters.Add("fakturaID", OleDbType.Char, 0, "fakturaID");
dataAdapter.InsertCommand.Parameters.Add("virksomhed", OleDbType.Char, 0, "virksomhed");
dataAdapter.InsertCommand.Parameters.Add("dato", OleDbType.Char, 0, "dato");
dataAdapter.InsertCommand.Parameters.Add("type", OleDbType.Char, 0, "type");
dataAdapter.InsertCommand.Parameters.Add("belobUdenMoms", OleDbType.Char, 0, "belobUdenMoms");
dataAdapter.InsertCommand.Parameters.Add("belobMedMoms", OleDbType.Char, 0, "belobMedMoms");
dataAdapter.InsertCommand.Parameters.Add("moms", OleDbType.Char, 0, "moms");
dataAdapter.InsertCommand.Parameters.Add("information", OleDbType.Char, 0, "information");
// Deklarere en genbruglig "insert command" med parameter
dataAdapter.UpdateCommand = connection.CreateCommand();
dataAdapter.UpdateCommand.CommandText = "update FakturaDatabase " +
"set virksomhed = ? " +
"set dato = ? " +
"set type = ? " +
"set belobUdenMoms = ? " +
"set belobMedMoms = ? " +
"set moms = ? " +
"set information = ? " +
"where fakturaID = ? ";
dataAdapter.UpdateCommand.Parameters.Add("fakturaID", OleDbType.Char, 0, "fakturaID");
dataAdapter.UpdateCommand.Parameters.Add("virksomhed", OleDbType.Char, 0, "virksomhed");
dataAdapter.UpdateCommand.Parameters.Add("dato", OleDbType.Char, 0, "dato");
dataAdapter.UpdateCommand.Parameters.Add("type", OleDbType.Char, 0, "type");
dataAdapter.UpdateCommand.Parameters.Add("belobUdenMoms", OleDbType.Char, 0, "belobUdenMoms");
dataAdapter.UpdateCommand.Parameters.Add("belobMedMoms", OleDbType.Char, 0, "belobMedMoms");
dataAdapter.UpdateCommand.Parameters.Add("moms", OleDbType.Char, 0, "moms");
dataAdapter.UpdateCommand.Parameters.Add("information", OleDbType.Char, 0, "information");
// Deklarere en genbruglig "insert command" med parameter
dataAdapter.DeleteCommand = connection.CreateCommand();
dataAdapter.DeleteCommand.CommandText =
"delete from FakturaDatabase where fakturaID = ?";
dataAdapter.DeleteCommand.Parameters.Add("fakturaID", OleDbType.Char, 0, "fakturaID");
} // *** BuildCommands ***
private void btnExit_Click(object sender, System.EventArgs e)
{
Application.Exit();
}
private void btnNew_Click(object sender, System.EventArgs e)
{
// Opret ny data ROW
DataRow newRow = dataTable.NewRow();
newRow["fakturaID"] = txtFakturaID.Text.Trim();
newRow["virksomhed"] = txtVirksomhed.Text.Trim();
newRow["dato"] = txtDato.Text.Trim();
newRow["type"] = ComboType.Text.Trim();
newRow["belobUdenMoms"] = txtBelobUdenMoms.Text.Trim();
newRow["belobMedMoms"] = txtBelobMedMoms.Text.Trim();
newRow["moms"] = txtMoms.Text.Trim();
newRow["information"] = txtInformation.Text.Trim();
// Opdatere databasen
try
{
dataSet.Tables["FakturaDatabase"].Rows.Add(newRow);
dataAdapter.Update(dataSet, "FakturaDatabase");
dataSet.AcceptChanges();
// Skriv til brugeren
MessageBox.Show("OPDATERET !!");
Application.DoEvents();
// Genopfrisk "listbox"
Fill_lb();
}
catch (OleDbException ex)
{
dataSet.RejectChanges();
MessageBox.Show(ex.Message);
MessageBox.Show(ex.ErrorCode.ToString());
}
}
private void btnChange_Click(object sender, System.EventArgs e)
{
// Hent den valgte ROW
DataRow selectedRow = dataTable.Rows[listBox1.SelectedIndex];
// Skriv til brugeren
Application.DoEvents();
// Redigere i den valgte ROW
selectedRow.BeginEdit();
selectedRow["virksomhed"] = txtVirksomhed.Text.Trim();
selectedRow["dato"] = txtDato.Text.Trim();
selectedRow["type"] = ComboType.Text.Trim();
selectedRow["belobUdenMoms"] = txtBelobUdenMoms.Text.Trim();
selectedRow["belobMedMoms"] = txtBelobMedMoms.Text.Trim();
selectedRow["moms"] = txtMoms.Text.Trim();
selectedRow["information"] = txtInformation.Text.Trim();
selectedRow.EndEdit();
// Hent alle ROW som er blevet ændret
DataSet dsChanges =
dataSet.GetChanges(DataRowState.Modified);
// Kontrolere om der er fejl
bool okayFlag = true;
if (dsChanges.HasErrors)
{
okayFlag = false;
string msg = "Error in row";
// Se på alle tabeller i dataSet
foreach (DataTable currTable in dsChanges.Tables)
{
// Find ROW med fejl
if (currTable.HasErrors)
{
// Hent ROW med fejl
DataRow[] errorRows = currTable.GetErrors();
// Gennemgå ROW og find dem med fejl
foreach (DataRow currRow in errorRows)
{
msg = msg + currRow["fakturaID"];
}
}
}
MessageBox.Show(msg);
}
// Ingen fejl "OK"
if (okayFlag)
{
// Opdatere databasen med ændringer
dataAdapter.Update(dsChanges, "FakturaDatabase");
// Skriv til brugeren
MessageBox.Show("Databasen har nu opdateret faktura nr. " + selectedRow["fakturaID"]);
Application.DoEvents();
// Opdatere databasen og opdatere "listbox"
dataSet.AcceptChanges();
Fill_lb();
}
else // Hvis der er fejl bliver ændringerne ikke gemt
dataSet.RejectChanges();
}
private void btnDelete_Click(object sender, System.EventArgs e)
{
// Hent ROW fra "listbox"
DataRow selectedRow = dataTable.Rows[listBox1.SelectedIndex];
string msg = " Faktura nummer " + selectedRow["fakturaID"] + " er nu slettet.";
// Slet ROW
selectedRow.Delete();
// Slet den også i databasen
try
{
dataAdapter.Update(dataSet, "FakturaDatabase");
dataSet.AcceptChanges();
// Opdatere databasen
Fill_lb();
// Skriv til brugeren
MessageBox.Show(msg);
Application.DoEvents();
}
catch (OleDbException ex)
{
dataSet.RejectChanges();
MessageBox.Show(ex.Message);
}
}
private void listBox1_SelectedIndexChanged(object sender, System.EventArgs e)
{
DataRow sourceRow = dataTable.Rows[listBox1.SelectedIndex];
LoadBuffers(sourceRow);
FillForm();
}
private void FillForm()
{
txtFakturaID.Text = fakturaID;
txtVirksomhed.Text = virksomhed;
txtDato.Text = dato;
ComboType.Text = type;
txtBelobUdenMoms.Text = belobUdenMoms;
txtBelobMedMoms.Text = belobMedMoms;
txtMoms.Text = moms;
txtInformation.Text = information;
}
private void label3_Click(object sender, EventArgs e)
{
}
}
}
