SqlDataAdapter Update Method won't work

[certolnut]

HI all

New to C#

I'm merging datasets. The source dataset is an excel file and the target
dataset is in SqlServer.

salesTracingCommisionNDCdataset1.Merge(ndcExcel,fa lse,MissingSchemaAction.Error)
The merge works fine. It will populate a datagrid after the merge is
complete. However, when I issue the command to update the database, nothing
happens. No errors, no exceptions. nothing.

sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);

I've checked the schemas, data typing, and I've ensured that the database
has a primary Key. I've also ensured that I have proper permissions on the
database

Also. I let the Wizard generate the Update Command code.

UPDATE SalesTracingCommissionNDCtable
SET ItemNumber = @ItemNumber, Qty = @Qty, InvoiceDate =
@InvoiceDate, BranchNo = @BranchNo
WHERE (BranchNo = @Original_BranchNo) AND (InvoiceDate =
@Original_InvoiceDate) AND (ItemNumber = @Original_ItemNumber) AND
(Qty = @Original_Qty OR
@Original_Qty IS NULL AND Qty IS NULL);
SELECT ItemNumber, Qty, InvoiceDate, BranchNo
FROM SalesTracingCommissionNDCtable
WHERE (BranchNo = @BranchNo) AND (InvoiceDate
= @InvoiceDate) AND (ItemNumber = @ItemNumber)

Any advice on debugging?? Any help is appreciated

[Nicholas Paldino [.NET/C# MVP]]

certolnut,

I think you should change the second parameter to true, to preserve the
changes in the dataset. I believe that when you pass false, it will cause
an AcceptChanges call to be made, which sets the state of all the rows to
Unchanged, which will not cause any updates to be made when passed through a
data adapter.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"certolnut" <ce*******@discussions.microsoft.com> wrote in message
news:14**********************************@microsof t.com...

HI all

New to C#

I'm merging datasets. The source dataset is an excel file and the target
dataset is in SqlServer.

salesTracingCommisionNDCdataset1.Merge(ndcExcel,fa lse,MissingSchemaAction.Error)
The merge works fine. It will populate a datagrid after the merge is
complete. However, when I issue the command to update the database,
nothing
happens. No errors, no exceptions. nothing.

sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);

I've checked the schemas, data typing, and I've ensured that the database
has a primary Key. I've also ensured that I have proper permissions on the
database

Also. I let the Wizard generate the Update Command code.

UPDATE SalesTracingCommissionNDCtable
SET ItemNumber = @ItemNumber, Qty = @Qty, InvoiceDate =
@InvoiceDate, BranchNo = @BranchNo
WHERE (BranchNo = @Original_BranchNo) AND (InvoiceDate =
@Original_InvoiceDate) AND (ItemNumber = @Original_ItemNumber) AND
(Qty = @Original_Qty OR
@Original_Qty IS NULL AND Qty IS NULL);
SELECT ItemNumber, Qty, InvoiceDate, BranchNo
FROM SalesTracingCommissionNDCtable
WHERE (BranchNo = @BranchNo) AND
(InvoiceDate
= @InvoiceDate) AND (ItemNumber = @ItemNumber)

Any advice on debugging?? Any help is appreciated

[Neal Whitney]

Hi Nicholas, Thanks for the reply.

I gave it a shot, no luck.

Thanks for the advice though, I'll make sure and have this set to "true" in
the future..

I'll keep trying.. It's just seems weird that it wouldn't throw some kind of
exception...

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:Oy**************@tk2msftngp13.phx.gbl...

certolnut,

I think you should change the second parameter to true, to preserve the changes in the dataset. I believe that when you pass false, it will cause
an AcceptChanges call to be made, which sets the state of all the rows to
Unchanged, which will not cause any updates to be made when passed through a data adapter.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"certolnut" <ce*******@discussions.microsoft.com> wrote in message
news:14**********************************@microsof t.com...

HI all

New to C#

I'm merging datasets. The source dataset is an excel file and the target
dataset is in SqlServer.

salesTracingCommisionNDCdataset1.Merge(ndcExcel,fa lse,MissingSchemaAction.Er
ror)

The merge works fine. It will populate a datagrid after the merge is
complete. However, when I issue the command to update the database,
nothing
happens. No errors, no exceptions. nothing.

sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);

