Ado.net DataSet Refresh

[Guest]

Hi

Is there any way to refresh a DataSet after inserting few records in a table
(containing foreignKey column) using adapters' InsertCommand and
ExecuteNonQuery(). I tried adapter's Fill(dataSet) but it does not add the
new records inserted to the dataset's table. I am doing something like:

adapter->InsertCommand = gcnew SqlCommand(L"INSERT INTO Sample " +
"(sampleID, type, dateReceived, dateExpiry, supplierID)"+
" VALUES (@sampID, @type, @dtRcvd, @dtExp, @suppID)", conn);

int r = adapter->InsertCommand->ExecuteNonQuery();
adapter->Fill(dataset);

Ofcourse all the parameter's are defined and the record is inserted to the
dataSource table.

Because of the foreignKey column I can not just add a newRow to DataSet and
then use adapter's Update() method as it throws an exception.

I am using VC++ (VisualStudio 2005 and SQL Server 2005) for the interface.

Thanks for any information.

Manjree

 

[William \(Bill\) Vaughn]

By default, Fill simply reexecutes the query and appends the new rows to an
existing DataTable... did you look for the new rows at the end?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Guest]

Thanks for your reply. I

I am using dataset to list all the records in CListView. And after insertion
when I list all the records in the table It does not list the newly inserted
records. Though if I close the application and run it again all the records
are listed via DataSet. Means that for the first time it Fills all the
records but after insertion it does not refresh it.

Manjree

 

[William \(Bill\) Vaughn]

How are you binding to the DataTable/DataView?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Guest]

Hi Bill

I am just fetching the data from the DataTable's row and displaying in the
list. The code is something like that:

DataRowCollection^ rows = samplesTable->Rows;
for (i = 0; i < rows->Count; i++)
{
row = rows->default;
supplierid = row->default[L"supplierID"]->ToString();
id = row->default[L"sampleID"]->ToString();
type = row->default[L"type"]->ToString();

int idx = lst.InsertItem(i, (CString)id);
lst.SetItemText(idx, 1, (CString)supplierid);
lst.SetItemText(idx, 2, (CString)type);;
}

After adding new records when I list the records It does not list the newly
inserted records.

Thanks.

Manjree

 

[William \(Bill\) Vaughn]

If you use one of the binding techniques, this refresh is done
automatically.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill

I am just fetching the data from the DataTable's row and displaying in the
list. The code is something like that:

DataRowCollection^ rows = samplesTable->Rows;
for (i = 0; i < rows->Count; i++)
{
row = rows->default;
supplierid = row->default[L"supplierID"]->ToString();
id = row->default[L"sampleID"]->ToString();
type = row->default[L"type"]->ToString();

int idx = lst.InsertItem(i, (CString)id);
lst.SetItemText(idx, 1, (CString)supplierid);
lst.SetItemText(idx, 2, (CString)type);;
}

After adding new records when I list the records It does not list the
newly
inserted records.

Thanks.

Manjree

How are you binding to the DataTable/DataView?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Guest]

Hi Bill

Is it possible to bind data with Windows Forms in my current VC++ (MFC
application) project?

Manjree

If you use one of the binding techniques, this refresh is done
automatically.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill

I am just fetching the data from the DataTable's row and displaying in the
list. The code is something like that:

DataRowCollection^ rows = samplesTable->Rows;
for (i = 0; i < rows->Count; i++)
{
row = rows->default;
supplierid = row->default[L"supplierID"]->ToString();
id = row->default[L"sampleID"]->ToString();
type = row->default[L"type"]->ToString();

int idx = lst.InsertItem(i, (CString)id);
lst.SetItemText(idx, 1, (CString)supplierid);
lst.SetItemText(idx, 2, (CString)type);;
}

After adding new records when I list the records It does not list the
newly
inserted records.

Thanks.

Manjree

How are you binding to the DataTable/DataView?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Thanks for your reply. I

I am using dataset to list all the records in CListView. And after
insertion
when I list all the records in the table It does not list the newly
inserted
records. Though if I close the application and run it again all the
records
are listed via DataSet. Means that for the first time it Fills all the
records but after insertion it does not refresh it.

