Solved

ADO field.properties problem

Posted on 2001-12-21
10
1,565 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:andyknott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 

Author Comment

by:andyknott
ID: 6689720
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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6689749
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.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6689759
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.
0
Independent Software Vendors: 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!

 

Author Comment

by:andyknott
ID: 6689776
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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6689788
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.
0
 

Author Comment

by:andyknott
ID: 6689800
I'm having a go with TimCottee's suggestion right now... its looking good sofar! :)
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6689823
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.
0
 
LVL 18

Accepted Solution

by:
mdougan earned 200 total points
ID: 6689853
Here is a sample script using DMO to do what you're trying to do.  I couldn't test it, because I have the wrong version of SQL DMO, but I'm sure you'll be able to figure it out if you try it.

Private Sub Command1_Click()
Dim oServer As New SQLDMO.SQLServer
Dim oTable As SQLDMO.Table

oServer.Connect ServerName:="SQLSERVERNAME", _
                    Login:="USERNAME", _
                    Password:="PASSWORD"
                   
Set oTable = oServer.Databases("DATABASENAME").Tables("TABLENAME")
MsgBox oTable.Script

End Sub
0
 

Author Comment

by:andyknott
ID: 6689861
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
0
 

Author Comment

by:andyknott
ID: 6689885
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
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
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 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…
Suggested Courses
Course of the Month9 days, 5 hours left to enroll

615 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