Creating a query to retrieve database table

[TATARPRO]

I want to write a database query to retrieve a table then fill it in a datagridview but I''ve tried but this's all I got

Imports System.Data.SqlClient
Module Personal_PrivateModule
    Public databaseLocation As String = Nothing 'the location is gotten from an open file dialog
    Dim Connection As SqlConnection
    Dim Command As SqlCommand
    Dim Reader As SqlDataReader
   
    Public Sub Connect()
        Try
            DatabaseSelect.ShowDialog()
            Dim ConnectionString As String = "Data Source=(LocalDB)\v11.0 ;AttachDbFilename=" & databaseLocation & ";" & "Integrated Security=True;Connect Timeout=30"
            Dim Connection As New SqlConnection(ConnectionString)
            Connection.Open()
            If ConnectionState.Open Then
                MsgBox("Connection Established...")

                Connection.Close()
                MsgBox("Connection Closed...")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

End Module

[jmcilhinney]

All you're doing there is opening and closing a connection. If you want to retrieve data then you need to actually execute a query over that connection. I suggest that you follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data for some code examples of common ADO.NET scenarios.

[TATARPRO]

I mean I want to retrieve table names

[jmcilhinney]

I mean I want to retrieve table names

There was nothing in your first post to suggest that that was the case. Please always provide a FULL and CLEAR explanation of the problem.

In that case, you should follow the Blog link in my signature below and check out my post on Retrieving Database Schema Information. You basically call one method and that will return a DataTable containing the requested schema info.

[GBeats]

Well first of all you need to setup your adapter or method of retrieving data.......

im using the same as you here, the only difference is the connection part is done in the background in .NET..

Friend Function GetDataSet_FromQuery(QryStr As String, ConStr As String) As Data.DataSet
            Dim Retry As Boolean = True

            GetDataSet_FromQuery = Nothing

            While Retry
                Try
                    GetDataSet_FromQuery = New DataSet()
                    Using Adapter = New SqlClient.SqlDataAdapter(QryStr, ConStr)

                        Adapter.Fill(GetDataSet_FromQuery)
                    End Using
                    Retry = False
                Catch ex As Exception
                    If MessageBox.Show("Error Retreiving Data..." & vbCrLf & "Error Location: Public Function GetDataSet_FromQuery" & vbCrLf & "Error Description: " & ex.Message, "Communication Error", MessageBoxButtons.RetryCancel) = DialogResult.Retry Then
                        Retry = True

                    Else
                        Retry = False
                        GetDataSet_FromQuery = Nothing
                    End If
                End Try
            End While

        End Function

so all this code does is start a connection and send a query and send the results to a dataset.....

INFORMATION_SCHEMA.TABLES will show you all the tables on the catalog your currently connected too

something like

SELECT * FROM INFORMATION_SCHEMA.TABLES

will provide you with a whole host of table information

and

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

should give you what your looking for.

just make sure your connection string is setup right and you set the initial catalog to the right DB.


there is also an SQL object Sys.Databases, and Sys.Tables available that you can also use in a query but im not sure the differences other than the schema is probably better and faster to use

[jmcilhinney]

Not that it's wrong but you don't actually need any SQL code. The GetSchema method of the connection object can handle all that.

[TATARPRO]

There was nothing in your first post to suggest that that was the case. Please always provide a FULL and CLEAR explanation of the problem.

In that case, you should follow the Blog link in my signature below and check out my post on Retrieving Database Schema Information. You basically call one method and that will return a DataTable containing the requested schema info.

where is the link to your signature?

[jmcilhinney]

It's not a link TO my signature. It's a link IN my signature. Signatures are the bit that gets added to the bottom of every post. If you can't see mine then you must have signatures turned off in your user settings for the site. I and others tend to put links to stuff we refer to often in our signatures so as not to have to repeatedly find and provide the same links.

Actually, I just realised that you have a signature set too, so I don't really understand why you need any clarification.

[GBeats]

Not that it's wrong but you don't actually need any SQL code. The GetSchema method of the connection object can handle all that.

Oh nice, ill have a look at that

[jmcilhinney]

Oh nice, ill have a look at that

Check out the blog post I mentioned. It goes through the use of GetSchema in a reasonable amount of detail. Here's a direct link.

[TATARPRO]

Ok I've seen the posts in your blog now and I've tried it. I have used the connection.GetSchema and it returned a table with four columns. How can I get make it return only the database tables?

[techgnome]

So, I took a minute to go look at teh blog... and the answer is there... in plain english. Code too. in C# and VB.Net... so that last question should never have been asked.
http://jmcilhinney.blogspot.com/2009...formation.html

Part way through the entry is this: "Let’s look at getting information from a specific collection. One of the most commonly queried collections is Tables, so let’s take a look at that." ... gee... and then "For now, edit the code in your form to get the schema for the Tables collection specifically:" followed by this:

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.DataGridView1.DataSuorce = connection.GetSchema("Tabels")
End Using

Took me less than 2 minutes for all this... now I'm kind of annoyed.

-tg

[jmcilhinney]

How can I get make it return only the database tables?

If what you actually mean is that you want just the table names then you can't, but why would that be a problem? If you don't want to display the other information then don't display it.

[TATARPRO]

Ok I used this code and it returned only table namenames but the first and last items in the combobox are not table names why?

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.ComboBox1.ValueMember = "TABLE_NAME"
    Me.ComboBox1.DataSource = connection.GetSchema("Tables")
End Using

[jmcilhinney]

Ok I used this code and it returned only table namenames but the first and last items in the combobox are not table names why?

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.ComboBox1.ValueMember = "TABLE_NAME"
    Me.ComboBox1.DataSource = connection.GetSchema("Tables")
End Using

That doesn't return just the table names. The DataTable still contains the same columns but you're only display one of those columns, which is exactly what I said you should do in the previous post.

Please read carefully what I am about to post and REMEMBER it. ALWAYS provide a FULL and CLEAR explanation of the problem. If you only provide a partial explanation then we have to waste our time and yours trying to get the whole picture.

In this case, you tell us that the first and last rows do not contain table names but you don't tell us what they do contain. It should be obvious that what they do contain is relevant information. My guess would be that they are system tables but, as I said, that's just a guess. If you have read the blog post I directed you to properly then you already know about restrictions so you should determine whether they can be filtered out using a restriction.

[TATARPRO]

this is the table it returns when I execute this code

 Public Function GetSchemaTables() As DataTable
        Dim con As New SqlConnection(CString)
        con.Open()
        Return con.GetSchema("Tables")

        con.Close()
    End Function

[TATARPRO]

i am also trying the restrictionvalues but I can't get through
i don't know how to put the values

  Public Function GetSchemaTables() As DataTable
        Dim con As New SqlConnection(CString)
        con.Open()
        Return con.GetSchema(restrictionValues:="3")

        con.Close()
    End Function

[jmcilhinney]

Hmmm... I was hopeful of a restriction to distinguish system tables from user tables but there doesn't appear to be one. It appears that you'd have to use a naming convention to distinguish them or not use the dbo schema. I guess that you can assume that anything beginning with "sys" is a system table although maybe some really bad developers might use that prefix themselves. I'm not sure what the other table is for so I'm not sure whether "dt" is also a standard system prefix.