?
Solved

ADO field.properties problem

Posted on 2001-12-21
10
Medium Priority
?
1,629 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 800 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…
Suggested Courses

621 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