TableAdapter.Update fails with missing parameter

I'm working in VS2005, C#. I have a form with a details view of records.
The Fill statement takes 1 parameter a BOXNUMBER so I fetch only one record
from the database table. Fill(MBDataSet.MBDetailsDataTable dataTable, string
BoxNumber)

After editing the fields I issue a tableadapter.update(dataset) command.
That update throws an exception and the message is :

"Parameterized Query '(@BoxNumber nvarchar(10),@LastName
nvarchar(25),@PhoneNumber nva' expects parameter @IsNull_BoxNumber, which was
not supplied."

If no changes were made to any field the update does not throw an excpetion.

I have made no code changes to any of the Designer generated code and have
only added event handlers to the Form.cs file to issue the update.
What is going wrong? A data view of the data set shows the changes made to
the fields OK.
How can I send the update back to the database using the table adapter?

--
Thanks
Morris

Hello Morris,
Sorry for delay, due to weekend.

To create a method for partial tableAdapter class, you can follow the steps
as below. Then you can expose the internal Adapter in that method, and
check each parameter value in its updating event. I believe you may find
some of them are empty. This is the reason why tableAdapter throw an error
while updating. But, I'm afraid to say this doesn't help on narrow down the
issue. This method doesn't tell us why the parameter isn't copied from
dataset. Each parameter should be filled in update () method automatically.
According to your description, you have made no code changes to any of the
Designer generated code and have only added event handlers to the Form.cs
file to issue the update. Could you please provide more detailed
information about how did you add the Typed DataSet and add event to issue
the update? I will try to reproduce the issue on my side and perform
further research on it.

http://msdn.microsoft.com/en-us/library/ms233697(VS.80).aspx
[How to: Extend the Functionality of a TableAdapter ]

==============================================================
namespace ConsoleApplication16.DataSet1TableAdapters
{
public partial class table1TableAdapter
{
public System.Data.SqlClient.SqlDataAdapter getDataAdatper()
{
return this._adapter;
}
}
}

In your application.cs file:
DataSet1TableAdapters.table1TableAdapter tta = new
ConsoleApplication16.DataSet1TableAdapters.table1TableAdapter();
tta.getDataAdatper().RowUpdating += new
System.Data.SqlClient.SqlRowUpdatingEventHandler(Program_RowUpdating);
....
==============================================================

Have a great day,
Best regards,
Wen Yuan
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

First I created 2 DataSources:
1) MBLDataset has 1 datatable MBList that retrieves only 3 columns from the
"Mailboxes" table in the SQL database. I left the datable to be in
DataGridview
2) MBDataSet has 1 datatable MBDetails that retrieves all the columns from
the same Mailboxes table. The databale was changed to show Details on the
form.

For the MBDataSet I modified the Fill command to take a paramter:
Fill(@BoxNumber) and modified the Select staetment to add: "WHERE
(BoxNumber = @BoxNumber)". That was done via the TableAdapter Configuration
Wizard.

Second I dragged on to the Form1 the MBList from the DataSources window and
that worked fine. All the recoords are retrieved from the table and it
display BoxNumber, LastName, FirstName in the data grid and it created the
navigation bar.

Third I dragged on the Form1 the MBDetails from the DataSources window and
it generated all the fields for detail view and allowed them to be edited. I
want to only retrieve the specific record that is selected in the MBList
dataGrid to be retrieved and shown in the Details fields. That is why I
changed the Fill command to use a Where clause. That part is working.

Finally I created a Button1 to Save the edits in the details record as
below. This is the code that gets the error message as you can see in the
Catch() statement.

private void button1_Click(object sender, EventArgs e)
{
try
{
this.mBDetailsBindingSource.EndEdit();
//this.mBDetailsTableAdapter.Update(this.mBDataSet.MBDetails);

this.mBDetailsTableAdapter.Update(this.mBDataSet.MBDetails.Rows[0]);
}
catch (System.Exception ex)
{
MessageBox.Show("Update failed. \nReason: " +
ex.Message.ToString(),"mbUpdate");
}
}


Also for the Form_Load and Row_enter functions is listed below.

I set the following

private void MailBoxForm_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the
'mailboxtest.Mailboxes' table. You can move, or remove it, as needed.
this.mailboxesTableAdapter.Fill(this.mailboxtest.Mailboxes);
// TODO: This line of code loads data into the
'mBLDataSet.MBList' table. You can move, or remove it, as needed.

