Visual Basic

Re: show/hide datagridview rows by setting trackbar value

3-Feb-12 11:51

Hi Luc!

Thank you very much for your time!

Unfortunately, the problem still exist in a same way as before. I see changes you have made, and agree that your way is better because you have made this part of code shorter and more clean, but still the whole thing doesn't work in a satisfying way (it still freezes when you move trackbar slider)

Alan N3-Feb-12 13:01

Alan N

3-Feb-12 13:01

Have you looked at how often your scroll handler is called? I would expect a continuous stream of events as the position is changed and scrolling from e.g. 1 to 10 will be refreshing the grid for each intermediate values.

Alan.

Re: show/hide datagridview rows by setting trackbar value

bezkintos3-Feb-12 14:43

Re: show/hide datagridview rows by setting trackbar value

3-Feb-12 14:43

I have just test it. I implemented a simple counter to count how many time handler is called when I move the slider on trackbar. As you can see in my first post my trackbar maxvalue is 40, so the slider can be moved across 40 trackbar ticks. I must say I expected just like you: if I move it from tick 1 to tick 10 it will call handler on each tick i.e. 9 times. But that's wrong, the answer is 2! Don't ask me why Smile | :)

Probably has something to do with a fact that when I start to drag the slider it doesn't freeze immediately but after it reaches first tick because till then it doesn't call handler i.e. it doesn't freeze. So thats 1. And if I continue to drag the slider it doesn't move (it stays on that first tick) but after a few seconds (probably after defreeze) it comes to that position I dragged it to. And that's 2. And that's the only explanation I can give to myself that has even little logic in it...

I did another test. I removed the loop which shows/hides datagridview rows. So, now trackbar doesn't freeze. If I drag slider slowly from 1 to 40, handler will be called 40 times, but if I move it faster it will be called 25 time, even more faster and it will be called 16 times... and the fastest as I can move it and it will be called 3 times. So even when it is not frozen by heavy code the number of calls will depend on speed you drag the slider...

modified 3-Feb-12 20:57pm.

Luc Pattyn4-Feb-12 3:30

Re: show/hide datagridview rows by setting trackbar value

4-Feb-12 3:30

Hi,

I looked somewhat closer, and I now think the bottleneck is you store your data as text inside TextBoxes, so all filtering requires a continuous stream of string-to-long conversions.

I performed an experiment (in C#) with a proper DataTable+DataGridView+BindingSource setup, and it works fluently. Here is the gist of it:

namespace WindowsFormsApplication1 {
	public partial class Form1 : Form {
		BindingSource binding;
		public Form1() {
			InitializeComponent();
		}

		private void Form1_Load(object sender, EventArgs e) {
			DataTable table=new DataTable();
			table.Columns.Add("Date", typeof(DateTime));
			table.Columns.Add("Long", typeof(long));
			Random r=new Random();
			for(int i=0; i<5000; i++) {
				table.Rows.Add(new DateTime(2011, r.Next(1,12), r.Next(1,28)), r.Next(0,40));
			}
			binding=new BindingSource();
			binding.DataSource=table;
			dgv.DataSource=binding;
		}

		private void trb_ValueChanged(object sender, EventArgs e) {
			long min=trb.Value;
			binding.Filter="Long >= "+min;
			label1.Text = "Only rows with Long >= " + min + " are visible";
		}
	}
}

Also, by using the BindingSource, it is Microsoft's responsibility to make it snappy, and I guess they did it right.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.

bezkintos4-Feb-12 6:22

Re: show/hide datagridview rows by setting trackbar value

4-Feb-12 6:22

Hi Luc!

Yes, this is much better! Although for extremely large number of rows it is still freezing like before but it works ok for 50 000 row, even 100 000 rows can be manageable which is much better than situation I got till now (much much better!).

One question. What did you mean by that I store my data as text inside TextBoxes? I haven't do that. But if something like that (string-long, long-string conversions) can cause slower performance maybe that's the reason why it is still slower for over 50 000 rows because I had to modify one of your lines from long min=trb.Value; i.e. Dim min As Long = trackbar1.Value to Dim min As String = trackbar1.Value.ToString because I got errors in two lines which comes after that. If your c# code worked for you than it seems that c# can combine string+long and VB needs to same data type while combining.

I must now try to implement your suggestion to my real project. In my real project I populate my datagridview from large number of structured txt files. Once my datagridview is populatetd it doesn't use those txt files any more so I thought that after this point the performance will be the same as when using databinding. I still don't understand why is it faster while using DataTable+DataGridView+BindingSource but I see it is and that's enough for now, I'll deal with theories later Smile | :)

