Refresh data after update/delete/insert

[davy_g]

Hi all,

I have created a little app that can view, delete, insert and update records in a SQL Server Db.
My current problem is that when I update a record I sill get the old record when I scroll to it again.
I only get it updated when I close and newly open this screen.
I know that the cause is that my dataset still has these old data, so I should renew my dataset in order to get the new data.

For reading the data in the screen I have following code:

  Dim con As New SqlConnection
  Dim ds As New DataSet
  Dim da As SqlDataAdapter
  Dim sql As String

  con.ConnectionString = "bla bla"

  con.Open()

  sql = "SELECT id, naam FROM adressen"
  da = New SqlDataAdapter(sql, con)
  da.Fill(ds, "Adres")

  con.Close()

  txtId.Text = ds.Tables("Adres").Rows(0).Item(0)
  txtNaam.Text = ds.Tables("Adres").Rows(0).Item(1)

Then comes my update which works excellent. After this I want to refresh my dataset to immediately show my updated values so I can freely scroll in the records.

I have the following code for this:

  con.Open()

  da = New SqlDataAdapter(sql, con)
  da.Fill(ds, "Adres")

  con.Close()

  txtId.Text = ds.Tables("Adres").Rows(inc).Item(0)
  txtNaam.Text = ds.Tables("Adres").Rows(inc).Item(1)

It does not seem to work. I see my change directly disappear.
When I newly open my screen I have my value back again.

Oh the "inc" is just a counter which knows in which record I am working.

I've read something about disposing my dataset and then newly read it but this does also not work.
I would set ds.Dispose() in front of the rest of the code.
Is disposing an item clearing it from the memory (what they call the garbage collection?)?

Can anybody help me? What am I doing wrong?

Thanks

 

[davy_g]

I'm using following SQL but I get following error:

Error in list of function arguments: '=' not recognized.
Unable to parse query text.

SQL:
SELECT SUM(IIF(Dienst = 'A', 1, 0)) AS Count_A,
SUM(IIF(Dienst = 'B', 1, 0)) AS Count_B,
SUM(IIF(Dienst = 'C', 1, 0)) AS Count_C,
SUM(IIF(Dienst = 'D', 1, 0)) AS Count_D,
SUM(IIF(Dienst = 'F', 1, 0)) AS Count_F,
SUM(IIF(Dienst = 'J', 1, 0)) AS Count_J,
SUM(IIF(Dienst = 'L', 1, 0)) AS Count_L,
SUM(IIF(Dienst = 'M', 1, 0)) AS Count_M,
SUM(IIF(Dienst = 'S', 1, 0)) AS Count_S,
SUM(IIF(Dienst = 'T', 1, 0)) AS Count_T
FROM Uren
WHERE Datum >= ? AND Datum < ?

What am I doing wrong?

I've also tried SWITCH but as I here have to give a = also, it also does not work

 

[cjard]

How annoying. The query is actually legal access syntax

(go to http://www.w3schools.com/sql/sql_tryit.asp
and use: SELECT SUM(IIF(city = 'Portland', 1, 0)) FROM customers )

but i think the IDE might be trying to be too clever in understanding the query.

I would advocate storing this complicated part as a Query (VIEW) in the database, then selecting from that.

In VB2005. It gets me too, more so with oracle because the IDE really doesnt understand advanced oracel syntax. Most my queries for reporting go into views in the database.

Make the view like:

SELECT 
Datum,
(IIF(Dienst = 'A', 1, 0)) AS _A, 
(IIF(Dienst = 'B', 1, 0)) AS _B, 
(IIF(Dienst = 'C', 1, 0)) AS _C, 
(IIF(Dienst = 'D', 1, 0)) AS _D, 
(IIF(Dienst = 'F', 1, 0)) AS _F, 
(IIF(Dienst = 'J', 1, 0)) AS _J, 
(IIF(Dienst = 'L', 1, 0)) AS _L, 
(IIF(Dienst = 'M', 1, 0)) AS _M, 
(IIF(Dienst = 'S', 1, 0)) AS _S, 
(IIF(Dienst = 'T', 1, 0)) AS _T
FROM Uren


Then do this in the IDE:

SELECT 
SUM(_A), 
SUM(_B), 
SUM(_C), 
SUM(_D), 
SUM(_F), 
SUM(_J), 
SUM(_L), 
SUM(_M), 
SUM(_S), 
SUM(_T)
FROM Uren_View
WHERE Datum >= ? AND Datum < ?



Often i wish the IDE would stop trying to be clever, and read the query you write.. It should just run it and  look at the datatypes of the result.. Bah

 

[davy_g]

Yesterday I've tried your method cjard and it works fine. Now I can throw away all these obsolete tableadapters

Just a few more items and I can rollout my first version