this.mbDefaultsTableAdapter.Fill(this.mbDefaultsDataSet.MBDefaults);
this.mBListTableAdapter.Fill(this.mBLDataSet.MBList);
this.mBDetailsTableAdapter.Fill(this.mBDataSet.MBDetails,
this.mBListDataGridView.CurrentRow.Cells[0].Value.ToString());
this.mBListDataGridView.RowEnter += new
System.Windows.Forms.DataGridViewCellEventHandler(this.mBListDataGridView_RowEnter);
this.mBListDataGridView.RowsAdded += new
System.Windows.Forms.DataGridViewRowsAddedEventHandler(this.mBListDataGridView_RowsAdded);

}

private void mBListDataGridView_RowEnter(object sender, EventArgs e)
{
// retrieve the selected record's details.
int intFill =
this.mBDetailsTableAdapter.Fill(this.mBDataSet.MBDetails,
this.mBListDataGridView.SelectedRows[0].Cells[0].Value.ToString());
if (intFill == 0) // no existing record must be new so get
default values.
{
// get default values from NewAccountDefault Table.
this.boxNumberTextBox.Text =
mBListDataGridView.SelectedRows[0].Cells[0].Value.ToString();
this.lastNameTextBox.Text =
mBListDataGridView.SelectedRows[0].Cells[1].Value.ToString();
this.firstNameTextBox.Text =
mBListDataGridView.SelectedRows[0].Cells[2].Value.ToString();
this.maxNewMessagesTextBox.Text =
this.mbDefaultsDataSet.MBDefaults.Rows[0].ItemArray.GetValue(1).ToString();
this.maxSavedMessagesTextBox.Text =
this.mbDefaultsDataSet.MBDefaults.Rows[0].ItemArray.GetValue(2).ToString();
this.maxMessageLengthTextBox.Text =
this.mbDefaultsDataSet.MBDefaults.Rows[0].ItemArray.GetValue(3).ToString();
this.maxMessageAgeTextBox.Text =
this.mbDefaultsDataSet.MBDefaults.Rows[0].ItemArray.GetValue(4).ToString();
this.flagsTextBox.Text =
this.mbDefaultsDataSet.MBDefaults.Rows[0].ItemArray.GetValue(5).ToString();
}
}

The MBDetails.Update() command was also generated by the TableAdapter
Configuration Wizard.

I am guessing it has to do with the fact I added the Where clause in the
Fill()?

Does this give you any clue as to what is going wrong?

Thanks for your help.

--
Thanks
Morris


""Wen Yuan Wang [MSFT]"" wrote:

> Hello Morris,
> Sorry for delay, due to weekend.
>
> To create a method for partial tableAdapter class, you can follow the steps
> as below. Then you can expose the internal Adapter in that method, and
> check each parameter value in its updating event. I believe you may find
> some of them are empty. This is the reason why tableAdapter throw an error
> while updating. But, I'm afraid to say this doesn't help on narrow down the
> issue. This method doesn't tell us why the parameter isn't copied from
> dataset. Each parameter should be filled in update () method automatically.
> According to your description, you have made no code changes to any of the
> Designer generated code and have only added event handlers to the Form.cs
> file to issue the update. Could you please provide more detailed
> information about how did you add the Typed DataSet and add event to issue
> the update? I will try to reproduce the issue on my side and perform
> further research on it.
>
> http://msdn.microsoft.com/en-us/library/ms233697(VS.80).aspx
> [How to: Extend the Functionality of a TableAdapter ]
>
> ==============================================================
> namespace ConsoleApplication16.DataSet1TableAdapters
> {
> public partial class table1TableAdapter
> {
> public System.Data.SqlClient.SqlDataAdapter getDataAdatper()
> {
> return this._adapter;
> }
> }
> }
>
> In your application.cs file:
> DataSet1TableAdapters.table1TableAdapter tta = new
> ConsoleApplication16.DataSet1TableAdapters.table1TableAdapter();
> tta.getDataAdatper().RowUpdating += new
> System.Data.SqlClient.SqlRowUpdatingEventHandler(Program_RowUpdating);
> ....
> ==============================================================
>
> Have a great day,
> Best regards,
> Wen Yuan
> Microsoft Online Community Support
> Delighting our customers is our #1 priority. We welcome your comments and
> suggestions about how we can improve the support we provide to you. Please
> feel free to let my manager know what you think of the level of service
> provided. You can send feedback directly to my manager at:
> msdnmg@microsoft.com.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Hello Morris,