I just hope I'll succeed in adding data from txt files to DataTable and than populate my datagridview from DataTable using BindingSource like you did - for now I don't see why it wouldn't work...

Thanks again!

Luc Pattyn4-Feb-12 6:43

Re: show/hide datagridview rows by setting trackbar value

4-Feb-12 6:43

Hi again,

1.
don't do Dim min As String, the minimum value IS a number, so keep it a number. Always try and use the most appropriate data type. Strings exist for humans (to visualize data that probably is not textual at all), not for computers.

2.
C# automatically applies .ToString() when you write a concatenation, as in "text"+number, it treats that like "text"+number.ToString()
As VB.NET doesn't do that, you have to add an explicit .ToString()

3.
My approach works on the DataTable where the "Long" column contains numbers, therefore the filtering is done based on numbers, not strings (even when you pass the minimum value as a string to the BindingSource); yours had just a DGV with two TextBoxColumns, and whatever sits in a TextBox is a string, so it was converting every TextBox's content to a number in order to do the comparison.

4.
It makes no sense to me to have 50,000 rows in a DGV, noone is interested in browsing such a list. Apply a filter, apply pagination, or any other scheme that limits the amount of data presented to the user, so things stay small and agile. Example: this web page only shows 10 or 100 messages, not the full million of messages CP is currently holding in its database.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.

modified 4-Feb-12 13:06pm.

bezkintos4-Feb-12 7:51

Re: show/hide datagridview rows by setting trackbar value

4-Feb-12 7:51

1. OK

2. OK

3. Now I understood what you have meant. With this lines I set my columns as Textboxes:

Dim column1 = New DataGridViewTextBoxColumn()
Dim column2 = New DataGridViewTextBoxColumn()

I actually forgot about those lines and thought that next two lines are determining the datatype of my columns

column1.ValueType = GetType(System.DateTime)
column2.ValueType = GetType(System.Int64)

for example when I sort column2 it is sorted like number, not like text, i.e. it is not sorted 1,10,11,2... but it is sorted like number 1,2,10,11... so that's probably why I forgot about those are actually textboxcolumns

4. I agree no one likes to scroll through 50 000 rows. In my application, no one has to because user first use several filters (trackbar is only one of them), and then in the end scroll among 20-30 rows. But all those data (rows) must be in datagridview because there are some calculations made that involves whole dataset. I probably could do it in a better way, but now is to late. I'm working on this for a long time, and changing the methodology now would be a big step back... I haven't anticipate some thing in the beginning so now I must struggle with it Sigh | :sigh:

But as I said, it works ok till 50 000 rows, and I expect that number of rows will actually never be reached.

Luc Pattyn4-Feb-12 8:02

Re: show/hide datagridview rows by setting trackbar value

4-Feb-12 8:02

3. you may be right, I would have to investigate further.

4. what you really should do then is keep the data in a DataTable, not in a DataGridView. A DataTable holds data, a DataGridView holds AND displays data, which is much more expensive. I suggest you still reconsider, switching from DGV to DT probably isn't that hard.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.

bezkintos4-Feb-12 8:34

Re: show/hide datagridview rows by setting trackbar value

