We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Display Column Name dynamically in VB application

rajender123
rajender123 asked
on
Medium Priority
256 Views
Last Modified: 2010-05-01
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
Comment
Watch Question

Bob LambersonSoftware Engineer

Commented:
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.
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.

Author

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.
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.

Author

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.........................................................
'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

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.
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Bob LambersonSoftware Engineer

Commented:
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

Author

Commented:
thanks all of you guys.....
Bob LambersonSoftware Engineer

Commented:
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.
thankyou experts.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.