Thanks for your reply and detailed information. I tried the steps which you
posted in thread. I created two different datasets base on the same table,
one retrieves only id column from the DB table and another one is detailed
information. I drag-drop them on the form. Thus, VS IDE generates GridView
and Navigationbar automatically. At last, I pasted the code snippet in
GridView and Button Event. TableAdapter.Update() method works fine after I
edited GridView. I never got any error said parameter is empty. Actually,
@IsNull_BoxNumber Parameter should be filled in updat() faction by .net. I
checked the source code in Reflector. @IsNull_BoxNumber Parameter is filled
automatically if it's not null and SourceColumnNullMapping is true.

DbParameter parameter2 = parameter as DbParameter;
if ((parameter2 != null) && parameter2.SourceColumnNullMapping)
{
parameter.Value = ADP.IsNull(parameter.Value) ?
ParameterValueNullValue : ParameterValueNonNullValue;
}

If it's possible, could you please send us a simply project to reproduce
the issue on our side? You can get me at v-wywang@microsoft.com. We are
willing to drill into the project and figure out the root cause, if you can
send us it to reproduce the issue.

Please feel free to let us know if you have any more concern. We are glad
to assist you.

Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Wen

Since I suspected my default Fill() the cause ( since I modified it to take
a parameter), I went back to my project and deleted the MBDetails data set
and rectreated it but this time I left the default Fill() query as is with no
parameters vs before when I had added @BoxNumber so it was Fill(Dataset,
Boxnumber) in order to satify the Where Boxnumber=@Boxnumber clause of the
Select Query).

Then I created a second Select Query (with the Designer) and that one was
called FillByBoxNumber(Dataset, BoxNmuber). I used this second select query
to retrieve the desired record in the Form_Load() and RowEnter/Add() event
handler. And that worked and then the Updates started working.

So I am guessing that altering the default Fill to take a parameter for a
Where clause and then telling the Wizard to generate the Update quiries was
the problem. But now the Updates are working. I could try to recreate the
problem and send that to you if you want? I did not go back to confirm my
theory.

But alteast my Updates work and want to thank you for your assistance and
guidance which were critical to me working it out here.

I have 3 more ques. but I understand they should be new postings, but I ask
them here so you can answer or tell which is the best group for them to be
posted in.

1. Is there a utility which can display/trace the stack of windows message /
events that are sent to my forms for debugging purposes?

2. I see that when I enter a new value in the bindingNavigatorPositionItem
field and hit Enter I do not get an bindingNavigatorPositionItemTextChanged
event Only when I click on another field does that event get sent. What event
do I need to handle the user hitting Enter after changing the field value?

3. What is the best pratice for having the bindingNavigatorMoveNextItem,
Previous, First, and LastItem Events all perform the same function. Should
I define each button's EventHandler to perfom the same Fill () statement or
Define the same EventHandler for each buttons Click event?

Thanks again so much for your help.
Sincerely
--
Morris


""Wen Yuan Wang [MSFT]"" wrote:

> Hello Morris,
>
> Thanks for your reply and detailed information. I tried the steps which you
> posted in thread. I created two different datasets base on the same table,
> one retrieves only id column from the DB table and another one is detailed
> information. I drag-drop them on the form. Thus, VS IDE generates GridView
> and Navigationbar automatically. At last, I pasted the code snippet in
> GridView and Button Event. TableAdapter.Update() method works fine after I
> edited GridView. I never got any error said parameter is empty. Actually,
> @IsNull_BoxNumber Parameter should be filled in updat() faction by .net. I
> checked the source code in Reflector. @IsNull_BoxNumber Parameter is filled
> automatically if it's not null and SourceColumnNullMapping is true.
>
> DbParameter parameter2 = parameter as DbParameter;
> if ((parameter2 != null) && parameter2.SourceColumnNullMapping)
> {
> parameter.Value = ADP.IsNull(parameter.Value) ?
> ParameterValueNullValue : ParameterValueNonNullValue;
> }
>
> If it's possible, could you please send us a simply project to reproduce
> the issue on our side? You can get me at v-wywang@microsoft.com. We are
> willing to drill into the project and figure out the root cause, if you can
> send us it to reproduce the issue.
>
> Please feel free to let us know if you have any more concern. We are glad
> to assist you.
>
> Have a great day,
> Best regards,
> Wen Yuan
>
> Microsoft Online Community Support
> Delighting our customers is our #1 priority. We welcome your comments and
> suggestions about how we can improve the support we provide to you. Please
> feel free to let my manager know what you think of the level of service
> provided. You can send feedback directly to my manager at:
> msdnmg@microsoft.com.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Hello Morris,
Thanks for your reply.