I've checked the schemas, data typing, and I've ensured that the database has a primary Key. I've also ensured that I have proper permissions on the database

Also. I let the Wizard generate the Update Command code.

UPDATE SalesTracingCommissionNDCtable
SET ItemNumber = @ItemNumber, Qty = @Qty, InvoiceDate =
@InvoiceDate, BranchNo = @BranchNo
WHERE (BranchNo = @Original_BranchNo) AND (InvoiceDate =
@Original_InvoiceDate) AND (ItemNumber = @Original_ItemNumber) AND
(Qty = @Original_Qty OR
@Original_Qty IS NULL AND Qty IS NULL);
SELECT ItemNumber, Qty, InvoiceDate, BranchNo FROM SalesTracingCommissionNDCtable
WHERE (BranchNo = @BranchNo) AND
(InvoiceDate
= @InvoiceDate) AND (ItemNumber = @ItemNumber)

Any advice on debugging?? Any help is appreciated

[Nicholas Paldino [.NET/C# MVP]]

Neal,

Are you trying to update in the event handler for a UI component? If
so, then try and wrap the code in a try/catch block. Exceptions that are
thrown in these event handlers are swallowed by the framework.

Also, check to make sure that the rows don't have a state of "unchanged"
associated with them.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Neal Whitney" <wh**********@hotmail.com> wrote in message
news:eR**************@TK2MSFTNGP14.phx.gbl...

Hi Nicholas, Thanks for the reply.

I gave it a shot, no luck.

Thanks for the advice though, I'll make sure and have this set to "true"
in
the future..

I'll keep trying.. It's just seems weird that it wouldn't throw some kind
of
exception...

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:Oy**************@tk2msftngp13.phx.gbl...

certolnut,

I think you should change the second parameter to true, to preserve

the

changes in the dataset. I believe that when you pass false, it will
cause
an AcceptChanges call to be made, which sets the state of all the rows to
Unchanged, which will not cause any updates to be made when passed
through

a

data adapter.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"certolnut" <ce*******@discussions.microsoft.com> wrote in message
news:14**********************************@microsof t.com...

> HI all
>
> New to C#
>
> I'm merging datasets. The source dataset is an excel file and the
> target
> dataset is in SqlServer.
>
> salesTracingCommisionNDCdataset1.Merge(ndcExcel,fa lse,MissingSchemaAction.Er
ror) >
>
> The merge works fine. It will populate a datagrid after the merge is
> complete. However, when I issue the command to update the database,
> nothing
> happens. No errors, no exceptions. nothing.
>
> sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);
>
> I've checked the schemas, data typing, and I've ensured that the database > has a primary Key. I've also ensured that I have proper permissions on the > database
>
> Also. I let the Wizard generate the Update Command code.
>
> UPDATE SalesTracingCommissionNDCtable
> SET ItemNumber = @ItemNumber, Qty = @Qty, InvoiceDate =
> @InvoiceDate, BranchNo = @BranchNo
> WHERE (BranchNo = @Original_BranchNo) AND (InvoiceDate =
> @Original_InvoiceDate) AND (ItemNumber = @Original_ItemNumber) AND
> (Qty = @Original_Qty OR
> @Original_Qty IS NULL AND Qty IS NULL);
> SELECT ItemNumber, Qty, InvoiceDate, BranchNo > FROM SalesTracingCommissionNDCtable
> WHERE (BranchNo = @BranchNo) AND
> (InvoiceDate
> = @InvoiceDate) AND (ItemNumber = @ItemNumber)
>
> Any advice on debugging?? Any help is appreciated

[certolnut]

Ok, Well, I wasn't trying to update a UI component by I went ahead and
wrapped the code anyway (always a good idea), but still no luck.

try
{

salesTracingCommisionNDCdataset1.Merge(ndcExcel,tr ue,MissingSchemaAction.Err
or);
sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);
}
catch
{

Console.WriteLine("error");
// Error during Update, add code to locate error, reconcile
// and try to update again.
}

