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]

Hmm I've just read an interesting article about disposing. It claims that I should directly dispose an object after I've used it because when I do it later it may not be possible (don't know the reason anymore).

So back to my code, how should I adapt it in order to get it working?
Is it ds.Dispose() which I should set right after I close my connection?

 

[cjard]

Your data access is pretty much all wrong, especially for your version of .NET. I'd recommend you go the new way of data access for a start, and that you use binding instead of manually transferring the values in and out of the text boxes.

The purpose of an update/insert/delete via the Update() statement is to syncronise the database with the local copy. No refresh should be needed (for the same reason that you dont close and re-open a word document every time you save it)

 

[alander]

Wrong use doing hardcode in 2.0, use tableadapters, bindingsource instead..

alll I have to do is drag and drop via the dataset designer

 

[davy_g]

So how should I use my data access then? How does this binding work?

This drag 'n' drop, is this the way to go in .NET 2.0?
If yes, how can I read data out of a table where e.g. the field type in the db should be 2? I only want to display records with type 2 in this window.
And how do I update/delete/insert new records?

So no working with SqlCommand anymore?

And how does it work using OOP? Let's say I have 2 objects, firstline incident and secondline incident (the only thing they differ in is the field type in the table incidents). Or is it 1 object because it is only 1 db-table?

I do my research via internet and there is so much to find that it is hard, as a n00b, to know what to do.

 

[Arg81]

The best thing to do is read through Cjards link to DW2.0 - a load of MSDN guides to the .net 2.0 framework and using Visual Studio 2005.

With using designers, you simply create dataTables within a dataset visually. you can then have queries set to each dataTable - so 1 query would be to FillByFieldType.

All you do on your form is bind control(s) to your DataTable, and call the query, usually along the lines of

me.tableAdapter.FillByFieldType(me.dataset.datatable, "2")

that will then run the query and retrieve all rows where the field type is 2. Obviously in the above example you would have your own set names for
tableAdapter
dataSet
dataTable

for example;

me.CustomerTableAdapter.FillByCustomerName(me.dsCustomer.Customer, "Amazon")

As I say, best bet is the link in Cjard's sig. If you can't see the one I mean, it's this one
http://msdn2.microsoft.com/en-us/library/fxsa23t6(vs.80).aspx
^^ This is for .net 2.0 and VS2005

Good luck , if you need any further help just post up and someone can try to lead you the right way.

 

[cjard]

So how should I use my data access then?

As per the DW2 link in my sig (i know you hate online tutorials, but just follow some of them through, and then once you know the end result I can fill in the ionfo on how it gets there under the hood. If I teach you that now, it would be like trying to teach mechanics to a 10 year old; they cant drive so they are unlikely to truly appreciate whats going on. COme the time when the kid can drive and sees how a car performs etc, he might be more interested in whats going on in the engine)

How does this binding work?

Embodiment of the MVC concept, read up on MVC in wikipedia if you never heard of it. Simply put, the Model is the data, the View is how the data is shown, and the Controller is how the data is manipulated. In a real world example, think of one of those machines at a gaming arcade where you have to control the grab hook to win yourself a cuddly toy. The Model is the toy/toys. The View is the light-distorting window they put in to make it impossible to judge where the toy is. The controller is the 2 buttons for the grab hook.
In OO terms, the data lives in a DataTable. A GUI element might be a view (read only Label), controller (Delete button) or both (Textbox views and also changes data). THe important thing is the data is not stored in the gui element any more. None of this myCombo.ListItems.Add() rubbish

Binding conencts the V/C to the M. When the model changes, binding will cause the control to update its display. When the controller makes a change, binding applies the change to the model. Binding is a connector, nothing more.

This drag 'n' drop, is this the way to go in .NET 2.0?

DnD writes code for you. If you wrote it yourself, it would be the same ends with a lot more work. Remember, there is no magic here, no voodoo. Is all just a GUI designer writitng back end code. Its the way Visual Studio is going, yes.. thats why it's VISUAL Studio, not Notepad

If yes, how can I read data out of a table where e.g. the field type in the db should be 2?

2 is a value of Type integer. There is no Type "2". Be careful when using reserved words when talking to other OO programmers. If your database column is called Type, I recommend renaming it also, because it might even be confusing for the IDE, let alone the programmer (Just try calling a db column System and watch it fall apart)

I only want to display records with type 2 in this window.

Well, you've gone from askjing a very generic question about the framework and the way the MVC concept works, to the very soecific case of how to work with record matching an exact criteria of 2 something.. If you read the tutorials and try them out (its really worth taking a day out of your working life to understand the general before launching into the specific) then you would know how to solve your specific case..

And how do I update/delete/insert new records?

It's all in the tutorials..

So no working with SqlCommand anymore?

Given that you're an oracle guy, I'm surprised you ever did. SqlCommand is for SQLServer. No, you dont work with commands directly any more, in the same way that you dont write code like this just to put a button in the bottom right of the form:

// 
      // signOffOrCommentButton
      // 
      this.signOffOrCommentButton.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | 
System.Windows.Forms.AnchorStyles.Right)));
      this.signOffOrCommentButton.Enabled = false;
      this.signOffOrCommentButton.Location = new System.Drawing.Point(693, 469);
      this.signOffOrCommentButton.Name = "signOffOrCommentButton";
      this.signOffOrCommentButton.Size = new System.Drawing.Size(114, 23);
      this.signOffOrCommentButton.TabIndex = 6;
      this.signOffOrCommentButton.Text = "Sign Off or Comment";
      this.signOffOrCommentButton.UseVisualStyleBackColor = true;
      this.signOffOrCommentButton.Click += new System.EventHandler(this.SignOffOrCommentButton_Click);

