Help --> Connect to a DataBase

[comcrack]

Hy

 

I'm triing to connect to a Microsoft access database. This databass is there : 'c:\dbtest.mdb'. After connecting I need to list the tables in a array and than list the columns of one of these tables in an other array.

 

How can I do that in VB.

 

 

Thank You

 

ComCrack

[dynamic_sysop]

top of your form's code window you need this :

Imports System.Data.OleDb

then you can do the following , making sure you specify your .mdb's location and table etc...

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:/bin.mdb"
       Dim strCommand As String = "SELECT * FROM binFrm"
       OpenAccess(strconnection, strCommand)
   End Sub

   Public Function OpenAccess(ByVal strConn As String, ByVal strComm As String)
       Dim DBCon As New OleDbConnection(strConn)
       DBCon.Open()
       Dim DBCommand As New OleDbCommand(strComm, DBCon)
       Dim DBAdapt As New OleDbDataAdapter(DBCommand)
       Dim DBset As New DataSet()
       DBAdapt.Fill(DBset, "binFrm")
       DataGrid1.DataSource = DBset.Tables("binFrm") '/// add the entire access database to a datagrid.
       DBCon.Close()
       DBCommand.Dispose()
       DBAdapt.Dispose()
       DBset.Dispose()
   End Function

hope that helps.

[comcrack]

Thank you for the connection but now I need a list of my tables and my colunms.

[dynamic_sysop]

you could try something like this :

   Public Function OpenAccess(ByVal strConn As String, ByVal strComm As String)
       Dim i As Integer, x As Integer
       Dim DBCon As New OleDbConnection(strConn)
       DBCon.Open()
       Dim DBCommand As New OleDbCommand(strComm, DBCon)
       Dim DBAdapt(2) As OleDbDataAdapter '/// make an array of each table in your database.
       DBAdapt(1) = New OleDbDataAdapter(DBCommand)
       DBAdapt(2) = New OleDbDataAdapter(DBCommand)
       Dim DBset As New DataSet()
       DBAdapt(1).Fill(DBset, "binFrm")
       DBAdapt(2).Fill(DBset, "Addresses")
       For i = 0 To DBset.Tables.Count - 1
           For x = 0 To DBset.Tables.Item(i).Columns.Count - 1
               Dim strTable As String = DBset.Tables.Item(i).TableName
               Dim strColumn As String = DBset.Tables.Item(i).Columns(x).ColumnName
               MessageBox.Show("the tabel: " & strTable & "  Contains the following Columns:" & Chr(10) & strColumn)
           Next
       Next
       DBCon.Close()
       DBCommand.Dispose()
       DBAdapt(1).Dispose()
       DBAdapt(2).Dispose()
       DBset.Dispose()
   End Function

[comcrack]

