Solved

Field type function

Posted on 2002-07-19
6
206 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
Comment Utility
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
Comment Utility
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
Comment Utility
Timcottee

in you example must i do an if statement for each different type of field type
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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
Comment Utility
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
Comment Utility
OOPS,
Change:
GetFields = GetFields & Trim(Val(fld.Type)) & ","
To:
GetFields = GetFields & Trim(Str(fld.Type)) & ","

Enjoy your work,  P1
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
DIR issue 7 46
bit defender blocks good applications 2 50
MS Access Search and Replace Using VBA 6 40
Determine Range to Select 5 33
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…
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…

771 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

10 Experts available now in Live!

Get 1:1 Help Now