[RESOLVED] Retrieve multiple records from ... where ...

Ola,

I know this is basic, but my mind is totally blanc. I'm trying to retrieve all records from a table with a specific code.

Code:

---

Dim cmd As New MySqlCommand("SELECT myValueRow FROM myTable WHERE  searchRow = 'test'", myconnection)
myreader = cm.executereader

while reader.read
  debug.print(.....)  '  eeeh.... can't use executescaler, want all of them. "reader.item("myvaluerow") also returns one value
end while

---

Thanks in advance.

vbnet Code:

myreader = cm.executereader

Shouldn't that be:-

vbnet Code:

myreader = cmd.executereader

?

Yeah, was a typo. Sorry. I typed it manually.
but not solving the issue :p

when SQL is guided with a criteria = test , i think that is y all records are not been returned

Quote:

---

"SELECT myValueRow FROM myTable WHERE searchRow = 'test'"

---

ultimately it is SQL which is extracts the rows of data, which is the matter of concern in this case.

next is lets say SQL returns all your needy data, but the code is not returning
of course your code returns all the data, but can be red one row only at a time, since you are using datareader.
if you need all at once then use dataadapter + dataset to return a datatable

what exactly you are looking for
yours mk me rn

I really don't understand what you are saying m8. Sorry.

Let's say that this is my table

Code:

---

1    radjesh  25
2    niya      25
3    m_m_r    26

---

I want to retrieve the people who have an "id" of 25, which should result in "radjesh" and "niya". With the code I used I only got "radjesh".
I hpe this example is a bit more clear.

It would help if you showed the actual code. There may be a problem that does not show up in the abreviated version of the first post.

Uuuuh... That is actually the whole code. Nothing fancy. ;)

vb.net Code:

conn.open
Dim x_id As String = x_tree.SelectedNode.Cells(3).Text
Dim cmd As New MySqlCommand("SELECT _pl FROM list WHERE gem_id = " & x_id, conn)
Dim reader As MySqlDataReader
reader = cmd_gemplaats.ExecuteReader
While reader.Read
   'debug.print(..)
End While
conn.close

Oh i got it rajesh, are you using MySQL
i feel nothing wrong with the SQL or your code, issue is at the table data

Check (1) what rows is being returned if the query is run in the database it self

Quote:

---

SELECT myValueRow FROM myTable WHERE searchRow = 'test'
GROUP BY myValueRow

---

is the query returning more than 1 row of id = 25 ?
please check

That query looks right to me. It seems you are searching for a string. Perhaps its a case-sensitivity issue or there is a space in one of the strings that cause the Where to evaluate it to false thus not return it.

Well, it is was a "space" issue, then it wouldn't return a single value/record, but it does return one when I use reader.ExecuteScaler.ToString.

If that is the actual code

Code:

---

While reader.Read
  'debug.print(..)
End While

---

There is nothing for it to do.

I thought perhaps there was something in the While that was causing an expected result but what you show here there is nothing there at all.

In my OP I told what I tried. This is simply what I tried. Come one... I'm not that stupid (I think...)

Sorry, You never know though I have seen and even experienced some pretty odd things, even from top notch programmers.

Ok Ok
but radjesh out of curiosity,, i insist that what is the out put of this query if runs with in your MySQL server command prompt

Quote:

---

SELECT myValueRow FROM myTable WHERE searchRow = 'test'
GROUP BY myValueRow

---

returns correct out put ?

Code:

---

conn.open

Dim x_id As String = x_tree.SelectedNode.Cells(3).Text

Dim cmd As New MySqlCommand("SELECT _pl FROM list WHERE gem_id = "' & x_id & '", conn)

Dim reader As MySqlDataReader

reader = cmd_gemplaats.ExecuteReader

While reader.Read

  'debug.print(..)

End While

conn.close

---

try this.

Sorry for the late reply. Had a friend over.

I tried it again with the same code and had a good and long look again. Suddenly I saw that the code was correct all the way. The debug.print returned all the values, but the real issue seems to be the way I want to present them. :sick:

I have an rtb on my form where I want to show the returned values.

This show all the values:
rtb.text = reader.item("column1").tostring (e.g. radjesh / m_m_r / datamiser / niya)

...But this only shows the last value only:
rtb.text = "testing " & reader.item("column1").tostring (niya)

Why does this happen and how do I fix this?

.text property of the RTB will set only the current text to display , it won't write or add the new line
try .appendtext property

Ah! Yes! the AppendText. Totally forgot about that one. Thanks m8.