Re: SQL Server 2005 with VB.net 2005

On 10 juin, 11:06, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Omar Abid (omar.abid2...@xxxxxxxxx) 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...@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: Vb newb needs help on data import/export
    ... software that just had to select from um db in a MS access data base ... and insert into a SQL server Database. ... Dim cnConn As New OleDbConnection ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Control buttons will not work
    ... At the present time there are only two records on the data base I started ... In the main form Contacts I have Control buttons for: First Record, ... Private Sub Find_cmd53_Click ... On Error GoTo Err_Find_cmd53_Click ...
    (microsoft.public.access.forms)
  • Re: Blank Record
    ... You would code it to undo the entry like this: ... Private Sub cmdNameOfButton_Click ... information and Exit the Data Base, the next time you open the form you ...
    (microsoft.public.access.gettingstarted)
  • connect to data base using control
    ... I have this code to connect to SQL Data base my question is there any ... Private Sub Form2_Load(ByVal sender As System.Object, ... Dim cn As New SqlConnection ...
    (microsoft.public.dotnet.languages.vb.controls)
  • RE: Print Curent Record From Form on Report
    ... "Srenfro" wrote: ... I have a printing problem with my data base I have a print button that ... Private Sub cmdPrintRecord_Click ...
    (microsoft.public.access.reports)
Loading