SQL Server 2005 with VB.net 2005

[Omar Abid]

Hi every body,
Im using VB 2005 to create a program that open SQL Data base
The problem that i want to detect the tables of a database
so how can i know a data base tables instantly
Thank you
omar.abid@hotma il.com
omar.abid2006@g mail.com
Omar abid

[Ed Murphy]

Re: SQL Server 2005 with VB.net 2005

Omar Abid wrote:

Im using VB 2005 to create a program that open SQL Data base
The problem that i want to detect the tables of a database
so how can i know a data base tables instantly

(When posting to multiple newsgroups, please cross-post to
all at once, rather than multi-posting to each one separately.)

Anyway, Google ("SQL Server 2005" systables) and see if that
points you in the right direction.

[Karl]

Re: SQL Server 2005 with VB.net 2005

There is a view that has a list of all the columns in the whole
database. I believe its information_sys .columns

[Omar Abid]

Re: SQL Server 2005 with VB.net 2005

On 9 juin, 18:51, Ed Murphy <emurph...@soca l.rr.comwrote:

Omar Abid wrote:

Im using VB 2005 to create a program that open SQL Data base
The problem that i want to detect the tables of a database
so how can i know a data base tables instantly

>
(When posting to multiple newsgroups, please cross-post to
all at once, rather than multi-posting to each one separately.)
>
Anyway, Google ("SQL Server 2005" systables) and see if that
points you in the right direction.

i didn't know how to do that can u explain more to me

[Omar Abid]

Re: SQL Server 2005 with VB.net 2005

On 10 juin, 08:39, Karl <knelso...@gmai l.comwrote:

There is a view that has a list of all the columns in the whole
database. I believe its information_sys .columns

Thank u for reply but how to do that with VB2005

[Ed Murphy]

Re: SQL Server 2005 with VB.net 2005

Omar Abid wrote:

On 9 juin, 18:51, Ed Murphy <emurph...@soca l.rr.comwrote:

>Omar Abid wrote:

>>Im using VB 2005 to create a program that open SQL Data base
>>The problem that i want to detect the tables of a database
>>so how can i know a data base tables instantly

>(When posting to multiple newsgroups, please cross-post to
>all at once, rather than multi-posting to each one separately.)
>>
>Anyway, Google ("SQL Server 2005" systables) and see if that
>points you in the right direction.

>
i didn't know how to do that can u explain more to me

If you don't know how to use Google, then you need more help than
anyone in this newsgroup is likely to be able to provide.

[Omar Abid]

Re: SQL Server 2005 with VB.net 2005

On 10 juin, 09:57, Ed Murphy <emurph...@soca l.rr.comwrote:

Omar Abid wrote:

On 9 juin, 18:51, Ed Murphy <emurph...@soca l.rr.comwrote:

Omar Abid wrote:
>Im using VB 2005 to create a program that open SQL Data base
>The problem that i want to detect the tables of a database
>so how can i know a data base tables instantly
(When posting to multiple newsgroups, please cross-post to
all at once, rather than multi-posting to each one separately.)

>

Anyway, Google ("SQL Server 2005" systables) and see if that
points you in the right direction.

>

i didn't know how to do that can u explain more to me

>
If you don't know how to use Google, then you need more help than
anyone in this newsgroup is likely to be able to provide.

See man i use google to read news my emails and so on...
For google groups i don't use it so much and then i don't need to know
how it works....
Thank u 4all

[Erland Sommarskog]

Re: SQL Server 2005 with VB.net 2005

Omar Abid (omar.abid2006@ gmail.com) writes:

Im using VB 2005 to create a program that open SQL Data base
The problem that i want to detect the tables of a database
so how can i know a data base tables instantly

Your question is very open-ended and it is not clear what you really
want assistance with. Is it running a metadata query in SQL Server? Or
is about data access from VB .Net in general? In the latter case, I would
suggest that you are better off by first learning the basics before you
play with metadata.

Nevertheless, a query you could run to get all tables is this one:

