We help IT Professionals succeed at work.

VB SQL Display data type of a field

Medium Priority
378 Views
Last Modified: 2013-12-25
Is there a way to display the data type of a field that I am displaying on a VB form?

I am retrieving data correctly from a SQL table and displaying it on a form.  I just want one more text box, disabled but visable, to show it's data type.

During the SQL select, is there a way to retrieve that information?
Private Sub lstGuiParm_Click()
    Dim fsCmd As String

    fsCmd = ""
    fsCmd = fsCmd & "SELECT  ParmNm, StatusCd, DefaultParmValue "
    fsCmd = fsCmd & "FROM    GuiParmList "
    fsCmd = fsCmd & "WHERE   ParmNm = '" & lstGuiParm.Text & "'"

    Set goRecSet = goConn.Execute(fsCmd)
        While Not goRecSet.EOF
            txtParmNmGui.Text = goRecSet("ParmNm")
            txtStatusGui.Text = goRecSet("StatusCd")
            txtDefaultParmGui.Text = goRecSet("DefaultParmValue")
            goRecSet.MoveNext
        Wend
End Sub

Open in new window

Comment
Watch Question

http://support.microsoft.com/kb/193947

txtMyType.text = goRecSet("ParmNm").Type

Commented:
what is the definition of GuiParmList? does it contain the table and column names? if so a query like the one below may help you...
Private Sub lstGuiParm_Click()
    Dim fsCmd As String

    fsCmd = ""
	fsCmd = fsCmd & "SELECT  ParmNm, StatusCd, DefaultParmValue, T.name "
    fsCmd = fsCmd & "FROM    GuiParmList G "
    fsCmd = fsCmd & "INNER JOIN sys.sysobjects O ON G.tableName = O.name "
    fsCmd = fsCmd & "INNER JOIN sys.syscolumns C ON ON O.id = C.id AND C.name = G.columnName "
    fsCmd = fsCmd & "INNER JOIN sys.systypes T ON C.xtype = T.xtype "    
    fsCmd = fsCmd & "WHERE   ParmNm = '" & lstGuiParm.Text & "'"
	
	Set goRecSet = goConn.Execute(fsCmd)
        While Not goRecSet.EOF
            txtParmNmGui.Text = goRecSet("ParmNm")
            txtStatusGui.Text = goRecSet("StatusCd")
            txtDefaultParmGui.Text = goRecSet("DefaultParmValue")
			txtDataType.text = goRecSet("name")
            goRecSet.MoveNext
        Wend
End Sub

Open in new window

Author

Commented:
DanielWilson - I tried both:
txtMyType.text = goRecSet("ParmNm").Type
and
txtMyType.text = LTrim(Str(goRecSet("ParmNm").Type)) (per the example on the MS site)

but both display 200, rather than STRING.
In order to get a nice-looking type name, you're going to have to do a lookup with the field Type.

And, those will be DB types, not Visual Basic types.  So ... 200 is a Varchar ... which we would want to place in a VB String ... but it's not going to say "string".

Author

Commented:
DanielWilson, thank you for the clarification.

tigin44, I get Invalid column name G.tableName when using the inner joins.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.