Refresh DataSet - please Help Me :-(

[chreo]

I GIVE UP...I can't refresh datagrid (dataset)

I have datagrid = dgrPaczki
Datasource of that datagrid = DataSetPaczki1.PACZKI_PAKOWALNIA (forgive me Polish words)
Dataadapter which fills my DataSetPaczki1 = adaPaczki
Dataadapter selects rows returned by user defined function = PACZKI_Z_AKCJI
To select rows from that function it uses sqlCommand = comPaczki

I will explain how it works:

First : sqlCommand (comPaczki):
Me.comPaczki = New System.Data.SqlClient.SqlCommand
Me.comPaczki.Connection = Me.SqlConnection1
Me.comPaczki.Parameters.Add(New System.Data.SqlClient.SqlParameter("@AKCJA_ID",.....Int,..., "0"))
Me.comPaczki.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DATA_OD",...SmallDateTime.., ""))
Me.comPaczki.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DATA_DO",...SmallDateTime.., ""))

Dim strSQL As String - (this is SelectCommand for DataAdapter)
strSQL = "SELECT * FROM " & "PACZKI_Z_AKCJI" & "(@AKCJA_ID,@DATA_OD,@DATA_DO)"


When I click button then dataadapter fills dataset:

Me.adaPaczki.SelectCommand = Me.comPaczki

Me.comPaczki.Parameters(0).Value = cmbAkcja.SelectedValue
Me.comPaczki.Parameters(1).Value = datOd.Value
Me.comPaczki.Parameters(2).Value = datDo.Value

DataSetPaczki1.PACZKI_PAKOWALNIA.Clear()
adaPaczki.Fill(DataSetPaczki1, "PACZKI_PAKOWALNIA")
(and then is code for tablestyle - which hides some columns, changes size of colums... but I think it is not a problem)

-------------------------------------------
NOW WILL BE A PROBLEM
-------------------------------------------

Everything works fine - I can hit the button and datagrid shows always rows.
I can change the parameters from comPaczki (for example startDate and endDate).
And it always fills the datagrid.

But...

I have command which allows user to add new row to SQL database.
It works also fine. No errors.
When I check in MS SQL - new row is added.

But...

After that I clear DataSet.
DataSetPaczki1.PACZKI_PAKOWALNIA.Clear()

Then I fill dataadapter again.
adaPaczki.Fill(DataSetPaczki1, "PACZKI_PAKOWALNIA")

And it shows old rows!!
:-(

Why...? Why... :-(

Please help me.
I am wasting third day for that.
I have checked so many pages and forums...
No solution.

 

[Cor Ligthert]

Chreo,

Can you try to set after your fill the datagrid.datasource = nothing and
than again
datagrid.datasource = DataSet.packets.gluepackets

Cor

 

[chreo]

Ok... I did it...
But still is the same problem.

But I found something.
I had only one dataadapter.
I added second dataadapter.

And after adding a row to database I use second dataadapter to fill dataset.
But when I add another row to database and use again second dataadapter then
it doesn't work.

So I still have a problem,
I can add 1000 dataadapters to my form and after every change in database
use another dataadapter.

Maybe I can delete everytime the same dataadapter and create it again?
I am trying to do this but it doesn't work.

Is it possible that old values (old rows) are saved in dataadapter?
I thought that rows are saved in some other place (dataset?)
But I wrote that earlier - even if I cleat dataset - old rows come back.
But if I use new dataadapter to fill dataset - it works...

BUT HOW TO USE THE SAME DATAADAPTER EVERYTIME? :-(

 

[Cor Ligthert]

Chreo,

You write first SQL command. Are you maybe adding more parameters than you
think and therefore is it with the second fill parameter 4,5,6 etc, while
1,2 and 3 are still unchanged?

Just a thought,

Cor

 

[chreo]

It is not problem with adding paramters, because they are added only on
begining and after that only values of parameters change.

I found something mysterious, but when I find out where is the reason then
maybe I solve my problem...
Read this:

I don't need to add new dataadapter to fill datagrid after adding new row.

I can use one dataadapter and add new row, and then another...and another...

But after about 20-25seconds I can't add any rows

Is it some timeout?
In which control?

 

[Cor Ligthert]

Chreo,

I looked what more at your code.
I never did it that way so I am guessing.

You are forever filling 4 tables.
From which one is that packet one.
Than you clear from the dataset that packet one
fill the packet one
And refill the others again
Than you clear from the dataset that packet one
fill the packet one
And refill the others again

(I use for every table in this case another adapter)

Is that what you are up to?

Cor

 

[chreo]

Cor Ligthert - You are the only one who tries to help me.
Thanx.
But my problem is so weird so even I don't know what is going on.

I cut my code and now is only one adapter which fills DataSet everytime
(after adding row)

But I don't know why everything works only 15 seconds (I think that is
timeout of SQLConnection).
If I add 5 times new row (every row in 3seconds) then all 5 rows are added
to datagrid.
After 15 second I still add new rows but only to database (they don't appear
in datagrid)

But where is the reason
I dispose dataadapter and add it again after new rows.
I do it also with my command which selects rows.

I am still looking for that timeout.
Where it can be?

 

[Cor Ligthert]

Chreo,

It is weird as you say. In your situation would I make a very simple program
that simulates what you do with only those essentials. And than try that.
When you than still have problems you show that, what makes it easier to
help you as well.

And than to get more help it probably better to ask this in the newsgroup

Adonet
news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet

Web interface:
http://communities2.microsoft.com/c.../?dg=microsoft.public.dotnet.framework.adonet

With this not telling you to leave here, I am active in that newsgroup as
well.

However when you dump it in with the documentation as your problem is now,
do I give you not much change, however you can try that as well first (what
I would not do, I would make first that little test program. There is much
more change on help when it is easy to understand).

Cor

 

[Doug Taylor]

Chreo,

It is weird as you say. In your situation would I make a very simple program
that simulates what you do with only those essentials. And than try that.
When you than still have problems you show that, what makes it easier to
help you as well.

And than to get more help it probably better to ask this in the newsgroup

Adonet
news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet

Web interface:
http://communities2.microsoft.com/c.../?dg=microsoft.public.dotnet.framework.adonet

With this not telling you to leave here, I am active in that newsgroup as
well.

However when you dump it in with the documentation as your problem is now,
do I give you not much change, however you can try that as well first (what
I would not do, I would make first that little test program. There is much
more change on help when it is easy to understand).

Cor

Firstly let me say I agree with Cor, the best approach to a problem
like this is to reduce it to a very simple problem and then add
complexity and see where it goes wrong.

Secondly, the usual approach to refreshing a dataset is to have one
dataadapter and use it to fill your base dataset.

When you want to refresh it

1) Update the database with any changes made.
2) accept changes
3) create a second dataset and fill that with what has changed in the
database, ideally using a timestamp type field.
4) merge this dataset into your original dataset, .Net will then
produce a single dataset.
5) If you need to identify what has changed you can do that as well
with the appropriate calls.