SELECT quotename(schem a_name(schema_i d)) + '.' + quotename(name)
FROM sys.tables
ORDER BY 1

As for running it from VB - there are many possible variations depending
on what you want to do with the data. Here is a console-mode prorgam that
just prints the table names, and which uses DataAdapater.Fi ll. It also
includes some error handling.

Imports System.Data
Imports System.Data.Sql
Imports System.Data.Sql Client

Module Bugtest

Private Sub ErrorFill(ByVal sender as Object, ByVal args as FillErrorEventA rgs)
Console.WriteLi ne(args.Errors. Message)
args.Continue = true
End Sub


Private Sub PrintSqlErrors( ByVal errors As SqlErrorCollect ion, ByVal what As String)
Dim e As SqlError
For Each e In errors
Console.Write (Now.ToString(" HH:mm:ss.fff") & " " & what & _
" Message: Msg " & e.Number.ToStri ng() & _
", Severity " & e.Class.ToStrin g() & _
", State: " & e.State.ToStrin g() & _
", Procedure: " & e.Procedure & _
", Line no: " & e.LineNumber.To String & vbCrLf & _
e.Message & vbCrLf)
Next
End Sub

Private Sub OutputException (ex As Exception)
If TypeOf ex Is SqlException Then
Dim SqlEx As SqlException = DirectCast(ex, SqlException)
PrintSqlErrors( SqlEx.Errors, "Error")
Else
Console.WriteLi ne(ex.ToString( ))
End if
End Sub

Private Sub SqlInfoMessage( ByVal sender As Object, ByVal e As SqlInfoMessageE ventArgs)
PrintSqlErrors( e.Errors, "INFO MSG")
End Sub

Private Sub PrintDataTable( ByVal tbl As DataTable)
Console.Writeli ne ("============= =============== =============== ==============" & vbCrLf)
For Each col As DataColumn In tbl.Columns
Console.Writeli ne (col.ColumnName & vbTab)
Next col
Console.Writeli ne (vbCrLf)
For Each row As DataRow In tbl.Rows
For Each col As DataColumn In tbl.Columns
Console.Writeli ne (row(col).ToStr ing() & vbTab)
Next col
Console.Writeli ne(vbCrLf)
Next row
End Sub


Public Sub Main()

Dim cn As New SqlConnection, _
strConn As String

' This does not help.
' AddHandler cn.InfoMessage, AddressOf SqlInfoMessage

' Connection string, change server and database!
strConn = "Applicatio n Name=systablesd emo;Integrated Security=SSPI;"
strConn &= "Data Source=(local); Initial Catalog=Adventu reWorks"

Try
cn.ConnectionSt ring = strConn
cn.Open()
Catch ex As Exception
Console.Writeli ne(ex.Message, "Connection failed!")
cn = Nothing
Exit Sub
End Try

Dim cmd As SqlCommand = cn.CreateComman d()

cmd.CommandText = "SELECT quotename(schem a_name(schema_i d)) + "
cmd.CommandText &= " '.' + quotename(name) FROM sys.tables "
cmd.CommandText &= "ORDER BY 1"
Dim dt As New DataTable, _
da As SqlDataAdapter = New SqlDataAdapter( cmd), _
no_of_rows As Integer
AddHandler da.FillError, AddressOf ErrorFill
Try
no_of_rows = da.Fill(dt)
Catch e As Exception
OutputException (e)
End Try
Console.Writeli ne("No of rows filled " & no_of_rows.ToSt ring() & vbCrLf)
PrintDataTable( dt)

cn.Close()
cn.Dispose()

End Sub

End Module




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ed Murphy]

Re: SQL Server 2005 with VB.net 2005

Omar Abid wrote:

On 10 juin, 09:57, Ed Murphy <emurph...@soca l.rr.comwrote:

>Omar Abid wrote:

