Solved

Field type function

Posted on 2002-07-19
6
210 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
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.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

830 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