sorting msql on VB.net

[ninjatalon]

I've tried google a way to sort a table but couldn't get my code working.

My program lets the user input enter information such as first name and last name in textboxes, once done they click on update button(so far everything works).

On the top right of the form it has a Combobox and it displays a list of all the names of poeple the User inputed... but it shows it in the order the user inputed.

I want to have a sort button so that way when the user clicks on "sort" it will alphabetically sort the msql table by last name and reupdate that on the Combobox (most importantly save msql with the new order of names)

[zy_430l]

does your update button save the records to your database?? and how do you want it to be sorted?is it by last name or first name? :)

[jlego]

you can sort the data using mysql

examples would be

select * from table order by column_name DESC
select * from table order by column_name ASC

this will return sorted results way faster than if u wrote a manual sort in vb .net

as for a combobox, it has a build in sort property. simple enable it to true

if you still want to sort the data using .netplease let me know

[ninjatalon]

does your update button save the records to your database?? and how do you want it to be sorted?is it by last name or first name? :)

Yes it saves the two Textboxes (txtFirstName and txtLastName) into the database and while updating ...it combines both and saves the full name into another field("Full Name") in the database.

Combobox retrieves ("Full name") from database

Edit: My problem is with coding to sort it.
heres a part of the code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim daSort As New MySqlDataAdapter
Dim command As New MySqlCommand
Dim sqlscript = "SELECT * FROM `" & get_user() & "` ORDER BY `Last Name` ASC"

command.Connection = cs
command.CommandText = sqlscript

daSort.SelectCommand = command
daSort.Fill(ds.Tables(get_user())) ''**get_user() is the name of the table**
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Say if I have two rows in my database with names "Brian Evans, John Doe"

Combobox shows:
Brian Evans
John Doe

Then when I run the code that i have

Combobox shows:
Brian Evans
John Doe
John Doe ''**this is where it makes a sort**
Brian Evans

But when i close the program and reopen it nothing is saved

Edit: Jlego I won't be able to use the sort inside combobox property because it will sort the full names but it will show a full name on the combobox but on txtFirstName and txtLastName it will have some other name. Its all linked with my database.

[zy_430l]

Dim myData as New MySqlDataReader
Dim daSort As New MySqlDataAdapter
Dim command As New MySqlCommand
Dim sqlscript = "SELECT FullName FROM `" & get_user() & "` ORDER BY `Last Name` ASC"

command.Connection = cs
command.CommandText = sqlscript

daSort.SelectCommand = command
myData= command.ExecuteQuery

While myData.read
    ComboBox1.Items.Add(myData.GetString(0))
End While

i think the data are saved to your database. you said your problem is when you close the program and open it, nothing is saved.

try putting the codes in your sort button and your form load event.

hope this helps. :D

[ninjatalon]

With the code i posted it doesn't save it to the database. I confirm it using Mysql Workbench.

myData=command.ExecuteQuery ''doesn't work there isn't a ExecuteQuery

table name 'is "get_user()" column name ' "Last Name" '
"SELECT * FROM `" & get_user() & "` ORDER BY `Last Name` ASC"

I don't know what * is for.

Basically all i need to do is send the script to my database and for my database to sort (reconfigure) the order of all entries by the Last Name. After that I can make my program using a function to reload the information which should be in alphabetical order.

[zy_430l]

oops! its

myData = command.ExecuteReader()

the character "*" is for selecting all of the fields in your database.

and as for your "update" button. type these;

Dim command as New MySqlCommand
Dim myData as New MySqlDataReader
Dim myAdapter as New MySqlDataAdapter
Dim sqlscript as String

cs.Open()
command.Connection = cs
'assuming text1 is for firstname and text2 is for lastname
sqlscript = "INSERT INTO get_User() (LastName,FirstName,FullName) values ('" & Text1.Text & "','" & Text2.Text & "','" & Text1.Text & Text2.Text & "')"
command.CommandText = sqlquery
myAdapter.InsertCommand = command
command.ExecuteNonQuery()
cs.Close()

[ninjatalon]

:( still can't get it to sort.

When running the script does my database automatically sort by last name?

I just need the code to execute it. But im still having a hard time as it won't do anything.

my table is called : HEROES (get_user())
the column of which i want to sort is called: Last Name

[ninjatalon]

I think i know what was happening . I was the one misunderstanding how it works.
I thought by running the script it would automatically change the rows in my database in order by last name and stay like that.

But what it truly does, it sends me my table in order by last name. So it displays it to me in order but doesn't have it in order in my database.

Now i have to figure out how to run this script as i only want it run once a month.

[zy_430l]

normally, a table in your database sorts the order of your data according to your primary key(a field in your table that is unique and has no repeating records). but if your table does not have a primary key declared, the data inside your table will store the data with respect to what was last inputted. the trick is using query statements that would display your data in whatever way you desire.