>>On 9 juin, 18:51, Ed Murphy <emurph...@soca l.rr.comwrote:
>>>Omar Abid wrote:
>>>>Im using VB 2005 to create a program that open SQL Data base
>>>>The problem that i want to detect the tables of a database
>>>>so how can i know a data base tables instantly
>>>(When posting to multiple newsgroups, please cross-post to
>>>all at once, rather than multi-posting to each one separately.)
>>>Anyway, Google ("SQL Server 2005" systables) and see if that
>>>points you in the right direction.
>>i didn't know how to do that can u explain more to me

>If you don't know how to use Google, then you need more help than
>anyone in this newsgroup is likely to be able to provide.

>
See man i use google to read news my emails and so on...

Do you also use it as a search engine?

[Omar Abid]

Re: SQL Server 2005 with VB.net 2005

On 10 juin, 11:06, Erland Sommarskog <esq...@sommars kog.sewrote:

Omar Abid (omar.abid2...@ gmail.com) writes:

Im using VB 2005 to create a program that open SQL Data base
The problem that i want to detect the tables of a database
so how can i know a data base tables instantly

>
Your question is very open-ended and it is not clear what you really
want assistance with. Is it running a metadata query in SQL Server? Or
is about data access from VB .Net in general? In the latter case, I would
suggest that you are better off by first learning the basics before you
play with metadata.
>
Nevertheless, a query you could run to get all tables is this one:
>
SELECT quotename(schem a_name(schema_i d)) + '.' + quotename(name)
FROM sys.tables
ORDER BY 1
>
As for running it from VB - there are many possible variations depending
on what you want to do with the data. Here is a console-mode prorgam that
just prints the table names, and which uses DataAdapater.Fi ll. It also
includes some error handling.
>
Imports System.Data
Imports System.Data.Sql
Imports System.Data.Sql Client
>
Module Bugtest
>
Private Sub ErrorFill(ByVal sender as Object, ByVal args as FillErrorEventA rgs)
Console.WriteLi ne(args.Errors. Message)
args.Continue = true
End Sub
>
Private Sub PrintSqlErrors( ByVal errors As SqlErrorCollect ion, ByVal what As String)
Dim e As SqlError
For Each e In errors
Console.Write (Now.ToString(" HH:mm:ss.fff") & " " & what & _
" Message: Msg " & e.Number.ToStri ng() & _
", Severity " & e.Class.ToStrin g() & _
", State: " & e.State.ToStrin g() & _
", Procedure: " & e.Procedure & _
", Line no: " & e.LineNumber.To String & vbCrLf & _
e.Message & vbCrLf)
Next
End Sub
>
Private Sub OutputException (ex As Exception)
If TypeOf ex Is SqlException Then
Dim SqlEx As SqlException = DirectCast(ex, SqlException)
PrintSqlErrors( SqlEx.Errors, "Error")
Else
Console.WriteLi ne(ex.ToString( ))
End if
End Sub
>
Private Sub SqlInfoMessage( ByVal sender As Object, ByVal e As SqlInfoMessageE ventArgs)
PrintSqlErrors( e.Errors, "INFO MSG")
End Sub
>
Private Sub PrintDataTable( ByVal tbl As DataTable)
Console.Writeli ne ("============= =============== =============== ==============" & vbCrLf)
For Each col As DataColumn In tbl.Columns
Console.Writeli ne (col.ColumnName & vbTab)
Next col
Console.Writeli ne (vbCrLf)
For Each row As DataRow In tbl.Rows
For Each col As DataColumn In tbl.Columns
Console.Writeli ne (row(col).ToStr ing() & vbTab)
Next col
Console.Writeli ne(vbCrLf)
Next row
End Sub
>
Public Sub Main()
>
Dim cn As New SqlConnection, _
strConn As String
>
' This does not help.
' AddHandler cn.InfoMessage, AddressOf SqlInfoMessage
>
' Connection string, change server and database!
strConn = "Applicatio n Name=systablesd emo;Integrated Security=SSPI;"
strConn &= "Data Source=(local); Initial Catalog=Adventu reWorks"
>
Try
cn.ConnectionSt ring = strConn
cn.Open()
Catch ex As Exception
Console.Writeli ne(ex.Message, "Connection failed!")
cn = Nothing
Exit Sub
End Try
>
Dim cmd As SqlCommand = cn.CreateComman d()
>
cmd.CommandText = "SELECT quotename(schem a_name(schema_i d)) + "
cmd.CommandText &= " '.' + quotename(name) FROM sys.tables "
cmd.CommandText &= "ORDER BY 1"
Dim dt As New DataTable, _
da As SqlDataAdapter = New SqlDataAdapter( cmd), _
no_of_rows As Integer
AddHandler da.FillError, AddressOf ErrorFill
Try
no_of_rows = da.Fill(dt)
Catch e As Exception
OutputException (e)
End Try
Console.Writeli ne("No of rows filled " & no_of_rows.ToSt ring() & vbCrLf)
PrintDataTable( dt)
>
cn.Close()
cn.Dispose()
>
End Sub
>
End Module
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

