Link to home
Start Free TrialLog in
Avatar of andyknott
andyknott

asked on

ADO field.properties problem

Hi,

I am writing a VB program that will open an SQL server 2000 table and create a file (string) containing the T-SQL required to create that table.

I've got it to work to an extent, I can get the field names, sizes, types and identies (if any). But I can't seem to get the default value.

I can test this property : rs.Fields(x).Properties("ISAUTOINCREMENT") which tells me whether the field has an identity. But when I test
If rs.Fields(x).Properties("HASDEFAULT") = True Then
Or
rs.Fields(x).Properties("DEFAULTVALUE")
I get an item cannot be found in the collection.. error 3265

Why is this? The book I have says that HASDEFAULT is a property of the field.properties object.

I'm using Win 2000, VB 6.0, ADO 2.6, SQL server 2000 and SQLOLEDB provider with open method:
sSQL = "SELECT * FROM " + Form1.txtTableName
Set rs = cn.Execute(sSQL, 2)

Any help would be greatly appreciated.

Thanks,

Andy
Avatar of andyknott
andyknott

ASKER

PS here is a code snipet:

For x = 0 To rs.Fields.Count - 1
    lngAtts = rs.Fields(x).Attributes
'On Error Resume Next
    Select Case rs.Fields(x).Type
           
        Case 16 'TinyInt
            If rs.Fields(x).Properties("ISAUTOINCREMENT") = True Then
                IDStr = " Identity "
            Else
                IDStr = " "
            End If
            If rs.Fields(x).Properties("HASDEFAULT") = True Then
                DefaultStr = " Default " + rs.Fields(x).Properties("DEFAULTVALUE") + " "
            Else
                DefaultStr = " "
            End If
            If (lngAtts And adFldIsNullable) Then
                Print #1, " " + rs.Fields(x).Name + " TinyInt" + IDStr + DefaultStr + ","
            Else
                Print #1, " " + rs.Fields(x).Name + " TinyInt Not Null" + IDStr + DefaultStr + ","
            End If

    end select
next
I don't know the answer, but since (I think) Properties is a collection, you should be able to cycle through and find the valid choices:

for i = 0 to rs.Fields(x).Properties.count - 1
   debug.print rs.Fields(x).Properties(i).name
next i

The valid choices should show in your immediate/debug window.
Use this instead, it will give you the necessary information:

Set rst = cn.OpenSchema(adSchemaColumns)

Then use the value of rst.Fields("COLUMN_DEFAULT").Value as the default, you can also test rst.Fields("COLUMN_HASDEFAULT").Value to see whether it has one or not.

The schema is a better way to get much of this information rather than trying to extract it from a standard recordset.
Hi rspahitz,

Thanks that helped a bit, the properties listed are

BASECATALOGNAME
BASECOLUMNNAME
BASESCHEMANAME
BASETABLENAME
COLLATINGSEQUENCE
COMPUTEMODE
DATETIMEPRECISION
ISAUTOINCREMENT
ISCASESENSITIVE
ISSEARCHABLE
OCTETLENGTH
KEYCOLUMN
COMPFLAGS
SORTID
BASETABLEINSTANCE
TDSCOLLATION

My book has these but a few more aswell. could it be the provider or connection effecting the properties available?

Any ideas how I could find the default value of a field?

Or back to the root of the problem if you know another way to get the TSQL that created an existing table in SQL 2000.

Thanks
I think that TimCottee's idea will likely lead you to the answer.  By looking at the schema, you can more readily get everything the database knows about the table.

As for the difference between your book and your actual results, the problem could be a version issue, a feature issue, or maybe a type-cast (different table type) issue.
I'm having a go with TimCottee's suggestion right now... its looking good sofar! :)
Just curious, how did you declare your recordset?  Make sure that you preface it with:

Dim rs as ADODB.Recordset

Also, if you really want access to everything in the SQL Server database through your code, you should download/install SQL DMO (you may already have it if you are under Windows 2000).

SQL DMO is Data Management Objects, I think, and it lets you reference table schemas, stored procs, views, everything.  You could build your own interface to simulate Enterprise Manager if you want with these things.
ASKER CERTIFIED SOLUTION
Avatar of mdougan
mdougan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi TimCottee,

Thanks for you proposed solution, I know you are right, unfortunatly I'm a little confused. I think I understand that using:

Set rs = cn.OpenSchema(adSchemaColumns)

opens a recordset contaning all the schema data for the database that the connection is connected to.

How do i access the schema data for each of the fields in one particular table with the database?

I'd be very greatful for a little example code. I'll put the points up.

Thanks
Hi mdougan,

Bloody hell, that worked! Wished I'd asked sooner and not spent all day in big loops checking schema and creating strings!

Thanks!

Others, thanks for your input, hope I have not put you out too much.

Andy