dcsimg
Results 1 to 18 of 18

Thread: Creating a query to retrieve database table

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    238

    Post Creating a query to retrieve database table

    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
    Code:
    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
    Nothing I post is Self Reliable. Use it at your own risk

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Creating a query to retrieve database table

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    238

    Re: Creating a query to retrieve database table

    I mean I want to retrieve table names
    Nothing I post is Self Reliable. Use it at your own risk

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Creating a query to retrieve database table

    Quote Originally Posted by TATARPRO View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    911

    Re: Creating a query to retrieve database table

    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..

    Code:
    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
    Code:
    SELECT * FROM INFORMATION_SCHEMA.TABLES
    will provide you with a whole host of table information

    and

    Code:
    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
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Creating a query to retrieve database table

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    238

    Re: Creating a query to retrieve database table

    Quote Originally Posted by jmcilhinney View Post
    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?
    Nothing I post is Self Reliable. Use it at your own risk

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Creating a query to retrieve database table

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  9. #9
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    911

    Re: Creating a query to retrieve database table

    Quote Originally Posted by jmcilhinney View Post
    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
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Creating a query to retrieve database table

    Quote Originally Posted by GBeats View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    238

    Re: Creating a query to retrieve database table

    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?
    Nothing I post is Self Reliable. Use it at your own risk

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,821

    Re: Creating a query to retrieve database table

    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:
    Code:
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Creating a query to retrieve database table

    Quote Originally Posted by TATARPRO View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    238

    Re: Creating a query to retrieve database table

    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?
    Code:
    Using connection As New SqlConnection(connectionString)
        connection.Open()
        Me.ComboBox1.ValueMember = "TABLE_NAME"
        Me.ComboBox1.DataSource = connection.GetSchema("Tables")
    End Using
    Nothing I post is Self Reliable. Use it at your own risk

  15. #15
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Creating a query to retrieve database table

    Quote Originally Posted by TATARPRO View Post
    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?
    Code:
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    238

    Re: Creating a query to retrieve database table

    this is the table it returns when I execute this code
    Code:
     Public Function GetSchemaTables() As DataTable
            Dim con As New SqlConnection(CString)
            con.Open()
            Return con.GetSchema("Tables")
    
            con.Close()
        End Function
    Name:  sc_tables.PNG
Views: 35
Size:  10.7 KB
    Nothing I post is Self Reliable. Use it at your own risk

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    238

    Re: Creating a query to retrieve database table

    i am also trying the restrictionvalues but I can't get through
    i don't know how to put the values
    Code:
      Public Function GetSchemaTables() As DataTable
            Dim con As New SqlConnection(CString)
            con.Open()
            Return con.GetSchema(restrictionValues:="3")
    
            con.Close()
        End Function
    Nothing I post is Self Reliable. Use it at your own risk

  18. #18
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Creating a query to retrieve database table

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width