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("I SAUTOINCRE MENT") which tells me whether the field has an identity. But when I test
If rs.Fields(x).Properties("H ASDEFAULT" ) = True Then
Or
rs.Fields(x).Properties("D EFAULTVALU E")
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
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("I
If rs.Fields(x).Properties("H
Or
rs.Fields(x).Properties("D
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
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.co unt - 1
debug.print rs.Fields(x).Properties(i) .name
next i
The valid choices should show in your immediate/debug window.
for i = 0 to rs.Fields(x).Properties.co
debug.print rs.Fields(x).Properties(i)
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(adSchemaColu mns)
Then use the value of rst.Fields("COLUMN_DEFAULT ").Value as the default, you can also test rst.Fields("COLUMN_HASDEFA ULT").Valu e 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.
Set rst = cn.OpenSchema(adSchemaColu
Then use the value of rst.Fields("COLUMN_DEFAULT
The schema is a better way to get much of this information rather than trying to extract it from a standard recordset.
ASKER
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
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.
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.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(adSchemaColu mns)
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
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(adSchemaColu
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
ASKER
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
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
ASKER
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("I
IDStr = " Identity "
Else
IDStr = " "
End If
If rs.Fields(x).Properties("H
DefaultStr = " Default " + rs.Fields(x).Properties("D
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