It's C# but youll get the gist.. You drag, drop and set properties, you dont generate thousands of lines of code like that by hand. The IDE makes data access very easy by writing code and putting it all in logical relevant places. Its also good, correct code and not intended for you to mess with it. Dont perceive this as losing control though; Troy has that problem, and he still writes thousands of lines of code needlessly to get to the stage I do with a couple of clicks. Maybe I'm lazy, but I'm happy to devolve responsibility for generating tedious DAL code to a tool; it does a better job in secodns than I can do in weeks, and I'm good.

And how does it work using OOP? Let's say I have 2 objects, firstline incident and secondline incident (the only thing they differ in is the field type in the table incidents). Or is it 1 object because it is only 1 db-table?

Youre thinking/worrying about this a little too much.. The object here is a IncidentDataRow. It will have a .IncidentType (dont call it .Type) property. The db table is modeled by the IncidentDataTable object. IncidentDataTable can be considered a collection of IncidentDataRow

I do my research via internet and there is so much to find that it is hard, as a n00b, to know what to do.

Just take a day out to STOP WORKING ON YOUR PROJECT, and DO THE EXAMPLES as they stand. Yep that means downloading the Northwind database. Yep that means not having anything to show your boss at 5pm, but its on-the-job training, which we all must do(unless you wanna take this home, depends on your dedication) if we want to stay ahead in the technology game.

 

[davy_g]

Wow thnx for your help you both!

 

[JohnH]

Thread split, please stay on topic, and start new threads for new topics.

 

[davy_g]

Yesterdayevening I've tried to use this way of programming. I've dragged a few columns in my form and saw that 4 items where set on my form. In the background a dataset and tableadapter were created with the methods Fill and Get (don't know what that is now).
I immediately got my data in my screen and could scroll, insert, delete etc and that with so less effort!

I've also created a new tableadapter and dataset with which I get specific data which are used for reportingpurposes. In my data sources windows I saw that this dataset was automatically added.
I've dragged it into my form and voila, I got my data. Deleted the navigatorbinding (believe it's called that way), set the textboxes to disabled and voila, got my data.