I'm glad to hear you resolved the issue. It seems the issue goes way after
you re-defined a new query for Fill method. VS IDE generates
Update/Insert/Delete method base on default select query. I agree with you.
This may accused because VS IDE generates the wrong code when you made
change in default Fill query. Have you applied VS 2005 SP1 on machine? This
seems like a product issue. Most of IDE issue has been addressed in VS 2005
sp1. Moreover, I will log it into our internal DB for product team to
investigate.

In order to capture the Enter key in BindingNavigatorPossitonItem, I'd like
to suggest you can hook on KeyDown event and check its key code.
eg:
private void bindingNavigatorPositionItem_KeyDown(object sender,
KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{//add code}
}

To execute the same code in many different events, I think the simplest way
is define a help methed and call this method in each item event.
eg:
private void bindingNavigatorPositionItem_TextChanged(object
sender, EventArgs e)
{ executeMe();}

private void bindingNavigatorMoveLastItem_Click(object sender,
EventArgs e)
{ executeMe()}

public void executeMe()
{//code}

For such winform control related issue, we can also post it in
"microsoft.public.dotnet.framework.windowsforms.controls".
You may also refer to the following link, all Managed Newsgroup has been
listed in it.
http://msdn.microsoft.com/en-us/sub...s/aa974230.aspx
[MSDN Managed Newsgroups]

As far as I know, we can get windows message by spy++ tool. But, I'd like
to suggest you can post it in "microsoft.public.vsnet.debugging". This is
also a managed group. There may be some better suggestion to trace the
windows message.

Hope this helps. If you have any more concern, please also feel free to let
us know. We are glad to assist you.
Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Morris,

This is Wen Yuan, again. I just want to check if you have any more concern
or there is anything we can help with.
Please feel free to let us know if you need any further assistance. We are
glad to assist you.

Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Wen
Your last set of answers were very helpfull and got me on my way.
Everything is moving along well. Thanks Again.

I do have a general question, How can I tell what is the set and sequence
of events are generated when I use some controls. For example with the
DataGridView I am finding that some times when a row is selected by different
method the Rows.Selected is the one I want in the RowEnter EventHandler but
sometimes the previous row is still selected at that point in time and I need
to use a different event handler to get the newly selected row in the
Rows.Selected property , like when I use the binding navigator button to
select a row.

If I can know all the relevent events that will be sent when I use a control
that would let me know what event handler I have to make and in what order.
I tried Spy++ but that generates alot of events I am not sure which are the
ones relevent to my specfic controls. This is probably for other group so I
can post there if that better.

Thanks again for all your help.
--
Thanks
Morris


""Wen Yuan Wang [MSFT]"" wrote:

> Hello Morris,
>
> This is Wen Yuan, again. I just want to check if you have any more concern
> or there is anything we can help with.
> Please feel free to let us know if you need any further assistance. We are
> glad to assist you.
>
> Have a great day,
> Best regards,
> Wen Yuan
>
> Microsoft Online Community Support
> Delighting our customers is our #1 priority. We welcome your comments and
> suggestions about how we can improve the support we provide to you. Please
> feel free to let my manager know what you think of the level of service
> provided. You can send feedback directly to my manager at:
> msdnmg@microsoft.com.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Hello Morris,
Thanks for your reply. It's my pleasure to assist you.

Actually, as far as I know, we haven't a good tool to trace the sequence of
event generated by Controls in .net framework. What we will do is to handle
all events which could fire on that control. We can check out MSDN document
to know which event could fires in the control. Then, output the event name
in each handler. Thereby, we can know the sequence of events. Regarding to
Spy++, this is a tool to receive event from Windows. I'm afraid the even
traced by Spy++ couldn't match the event name in .net framework well. A
general way we use is to output each event name in code.
eg:
private void mBDetailsDataGridView_CellClick(object sender,
DataGridViewCellEventArgs e)
{
Console.WriteLine("CellClick");
}

private void mBDetailsDataGridView_CellContentClick(object sender,
DataGridViewCellEventArgs e)
{
Console.WriteLine("CellContentClick");
}

private void mBDetailsDataGridView_CellBeginEdit(object sender,
DataGridViewCellCancelEventArgs e)
{
Console.WriteLine("CellBeginEdit");
}

..................

Moreover, if you are not sure which event is what you need, you can also
post it in "microsoft.public.dotnet.framework.windowsforms.controls".
This is also a managed newsgroup. The people in that newsgroup is very
famariy with winform controls event, and be able to help in such field.

Hope this helps. If there is anything unclear, please don't hesitate to let
me know again. We are glad to assist you.

Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.