4-Feb-12 8:34

I have already started DGV to DT boring job... Smile | :)

Now I'm sure that if i succeed in this, my application will work faster.
TNX!

Luc Pattyn4-Feb-12 9:19

having trouble replacing Trademark and Registered symbol with Regex

4-Feb-12 9:19

You're welcome.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.

David Mujica2-Feb-12 6:18

David Mujica

2-Feb-12 6:18

I'm trying to remove the ® and ™ symbols in a string using Regex and it doesnt work for the ™ symbol.

Here is some example code:

Dim s1, s2, s3 As String
      Dim i As Integer

      s1 = "Trademark" + Chr(153) + " Registered" + Chr(174)


      s2 = System.Text.RegularExpressions.Regex.Replace(s1, "[\x99]", "(TM)")
      s3 = System.Text.RegularExpressions.Regex.Replace(s1, "[\xAE]", "(R)")


      Debug.Print("Orig: " + s1)
      For i = 0 To s1.Length - 1
          Debug.Print("     " + s1.Substring(i, 1) + " " + Asc(s1.Substring(i, 1)).ToString("X") + " " + Asc(s1.Substring(i, 1)).ToString)
      Next

      Debug.Print("S2  :  " + s2)
      Debug.Print("S3  :  " + s3)

This code seems to work for the (R) symbol, but not the (TM).

However, if I use Chr(153) instead of \x99 in the regex call, the code works fine.

What am I doing wrong?
Thanks.

Re: having trouble replacing Trademark and Registered symbol with Regex

Luc Pattyn2-Feb-12 9:05

2-Feb-12 9:05

chr() and asc() are ancient ANSI-oriented functions.

A little experiment taught me chr(153) generates unicode character x2122.
Using AscW() rather than Asc() would reveal the truth about your strings.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.

ADO.net

Amanjot1-Feb-12 12:09

Amanjot

1-Feb-12 12:09

Hi,
I have data in Access Database which I am filling in the DataGridView to QA/QC raw data downloaded from a climate station. I am having trouble updating the database using update command. I am using following steps:
1. Locating access database:
Private Sub AccessDataFileButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AccessDataFileButton.Click
OpenFileDialog2.Filter = "Microsoft Access File(*.mdb)|*.mdb|Microsoft Access File(*.accdb)|*.accdb|All files (*.*)|*.*"
OpenFileDialog2.Title = "Input Data File with Real-time Water Quality Time Series"
If OpenFileDialog2.ShowDialog = DialogResult.OK Then
accessDataFile = OpenFileDialog2.FileName
'MsgBox("accessFile= " & OpenFileDialog2.FileName)
Me.AccessFileTextBox.Text = accessDataFile

End If

End Sub

2. Filling DataGridView:
Sub fillDataGrid1()
' Read Access Table to find date and time of the last data
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessDataFile
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
myConnection.Open()

Dim inpTable As String = "rawClimateData" 'Access database table
Dim cmd As String = "Select * from " & inpTable '& " ORDER BY mdateTime"
Dim dbCmd As OleDbCommand = New OleDbCommand(cmd, myConnection)

' create a data adapter
Try
da = New OleDbDataAdapter(cmd, myConnection)
Catch ex As Exception ' Catch the error.
MsgBox(ex.ToString) ' Show friendly error message.
End Try

' create a new dataset
Dim table As New DataTable
myDataset = New DataSet()
Try
Me.da.Fill(table)
da.Fill(myDataset, inpTable)
Me.DataGridView1.DataSource = table
Catch ex As Exception
MsgBox(ex.ToString)
End Try
myConnection.Close()

End Sub
3. Updating database (update button)
Private Sub TryTry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upDate.Click

Try

Me.da.Update(Me.myDataset.Tables("rawClimateData"))
Me.myDataset.AcceptChanges()

Catch ex As Exception
MsgBox(ex.Message)
End Try
MsgBox("upDateComplete")
End Sub