Manjree

:

By default, Fill simply reexecutes the query and appends the new rows
to
an
existing DataTable... did you look for the new rows at the end?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hi

Is there any way to refresh a DataSet after inserting few records in
a
table
(containing foreignKey column) using adapters' InsertCommand and
ExecuteNonQuery(). I tried adapter's Fill(dataSet) but it does not
add
the
new records inserted to the dataset's table. I am doing something
like:

adapter->InsertCommand = gcnew SqlCommand(L"INSERT INTO Sample " +
"(sampleID, type, dateReceived, dateExpiry,
supplierID)"+
" VALUES (@sampID, @type, @dtRcvd, @dtExp, @suppID)", conn);

int r = adapter->InsertCommand->ExecuteNonQuery();
adapter->Fill(dataset);

Ofcourse all the parameter's are defined and the record is inserted
to
the
dataSource table.

Because of the foreignKey column I can not just add a newRow to
DataSet
and
then use adapter's Update() method as it throws an exception.

I am using VC++ (VisualStudio 2005 and SQL Server 2005) for the
interface.

Thanks for any information.

Manjree

 

[William \(Bill\) Vaughn]

While I don't understand why you're using VC++, the code to do this can be
created by Visual Studio 2005 or you should be able to do it yourself.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill

Is it possible to bind data with Windows Forms in my current VC++ (MFC
application) project?

Manjree

If you use one of the binding techniques, this refresh is done
automatically.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill

I am just fetching the data from the DataTable's row and displaying in
the
list. The code is something like that:

DataRowCollection^ rows = samplesTable->Rows;
for (i = 0; i < rows->Count; i++)
{
row = rows->default;
supplierid = row->default[L"supplierID"]->ToString();
id = row->default[L"sampleID"]->ToString();
type = row->default[L"type"]->ToString();

int idx = lst.InsertItem(i, (CString)id);
lst.SetItemText(idx, 1, (CString)supplierid);
lst.SetItemText(idx, 2, (CString)type);;
}

After adding new records when I list the records It does not list the
newly
inserted records.

Thanks.

Manjree

:

How are you binding to the DataTable/DataView?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Thanks for your reply. I

I am using dataset to list all the records in CListView. And after
insertion
when I list all the records in the table It does not list the newly
inserted
records. Though if I close the application and run it again all the
records
are listed via DataSet. Means that for the first time it Fills all
the
records but after insertion it does not refresh it.

Manjree

:

By default, Fill simply reexecutes the query and appends the new
rows
to
an
existing DataTable... did you look for the new rows at the end?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hi

Is there any way to refresh a DataSet after inserting few records
in
a
table
(containing foreignKey column) using adapters' InsertCommand and
ExecuteNonQuery(). I tried adapter's Fill(dataSet) but it does
not
add
the
new records inserted to the dataset's table. I am doing something
like:

adapter->InsertCommand = gcnew SqlCommand(L"INSERT INTO Sample "
+
"(sampleID, type, dateReceived, dateExpiry,
supplierID)"+
" VALUES (@sampID, @type, @dtRcvd, @dtExp, @suppID)", conn);

int r = adapter->InsertCommand->ExecuteNonQuery();
adapter->Fill(dataset);

Ofcourse all the parameter's are defined and the record is
inserted
to
the
dataSource table.

Because of the foreignKey column I can not just add a newRow to
DataSet
and
then use adapter's Update() method as it throws an exception.

I am using VC++ (VisualStudio 2005 and SQL Server 2005) for the
interface.

Thanks for any information.

Manjree

 

[WenYuan Wang [MSFT]]

Hello Manjree
Thanks for Bill's help.

It seems Bill have resolved your issue.
Do you still have anything unclear?
Please feel free to update here if there is anything we can help with.
We're glad to assist you.

Have a great day,
Sincerely,
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hello WenYuan

