My database table is not ordered

Sometimes when I insert a new row to a table in my database, it comes before the last row.

I will explain. If I have the following table:

ID Name
1 James
2 Terry
3. Miriam
4. Arthur

and I want to insert a new row with "Danny", sometimes this happens:

ID Name
1 James
2 Terry
3. Miriam
5. Danny
4. Arthur

My code works in 99 percent of the cases, but sometimes it just happens. I do not know what to do? Is this normal?

I work with ASP.NET, VB.NET, .NET 3.5 and MySQL database with autoIncrem开发者_JAVA技巧ent on the ID column. I've seen it also happen in Access and SQL Server.

This is my code:

  ' insert to user_table
    Dim connString As String = ConfigurationManager.ConnectionStrings("mysql_ConnString").ConnectionString
    Dim conn As MySqlConnection = New MySqlConnection(connString)
    Dim sqlCommand As String

    sqlCommand = "INSERT INTO user_table (Nickname,Email,Pass,SubscriptionMode,SignupDate,LastVisitDate,VisitCounter) VALUES (@Nickname,@Email,@Pass,@SubscriptionMode,@SignupDate,@LastVisitDate,@VisitCounter)"

    Dim cmd As New MySqlCommand(sqlCommand, conn)

    Try
        conn.Open()
        cmd.Parameters.AddWithValue("@Nickname", txtNickname.Text)
        cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
        cmd.Parameters.AddWithValue("@Pass", password)
        cmd.Parameters.AddWithValue("@SubscriptionMode", 1)
        cmd.Parameters.AddWithValue("@SignupDate", Date.Now)
        cmd.Parameters.AddWithValue("@LastVisitDate", Date.Now)
        cmd.Parameters.AddWithValue("@VisitCounter", 0)
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        GlobalFunction.sendToLog(ex, "problem in create profile page - sub: insertUser_table")
        GlobalFunction.jsMessage(Me, "problem in create profile page - sub: insertUser_table")
        Return False
    Finally
        conn.Close()
    End Try

In general, when you do

select * from Users

you cannot rely on the order of records. if you need your records in some particular order, you'll have to explicitly specify an order by clause

select * from Users order by ID

MySql is a Relational Database Management System which means that it is based on the relational model. The physical order of the rows of a table (which represents a relation) is of no importance. You must think of tables as Unorder Sets. If you want to present your data in a specific order you must use the order by clause.

I think that if you use InnoDB you can use a clustered index to specify the order of the rows.

To correct your question: My database table is not ordered

Your database table is ordered, but your select statement is not ordered.
And because it is not that's why you're not getting an ordered result.
The result that you are getting is in undefined order!

If you want an ordered result you must always specify and order by clause in your query.

Make ID your primary key and make it indexed. No matter what your newly entered row will be the last one. For selecting use the suggestions above about order by.

As Bala R said, you can't have SQL determine how you want to organize your data, so you want to do something like this:

SELECT * FROM Users ORDER BY id ASC

ex: (1,2,3,4,5)

Or this:

SELECT * FROM Users ORDER BY id DESC

ex: (5,4,3,2,1)