|
since I've never users parameters i have a question - assume my table consists of 20 fields. does that mean I need to define 20 parameters, one for each field?
|
|
|
|
|
Yup.
Bastard Programmer from Hell 
|
|
|
|
|
Is there any workarounds on how to read the data from sql join using sqldatareader.
query ="SELECT TABLE1.FID FROM TABLE2 WHERE TABLE1.ID = TABLE2.ID"
Dim sqlRead As SqlDataReader
cmd.CommandType = CommandType.StoredProcedure
sqlRead = cmd.ExecuteReader
While sqlRead .Read()
Dim str as String = sqlRead("FID")
End While
Yes, I know that the good solution is to put "AS" clause on query.
Let say, TABLE1.FID AS FID.
But the problem is I have 100 StoredProcedures and each has no alias.
It's very difficult if I will modify all the SP just to put that clause.
Thanks in advance
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
You can retrieve datareader results by using the index, like this:-
While dr.Read
Dim id As String = dr(0).ToString()
id = dr.GetString(0)
id = dr.GetInt32(0)
id = dr.GetDateTime(0)
id = dr.GetDecimal(0)
End While
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
thank you for quick reply.
i found out also that if i will not put dr.read in an if statement, it causes an error. weird.
like this...
if dr.read then
while....
endif
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
I would change your
if dr.read then
to
if dr.hasrows then
This is a check to see if the datareader has any rows in its collection. further reading Datareader.hasrows[^]
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
|
|
|
|
|
how can i read the datareader backward.
let say i have data
number=1,2,3,4,5,6,7,8,9,10.
dr(number)
in dataset,
ds.Tables(0).Rows.Count - 1 To 0 Step -1
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
You can't read a datareader backward as it is a forward only reader. You will have to do an order by on your sql query to return the rows in the order you wish them. i.e
SELECT id, name FROM employees ORDER BY id DESC or something like that.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Hi,
Wayne's suggestion is viable, but using index numbers instead of field names is not recommended, because your code will become harder and harder to maintain, when you add more fields to your query.
Generally speaking you may want to adapt your code thus:
If sqlRead.HasRows Then
Dim str as String = String.Empty
Do While sqlRead.Read()
str = sqlRead.Item("FID").ToString
End While
End If
If your query has only a single field with the name FID, the DataReader will automatically translate FID to TABLE1.FID.
Just as a matter of interest, are you using MS SQL or some other DB, because I can't get your query to work? In other words, what I am wondering is, the problem may simply be the query, and not so much the code.
Cheers,
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
Ummm, If query is actual code in your app, it is not a stored procedure. That's straight up CommandType.Text. A stored procedure in your code would be the name of a stored proc in the database itself.
|
|
|
|
|
how to get connection string in vb.net.....???
i made database from vb.net itself
n copy connection string but i doesn't work....
can one help me in my problem
i used dis code....
Dim ConnectionString As String = "Data Source=|DataDirectory|\Database1.sdf"
//above codes gives some error
Dim Connection As New SqlConnection(ConnectionString)
Dim command As New SqlCommand("SELECT * FROM student", Connection)
Try
Connection.Open()
command.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
Connection.Close()
End Try
|
|
|
|
|
Have a look at www.connectionstrings.com[^]
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
|
|
|
|
|
1) You cannot copy a connectionstring and "hope" that it works. It won't.
2) You are reading a SqlCE database (a local file-based db), and using the SqlClient-objects (SqlConnection, SqlCommand) - that will not work. They are meant for use with Sql Server. SqlCe has it's own[^].
3) ExecuteNonQuery will never show results, only the number of rows that were affected.
Can you check the file-extension of your physical database-file? If you can tell us what type of database you're using, we could provide the correct access-code.
Bastard Programmer from Hell
|
|
|
|
|
Hi guys
I want to copy Content from Word Document (2007 or 2010) . and I am Using File Stream to do this action.
here is the Code
=====================================================
Dim strPath As String = "C:\Users\Toshiba\Desktop\Test.docx"
Dim fStream As New FileStream(strPath.ToString, FileMode.Open, FileAccess.Read)
Dim sReader = New StreamReader(fStream)
sReader.BaseStream.Seek(0, SeekOrigin.Begin)
While sReader.Peek() > 0
RichTextBox1.Text += sReader.ReadLine()
End While
sReader.Close()
====================================================
I don't no when I copy the Content to RichBox it copy garbage data ..
But when i copy from txt file it works fine with out any mistake .
But i need to copy from word document (2007 or 2010) because my User request is this .
so can any modified my code to copy from word without garbage data ..
i try to modify by set this
----------------------------------------
Dim sReader = New StreamReader(fStream, System.Text.Encoding.Unicode)
----------------------------------------
but no change on the result..
so am waiting your replays either by edit the above code or give my easiest way to do this action ..
thanks in advance
|
|
|
|
|
You cannot use a FileStream to read a Word document, as these files are not just text files but compressed xml files. To interact with Word documents, you need to use the Office Interop assemblies. Here[^] is a good place to start {MSDN).
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Hi.
I need a help with showing/hiding rows in datagridview by setting the trackbar value. The below example works if there are not too much rows. If there are greater number of rows then after you change a value of trackbar you must wait for a few seconds for the datagridview to redraw and only then you can use trackbar again...
Apparently I must use multithreading to achieve what I want: when I use trackbar, datagridview starts to redraw, but if I use trackbar again before datagridview finishes redrawing, it immediately stops redrawing according to previous trackbar value and starts to redraw from the beginning according to current trackbar value. That way trackbar will never be frozzen (stuck, unusable...).
I tried with backgroundworker but got error "Cross-thread operation not valid: Control '' accessed from a thread other than the thread it was created on." - Maybe didn't use it right?
P.S. I have old and slow computer. If you have new and fast computer maybe you wont experience what I'm describing here so try to increase number of added rows in line 'For i As Integer = 1 To 5000' (I added 5000 rows here) - and if you have even older and slower computer than you will have to decrease this value or you will have to wait to long after you drag the thumb in trackbar.
Public Class Form1
WithEvents trackbar1 As New TrackBar
WithEvents DataGridView1 As New DataGridView
Dim label1 As New Label
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Me.Controls.Add(trackbar1)
trackbar1.Location = New Point(0, 150)
trackbar1.Size = New Size(220, 140)
trackbar1.Maximum = 40
trackbar1.LargeChange = 1
trackbar1.SmallChange = 1
AddHandler trackbar1.Scroll, AddressOf TrackBar1_ScrollHandler
Me.Controls.Add(label1)
label1.Location = New Point(0, 200)
label1.Size = New Size(220, 140)
label1.Text = "Only rows with values in Column2 greater than 0 are visible"
Me.Controls.Add(DataGridView1)
DataGridView1.Location = New Point(0, 0)
DataGridView1.Size = New Size(220, 140)
DataGridView1.AllowUserToAddRows = False
DataGridView1.RowHeadersVisible = False
DataGridView1.TabIndex = 1
Dim column1 = New DataGridViewTextBoxColumn()
column1.Name = "Column 1"
column1.ValueType = GetType(System.DateTime)
column1.ReadOnly = True
DataGridView1.Columns.Add(column1)
Dim column2 = New DataGridViewTextBoxColumn()
column2.Name = "Column 2"
column2.ValueType = GetType(System.Int64)
column2.ReadOnly = True
DataGridView1.Columns.Add(column2)
Dim rnd1 As New Random
For i As Integer = 1 To 5000
DataGridView1.Rows.Add(DateSerial(2011, rnd1.Next(1, 12), rnd1.Next(1, 28)), rnd1.Next(0, 40))
Next
End Sub
Private Sub TrackBar1_ScrollHandler(sender As System.Object, e As System.EventArgs)
For i As Long = 0 To (DataGridView1.Rows.Count - 1)
If DataGridView1.Rows(i).Cells("Column 2").Value < trackbar1.Value Then
DataGridView1.Rows(i).Visible = False
Else
DataGridView1.Rows(i).Visible = True
End If
Next
label1.Text = "Only rows with values in Column2 greater than " + trackbar1.Value.ToString + " are visible"
End Sub
End Class
|
|
|
|
|
Hi,
I would change two things:
1. prevent the DGV from redrawing itself while you are changing the row visibilities.
2. Improve the speed of the trackbar loop.
So it might end up looking somewhat along these lines:
Private Sub TrackBar1_ScrollHandler(sender As System.Object, e As System.EventArgs)
Dim minval as int = trackbar1.Value
DataGridView1.SuspendLayout
For Each row as DataGridViewRow in DataGridView1.Rows
row.Visible = row.Cells("Column 2").Value >= minval
Next
DataGridView1.ResumeLayout
label1.Text = "Only rows ... " + trackbar1.Value.ToString + " are visible"
End Sub
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 2-Feb-12 21:40pm.
|
|
|
|
|
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)
|
|
|
|
|
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.
|
|
|
|
|
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
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.
|
|
|
|
|
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.
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.
|
|
|
|
|
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
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!
|
|
|
|
|
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.
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.
|
|
|
|
|
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  But as I said, it works ok till 50 000 rows, and I expect that number of rows will actually never be reached.
|
|
|
|
|
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.
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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