I tried looking for the Row property that you were talking about, but I
couldn't find it in the debugger..

I found a rowDiffId property (set to undefined) and a tablerowchanged
property (also set to undefined). the count has changed from 0 to 252
(correct number of new rows). But still nothing..

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:On**************@TK2MSFTNGP10.phx.gbl...

Neal,

Are you trying to update in the event handler for a UI component? If
so, then try and wrap the code in a try/catch block. Exceptions that are
thrown in these event handlers are swallowed by the framework.

Also, check to make sure that the rows don't have a state of "unchanged" associated with them.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Neal Whitney" <wh**********@hotmail.com> wrote in message
news:eR**************@TK2MSFTNGP14.phx.gbl...

Hi Nicholas, Thanks for the reply.

I gave it a shot, no luck.

Thanks for the advice though, I'll make sure and have this set to "true"
in
the future..

I'll keep trying.. It's just seems weird that it wouldn't throw some kind of
exception...

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:Oy**************@tk2msftngp13.phx.gbl...

certolnut,

I think you should change the second parameter to true, to preserve

the

changes in the dataset. I believe that when you pass false, it will
cause
an AcceptChanges call to be made, which sets the state of all the rows to Unchanged, which will not cause any updates to be made when passed
through

a

data adapter.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"certolnut" <ce*******@discussions.microsoft.com> wrote in message
news:14**********************************@microsof t.com...
> HI all
>
> New to C#
>
> I'm merging datasets. The source dataset is an excel file and the
> target
> dataset is in SqlServer.
>
>

salesTracingCommisionNDCdataset1.Merge(ndcExcel,fa lse,MissingSchemaAction.Er ror)

>
>
> The merge works fine. It will populate a datagrid after the merge is
> complete. However, when I issue the command to update the database,
> nothing
> happens. No errors, no exceptions. nothing.
>
> sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);
>
> I've checked the schemas, data typing, and I've ensured that the

database

> has a primary Key. I've also ensured that I have proper permissions

on the

> database
>
> Also. I let the Wizard generate the Update Command code.
>
> UPDATE SalesTracingCommissionNDCtable
> SET ItemNumber = @ItemNumber, Qty = @Qty, InvoiceDate =
> @InvoiceDate, BranchNo = @BranchNo
> WHERE (BranchNo = @Original_BranchNo) AND (InvoiceDate =
> @Original_InvoiceDate) AND (ItemNumber = @Original_ItemNumber) AND
> (Qty = @Original_Qty OR
> @Original_Qty IS NULL AND Qty IS NULL);
> SELECT ItemNumber, Qty, InvoiceDate,

BranchNo

> FROM SalesTracingCommissionNDCtable
> WHERE (BranchNo = @BranchNo) AND
> (InvoiceDate
> = @InvoiceDate) AND (ItemNumber = @ItemNumber)
>
> Any advice on debugging?? Any help is appreciated

