Solved

Field type function

Posted on 2002-07-19
6
209 Views
Last Modified: 2012-08-13
Hi

I have a function that gets a list of field values for a table and buils a insert statement dynamically.I need to kno how to a simple way of getting the field type so that the value can be put into string quotes if it is a string and remove string quotes if it is a interger.
here is my function

Private Function GetFields(rs As Adodb.Recordset) As String

Dim fld As Adodb.Field

     For Each fld In rs.Fields
        'If field type interger  Then
            GetFields = GetFields & fld.Value & ","
        Else
        'if field type is string
            GetFields = GetFields & "'" & fld.Value & "',"
        End If
      Next

End Function
0
Comment
Question by:Vignette
6 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7164489
I use ADOX for this:

' ----- Retrieve the schema through ADOX.
Set catSchema = New ADOX.Catalog
Set catSchema.ActiveConnection = pconConnection
   
' ----- Scan the list of tables.
For Each tblSchema In catSchema.Tables
    If (tblSchema.Type = "TABLE") Then


                strDataType = GetFieldType(tblSchema.Columns(strColumnName).Type, _
                                           tblSchema.Columns(strColumnName).Precision, _
                                           tblSchema.Columns(strColumnName).NumericScale, _
                                           tblSchema.Columns(strColumnName).DefinedSize)
   End If
Next


Private Function GetFieldType(ByVal plngType As Long, _
                              ByVal pintPrecision As Integer, _
                              ByVal pintNumericScale, _
                              ByVal plngDefinedSize As Long) As String
    ' ----- Return the type of the indicated field as a string.
    On Error GoTo ErrorHandler

    Select Case plngType
        Case adTinyInt
            GetFieldType = "Tiny Int"
        Case adUnsignedTinyInt
            GetFieldType = "Unsigned Tiny Int"
        Case adSmallInt
            GetFieldType = "Small Int"
        Case adUnsignedSmallInt
            GetFieldType = "Unsigned Small Int"
        Case adInteger
                GetFieldType = "Integer"
        Case adUnsignedInt
                GetFieldType = "Unsigned Integer"
        Case adBigInt
                GetFieldType = "Big Integer"
        Case adUnsignedBigInt
                GetFieldType = "Unsigned Big Integer"
        Case adSingle
            GetFieldType = "Single"
        Case adDouble
            GetFieldType = "Double"
        Case adCurrency
            GetFieldType = "Currency"
        Case adDecimal
            GetFieldType = "Decimal"
        Case adNumeric
'            GetFieldType = "Numeric (" & colSchema.Precision & "," & colSchema.NumericScale & ")"
            GetFieldType = "Numeric (" & pintPrecision & "," & pintNumericScale & ")"
        Case adBoolean
            GetFieldType = "Bit"
        Case adUserDefined, adVariant, adBSTR
            GetFieldType = "Other"
        Case adGUID
            GetFieldType = "GUID"
        Case adDate, adDBDate, adDBTime, adDBTimeStamp
            GetFieldType = "Date/Time"
        Case adChar, adWChar
            ' ----- For text fields, determine the length as well.
'            GetFieldType = "Char(" & colSchema.DefinedSize & ")"
            GetFieldType = "Char(" & plngDefinedSize & ")"
        Case adVarChar, adVarWChar
            ' ----- For text fields, determine the length as well.
            GetFieldType = "VarChar(" & plngDefinedSize & ")"
        Case adLongVarChar, adLongVarWChar
            GetFieldType = "Memo"
        Case adBinary, adVarBinary, adLongVarBinary
            GetFieldType = "Binary"
        Case Else
            GetFieldType = "Unknown"
    End Select
    Exit Function

ErrorHandler:
    ' ----- Something is wrong with this entry.
    GetFieldType = "Unknown"
    Exit Function
End Function
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7164508
Emoreau's method will work fine but is perhaps a little overkill for your requirements:

Private Function GetFields(rs As Adodb.Recordset) As String

Dim fld As Adodb.Field

    For Each fld In rs.Fields
       If fld.Type = 3 Then 'adInteger : i.e., Integer
           GetFields = GetFields & fld.Value & ","
       Else
       if fld.Type = 202 Then 'adVarWChar : i.e., String
           GetFields = GetFields & "'" & fld.Value & "',"
       End If
     Next

End Function

And so on, the key thing is that the field member in the fields collection has a type property that will be a member of the enum as demonstrated by emoreau.
0
 

Author Comment

by:Vignette
ID: 7164518
Timcottee

in you example must i do an if statement for each different type of field type
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 43

Expert Comment

by:TimCottee
ID: 7164530
No not at all, this was just modifying your original code, the best method is to combine emoreau's use of a select case statement with the fields collection .Type property (ignoring the ADOX bit) and go from there.
0
 
LVL 4

Accepted Solution

by:
P1 earned 100 total points
ID: 7165882
You can just convert them into their enums.

Private Function GetFields(rs As Adodb.Recordset) As String

Dim fld As Adodb.Field

    For Each fld In rs.Fields
        GetFields = GetFields & Trim(Val(fld.Type)) & ","
    Next
        'Remove ending ','
        GetFields = Left(GetFields,Len(GetFields)-1)
End Function

Output looks like, "2,3,4,11,17,129,..."

Decode like this later.
             Case adSmallInt         '2
             Case adInteger          '3
             Case adSingle           '4
             Case adBoolean          '11
             Case adUnsignedTinyInt  '17
             Case adChar             '129
             ...

Enjoy your work,  P1
0
 
LVL 4

Expert Comment

by:P1
ID: 7165888
OOPS,
Change:
GetFields = GetFields & Trim(Val(fld.Type)) & ","
To:
GetFields = GetFields & Trim(Str(fld.Type)) & ","

Enjoy your work,  P1
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

777 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