How to retrieve all data in a database from columns

[Mr.M]

Hi Dw.

I have a MS Access database that has 9 columns what I want to do is to retrieve all data from a database only on 5 columns.

I think I have to use the select * from data but I don't know how to select the columns I want to retrieve because I can't use the WHERE test = '" because I want to retrieve all database records but only selected columns.

A very good example of what I want to achieve is thisselect all usernames, birthdates, last_seen_status now this is just an example where a database has users records in the database you may have as many columns as possible including name, surname, sex, location, username, last_seen_status,birthdates, registration_Date and more but only need to retrieve data from 'username,birthdates,last_seen_status` from the database for all the users that had registered or is registered in this database.

Hope I didn't confuse you on this. I saw something with wildcards but was a bit complicated the problem is that I didn't know how to apply it to my scenario. I saw it here: http//www.codeguru.com/columns/vb/doing-data-extraction-with-visual-basic.net.htm

[Mr.M]

Just made some modifications on my code as follows:

 ConnDB()
 Dim da As New OleDbDataAdapter(("select username, regDate, birthdate, LastSeen from mytable;"), conn)
 Dim dt As New DataTable
 da.Fill(dt)
 Try
 If dt.Rows.Count > 0 Then
 Musername = dt.Rows(0).Item("username") & ""
 MregDate = dt.Rows(0).Item("regDate") & ""
 Mbirthdate = dt.Rows(0).Item("birthdate") & ""
 MlastSeen = dt.Rows(0).Item("lastSeen") " ""

Now this does give me the record on my client side but the problem is that its only give one record. Currently I have 5 records on my database, what I'm trying to do is get all these records data but only the selected columns.

I don't want to send out the complete person record but only a selected columns data I want to send.

I'm not sure if I have to use a For Each and if I have to use it. How could I apply to my scenario.

I've also looked through this https://www.commandprompt.com/ppbook/x5802 and found the plan that I've used on this code post. I saw another with select * from mytabe WHERE scondition = condition which would have perfectly answered/solved my problem if it had worked on me but it didn't. The client side just froze.

This would have answered because I would use the scondition where let say I want to get all the currently online users I would have done it like this: select * FROM myTable WHERE lastSeen = Online;" that would have solved my problem but this didn't work as I've stated that the client side just freezes.

I thin I have to mention that on the client side I have a timer which adds the received data from server to a ListView which has 5 columns and I have a backgroundworker which execute the timer because if I execute it within the form, the form won't be usable as the client keep sending a signal to the server to send in this data and the client checks if the data a server sends is already been added to the listview if it has been added it then ignore the data and resend the request again and again for as long as the client is connected to the server. This is to make sure new data that has been added to the database is sent to the client.

I could have used a Backgroundworker to add data to the listView but the problem is that its doesn't allow cross_threading so I use a timer which is executed by a backgroundworker. But the problem also arise after maybe 3 or 5 seconds the client don't respond(freezes).

[Mr.M]

Wildcards is actually not what I wanted, just saw some samples of wildcards and it not what I want. I think I'm lost in this. Don't know which to go with. Will try now the dataset stuff and see if it be of any help.

I didn't think that this would be so difficult. The only thing prevent completion of my project is this problem. In all samples, tutorials I've seen so far there is not even one that shows similar to what I want, those that are a bit close to it, their problem is that they don't show how you would assign or add what was retrieved from database to variables.

[Mr.M]

Ow also just to mention. The problem of client freezing, I just increased the timer interval and that solved my problem because now a timer fire or sends the request signal after 5000 interval instead of 100 interval because that was causing a server to send data in a very shot time in such a point that the signal was received while the server thread hasn't done the prev request and that was producing problems in opening the database.

The only problem I'm having or remains is that of retrieving all data from the entire database but only populate/retrieve data from certain columns.

[tinstaafl]

See if something like this works to select all the data from only certain columns:

"select username as username, regDate as regDate, birthdate as birthdate, LastSeen as LastSeen from mytable;"

To display it a DataGridView works well

DataGridView1.DataSource = dt

[DenisOxon]

Alternatively, create a query in access which has all the information (fields, data selected, etc) you want, then access the query and not the table in your program.

[Mr.M]

Hi guys. Well this turns out to be the client that's having a problem. Just retyped the code on the server side and I used this:

 sql = "select * from mytable;"
 cmd = New OleDbCommand(sql, conn)
 dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 Do While dr.Read = True
 Username = dr(0)
 response = "Shop:<" & Username & ">"
 Loop

This works great. On the server side before response if I put MsgBox(Username) I'm able to get all the usernames as a message box. The response is just a string variable which I use to send formatted response to the client. The problem is that, the client only gets the last record. Meaning it won't get any usernames except the last username.

I use a BackgroundWorker to send request and receive response and assign it to global variables and I have a timer which keeps trying to add these global variables contents into ListView infinity. But my problem is that client only receive one data which is the last record on the database. I have 5 records but it won't get records from 1 to 4 only gets the 5th record which is the last record.

What am I missing here?

[Mr.M]

Just solved this problem. I simply assigned the values from database to the response then instead of sending the response before the End Sub I simply send response after assigning the values to variables.