[RESOLVED] How Can I first generate Random Numbers...

[dr_aybyd]

I have code to save records to my mysql database.
But first I want to read what is the last number of record and add 1000 on that? What is the proper way to query that first. Thanks.
I want to make the result at unique ID of my clients. Here is my chunks of codes.

    Private Sub addClient_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As MySqlConnection
        conn = New MySqlConnection
        With conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = cnString
            .Open()

        End With
        Dim strSQL As String = "SELECT * from tblclients"
        daClients.SelectCommand = New MySqlCommand(strSQL, conn)
        Dim myCB As MySqlCommandBuilder = New MySqlCommandBuilder(daClients)
        daClients.Fill(dsClients, "Clients")
        conn.Close()
    End Sub

And for my saving to database button

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim dt As DataTable = dsClients.Tables("Clients")
        Dim newRow As DataRow

        newRow = dt.NewRow()
        newRow("lastName") = txtLastname.Text
        newRow("firstName") = txtFirstname.Text
        dt.Rows.Add(newRow)
        daClients.Update(dsClients, "Clients")
        MsgBox("Record successfully saved.", MsgBoxStyle.Information)

    End Sub

[dr_aybyd]

I tried to codes by using MAX
But no return value

here is my code

Private Sub RandomID()
        Dim myReader As MySqlDataReader
        Dim conn As MySqlConnection
        conn = New MySqlConnection
        conn.ConnectionString = cnString
        conn.Open()
        Dim temp As String
        Try
            Dim sql As String = "SELECT  MAX(ID) FROM tblclients "
            Dim comm As MySqlCommand = New MySqlCommand(sql, conn)
            myReader = comm.ExecuteReader
            If myReader.HasRows Then
                While myReader.Read()
                    temp = myReader.Item("ID") + 1000
                End While
            End If
            myReader.Close()
        Catch ex As Exception

        End Try
        conn.Close()
        TextBox1.Text = String.Concat(temp)
    End Sub

[jmcilhinney]

Your result set has no column named "ID". When you apply an aggregate function to a column, the result doesn't inherently get the same name. If you want your aggregate function result to have a name then you must give it an alias.

That said, you wouldn't use a DataReader to get a single value. That's specifically what ExecuteScalar is for.

[dr_aybyd]

How about if I use LAST_INSERT_ID()?
Can you help me recode that for to use LAST_INSERT_ID() instead of my current code that use MAX()
I need to make that work so that I have a unique Client ID.
Thanks.

[jmcilhinney]

I don't really understand what you're trying to achieve. Can't you just set that column to be an identity, or whatever the MySQL equivalent is?

[dr_aybyd]

I don't really understand what you're trying to achieve. Can't you just set that column to be an identity, or whatever the MySQL equivalent is?

Sorry for that sir.
What I really want to achieved is I need to get the last ID of my records.
Coz I need that to make some unique id for my clients aside from that wich is auto increment.
Something like:
ClientID | Last Name | First Name
CL-1001 | Aybyd | Aybyd
CL-1002 | test2 | test


CL-1001 is must be the result of 1000 + the last increment ID

[jmcilhinney]

You don't actually need to do any work to have that happen. You should simply add an evaluated column that to your table, either in the database or just your DataTable. The expression for the column would get the value of the ID column, add 1000 to it, convert it to a string and then prepend "CL-" to it. You can then just treat that column like any other column and it will give you the correct data.

[dr_aybyd]

Thanks jmcilhinney it is working now.
Here is my codes

    Private Sub RandomID()
        Dim conn As MySqlConnection
        conn = New MySqlConnection
        conn.ConnectionString = cnString
        conn.Open()
        Dim LastID As String
        Dim sql As String = "SELECT  MAX(ID) FROM tblclients "
        Dim comm As MySqlCommand = New MySqlCommand(sql, conn)
        LastID = comm.ExecuteScalar()
        TextBox1.Text = "CL-" & LastID + 1000
        conn.Close()
    End Sub

Resolved

[dr_aybyd]

woahh... I found error.
When my database is empty it throw me an error here.

LastID = comm.ExecuteScalar()

It says NULL.
But if my table has records its working.. how can I trap that error?

[jmcilhinney]

That is a fine example of why this is the wrong way to do what you're trying to do and you should do as I suggested in post #7.

[dr_aybyd]

Its totally working now.
Just add some empty result trapping codes.
When the result Row is 0
Will be automatically set the random code to CL-1000