Thank u for the your reply and the links.
What I really want is too easy and clear :
I create a program that open SQL 2005 Data Bases and show tables of
the data base.
What I have done is to get a table data but I have to know the table
name before opening any one.
What I want is to know the tables name of the current data base.
Any idea ?
Omar Abid

[Erland Sommarskog]

Re: SQL Server 2005 with VB.net 2005

Omar Abid (omar.abid2006@ gmail.com) writes:

Thank u for the your reply and the links.
What I really want is too easy and clear :
I create a program that open SQL 2005 Data Bases and show tables of
the data base.
What I have done is to get a table data but I have to know the table
name before opening any one.
What I want is to know the tables name of the current data base.
Any idea ?

I just posted a sample on how to do it. You even reply to that post. Could
you care to explain why it does not address your problem?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Omar Abid]

Re: SQL Server 2005 with VB.net 2005

On 11 juin, 10:04, Erland Sommarskog <esq...@sommars kog.sewrote:

Omar Abid (omar.abid2...@ gmail.com) writes:

Thank u for the your reply and the links.
What I really want is too easy and clear :
I create a program that open SQL 2005 Data Bases and show tables of
the data base.
What I have done is to get a table data but I have to know the table
name before opening any one.
What I want is to know the tables name of the current data base.
Any idea ?

>
I just posted a sample on how to do it. You even reply to that post. Could
you care to explain why it does not address your problem?
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

I tried the code but I want any SQL Data Base and not Adventure Works

[Erland Sommarskog]

Re: SQL Server 2005 with VB.net 2005

Omar Abid (omar.abid2006@ gmail.com) writes:

I tried the code but I want any SQL Data Base and not Adventure Works

Just replace AdventureWorks in the connection string with the database
of your choice. I used AdventureWorks to give you a working sample.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Piero 'Giops' Giorgi]

Re: SQL Server 2005 with VB.net 2005

On 11 Giu, 14:30, Erland Sommarskog <esq...@sommars kog.sewrote:

I tried the code but I want any SQL Data Base and not Adventure Works

>
Just replace AdventureWorks in the connection string with the database
of your choice. I used AdventureWorks to give you a working sample.

And just to be more specific...

' Connection string, change server and database!
strConn = "Applicatio n Name=systablesd emo;Integrated
Security=SSPI;"
strConn &= "Data Source=(local); Initial Catalog= ***** YOUR
DATABASE HERE **** "

P

[Omar Abid]

Re: SQL Server 2005 with VB.net 2005

On 11 juin, 23:30, Erland Sommarskog <esq...@sommars kog.sewrote:

Omar Abid (omar.abid2...@ gmail.com) writes:

I tried the code but I want any SQL Data Base and not Adventure Works

>
Just replace AdventureWorks in the connection string with the database
of your choice. I used AdventureWorks to give you a working sample.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

Thank you the problem was solved, and I used mid to get the table
name, because the output was "[DBO].[table]"
Thanks again for your help
Omar Abid