No value given for one or more required parameters on relation update

[gordonfmoore]

Here's a variation on the old chestnut, but I can't get a handle on it.

I have a database that has a many to many relation. Table A has two
columns AID and AName, table B has two columns BID and BName and table
AB has three columns ABID, AID and BID for relating tables A and B.

(I am using Access, but don't use the autogenerated ID, although I let
it operate.)

I create a relation which contains ABID, AID, BID and BName.

If I edit BName in this relation I get the above error when I try to
update the databases, but I am only updating AB with the IDs, not the
name info and yet I am getting the error:

System.Data.OleDb.OleDbException: No value given for one or more
required parameters.
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 ConsoleApplication1.Program.Main(String[] args) in C:\Documents
and Settings\Gordon\My Documents\Visual Studio
2005\Projects\ConsoleApplication1\ConsoleApplication1\Program.cs:line
111

0 A and 0 B and 0 AB records updated - from my line of code

Line 111 contains the: i = daAB.Update(ds, "AB"); line

I have noted by cycling through the datarows of the AB table in the
dataset that the one that is changed is getting its status changed to
modified, which is fair enough. I suspect that since the
dataadapter.update is seeing that change then it wants to update the AB
table in the access table, even though it doesn't need to.

Any pointers would be gratefully appreciated, as well as any thoughts
on the update code, as I found this pretty complex to get my head
around - possibly where there are errors?

I have stripped my code as much as possible, but this is what generates
the error:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
string myDB = "AorB.mdb";
OleDbConnection conn = null;
DataSet ds;
OleDbDataAdapter daA;
OleDbDataAdapter daB;
OleDbDataAdapter daAB;
OleDbDataAdapter daBA;
DataRelation drAB;
DataRelation drBA;
//create a connection string to the access database
conn = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;
User Id=;Password=;
Data Source=" + myDB);

//clear the dataset
ds = null;
ds = new DataSet();

//set up the link
daA = new OleDbDataAdapter("SELECT * FROM A ORDER BY
AName", conn);
daB = new OleDbDataAdapter("SELECT * FROM B ORDER BY
BName", conn);

//fill the dataset with data
daA.Fill(ds, "A");
daB.Fill(ds, "B");

//Create Data Relation from A to B,
daAB = new OleDbDataAdapter("SELECT AB.ABID, AB.AID,
AB.BID, B.BName FROM B INNER JOIN AB ON B.BID = AB.BID ORDER BY
B.BName", conn);
daAB.Fill(ds, "AB");

drAB = new DataRelation("AToB",
ds.Tables["A"].Columns["AID"],
ds.Tables["AB"].Columns["AID"], true);
ds.Relations.Add(drAB);

//daBA = new OleDbDataAdapter("SELECT AB.ABID, AB.BID,
AB.AID, A.AName FROM A INNER JOIN AB ON A.AID = AB.AID ORDER BY
A.AName", conn);
//daBA.Fill(ds, "BA");

//drBA = new DataRelation("BToA",
// ds.Tables["B"].Columns["BID"],
// ds.Tables["BA"].Columns["BID"], true);
//ds.Relations.Add(drBA);

ds.EnforceConstraints = true;


//Change the BName in the first row of the AB table
DataRow[] dr = ds.Tables["AB"].Select();
DataRow r = dr[0];
r["BName"] = "Help";

//update database
string updateSQLA = "Update A set [AID]=?, [AName]=? where
[AID]=?";
//
//the @ is to make sure that it is treated as is, no
confusion with C# keywords
daA.UpdateCommand = new OleDbCommand(updateSQLA, conn);
daA.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer,
10, "AID");
daA.UpdateCommand.Parameters.Add("@AName",
OleDbType.VarWChar, 50, "AName");

OleDbParameter AID1 =
daA.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer, 10, "AID");
AID1.SourceVersion = DataRowVersion.Original;

string updateSQLB = "Update B set [BID]=?, [BName]=? where
[BID]=?";
//
daB.UpdateCommand = new OleDbCommand(updateSQLB, conn);
daB.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer,
10, "BID");
daB.UpdateCommand.Parameters.Add("@BName",
OleDbType.VarWChar, 50, "BName");

OleDbParameter BID1 =
daB.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID");
BID1.SourceVersion = DataRowVersion.Original;

//now need to update AB table in real database
string updateSQLAB = "Update AB set [ABID]=?, [AID]=?,
[BID]=? where [AID]=? and [BID]=?";
//
daAB.UpdateCommand = new OleDbCommand(updateSQLAB, conn);
daAB.UpdateCommand.Parameters.Add("@ABID",
OleDbType.Integer, 10, "ABID");
daAB.UpdateCommand.Parameters.Add("@AID",
OleDbType.Integer, 10, "AID");
daAB.UpdateCommand.Parameters.Add("@BID",
OleDbType.Integer, 10, "BID");

