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

[Radjesh Klauke]

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.

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.

[Niya]

myreader = cm.executereader

Shouldn't that be:-

myreader = cmd.executereader

?

[Radjesh Klauke]

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

[make me rain]

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

"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

[Radjesh Klauke]

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

Let's say that this is my table

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.

[DataMiser]

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.

[Radjesh Klauke]

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

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

[make me rain]

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

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

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

[Niya]

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.

[Radjesh Klauke]

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.

[DataMiser]

If that is the actual 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.

[Radjesh Klauke]

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

[DataMiser]

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

[make me rain]

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

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

returns correct out put ?

[marniel647]

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.

[Radjesh Klauke]

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.

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?

[make me rain]

.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

[Radjesh Klauke]

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