So for each new SQL with which I read data I should create a new tableadapter and dataset. I could make little variations in the same tableadapter but for totally different SQL's I needed to create new tableadapters and datasets.
I also found out where I could manipulate e.g. my DELETE-statement and so on.

Tonight I'll try to experiment some more.

 

[cjard]

Yesterdayevening I've tried to use this way of programming. I've dragged a few columns in my form and saw that 4 items where set on my form. In the background a dataset and tableadapter were created with the methods Fill and Get (don't know what that is now).

Fill and Get do:
Fill an existing datatable
Get a new datatable

To see the background code, click Show All Files toolbar button (use tooltips to find) in Solution Explorer and look in DataSet1.Designer.vb file
Dont mess!

I've dragged it into my form and voila, I got my data. Deleted the navigatorbinding

Binding nav is only a component that changes the .Position of the BindingSource, you are right in that it is not needed

So for each new SQL with which I read data I should create a new tableadapter and dataset.

If one sql returns same data as another, then put it in the already existing tableadapter
e.g these sqls:

SELECT * FROM Person WHERE name = :NAME
SELECT * FROM Person WHERE age = :AGE
SELECT * FROM Person WHERE insurancenum = :INS_NUM

Go in the PersonTableAdapter and have names you decide. I recommend:
FIllByName
FillByAge
FillByInsuranceNumber

I could make little variations in the same tableadapter but for totally different SQL's I needed to create new tableadapters and datasets.

True

I also found out where I could manipulate e.g. my DELETE-statement and so on.

Tonight I'll try to experiment some more.

Have fun

 

[davy_g]

If one sql returns same data as another, then put it in the already existing tableadapter
e.g these sqls:

SELECT * FROM Person WHERE name = :NAME
SELECT * FROM Person WHERE age = :AGE
SELECT * FROM Person WHERE insurancenum = :INS_NUM

Go in the PersonTableAdapter and have names you decide. I recommend:
FIllByName
FillByAge
FillByInsuranceNumber

Well I've tried something like this yesterday but it didn't work out exactly as I wanted it to.

I have a table called Uren (=hours) with a field Dienst (=shift) in it. The user can give in several values like A, B, C and so on.
For a certain period I want to see how many A's, B's and so on I have so I've created a new tableadapter with dataset and used following SQL:

SELECT COUNT(Dienst) AS Totaal FROM Uren WHERE Dienst = ? AND Datum >= #2007-08-01# and Datum < #2007-09-01#

I thought to be clever using this parameter and then give in this this value in my code using the code

Me.TableAdapterUren.FillByA(Me.DataSetUren.Uren, "A")

The value of this Fill would fill the textbox txtA on my screen.
I also have another textbox, txtB, on my screen and want to have the amount of B's in it, so normally I would use the code

Me.TableAdapterUren.FillByB(Me.DataSetUren.Uren, "B")

If I use both codes under each other I only get my B's because the result of the A's is overwritten by my B-code.

I've solved this by creating one tableadapter and dataset for my A's, one for my B's and so on.
It works fine but....too many tableadapters and datasets in my opinion.

Is there no easier way to get the same result?

Thanks

PS I am not that far that I have a datagridview on my form with which I can delete/insert/update data, 1 textbox that is automatically filled with the current year and 1 combobox that is automatically filled with the current month (long name: August).
My A's and B's and so on are automatically counted whenever I change my selection in my textbox or combobox and press the button "Refresh", and my datagridview is automatically updated.

 

[Arg81]

instead of declaring it like "A" and "B" , have a textbox on your form where you type in A or B, and change it so

Me.TableAdapterUren.FillByB(Me.DataSetUren.Uren, me.textbox1.text)

Whatever is typed into the textbox is used as the variable. You could even change the textbox to a label, and use radio buttons for A B and C , and when one is selected the value is shown on the label - you can then use the label text as your parameter.

Or have a combobox and put some items in it. You would then use me.combobox1.text as the variable...

 

[cjard]

Well I've tried something like this yesterday but it didn't work out exactly as I wanted it to.

I have a table called Uren (=hours) with a field Dienst (=shift) in it. The user can give in several values like A, B, C and so on.
For a certain period I want to see how many A's, B's and so on I have so I've created a new tableadapter with dataset and used following SQL:

SELECT COUNT(Dienst) AS Totaal FROM Uren WHERE Dienst = ? AND Datum >= #2007-08-01# and Datum < #2007-09-01#

I thought to be clever using this parameter and then give in this this value in my code using the code

Me.TableAdapterUren.FillByA(Me.DataSetUren.Uren, "A")

I wouldnt have done that.. instead, I'd have done this:

SELECT SUM(IIF(Dienst = 'A', 1, 0)) as Count_A, SUM(IIF(Dienst = 'B', 1, 0)) as Count_B FROM Uren WHERE Datum >= ? and Datum < ?


How this works:
Choose all records between the parameterized dates. The first column, if Diesnt is an A, it becomes 1, otherwise it becomes 0. Similar logic for the B column. Now if we SUM all the 1 and 0 we get a count of how many were a and how many were b

We make our tableadapter have a name of FillCountOfDienstBetweenDates

We bind textbox A to the column Count_A
We bind textbox B to the column COunt_B

Every tme we fill the table, the boxes show the new values. The good thing now is our dates are parameterised so we can reuse this tomorrow with different dates

The value of this Fill would fill the textbox txtA on my screen.
I also have another textbox, txtB, on my screen and want to have the amount of B's in it, so normally I would use the code

Me.TableAdapterUren.FillByB(Me.DataSetUren.Uren, "B")

No, you wouldnt, because youre deleteing the contents of Uren and refilling with a different value. Now both your text boxes will show what is in Uren, i'e' the count of B

If I use both codes under each other I only get my B's because the result of the A's is overwritten by my B-code.

I think you can understand why..

I've solved this by creating one tableadapter and dataset for my A's, one for my B's and so on.
It works fine but....too many tableadapters and datasets in my opinion.

Yep, youre way overcomplicating it, but thats okay.. Its just youre not quite 100% with the understanding yet.

This is not the sort of problem you can easily solve with FILL if youre not an SQL guru. You would have been better doing:

Right click existing Uren tableadapter
New Query
"SELECT THAT RETURNS A SINGLE VALUE"
Put in: SELECT COUNT(*) AS Totaal FROM Uren WHERE Dienst = 'A' AND Datum >= ? and Datum < ?
Call this query GetCountOfADienst

Right click existing Uren tableadapter
New Query
"SELECT THAT RETURNS A SINGLE VALUE"
Put in: SELECT COUNT(*) AS Totaal FROM Uren WHERE Dienst = 'B' AND Datum >= ? and Datum < ?
Call this query GetCountOfBDienst


Work it like:

textBoxA.Text = urenTableAdapter.GetCountOfADienst()
textBoxB.Text = urenTableAdapter.GetCountOfBDienst()



Dont try and use Fill for everything; become familiar in your mind with what happens if you refill an existing container with new data. Think carefully aout the difference between a fill and a get

Is there no easier way to get the same result?

Yes, lots of them Here are just 2. I'd go the Fill route (my fill way) but thats because I know how to pull SQL tricks to do it. The Get route i put ere may be easier to understand

PS I am not that far

Youre doing great, trust me

 

[davy_g]

Thanks a lot you both, this evening I'll try it again. I think I'll try the method with

SELECT SUM(IIF(Dienst = 'A', 1, 0)) as Count_A, SUM(IIF(Dienst = 'B', 1, 0)) as Count_B FROM Uren WHERE Datum >= ? and Datum < ?

And then we'll see from there what we'll do next