Well! I am completely confused.
First I must explain my problem properly. I've a VC++ Project (VS2005). I
have to add database support to the existing project. I am using ADO.Net for
that.
I am trying to add a new record to a SampleTable that has a foreignKey
column. So I am using Adapter's InserCommand for that (as Update() throws an
exception because of the foreignKey).
The problem is that it adds record to to DataSource but when I refresh the
DataSet using Adapter->Fill(dataSet) It does not refresh it as I can not list
the newly added records in the list (CListView class).

As Bill suggested databinding, I can not find any way of adding a complete
Windows Forms to my exixsing VC++ project (though there are ways to include
windows form control).

Thanks for any information.

Manjree

 

[Guest]

Hi WenYuan

An Another problem with ADO.net using in VC++.

To delete a record in Parent Table, How can I set up the
ForeignKeyConstraint's DeleteRule. I am trying something like following with
parent column pCol and child column cCol:

sampFKC = gcnew ForeignKeyConstraint(pCol,cCol);
sampFKC->DeleteRule = Rule->SetNull;

which is giving error

error C2275: 'System:ata::Rule' : illegal use of this type as an expression

How do I declare 'Rule'.

Thanks

Manjree

 

[WenYuan Wang [MSFT]]

Dear Manjree

I think it should be something like
sampFKC->DeleteRule=System:ata::Rule::SetNull;

Hope this helps.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Dear Manjree,

According to your description, you meet an issue that the inserted data
rows are not retrieved by dataadapter. However, if you close the
application and open it again, all new records are listed via DataSet. If I
misunderstand anything, please correct me thanks.

For such issue, please check the following two sections.
1) Please make sure new records have been inserted into underlying database.
You may open the underlying database and check each table and rows.

2) Please change the code snippet you pasted in the initial post.
What if you clear the dataset and fill it again? Will all rows be
retrieved by DataAdatper?

int r = adapter->InsertCommand->ExecuteNonQuery();
dataset->Clear();
adapter->Fill(dataset);

If this is the case, the issue may relate to adapter or dataset.

Please kindly test the above method and let me know the result. We are glad
to assist you.
Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Thanks WenYuan. It worked.

I have got another problem. I am deleting records in the database using
stored procedure in sql2005. The procedure is:

CREATE PROCEDURE dbo.DeleteSupp (@suppID VarChar)

AS
/* SET NOCOUNT ON */
DELETE FROM Supplier WHERE supplierID = @suppID

DELETE FROM SamplePrep WHERE sampleID = ANY
(SELECT sampleID FROM Sample WHERE supplierID = @suppID)

DELETE FROM Sample WHERE supplierID = @suppID
RETURN


The problem is that it does not work when I supply a value for @suppID (say
'nnn')but if in place of @suppID I give any value say:

DELETE FROM Supplier WHERE supplierID = 'nnn'


then it deletes the record.

Thanks for your support.
Manjree

 

[Guest]

Dear WenYuan

You understood my problem very well.
I checked; the records are being inserted into the database but as you say
are not being retrieved by the adapter to refresh the dataset. I tried
exactly the same code you suggested but it clears the data set but doesnot
fill it again.

Thanks.

Manjree

 

[WenYuan Wang [MSFT]]

Dear Manjree,
Sorry for the delay, due to weekend.

I tried exactly the same code you suggested but it clears the data set but

does not fill it again.

Do you mean the adapter doesn't fill dataset again after we cleared dataset?
I suspect the select command of current adapter has been modified before we
use it to fill cleared dataset.

Select command is the component which Dbdataadapter used it for filling
dataset.
When we call Dbdataadapter'fill method, the implement of Dbdataadapter will
call its Select command's execute method to retrieve the data rows from the
underlying database. Thus, if the Dbdataadpter could not get rows from
underlying database, there is something wrong with select command.
Please check the commandText property of this command before filling data
into dataset

int r = adapter->InsertCommand->ExecuteNonQuery();
dataset->Clear();
System::Console::WriteLine(adapter->SelectCommand->CommandText);
// please let me know the output string.
adapter->Fill(dataset);

Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Dear Manjree,

Did you receive any error message when you executed SP from ado.net?
For such issue, I would like to suggest you use SQL profile to trace server.