[Nicholas Paldino [.NET/C# MVP]]

Neal,

You want to check the RowState property on the DataRow (if you are
looking at a DataRowView, then use the Row property to expose the row). If
that value is DataRowState.Unchanged, then it will not be processed by a
data adapter.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"certolnut" <wh**********@hotmail.com> wrote in message
news:ub**************@TK2MSFTNGP12.phx.gbl...

Ok, Well, I wasn't trying to update a UI component by I went ahead and
wrapped the code anyway (always a good idea), but still no luck.

try
{

salesTracingCommisionNDCdataset1.Merge(ndcExcel,tr ue,MissingSchemaAction.Err
or);
sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);
}
catch
{

Console.WriteLine("error");
// Error during Update, add code to locate error, reconcile
// and try to update again.
}

I tried looking for the Row property that you were talking about, but I
couldn't find it in the debugger..

I found a rowDiffId property (set to undefined) and a tablerowchanged
property (also set to undefined). the count has changed from 0 to 252
(correct number of new rows). But still nothing..

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:On**************@TK2MSFTNGP10.phx.gbl...

Neal,

Are you trying to update in the event handler for a UI component? If
so, then try and wrap the code in a try/catch block. Exceptions that are
thrown in these event handlers are swallowed by the framework.

Also, check to make sure that the rows don't have a state of

"unchanged"

associated with them.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Neal Whitney" <wh**********@hotmail.com> wrote in message
news:eR**************@TK2MSFTNGP14.phx.gbl...

> Hi Nicholas, Thanks for the reply.
>
> I gave it a shot, no luck.
>
> Thanks for the advice though, I'll make sure and have this set to
> "true"
> in
> the future..
>
> I'll keep trying.. It's just seems weird that it wouldn't throw some kind > of
> exception...
>
> "Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com>
> wrote
> in
> message news:Oy**************@tk2msftngp13.phx.gbl...
>> certolnut,
>>
>> I think you should change the second parameter to true, to
>> preserve
> the
>> changes in the dataset. I believe that when you pass false, it will
>> cause
>> an AcceptChanges call to be made, which sets the state of all the rows to >> Unchanged, which will not cause any updates to be made when passed
>> through
> a
>> data adapter.
>>
>> Hope this helps.
>>
>>
>> --
>> - Nicholas Paldino [.NET/C# MVP]
>> - mv*@spam.guard.caspershouse.com
>>
>> "certolnut" <ce*******@discussions.microsoft.com> wrote in message
>> news:14**********************************@microsof t.com...
>> > HI all
>> >
>> > New to C#
>> >
>> > I'm merging datasets. The source dataset is an excel file and the
>> > target
>> > dataset is in SqlServer.
>> >
>> >
> salesTracingCommisionNDCdataset1.Merge(ndcExcel,fa lse,MissingSchemaAction.Er > ror)
>> >
>> >
>> > The merge works fine. It will populate a datagrid after the merge is
>> > complete. However, when I issue the command to update the database,
>> > nothing
>> > happens. No errors, no exceptions. nothing.
>> >
>> > sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);
>> >
>> > I've checked the schemas, data typing, and I've ensured that the
> database
>> > has a primary Key. I've also ensured that I have proper permissions on > the
>> > database
>> >
>> > Also. I let the Wizard generate the Update Command code.
>> >
>> > UPDATE SalesTracingCommissionNDCtable
>> > SET ItemNumber = @ItemNumber, Qty = @Qty, InvoiceDate =
>> > @InvoiceDate, BranchNo = @BranchNo
>> > WHERE (BranchNo = @Original_BranchNo) AND (InvoiceDate =
>> > @Original_InvoiceDate) AND (ItemNumber = @Original_ItemNumber) AND
>> > (Qty = @Original_Qty OR
>> > @Original_Qty IS NULL AND Qty IS NULL);
>> > SELECT ItemNumber, Qty, InvoiceDate,
> BranchNo
>> > FROM
>> > SalesTracingCommissionNDCtable
>> > WHERE (BranchNo = @BranchNo) AND
>> > (InvoiceDate
>> > = @InvoiceDate) AND (ItemNumber = @ItemNumber)
>> >
>> > Any advice on debugging?? Any help is appreciated
>>
>>
>
>

[certolnut]

Hi Nicholas

Thanks so much again for your help.

After much wrangling, I found out that the RowState was indeed unchanged.

Now my only question is, how do I get this value moved to "changed"???
Clearly the rows have changed since the target dataset was completely empty
before, and now it has 11 rows which I can reference.

I must be missing a step here....
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:Oc**************@TK2MSFTNGP14.phx.gbl...

Neal,

You want to check the RowState property on the DataRow (if you are
looking at a DataRowView, then use the Row property to expose the row). If that value is DataRowState.Unchanged, then it will not be processed by a
data adapter.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"certolnut" <wh**********@hotmail.com> wrote in message
news:ub**************@TK2MSFTNGP12.phx.gbl...

Ok, Well, I wasn't trying to update a UI component by I went ahead and
wrapped the code anyway (always a good idea), but still no luck.

try
{

salesTracingCommisionNDCdataset1.Merge(ndcExcel,tr ue,MissingSchemaAction.Err or);
sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);
}
catch
{

Console.WriteLine("error");
// Error during Update, add code to locate error, reconcile
// and try to update again.
}

