473,324 Members | 2,257 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL Server 2005 with VB.net 2005

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
om*******@hotmail.com
om***********@gmail.com
Omar abid

Jun 9 '07 #1
16 4886
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.
Jun 9 '07 #2
There is a view that has a list of all the columns in the whole
database. I believe its information_sys.columns

Jun 10 '07 #3
On 9 juin, 18:51, Ed Murphy <emurph...@socal.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

Jun 10 '07 #4
On 10 juin, 08:39, Karl <knelso...@gmail.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

Jun 10 '07 #5
Omar Abid wrote:
On 9 juin, 18:51, Ed Murphy <emurph...@socal.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.
Jun 10 '07 #6
On 10 juin, 09:57, Ed Murphy <emurph...@socal.rr.comwrote:
Omar Abid wrote:
On 9 juin, 18:51, Ed Murphy <emurph...@socal.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

Jun 10 '07 #7
Omar Abid (om***********@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(schema_name(schema_id)) + '.' + 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.Fill. It also
includes some error handling.

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Module Bugtest

Private Sub ErrorFill(ByVal sender as Object, ByVal args as FillErrorEventArgs)
Console.WriteLine(args.Errors.Message)
args.Continue = true
End Sub
Private Sub PrintSqlErrors(ByVal errors As SqlErrorCollection, 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.ToString() & _
", Severity " & e.Class.ToString() & _
", State: " & e.State.ToString() & _
", Procedure: " & e.Procedure & _
", Line no: " & e.LineNumber.ToString & 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.WriteLine(ex.ToString())
End if
End Sub

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

Private Sub PrintDataTable(ByVal tbl As DataTable)
Console.Writeline ("================================================ =========" & vbCrLf)
For Each col As DataColumn In tbl.Columns
Console.Writeline (col.ColumnName & vbTab)
Next col
Console.Writeline (vbCrLf)
For Each row As DataRow In tbl.Rows
For Each col As DataColumn In tbl.Columns
Console.Writeline (row(col).ToString() & vbTab)
Next col
Console.Writeline(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 = "Application Name=systablesdemo;Integrated Security=SSPI;"
strConn &= "Data Source=(local);Initial Catalog=AdventureWorks"

Try
cn.ConnectionString = strConn
cn.Open()
Catch ex As Exception
Console.Writeline(ex.Message, "Connection failed!")
cn = Nothing
Exit Sub
End Try

Dim cmd As SqlCommand = cn.CreateCommand()

cmd.CommandText = "SELECT quotename(schema_name(schema_id)) + "
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.Writeline("No of rows filled " & no_of_rows.ToString() & vbCrLf)
PrintDataTable(dt)

cn.Close()
cn.Dispose()

End Sub

End Module


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 10 '07 #8
Omar Abid wrote:
On 10 juin, 09:57, Ed Murphy <emurph...@socal.rr.comwrote:
>Omar Abid wrote:
>>On 9 juin, 18:51, Ed Murphy <emurph...@socal.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?
Jun 10 '07 #9
On 10 juin, 11:06, Erland Sommarskog <esq...@sommarskog.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(schema_name(schema_id)) + '.' + 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.Fill. It also
includes some error handling.

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Module Bugtest

Private Sub ErrorFill(ByVal sender as Object, ByVal args as FillErrorEventArgs)
Console.WriteLine(args.Errors.Message)
args.Continue = true
End Sub

Private Sub PrintSqlErrors(ByVal errors As SqlErrorCollection, 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.ToString() & _
", Severity " & e.Class.ToString() & _
", State: " & e.State.ToString() & _
", Procedure: " & e.Procedure & _
", Line no: " & e.LineNumber.ToString & 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.WriteLine(ex.ToString())
End if
End Sub

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

Private Sub PrintDataTable(ByVal tbl As DataTable)
Console.Writeline ("================================================ =========" & vbCrLf)
For Each col As DataColumn In tbl.Columns
Console.Writeline (col.ColumnName & vbTab)
Next col
Console.Writeline (vbCrLf)
For Each row As DataRow In tbl.Rows
For Each col As DataColumn In tbl.Columns
Console.Writeline (row(col).ToString() & vbTab)
Next col
Console.Writeline(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 = "Application Name=systablesdemo;Integrated Security=SSPI;"
strConn &= "Data Source=(local);Initial Catalog=AdventureWorks"

Try
cn.ConnectionString = strConn
cn.Open()
Catch ex As Exception
Console.Writeline(ex.Message, "Connection failed!")
cn = Nothing
Exit Sub
End Try

Dim cmd As SqlCommand = cn.CreateCommand()

cmd.CommandText = "SELECT quotename(schema_name(schema_id)) + "
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.Writeline("No of rows filled " & no_of_rows.ToString() & vbCrLf)
PrintDataTable(dt)

cn.Close()
cn.Dispose()

End Sub

End Module

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/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

Jun 10 '07 #10
Omar Abid (om***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 11 '07 #11
On 11 juin, 10:04, Erland Sommarskog <esq...@sommarskog.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...@sommarskog.se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
I tried the code but I want any SQL Data Base and not Adventure Works

Jun 11 '07 #12
Omar Abid (om***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 11 '07 #13
On 11 Giu, 14:30, Erland Sommarskog <esq...@sommarskog.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 = "Application Name=systablesdemo;Integrated
Security=SSPI;"
strConn &= "Data Source=(local);Initial Catalog= ***** YOUR
DATABASE HERE **** "

P

Jun 11 '07 #14
On 11 juin, 23:30, Erland Sommarskog <esq...@sommarskog.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...@sommarskog.se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/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

Jun 12 '07 #15
Omar Abid (om***********@gmail.com) writes:
Thank you the problem was solved, and I used mid to get the table
name, because the output was "[DBO].[table]"
You could modify the query if you don't want the schema.

Then again, in SQL 2005, it may be dangerous to ignore the schema, since
SQL 2005 makes schemas useful, and it can be deceivable to work with.
The AdventureWorks database is a good example of this.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 12 '07 #16
On 12 juin, 23:10, Erland Sommarskog <esq...@sommarskog.sewrote:
Omar Abid (omar.abid2...@gmail.com) writes:
Thank you the problem was solved, and I used mid to get the table
name, because the output was "[DBO].[table]"

You could modify the query if you don't want the schema.

Then again, in SQL 2005, it may be dangerous to ignore the schema, since
SQL 2005 makes schemas useful, and it can be deceivable to work with.
The AdventureWorks database is a good example of this.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thank you for your advice, it was really helpful to me
Omar Abid

Jun 12 '07 #17

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: AlessanBar | last post by:
Hello Friends !! I have a strange problem, and I need to know what would be the source of this. I have a laptop computer with the following configuration: Pentium III Brand : Toshiba Speed :...
2
by: Kevin R | last post by:
I'm trying to get asp.net 1.1 running on my home PC. When I try creating a new ASP.NET Web Application in 'Visual Studio .NET 2003' I get the following error: "Visual Studio .NET has detected...
17
by: Danieltbt05 | last post by:
just installed SQL server 2000 and using my client , i can't locate the server. I used SQL query analyzer to search but no servers were found. Error message is as below Server : Msg17,level...
1
by: Peter | last post by:
I've purchased VS.NET 2005 Standard and have tried to install SQL Server 2005 Express, but get the following error in the error log. Please could someone help me.... Microsoft SQL Server 2005...
16
by: Jeremy S. | last post by:
I'm about to install VS.NET 2005 and SQL Server 2005 on a new/clean development machine (XP Pro/SP2 etc). Is the order of installation important (i.e., which product should I install first)? ...
10
by: amjad | last post by:
how to connection sql server table with aspx like pulling data from table to grid view.... simple example to start .... thanks
2
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of...
3
by: Steve | last post by:
Hi All I downloaded Sql server 2005 express SP2 and attempted to modify the Bootstrapper package files as I did with SP1 When i try to install SQL server as part of my VS 2005 deployment app I...
3
by: Lee T. Hawkins | last post by:
I am having a number of problems over the last two full days trying to get an ASP.NET 2.0 application to connect to a SQL Server 2005 database... First off, I built this application w/ Visual...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.