I want to take List of Datatables with ADODB using VB.Net..

[soma.gunasekaran]

I want to take List of Datatables and Columns with ADODB using
VB.Net.....
I wrote the code ...but i getting this Error......

"COMException was Unhandled"
"Operation is not allowed when the object is closed."

Pls some one help me....



Imports System
Imports System.Data.OleDb
Imports ADODB
Public Class Form1

Dim cn As ADODB.Connection = New ADODB.Connection()
Dim rs As ADODB.Recordset = New ADODB.Recordset

'Dim cmd As New SqlCommand
' Dim dr As SqlDataReader
Dim constr As String


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'cn.Open("dsn=pubs;uid=;pwd=;")
Dim constr As String

constr = "Provider=SQLOLEDB;Initial Catalog=pubs;User
ID=sa;Password=sa;Data Source=.;"
cn.ConnectionString = constr
End Sub


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
'Dim adSchemaColumns As Object
'rs = cn.OpenSchema(SchemaEnum.adSchemaColumns, New [Object]()
{"pubs", "dbo", "authors"})

rs = cn.OpenSchema(ADODB.SchemaEnum.adSchemaTables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})


While Not rs.EOF

Debug.Print(rs("COLUMN_NAME"))
rs.MoveNext()

End While

'constr = "Initial Catalog=pubs;User ID=sa;Password=sa;Data
Source=.;"
''cn.Open()
''cmd = New SqlCommand("select * from tablename", cn)
'dr = cmd.ExecuteReader()
'If dr.read Then
' RichTextBox1.Text = dr(0)
'End If
'cn.Close()

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button2.Click
Dim s As String

s = cn.OpenSchema(SchemaEnum.adSchemaPrimaryKeys, New [Object]
() {"pubs", "dbo", "authors"})

MsgBox(rs("COLUMN_NAME"))
End Sub


Private Sub Button3_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button3.Click

Dim criteria(3) As Object
criteria(1) = Nothing
criteria(2) = Nothing
criteria(0) = "pubs"
criteria(3) = "table"
rs = cn.OpenSchema(SchemaEnum.adSchemaTables, criteria)
While Not rs.EOF
Debug.Print(rs.Fields("TABLE_NAME"))
rs.MoveNext()
End While

End Sub
End Class

 

[RobinS]

Why does it have to be ADODB? Why not ADO.Net?

Robin S.

 

[soma.gunasekaran]

Why does it have to beADODB? Why not ADO.Net?

Robin S.

I want to take List of Datatables and Columns withADODBusing
VB.Net.....
I wrote the code ...but i getting this Error......

"COMException was Unhandled"
"Operation is not allowed when the object is closed."

Pls some one help me....

Imports System
Imports System.Data.OleDb
ImportsADODB
Public Class Form1

Dim cn AsADODB.Connection = NewADODB.Connection()
Dim rs AsADODB.Recordset = NewADODB.Recordset

'Dim cmd As New SqlCommand
' Dim dr As SqlDataReader
Dim constr As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'cn.Open("dsn=pubs;uid=;pwd=;")
Dim constr As String

constr = "Provider=SQLOLEDB;Initial Catalog=pubs;User
ID=sa;Password=sa;Data Source=.;"
cn.ConnectionString = constr
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
'Dim adSchemaColumns As Object
'rs = cn.OpenSchema(SchemaEnum.adSchemaColumns, New [Object]()
{"pubs", "dbo", "authors"})

rs = cn.OpenSchema(ADODB.SchemaEnum.adSchemaTables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})

While Not rs.EOF

End While

'constr = "Initial Catalog=pubs;User ID=sa;Password=sa;Data
Source=.;"
''cn.Open()
''cmd = New SqlCommand("select * from tablename", cn)
'dr = cmd.ExecuteReader()
'If dr.read Then
' RichTextBox1.Text = dr(0)
'End If
'cn.Close()

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button2.Click
Dim s As String

s = cn.OpenSchema(SchemaEnum.adSchemaPrimaryKeys, New [Object]
() {"pubs", "dbo", "authors"})

MsgBox(rs("COLUMN_NAME"))
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button3.Click

Dim criteria(3) As Object
criteria(1) = Nothing
criteria(2) = Nothing
criteria(0) = "pubs"
criteria(3) = "table"
rs = cn.OpenSchema(SchemaEnum.adSchemaTables, criteria)
While Not rs.EOF
Debug.Print(rs.Fields("TABLE_NAME"))
rs.MoveNext()
End While

End Sub
End Class- Hide quoted text -

- Show quoted text -

Because,Requirement is only connecting to ADODB through it will get
the Data tables.So iam doing this concept....pls tell me........

 

[RobinS]

Why does it have to beADODB? Why not ADO.Net?

Robin S.

I want to take List of Datatables and Columns withADODBusing
VB.Net.....
I wrote the code ...but i getting this Error......

"COMException was Unhandled"
"Operation is not allowed when the object is closed."

Pls some one help me....

Imports System
Imports System.Data.OleDb
ImportsADODB
Public Class Form1

Dim cn AsADODB.Connection = NewADODB.Connection()
Dim rs AsADODB.Recordset = NewADODB.Recordset

'Dim cmd As New SqlCommand
' Dim dr As SqlDataReader
Dim constr As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'cn.Open("dsn=pubs;uid=;pwd=;")
Dim constr As String

constr = "Provider=SQLOLEDB;Initial Catalog=pubs;User
ID=sa;Password=sa;Data Source=.;"
cn.ConnectionString = constr
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
'Dim adSchemaColumns As Object
'rs = cn.OpenSchema(SchemaEnum.adSchemaColumns, New [Object]()
{"pubs", "dbo", "authors"})

rs = cn.OpenSchema(ADODB.SchemaEnum.adSchemaTables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})

While Not rs.EOF

End While

'constr = "Initial Catalog=pubs;User ID=sa;Password=sa;Data
Source=.;"
''cn.Open()
''cmd = New SqlCommand("select * from tablename", cn)
'dr = cmd.ExecuteReader()
'If dr.read Then
' RichTextBox1.Text = dr(0)
'End If
'cn.Close()

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button2.Click
Dim s As String

s = cn.OpenSchema(SchemaEnum.adSchemaPrimaryKeys, New [Object]
() {"pubs", "dbo", "authors"})

MsgBox(rs("COLUMN_NAME"))
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button3.Click

Dim criteria(3) As Object
criteria(1) = Nothing
criteria(2) = Nothing
criteria(0) = "pubs"
criteria(3) = "table"
rs = cn.OpenSchema(SchemaEnum.adSchemaTables, criteria)
While Not rs.EOF
Debug.Print(rs.Fields("TABLE_NAME"))
rs.MoveNext()
End While

End Sub
End Class- Hide quoted text -

- Show quoted text -

Because,Requirement is only connecting to ADODB through it will get
the Data tables.So iam doing this concept....pls tell me........

-----------------------------------------------

Well, I can tell you how to get Schema information using VB6 and ADODB, and
I can tell you how to get Schema information using VB2005 and ADO.Net, but
I'm not sure either of those would help you, since you want VB2005 and
ADODB.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.