I tried looking for the Row property that you were talking about, but I
couldn't find it in the debugger..

I found a rowDiffId property (set to undefined) and a tablerowchanged
property (also set to undefined). the count has changed from 0 to 252
(correct number of new rows). But still nothing..

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:On**************@TK2MSFTNGP10.phx.gbl...

Neal,

Are you trying to update in the event handler for a UI component? If so, then try and wrap the code in a try/catch block. Exceptions that are thrown in these event handlers are swallowed by the framework.

Also, check to make sure that the rows don't have a state of

"unchanged"

associated with them.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Neal Whitney" <wh**********@hotmail.com> wrote in message
news:eR**************@TK2MSFTNGP14.phx.gbl...
> Hi Nicholas, Thanks for the reply.
>
> I gave it a shot, no luck.
>
> Thanks for the advice though, I'll make sure and have this set to
> "true"
> in
> the future..
>
> I'll keep trying.. It's just seems weird that it wouldn't throw some

kind

> of
> exception...
>
> "Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com>
> wrote
> in
> message news:Oy**************@tk2msftngp13.phx.gbl...
>> certolnut,
>>
>> I think you should change the second parameter to true, to
>> preserve
> the
>> changes in the dataset. I believe that when you pass false, it will
>> cause
>> an AcceptChanges call to be made, which sets the state of all the rows

to

>> Unchanged, which will not cause any updates to be made when passed
>> through
> a
>> data adapter.
>>
>> Hope this helps.
>>
>>
>> --
>> - Nicholas Paldino [.NET/C# MVP]
>> - mv*@spam.guard.caspershouse.com
>>
>> "certolnut" <ce*******@discussions.microsoft.com> wrote in message
>> news:14**********************************@microsof t.com...
>> > HI all
>> >
>> > New to C#
>> >
>> > I'm merging datasets. The source dataset is an excel file and the
>> > target
>> > dataset is in SqlServer.
>> >
>> >
>

salesTracingCommisionNDCdataset1.Merge(ndcExcel,fa lse,MissingSchemaAction.Er > ror)
>> >
>> >
>> > The merge works fine. It will populate a datagrid after the merge is >> > complete. However, when I issue the command to update the database, >> > nothing
>> > happens. No errors, no exceptions. nothing.
>> >
>> > sqlDataAdapter2.Update(salesTracingCommisionNDCdat aset1);
>> >
>> > I've checked the schemas, data typing, and I've ensured that the
> database
>> > has a primary Key. I've also ensured that I have proper permissions on

> the
>> > database
>> >
>> > Also. I let the Wizard generate the Update Command code.
>> >
>> > UPDATE SalesTracingCommissionNDCtable
>> > SET ItemNumber = @ItemNumber, Qty = @Qty, InvoiceDate

= >> > @InvoiceDate, BranchNo = @BranchNo
>> > WHERE (BranchNo = @Original_BranchNo) AND (InvoiceDate =
>> > @Original_InvoiceDate) AND (ItemNumber = @Original_ItemNumber) AND
>> > (Qty = @Original_Qty OR
>> > @Original_Qty IS NULL AND Qty IS NULL);
>> > SELECT ItemNumber, Qty, InvoiceDate,
> BranchNo
>> > FROM
>> > SalesTracingCommissionNDCtable
>> > WHERE (BranchNo = @BranchNo) AND
>> > (InvoiceDate
>> > = @InvoiceDate) AND (ItemNumber = @ItemNumber)
>> >
>> > Any advice on debugging?? Any help is appreciated
>>
>>
>
>