?
Solved

Display Column Name dynamically in VB application

Posted on 2006-03-30
15
Medium Priority
?
238 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
0
Comment
Question by:rajender123
  • 6
  • 3
  • 3
12 Comments
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 16339253
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
 
LVL 9

Expert Comment

by:pradapkumar
ID: 16340132
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
 

Author Comment

by:rajender123
ID: 16348735
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:pradapkumar
ID: 16349083
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
 

Author Comment

by:rajender123
ID: 16353143
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
 
LVL 9

Expert Comment

by:pradapkumar
ID: 16357600
'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
 
LVL 9

Expert Comment

by:pradapkumar
ID: 16359634
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
 
LVL 9

Accepted Solution

by:
pradapkumar earned 80 total points
ID: 16359760
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
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 16367285
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
 

Author Comment

by:rajender123
ID: 16370621
thanks all of you guys.....
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 16581181
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
 
LVL 9

Expert Comment

by:pradapkumar
ID: 16657668
thankyou experts.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 23 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question