Display Column Name dynamically in VB application

I  have  students table in DB

I want to display all the columns  in the VB application dynamically as follows:-

col name (shuld come in label): textbox1
col2 name (should come in label): textbox2

similary other  colname and  in front of each label, text box should come.

If i add/delete  cols in table and i load the application again, the new col name  should come in my Vb form.

How will i do this thing in VB application...

Please let me know
rajender123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LambersonSoftware EngineerCommented:
rajender123,
> I want to display all the columns  in the VB application dynamically as follows:-
Do you mean all the columns in the students table?
If so, just loop through all the fields in the tables' fields collection and set the controls to the values you find there. Something like this.

dim fld as field
for each fld in table.fields
    'fill in the label.caption property
    lbl1.caption = fld.name
next


Hope this is helpful.
0
pradapkumarCommented:
how u get the fields?
If it is thru record set then

label1.caption = rst.fields(0).Name


Like this u can set label captions.

I hope this will also help to u.
0
rajender123Author Commented:
No ...
I have the  students table which  have the cols as:-

Name:-
ID:-
First Name
Last Name

I  have one form with button.

I  want on click event of button . to display the  cols  of the student table..

Later, if i add  or remove  the  cols from student table.. it will reflect in the VB form also.

I want that everytime i click on button. it will dynamically pick the table cols name and siaply on the form.

The cols name i want to display in label.. so that means  dynamcially generating the labels also.

How  will i do in VB.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pradapkumarCommented:
I can't understand ur idea.
where u stored ur records?
Had u did anything previously? if so, please post code so i can understand exactly what u have and what u need.
Or simply post the existing code behind that form. I will suggest u the appropriate solution.
0
rajender123Author Commented:
we will go step by step./...


 1) I want to count the  total no of  cols in my sql table ( students table)
2) i want to run the loop and retrieve the cols name ( ie studentname, student  id etc)

3)   Display these cols names in  label....

say label(1).caption= cols(1).name
label2.caption=cols(2).name


How will i do this in VB.........................................................
0
pradapkumarCommented:
'BeginCountVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
   
Public Sub Main()
    On Error GoTo ErrorHandler

    ' recordset and connection variables
    Dim rstEmployees As ADODB.Recordset
    Dim Cnxn As ADODB.Connection
    Dim strSQLEmployees As String
    Dim strCnxn As String
   
    Dim intLoop As Integer
   
    ' Open a connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Northwind';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
   
    ' Open recordset with data from Employee table
    Set rstEmployees = New ADODB.Recordset
    strSQLEmployees = "Employee"
    'rstEmployees.Open strSQLEmployee, Cnxn, , , adCmdTable
    rstEmployees.Open strSQLEmployees, Cnxn, adOpenForwardOnly, adLockReadOnly, adCmdTable
    'the above two lines opening the recordset are identical as
    'the default values for CursorType and LockType arguments match those specified
   
    ' Print information about Fields collection
    Debug.Print rstEmployees.Fields.Count & " Fields in Employee"
   
    For intLoop = 0 To rstEmployees.Fields.Count - 1
        Debug.Print "   " & rstEmployees.Fields(intLoop).Name
    Next intLoop

    ' Print information about Properties collection
    Debug.Print rstEmployees.Properties.Count & " Properties in Employee"
   
    For intLoop = 0 To rstEmployees.Properties.Count - 1
        Debug.Print "   " & rstEmployees.Properties(intLoop).Name
    Next intLoop

    ' clean up
    rstEmployees.Close
    Cnxn.Close
    Set rstEmployees = Nothing
    Set Cnxn = Nothing
    Exit Sub
   
ErrorHandler:
    ' clean up
    If Not rstEmployees Is Nothing Then
        If rstEmployees.State = adStateOpen Then rstEmployees.Close
    End If
    Set rstEmployees = Nothing
   
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
   
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndCountVB

0
pradapkumarCommented:
the above comment will be suitable for MS SQL DBMS with employee table.
So, change the connection string accordingly for MySQL DBMS and Student table.
For appropriate connection string visit http://www.connectionstrings.com/
Wish u all the best.
0
pradapkumarCommented:
Try with the following one. It may suitable for ur problem.

Public Sub Command1_Click()
    On Error GoTo ErrorHandler

    ' recordset and connection variables
    Dim rstStudents As ADODB.Recordset
    Dim Cnxn As ADODB.Connection
    Dim strSQLStudents As String
    Dim strCnxn As String
   
    Dim intLoop As Integer
   
    ' Open a connection
    Set Cnxn = New ADODB.Connection
    strCnxn =  "DRIVER={MySQL ODBC 3.51 Driver}; ERVER=localhost;PORT=3306; DATABASE=myDatabase; USER=myUsername; PASSWORD=myPassword; OPTION=3;"
    Cnxn.Open strCnxn
   
    ' Open recordset with data from Employee table
    Set rstStudents = New ADODB.Recordset
    strSQLStudents = "SELECT * FROM students"
    set rstStudents = Cnxn.Execute(strSQLStudents)
   
    ' Print information about Fields collection
    Debug.Print rstStudents.Fields.Count & " Fields in Students Table"
   
    For intLoop = 0 To rstStudents.Fields.Count - 1
        Debug.Print "   " & rstStudents.Fields(intLoop).Name
    Next intLoop

    ' Print information about Properties collection
    Debug.Print rstStudents.Properties.Count & " Properties in Students"
   
    For intLoop = 0 To rstStudents.Properties.Count - 1
        Debug.Print "   " & rstStudents.Properties(intLoop).Name
    Next intLoop

    ' clean up
    rstStudents.Close
    Cnxn.Close
    Set rstStudents = Nothing
    Set Cnxn = Nothing
    Exit Sub
   
ErrorHandler:
    ' clean up
    If Not rstStudents Is Nothing Then
        If rstStudents.State = adStateOpen Then rstStudents.Close
    End If
    Set rstStudents = Nothing
   
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
   
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndCountVB
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob LambersonSoftware EngineerCommented:
rajender123,
Do you want to create the labels that will display the fields dynamically?

If so, here is a reference that may help you get started.      http://www.codeguru.com/vb/controls/vb_activex/activex/article.php/c3499/

Bob
0
rajender123Author Commented:
thanks all of you guys.....
0
Bob LambersonSoftware EngineerCommented:
I would recommend split between pradapkumar  and myself, but that would be less than the minimum 20 points, so based on the number of words coded, award the question to pradapkumar. I think both gave correct answers, just a lot of difficulty communicating.
0
pradapkumarCommented:
thankyou experts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.