I triied this :

 

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
       Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:/dbtest.mdb"
       Dim strCommand As String = "SELECT * FROM binFrm"'''''''''''''''''
       OpenAccess(strConnection, strCommand)
   End Sub

   Public Function OpenAccess(ByVal strConn As String, ByVal strComm As String)
       Dim i As Integer, x As Integer
       Dim DBCon As New OleDbConnection(strConn)
       DBCon.Open()
       Dim DBCommand As New OleDbCommand(strComm, DBCon)
       Dim DBAdapt(2) As OleDbDataAdapter '/// make an array of each table in your database.
       DBAdapt(1) = New OleDbDataAdapter(DBCommand)
       DBAdapt(2) = New OleDbDataAdapter(DBCommand)
       Dim DBset As New DataSet
       DBAdapt(1).Fill(DBset, "binFrm")
       DBAdapt(2).Fill(DBset, "Addresses")
       For i = 0 To DBset.Tables.Count - 1
           For x = 0 To DBset.Tables.Item(i).Columns.Count - 1
               Dim strTable As String = DBset.Tables.Item(i).TableName
               Dim strColumn As String = DBset.Tables.Item(i).Columns(x).ColumnName
               MessageBox.Show("the tabel: " & strTable & "  Contains the following Columns:" & Chr(10) & strColumn)
           Next
       Next
       DBCon.Close()
       DBCommand.Dispose()
       DBAdapt(1).Dispose()
       DBAdapt(2).Dispose()
       DBset.Dispose()
   End Function

 

But there is no table which have this name --> binFrm of this line SELECT * FROM binFrm

 

I change this line for --> SELECT * FROM UserTable

 

then the msgbox give me :

 

"the tabel: binFrm Contains the following Columns:

N°"

and

"the tabel: binFrm Contains the following Columns:

gh"

and

"the tabel: binFrm Contains the following Columns:

ghw"

and

"the tabel: Addresses Contains the following Columns:

N°"

and

"the tabel: Addresses Contains the following Columns:

gh"

and

"the tabel: Addresses Contains the following Columns:

ghw"

 

 

These columns are the colunms in my table 'UserTable'

but I don't have the table 'Adresses' or the table 'binFrm'

[dynamic_sysop]

the tables "binFrm" and "Addresses" are in 1 of my databases thats why:-\ that was an example showing how to open the tables , you would put your table names inplace of them:)

[comcrack]

ok but I don't know the tables because I need to chose the database when the program is running, It's why I need to list the tables in the database.

[jfackler]

I'm a little concerned with why you would be accessing a db that you were so unfamiliar with....

oledb.Database.ToString()

 

will get you the database that you are accessing in the connection as a string.

 

There are ways to access the schema of a table including it's column names and configuration, once you know the table you are interested in....but again, I'm not sure, as a programmer how you could utilize a db without some significant knowledge of your resource. Unless that is the goal, in which case this is an inappropriate post, and should be terminated.

 

Jon

[Mehyar]

Dim dt As New DataTable()

Dim restrictions(3) As Object

restrictions(0) = Nothing

restrictions(1) = Nothing

restrictions(2) = Nothing

restrictions(3) = "TABLE"

_Connection.Open()

dt = _Connection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, restrictions)

_Connection.Close()

 

_Connection is an object of type OleDbConnection (the connection to your database)

now dt is a table containing all your table names in the database

[comcrack]

Ok I'm not really good i'n VB and I didn't understand what i had to do to have the tables's names.

 

 

It's for my php and asp editor. I need to enter to any database on my computer to take tables name and colunms name to make the programation of the asp easyer.

 

 

Thank you

 

ComCrack

[mrdutchie]

Seems like this part is not working right

 

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:/dbtest.mdb"

Dim strCommand As String = "SELECT * FROM binFrm"'''''''''''''''''

OpenAccess(strConnection, strCommand)

End Sub

 

Public Function OpenAccess(ByVal strConn As String, ByVal strComm As String)

Dim i As Integer, x As Integer

Dim DBCon As New OleDbConnection(strConn)

DBCon.Open()

Dim DBCommand As New OleDbCommand(strComm, DBCon)

Dim DBAdapt(2) As OleDbDataAdapter '/// make an array of each table in your database.

DBAdapt(1) = New OleDbDataAdapter(DBCommand)

DBAdapt(2) = New OleDbDataAdapter(DBCommand)

Dim DBset As New DataSet

DBAdapt(1).Fill(DBset, "binFrm")

DBAdapt(2).Fill(DBset, "Addresses")

For i = 0 To DBset.Tables.Count - 1

 

Where you have

 

Dim strCommand As String = "SELECT * FROM binFrm

 

it fails on

 

DBAdapt(1).Fill(DBset, "binFrm")

DBAdapt(2).Fill(DBset, "Addresses")

 

both are showing the same information

 

If I change it to

 

Dim strCommand As String = "SELECT * FROM binFrm,addresses

 

then it will show for every table ALL the tables from both items

how to make it seperate?