Doug Taylor

 

[Cor Ligthert]

Doug,

Secondly, the usual approach to refreshing a dataset is to have one
dataadapter and use it to fill your base dataset.

Are you sure of that, I never saw it. As well does the designer not directly
take this choose in my opinion and when you use it won't create the update
statements for you.

I only use the designer to test something so I am not so sure.

Cor

 

[Doug Taylor]

Doug,
Are you sure of that, I never saw it. As well does the designer not directly
take this choose in my opinion and when you use it won't create the update
statements for you.

I'm probably wrong as I don't normally use datatables, I prefer the
business objects approach, but we won't get into arguments over that,
in my experience the dataadaptor creates one dataset which may contain
multiple tables, and multiple command objects one for select, insert,
update and delete, which it attaches to the dataadaptor command set.

Sure if you throw another table on it will create another dataadapter,
but it would be better to alter the schema for the dataset and have
them all handled together. So you have the model dataadapter is the
bottom layer handling the connection and commands and the dataset
handles the data and interaction with the GUI.

I personally don't like the automatically created statements and tend
to roll my own or more likely use stored procedures, that way you can
separate the maintenance of the statements from your app code.

Conceptually the dataset is a remote copy of your database and is an
extremely powerful object almost a full RDBMS in its own right and the
ability to extract subsets of a database, i.e. all values that have
changed since the last accept changes, rollback and merge with an
equivalent or almost equivalent dataset greatly reduce the amount of
code you need to write

 

[Cor Ligthert]

Dough,

I'm probably wrong as I don't normally use datatables, I prefer the
business objects approach, but we won't get into arguments over that,
in my experience the dataadaptor creates one dataset which may contain
multiple tables, and multiple command objects one for select, insert,
update and delete, which it attaches to the dataadaptor command set.

Do you have for me maybe an update SQL string as samples that updates 4
tables with one dataadapter in one time. It looks interesting to me.

I self have the approach that I use one dataadapter and change the commands,
however that is almost in my opinion the same as using more dataadapters.

Therfore I am very curious to your solution.

Cor

 

[Doug Taylor]

Dough,



Do you have for me maybe an update SQL string as samples that updates 4
tables with one dataadapter in one time. It looks interesting to me.

I self have the approach that I use one dataadapter and change the commands,
however that is almost in my opinion the same as using more dataadapters.

I didn't make myself clear, unless you are using a DB like Oracle 8 or
above where you can write update commands that span tables, you will
need to either have multiple command objects and switch them, or
multiple dataadaptors or change the commandtext of the command
objects, it is fairly trivial to write stored procedures that update
multiple tables, at least it is with Oracle's Pl/SQL or Java, I don't
know enough about SQL Server to comment there.

Sorry if I've mislead you.