//OleDbParameter ABID1 =
daA.UpdateCommand.Parameters.Add("@ABID", OleDbType.Integer, 10,
"ABID");
//ABID1.SourceVersion = DataRowVersion.Original;
OleDbParameter ABID2 =
daA.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer, 10, "AID");
ABID2.SourceVersion = DataRowVersion.Original;
OleDbParameter ABID3 =
daA.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID");
ABID3.SourceVersion = DataRowVersion.Original;

int g = 0, h = 0, i = 0;
try //AB first (important)
{
i = daAB.Update(ds, "AB");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

try
{
g = daA.Update(ds, "A");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

try
{
h = daB.Update(ds, "B");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

//for testing
MessageBox.Show(g.ToString() + " A and " + h.ToString() + "
B and " + i.ToString() + " AB records updated");

}
}
}

 

[Cor Ligthert [MVP]]

Hi,

(I am using Access, but don't use the autogenerated ID, although I let
it operate.)

There can be more, however what do you mean with this sentence above?

And please don't sent us a complete program, I seldom see that somebody
gives answers on those.

Cor

 

[gordonfmoore]

Hi Cor,

I use Access as my database. It automatically generates an ID (for
indexing). I found this can lead to concurrency problems when I need to
link an id to create the child and the new id I use internally to the
program can become different from the autogenerated id in Access. I
don't use it as the ID for A, B or AB, I take care of this myself. I
could have removed it, but haven't changed my design for this yet.
(Part of my development and learning cycle!)

The reason for showing the whole program is because the majority of the
program is taken up with the routines for updating the database, which
I originally created myself manually - again in the learning process.
My guess is that this is where the error is. That or the relation
itself.

But thanks for the interest anyway.

btw Quote: "There can be more, " ?? have I missed something?

Regards
Gordon

 

[gordonfmoore]

Drat, should have said I'm using .Net 2.0 beta 2, oops.

 

[gordonfmoore]

Just checked with .Net 1.1 - same problem.

 

[Cor Ligthert [MVP]]

Gordon,

When you access your access datatable which has autokeys, than it is using
that.

You can do yourself a good job to change that and set the unique keys to
Guids.

What you are doing with a dataset (it is easy as long as you do not update)
is complex enough without that autokey in my opinion.

http://msdn.microsoft.com/library/d...n-us/cpref/html/frlrfsystemguidclasstopic.asp

I hope this helps,

Cor

 

[gordonfmoore]

I'm sorry mate, I think you've misunderstood the situation here (or I
have!)

I'm not using the autokey or autoincrement or anything in my code or
using the Access generated key.

If I insert a new record to the Access database then Access just
increments the id - my app doesn't care about that, since I create and
maintain my own IDs - never knew about the GUID though, so thanks for
that. There is no relation defined in the database - there are just
three tables.

No, the real problem is that because the AB child in the dataset, which
contains columns ABID, AID, BID and BName (from my select query) is
having the BName changed in the dataset it is causing the dataadapter
update routines to think that the original table AB needs to be
updated, whereas in fact it doesn't because nothing in that has been
changed. Therefore it wants to go away and find some values - re ABID,
AID and BID, but it can't find anything. Well that's what I think
anyway.

All my other updates work fine (in the real app).

Or am I being totally dense here? Very possible?


The one thing I can't seem to do is to see what the actual SQL
statement with the actual values is that the dataadapter.update routine
generates.

If I try daAB.Update.UpdateCommand.CommandText all I get is the sql
statement and ? marks. how can I get at the actual SQL statement sent
out?? maybe then I could back track and see what is going on.

Anyway I really appreciate your input - this is driving me nuts.

Gordon

 

[gordonfmoore]

Sorted.

Guess what - a stupid typo.

//ABID1.SourceVersion = DataRowVersion.Original;
OleDbParameter ABID2 =
daA.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer, 10, "AID");
ABID2.SourceVersion = DataRowVersion.Original;
OleDbParameter ABID3 =
daA.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID");
ABID3.SourceVersion = DataRowVersion.Original;

should read

//ABID1.SourceVersion = DataRowVersion.Original;
OleDbParameter ABID2 =
daAB.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer, 10,
"AID");
ABID2.SourceVersion = DataRowVersion.Original;
OleDbParameter ABID3 =
daAB.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10,
"BID");
ABID3.SourceVersion = DataRowVersion.Original;

can you see it? daAB not daA - doh.

Thanks for having a look anyway folks.

Gordon