Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Field type function

Posted on 2002-07-19
6
Medium Priority
?
215 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
Industry Leaders: 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 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 300 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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 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…
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…
Suggested Courses

886 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