-
Nov 22nd, 2017, 06:58 PM
#1
Thread Starter
Hyperactive Member
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
-
Nov 22nd, 2017, 09:28 PM
#2
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.
-
Dec 7th, 2017, 08:29 PM
#3
Thread Starter
Hyperactive Member
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
-
Dec 7th, 2017, 08:58 PM
#4
Re: Creating a query to retrieve database table
Originally Posted by TATARPRO
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.
-
Dec 8th, 2017, 03:59 AM
#5
Fanatic Member
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
-
Dec 8th, 2017, 04:27 AM
#6
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.
-
Dec 8th, 2017, 07:34 PM
#7
Thread Starter
Hyperactive Member
Re: Creating a query to retrieve database table
Originally Posted by jmcilhinney
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
-
Dec 8th, 2017, 08:01 PM
#8
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.
-
Dec 8th, 2017, 08:15 PM
#9
Fanatic Member
Re: Creating a query to retrieve database table
Originally Posted by 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.
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
-
Dec 8th, 2017, 08:46 PM
#10
Re: Creating a query to retrieve database table
Originally Posted by GBeats
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.
-
Dec 12th, 2017, 11:05 AM
#11
Thread Starter
Hyperactive Member
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
-
Dec 12th, 2017, 11:46 AM
#12
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
-
Dec 12th, 2017, 05:08 PM
#13
Re: Creating a query to retrieve database table
Originally Posted by TATARPRO
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.
-
Dec 14th, 2017, 02:28 PM
#14
Thread Starter
Hyperactive Member
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
-
Dec 14th, 2017, 06:12 PM
#15
Re: Creating a query to retrieve database table
Originally Posted by 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?
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.
-
Dec 16th, 2017, 07:07 PM
#16
Thread Starter
Hyperactive Member
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
Nothing I post is Self Reliable. Use it at your own risk
-
Dec 16th, 2017, 07:09 PM
#17
Thread Starter
Hyperactive Member
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
-
Dec 16th, 2017, 11:24 PM
#18
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|