In some examples I have seen connecting database file while Form is loading; however, I have an option of browsing the access database file.

I know it should be simple, however, I am not a pro. Therefore, any tips/help would be greatly appreciated.

Cheers,

Amanjot

Re: ADO.net

Eddy Vluggen1-Feb-12 12:21

Create a unique Id for each row of table

1-Feb-12 12:21

"AcceptChanges[^]" only updates the object in memory, not the database itself.

I'd suggest you fire an UPDATE statement whenever you want to change something, or an INSERT if you want to add a tuple.

Bastard Programmer from Hell Suspicious | :suss:

Seema Bawa1-Feb-12 8:19

Seema Bawa

1-Feb-12 8:19

I have a table. It desn't have any Id which will be generated automatically.
my table (Table1)has 30 some rows and 3 columns(FirstName, LastName, Occupation).
I want to read each row one by one and wanted to create a unique ID which is of type uniqueIdentifier and fill another table with all these coulmns and newly created ID.

How can I do that

Simon_Whale1-Feb-12 12:16

Simon_Whale

1-Feb-12 12:16

you haven't said what the database is

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Eddy Vluggen1-Feb-12 12:30

1-Feb-12 12:30

Either Paradox 5.0 or DBaseIV format - which would you recommend?

Bastard Programmer from Hell Suspicious | :suss:

Simon_Whale1-Feb-12 21:58

Simon_Whale

1-Feb-12 21:58

Daffodil database[^] Big Grin | :-D

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Create a unique Id for Rows

RobCroll1-Feb-12 16:14

RobCroll

1-Feb-12 16:14

EDIT: Sorry I misread the question.
Could you iterate through the collection

For i As Integer = 0 To table.count - 1
	Dim selected As Row = table.row(i)
	newTable.add(New Row(i, selected.FirstName, selected.LastName, Occupation))
Next

Alternatively if you are adding to existing table you could use a GUID

For i As Integer = 0 To table.count - 1
	Dim selected As Row = table.row(i)
	newTable.add(New Row(Guid.NewGuid(), forstName, LastName, Occupation))
Next

- Use the database primary key and store in a column those width is 0.
- Some table rows have a tag property you can use to store the entity representing the record
- Some tables are bound directly to a data source, so by querying the selected row, you can access the unique Id.

For a more specific answer, you'll need to detail the scenario. At this stage we don't even know if your talking about a DataGrid, a DataTable or some other matrix type object.

"You get that on the big jobs."

Seema Bawa1-Feb-12 7:58

Seema Bawa

1-Feb-12 7:58

Re: Create a unique Id for Rows

Eddy Vluggen1-Feb-12 8:16

Optional parameters cannot have structure types

1-Feb-12 8:16

Why row by row, and not all at once?

SQL

SELECT NEWID()
     , [FirstName]
     , [LastName]
     , [Occupation]
  INTO MyNewTable
  FROM MyExistingTable

--edit;
You would have received more answers if you posted this in the Database[^]-forum.

Bastard Programmer from Hell Suspicious | :suss:

Clark Kent1231-Feb-12 5:53

Clark Kent123

1-Feb-12 5:53

That is according to MSDN[^]

I don't understand is... why? I am sure the answer is probably very simple, but for some reason my mind can't come up with the answer. Maybe it's cuz I am so frustrated with this error. Well, who knows.

What do I want to achieve?

Suppose the following...

Public Sub ChangeThePictureBoxBC(Optional newColor as System.Drawing.Color = Drawing.Color.Black)

Me.SomePictureBox.BackColor = newColor

End Sub

This does not seem wrong to me. Can someone please explain A) why the above sub is wrong without re-quoting the unhelpful MSDN document and B) what suggestion/advice can you give me to make an optional parameter that involves color.

Thanks in advance.

Re: Optional parameters cannot have structure types

Eddy Vluggen1-Feb-12 6:34