8 Replies - 1005 Views - Last Post: 15 October 2013 - 05:46 PM Rate Topic: -----

#1 eawedat  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 14
  • Joined: 20-November 11

DataColumn's AutoIncrement returns False always

Posted 14 October 2013 - 12:38 PM

Hey all,

I am interested to check out whether column has an autoincrement/allowdbnull property .

Having this code below , gives me always false although I already have one column that has autoincrement/allowdbnull property.


 Dim dt As New DataTable()
    Dim con As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & Application.StartupPath & "\test.mdb"
    Dim sql As String = "SELECT * from teachers"
    Dim i As Integer

        Dim dataAdapter As New OleDb.OleDbDataAdapter(sql, con)
        dataAdapter.Fill(dt)
        dataAdapter.Dispose()

  For Each column As DataColumn In dt.Columns
TextBox1.Text = TextBox1.Text & column.ColumnName & "      " & column.AutoIncrement & "    " & column.AllowDBNull & vbCrLf
 Next



thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: DataColumn's AutoIncrement returns False always

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3741
  • View blog
  • Posts: 13,086
  • Joined: 12-December 12

Re: DataColumn's AutoIncrement returns False always

Posted 14 October 2013 - 01:36 PM

AutoIncrement is a property that you can set and read on the DataColumn, it does not automatically create this information based on the underlying Access table, which is why it returns false for your columns.

Testing for this property in Access is a little tricky and I don't have code to hand.

  • The information is stored in a sys (system) table and can be retrieved with a sql-statement.
  • You can use the ADO/ADOX Object Model to obtain this information; I've posted some code I found below.

Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection

Try
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

Dim col As ADOX.Column
For Each col In ADOXCatalog.Tables("Table1").Columns
If col.Properties("AutoIncrement").Value = True Then
Console.WriteLine(col.Name)
End If
Next

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ADOConnection.Close()
End Try


I suppose there are other obscure/odd approaches such as trying to create a new record and letting it fail :dontgetit:
Was This Post Helpful? 1
  • +
  • -

#3 eawedat  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 14
  • Joined: 20-November 11

Re: DataColumn's AutoIncrement returns False always

Posted 15 October 2013 - 04:29 AM

thank you very much andrewsw
that worked for me ;)
I was wondering about getting the type of datacolumn.

I tried col.Type but did not work.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3741
  • View blog
  • Posts: 13,086
  • Joined: 12-December 12

Re: DataColumn's AutoIncrement returns False always

Posted 15 October 2013 - 04:49 AM

I guess it would be:

col.Properties("Type").Value

Type Property (ADOX)

200, for example, would represent a string. DataTypeEnums

I'm guessing, after a little searching.. as you might have done :whistling:. That is, search first and then experiment..

This post has been edited by andrewsw: 15 October 2013 - 04:50 AM

Was This Post Helpful? 1
  • +
  • -

#5 eawedat  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 14
  • Joined: 20-November 11

Re: DataColumn's AutoIncrement returns False always

Posted 15 October 2013 - 04:53 AM

My Chrome does not connect to MSDN->Microsoft , I do not know why.

anyway I tried

col.Properties("Type").Value



but I get this :

Item cannot be found in the collection corresponding to the requested name or ordinal.


Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3741
  • View blog
  • Posts: 13,086
  • Joined: 12-December 12

Re: DataColumn's AutoIncrement returns False always

Posted 15 October 2013 - 10:43 AM

The following alternative approach connects to SQL Server, but the code will work with Access.

'BeginTypeFieldVB
Public Sub Main()
    On Error GoTo ErrorHandler

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
   
    Dim Cnxn As ADODB.Connection
    Dim rstEmployees As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim strCnxn As String
    Dim strSQLEmployee As String
    Dim FieldType As String
    
    ' Open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
     
    ' Open recordset with data from Employees table
    Set rstEmployees = New ADODB.Recordset
    strSQLEmployee = "employee"
    rstEmployees.Open strSQLEmployee, Cnxn, , , adCmdTable
    'rstEmployees.Open strSQLEmployee, Cnxn, adOpenStatic, adLockReadOnly, adCmdTable
     ' the above two lines of code are identical
    
    Debug.Print "Fields in Employees Table:" & vbCr
    
    ' Enumerate Fields collection of Employees table
    For Each fld In rstEmployees.Fields
        ' translate field-type code to text
        Select Case fld.Type
            Case adChar
               FieldType = "adChar"
            Case adVarChar
               FieldType = "adVarChar"
            Case adSmallInt
               FieldType = "adSmallInt"
            Case adUnsignedTinyInt
               FieldType = "adUnsignedTinyInt"
            Case adDBTimeStamp
               FieldType = "adDBTimeStamp"
        End Select
        ' show results
        Debug.Print "  Name: " & fld.Name & vbCr & _
          "  Type: " & FieldType & vbCr
    Next fld
    
    ' clean up
    rstEmployees.Close
    Cnxn.Close
    Set rstEmployees = Nothing
    Set Cnxn = Nothing
    Exit Sub

ErrorHandler:
    ' clean up
    If Not rstEmployees Is Nothing Then
        If rstEmployees.State = adStateOpen Then rstEmployees.Close
    End If
    Set rstEmployees = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    Set fld = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndTypeFieldVB


Attribute VB_Name = "TypeField"

This post has been edited by andrewsw: 15 October 2013 - 10:45 AM

Was This Post Helpful? 1
  • +
  • -

#7 eawedat  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 14
  • Joined: 20-November 11

Re: DataColumn's AutoIncrement returns False always

Posted 15 October 2013 - 12:53 PM

Error 1 Name 'adCmdTable' is not declared.
Error 2 Name 'adChar' is not declared.
Error 3 Name 'adVarChar' is not declared.
Error 4 Name 'adSmallInt' is not declared.
Error 5 Name 'adUnsignedTinyInt' is not declared.
Error 6 Name 'adDBTimeStamp' is not declared.
Error 7 Name 'adStateOpen' is not declared.
Error 8 Name 'adStateOpen' is not declared.
Error 9 Operator '<>' is not defined for types 'Microsoft.VisualBasic.ErrObject' and 'Integer'.
Was This Post Helpful? 0
  • +
  • -

#8 eawedat  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 14
  • Joined: 20-November 11

Re: DataColumn's AutoIncrement returns False always

Posted 15 October 2013 - 01:10 PM

I think I found them under ADOX.DataTypeEnum.
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3741
  • View blog
  • Posts: 13,086
  • Joined: 12-December 12

Re: DataColumn's AutoIncrement returns False always

Posted 15 October 2013 - 05:46 PM

Yes, you can't use those enums directly from VB.NET, you can substitute their numerical values.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1