Solved

Field type function

Posted on 2002-07-19
6
207 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 69

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

914 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now