Sql and vb ???

[allanbech]

Hi i'm new in sql and vb...

I have a vb project with a *.mdb file where I'm trying to order data in the tables with input from a text box...

In my vb project I would like to have a text box where users can type a word and then order the data in the table.

I can do this without the text box like this
Data.recordsource = "SELECT Time, place, id FROM news ORDER BY xxxx"
Where the xxxx is where I would like the input from the text box..

Thanks :-))

Regards
Allan Bech

[barrk]

dim myvar as string

myvar = text1.text (value input from your text box)

Data.recordsource = "SELECT Time, place, id FROM news ORDER BY '" & myvar & "'"

[FrancisC]

If you do that, you will be relying on the user to type the field name exactly the way it is in your database.

Using ADO, you can retrieve the number of fields, as well as their names.


Dim rs As New ADODB.Recordset
Dim db as New ADODB.COnnection

Dim iFieldCount As Integer
Dim asFieldNames() As String

Dim idx As Integer

db.Open "Your_Connection_String"

rs.Open "select * from YourTable", db, adOpenStatic, adLockOptimistic, adCmdText

iFieldCount = rs.Fields.Count
ReDim asFieldNames(iFieldCount - 1)

' I didn't test that, so I am not sure if the Fields collection is zero-based...
For idx = 0 to iFieldCount - 1
asFieldNames(idx) = rs.Fields(idx).Name
Next idx



You can use this array, either to verifywhat has been type or you can populate a combo box with it...

Let me know if you need more help, as I said, I didn't try this code...

[barrk]

You make a VERY good point! However, I think you will still need to pass whatever field the user has selected to the sql statement so...you need a combination of my solution and yours. Something like this??:


Dim rs As New ADODB.Recordset
Dim db as New ADODB.COnnection

Dim iFieldCount As Integer
Dim asFieldNames() As String

Dim idx As Integer

db.Open "Your_Connection_String"

rs.Open "select * from YourTable", db, adOpenStatic, adLockOptimistic, adCmdText

iFieldCount = rs.Fields.Count
ReDim asFieldNames(iFieldCount - 1)

' I didn't test that, so I am not sure if the Fields collection is zero-based...
For idx = 0 to iFieldCount - 1
asFieldNames(idx) = rs.Fields(idx).Name
Next idx

.....You will need to determine which field the user has selected.

for idx = 0 to iFieldcount - 1
if asFieldNames(idx).selected = true then
myvar = rs.fields(idx).name
end if
next

Data.recordsource = "SELECT Time, place, id FROM news ORDER BY '" & myvar & "'"

[FrancisC]

You are totally right...

I probably went a little beyond the scope of the question... but yes, you still need to use the strings you retrieve, including then in your SQL statement...

Actually, the way I would do it is to place the code I posted in the Load event of the form you are working with and populate the combobox right then.

What barrk described will then be placed either behind a Command button, or in the Change event of the combobox...

[barrk]

I would recommend using a button rather than the on change event of the combo box. This allows for users to change their minds before sending the sql statement to the server.