Document SQL db 1

[MarkInCA]

Greetings!

I'm new to a company and will be doing some dataconversions. To get familiar with the data structure (and to dump it into rs) I've come up with:

'Loop through tables in Source to populate Result
catTbl.ActiveConnection = cn
j = catTbl.Tables.Count

For i = 1 To j
If Left(catTbl.Tables(i).Name, 3) <> &quot;sys&quot; Then
If Left(catTbl.Tables(i).Name, 4) <> &quot;TEMP&quot; Then
For k = 0 To catTbl.Tables(i).Columns.Count - 1
strSQL = &quot;INSERT INTO TS_BASE ( TableName, FieldName, DataType, FieldSize, FieldPrecision, SourceDB ) &quot; & _
&quot;Values ('&quot; & catTbl.Tables(i).Name & &quot;', '&quot; & catTbl.Tables(i).Columns(k).Name & &quot;', '&quot; & catTbl.Tables(i).Columns(k).Type & &quot;', '&quot; & catTbl.Tables(i).Columns(k).DefinedSize & &quot;', '&quot; & catTbl.Tables(i).Columns(k).Precision & &quot;', 'RWC');&quot;
cnResult.Execute strSQL
Next k
End If
End If
Next i

What do I need so I can find out if the column will not accept nulls?

Thanks

 

[paulbent]

Is this ADO? If so can test the Attributes property for various flags, something like:

Cbool(catTbl.Tables(i).Columns(k).Attributes And adFldIsNullable)

will return True if the field is nullable



Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[MarkInCA]

Thanks paulbent!

Sorry, should have explained. I am using VB6 and ADO to document a SQL database. This is a very good lead, although I received 'False' back on every column (which is untrue). I'll start pursuing 'adFldIsNullable'.

Thanks!

 

[CCLINT]

Did you use: AND ?

Also, test for adFldMayBeNull

 

[MarkInCA]

CCLINT -
Good question. I almost automatically said yes since I cut and pasted. But I double checked to be sure that I had:

CBool(catTbl.Tables(i).Columns(k).Attributes And adFldIsNullable)

(Which was just cut and pasted from code).

I had similar results with adFldMayBeNull.

Perhaps to explain a bit further. I am dumping this info into an access table (as a repository) for future reference.

 

[harebrain]

If you're targeting Access for reference, why don't you create a blank database, link to all the desired SQL Server tables, then run Tools | Analyze | Documenter on them?

 

[CCLINT]

Somehow I didn't look at your code close enough, or rather, I wasn't thinking straight (was late evening here) and failed to think that you were using the catalog object of ADOX and not ADODB, and therefore used the wrong value.

With the ADOX column object, the Nullable attribute is a different value (we used a value from the ADODB.FieldAttributeEnum and not from the ADOX.ColumnAttributesEnum )

CBool(catTbl.Tables(i).Columns(k).Attributes And adColNullable)

 

[tklear]

I'm trying to detect a whether a field is nullable from an ASP page using an ADODB recordset field. I'm getting an error because the ASP doesn't recognize the constant. Is there a value I can use instead?

Thanks.

 

[harebrain]

Have you included the adovbs.inc file in your ASP source? That's where the constants are declared.