0
  Dim cn As New MySql.Data.MySqlClient.MySqlConnection("server=...; User ID=...; password=...; database=...")

    cn.Open()
    Dim command As New MySqlCommand("select * from ...", cn)
    Dim r As MySqlDataReader = command.ExecuteReader
    While r.Read
        Dim sb As StringBuilder = New StringBuilder()
        While r.Read()
            sb.AppendLine(r("messages"))
        End While
        TextBox2.Text = sb.ToString
    End While

This code gets all messages from the MySQL table. How can i make it to only last(not read before) messages?

aydinoid
  • 3
  • 4
  • Is there something in the data you can use - e.g. an auto-incrementing ID? If so, then something like `select * from ... where ID > @MaxIDFromLastResults` may work. – Mark Aug 16 '16 at 17:38
  • No there is nothing like that. It stores only the message. – aydinoid Aug 16 '16 at 17:42
  • 1
    Then how would you determine the "last" message? I don't think an rdbms guarantees that the default sort order on retrieval matches the insertion order (especially when deletions are possible). Any data model for a chat application that does not associate a timestamp with each message seems fundamentally broken. – Zastai Aug 16 '16 at 17:46
  • Using LIMIT and OFFSET _may_ work, although as @Zastai mentioned, that will only work if the data is returned in the correct order - e.g. `select * from ...LIMIT 1000 OFFSET @NumberOfLinesInTextBox2Text`. – Mark Aug 16 '16 at 17:53
  • @FatihAydin: If the only data you *have* is the message itself then how can you determine if a message has been previously delivered or not? You need to store more information. – David Aug 16 '16 at 17:56
  • Yes, I know, I need that information but I dont know how to create that information. – aydinoid Aug 16 '16 at 18:02

1 Answers1

0

Supply to the code some kind of marker for where it left off. An incrementing identifier for the messages? A timestamp? It's your data, so it's your call. But basically, if you were to look at the data in the database manually, what value would you look for to determine "where you left off"? That's the value you want.

Then it's just a matter of putting that value into the WHERE clause. Something like this:

Dim command As New MySqlCommand("select * from SomeTable where MessageDate > @lastKnownDate", cn)
command.Parameters.Add(new MySqlParameter("@lastKnownDate", lastKnownDate))
// lastKnownDate would be the DateTime variable passed to this code.
// alternatively, use some other value as a "bookmark"
David
  • 208,112
  • 36
  • 198
  • 279
  • 1
    To elaborate: think about your data design. You need something unique to identify each record; an auto-increment column gives you a handy marker where to continue. Storing the message date makes sense, too. – Matthias Urlichs Aug 16 '16 at 17:54
  • I don't understand how create that lastKnownDate data beside the message? – aydinoid Aug 16 '16 at 17:58
  • @FatihAydin: Well, it doesn't *have* to be a time stamp. It can be whatever you have available. The most common approaches would be a time stamp or an incrementing identifier. Essentially, any time you query the data to show to the user, you should at the very least be sorting by some value. If that value is always incrementing (as an identifier or a time stamp naturally would), then that's the value you'd use. Any time updated information is requested from this code, whatever is requesting it would supply the maximum previously known value. – David Aug 16 '16 at 18:01
  • I got the mechanism but I have problems with coding it, I am new with MySQL. Can you enlarge your example? You wrote how to call that marker but I don't know how to create that marker. – aydinoid Aug 16 '16 at 18:10
  • @FatihAydin: Where *specifically* are you stuck? Clearly I can't write all aspects of your application for you. Can you narrow down the problem a bit? – David Aug 16 '16 at 18:11
  • In my database, columns only have 1 information which is the message. They don't even have a name. I don't know how to add and additional part for the marker. – aydinoid Aug 16 '16 at 18:15
  • @FatihAydin: So you're asking how to add a column in MySQL? http://stackoverflow.com/questions/16113570/how-to-add-new-column-to-mysql-table (Or a Google search for "MySQL add a column") – David Aug 16 '16 at 18:31
  • Okay, lets say we have a column named "Delivered" and it is "0" by default. If application receives the message it changes to "1". Is it logical? If yes how to do it? Edit: The last message can be different for two user. So there must be delivered_user1 and delivered_user2 columns. – aydinoid Aug 16 '16 at 18:37
  • @FatihAydin: That wouldn't work if more than one user is ever going to read the messages. It also requires an `UPDATE` operation which wouldn't otherwise be necessary with a simpler design. It also stores less information than a simpler design. Why not just go with a time stamp? At the very least you should really add something which can be used to *sort* the records. Currently you have no guarantee of the order in which the messages are even shown to the user. Which would make a chat system somewhat... unusable. – David Aug 16 '16 at 18:44
  • Lets say lastKnownDate is 7:30 so app must request the messages after that time stamp. But in this code Dim command As New MySqlCommand("select * from SomeTable where MessageDate=@lastKnownDate", cn) you select the messages which sent in 7:30. Am I wrong? – aydinoid Aug 16 '16 at 18:58
  • @FatihAydin: You're right, my mistake. That was supposed to be `>` instead of `=`. I've updated the answer. – David Aug 16 '16 at 18:59