1) Open SQL 2005 Server Management Studio.
2) Connect your Database Server and click "connect" button.
3) Click Tools|SQL Server Profile in the menu.
* Note: SQL server 2005 express edition doesn't have SQL Server Profile. If
you are developing with this edition, please let me know.
4) Connect your Database server and click "connect" button again in the
SQL Server Profile.
5) Create a new trace and select "Run" button with default configuration.
6) Reproduce the issue.
7) After repro the issue, please stop the select trace in SQL Server
Profile.
8) Save the trace file as SQL Server Profile trace file (*.trc)
(File|Saveas|TraceFile)

Please packet the trace file and send it to me ([email protected]).
By the way, please let me know the exact database name and stored procedure
name. I will review on it.

Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Dear WenYuan

When I use single table in adapter to fill in the dataset it works. But I
am using two tables (will be using more in future) of the database at the
moment in adapter. In that case it does not fill in the data set again.
Though it clears it. I checked the select command of the current adapter it
is same. I am doing something like:

String^ allSuppliers = L"SELECT * FROM Supplier";
String^ allSamples = L"SELECT * FROM Sample";
String^ select = String::Format(L"{0};{1}",allSuppliers,allSamples);
commandBuilder = gcnew SqlCommandBuilder(adapter);
conn->Open();
dataset = gcnew DataSet();
adapter = gcnew SqlDataAdapter(select,conn);
adapter->Fill(dataset);
DataTableCollection^ tables = dataset->Tables;
tables->default[0]->TableName = L"AllSuppliers";
tables->default[1]->TableName = L"AllSamples";
suppliersTable = tables->default[L"AllSuppliers"];
samplesTable = tables->default[L"AllSamples"];

This is the initialisation code that connects to the database and fills in
the dataset in the begining and works fine. Then I add a sample record in
another function AddSample() where I am doing something like:

dataset->Clear();
adapter->Fill(dataset);
DataTableCollection^ tables = dataset->Tables;
tables->default[0]->TableName = L"AllSuppliers";
tables->default[1]->TableName = L"AllSamples";
nRows = tables->default[L"AllSamples"]->Rows->Count;
str.Format("Records = %d ", nRows);
AfxMessageBox(str);

Here, it clears the dataset but does not fills it again as the no. of
records it gives is 0. I checked the commandTextProperty and the output
string it gives is:

SELECT* FROM Supplier;SELECT * From Sample

Which is same as earlier.

Thanks for all Information.

Manjree

 

[WenYuan Wang [MSFT]]

Hello Manjree,

The DBDataAdapter creates new DataTable named as Table in dataset, and
then fill data into it. If there have been already some tables in current
dataset, it will append new table behind them.

According to the code snippet, you have filled dataset before. Thus there
are two tables (AllSuppliers and AllSamples) in the current dataset. Then,
after a while, you fill the dataset again. As I mentioned above,
DBDataAdapter inserted two new DataTable behind AllSupplier table and
AllSamples table. DataSet->clear() method deletes all the rows in current
dataset, therefore, you notice there is no rows in
DataSet->tables->default[0] table.

Actually, new tables have been inserted as DataSet->tables->default[2] and
DataSet->tables->default[3]. Please check these tables, is there any row in
it?

For your case, what we need is dataset->Reset() method.
Reset() method clears all the rows in tables, deletes all the tables in
dataset also.
Then, new tables will be inserted as fist and second table in current
dataset again.
Please try the following method, and kindly let me know if this is what you
need.

dataset->Reset();
adapter->Fill(dataset);
DataTableCollection^ tables = dataset->Tables;
tables->default[0]->TableName = L"AllSuppliers";
tables->default[1]->TableName = L"AllSamples";
nRows = tables->default[L"AllSamples"]->Rows->Count;
str.Format("Records = %d ", nRows);
AfxMessageBox(str);

Hope this helps.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hello Wen

Thanks for the reply. It did work. So, Every time I refresh the dataset I
need to rename the tables. I m setting up the first column of each table as
the primary key. Do I need to reset the primary keys as well every time I
refresh the dataset ?

Regards

Manjree