SqlDataAdapter Update Method won't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,false,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(salesTracingCommisionNDCdataset1);

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,

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.
 
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 said:
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]
- (e-mail address removed)

certolnut said:
HI all

New to C#

I'm merging datasets. The source dataset is an excel file and the target
dataset is in SqlServer.
Click to expand...
salesTracingCommisionNDCdataset1.Merge(ndcExcel,false,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(salesTracingCommisionNDCdataset1);

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
Click to expand...
Click to expand...
 
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]
- (e-mail address removed)

Neal Whitney said:
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...

in
message news:[email protected]...
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]
- (e-mail address removed)

certolnut said:
HI all

New to C#

I'm merging datasets. The source dataset is an excel file and the
target
dataset is in SqlServer.
Click to expand...
salesTracingCommisionNDCdataset1.Merge(ndcExcel,false,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(salesTracingCommisionNDCdataset1);

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
Click to expand...
Click to expand...
Click to expand...
 
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,true,MissingSchemaAction.Err
or);
sqlDataAdapter2.Update(salesTracingCommisionNDCdataset1);
}
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 said:
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]
- (e-mail address removed)

Neal Whitney said:
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...

in
message news:[email protected]...
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]
- (e-mail address removed)

HI all

New to C#

I'm merging datasets. The source dataset is an excel file and the
target
dataset is in SqlServer.
Click to expand...
salesTracingCommisionNDCdataset1.Merge(ndcExcel,false,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(salesTracingCommisionNDCdataset1);

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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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]
- (e-mail address removed)

certolnut said:
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,true,MissingSchemaAction.Err
or);
sqlDataAdapter2.Update(salesTracingCommisionNDCdataset1);
}
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..



in
message news:[email protected]...
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]
- (e-mail address removed)

Neal Whitney said:
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]" <[email protected]>
wrote
in
message 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]
- (e-mail address removed)

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,false,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(salesTracingCommisionNDCdataset1);

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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]
- (e-mail address removed)

certolnut said:
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,true,MissingSchemaAction.Err
or);
sqlDataAdapter2.Update(salesTracingCommisionNDCdataset1);
}
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..



in
message news:[email protected]...
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]
- (e-mail address removed)

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]" <[email protected]>
wrote
in
message 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
Click to expand...
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]
- (e-mail address removed)

HI all

New to C#

I'm merging datasets. The source dataset is an excel file and the
target
dataset is in SqlServer.
Click to expand...
salesTracingCommisionNDCdataset1.Merge(ndcExcel,false,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(salesTracingCommisionNDCdataset1);

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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top