[RESOLVED] Winging It But, Getting Trouble Selecting Data From Database

Hello, i hope someone here can help me with this. I'm not sure exactly how to get this done but i think i may be on the right track. I'm using Visual Studio Express 2013 and the ACE provider, i've figured out that in order to send commands to the dataset i need to go through a table adapter but i haven't gotten much further. In the program i want to query the database for specific entries but so far i've been stopped by an unhandled exception.

Code:

---

    Public Function ConflictonCheck()

        Dim media As New MediaODataSetTableAdapters.MediaO_Sort_DatabaseTableAdapter

        media.Adapter.SelectCommand.CommandText = "SELECT * FROM [MediaO Sort Database]"

        'I want to see what's being returned
        MessageBox.Show(media.GetData.ToString)

    End Function

---

The second line causes the IDE to display the following

An unhandled exception of type 'System.NullReferenceException' occurred in MediaO.exe

Additional information: Object reference not set to an instance of an object.


I'm not 100% sure of how to issue commands to the tableadapter so i just tried sending a broad selection statement for testing purposes, in actual use i'm going to need to specify to the sql statement some variables in the program. These are what the statement should search for so i'd like instruction on incorporating variables into it, i've seen a statement online which read ID = @ID. Is the @ an identifier for a variable within the program called ID ? I assumed the first ID was a table name.

Any assistance offered will be greatly appreciated, thank you.

When you create a typed DataSet, a DataTable is generated for each database table and a corresponding table adapter is created for each DataTable. Each table adapter has Fill, GetData and Update methods. The Fill method will query the database and populate an existing DataTable with all the data from the database table. The GetData method will query the database and populate a new DataTable with all the data from the database table and then return that DataTable. The Update method will take an existing DataTable and save all the changes it contains back to the database.

In your case, you don't need or want to set the CommandText of anything. The table adapter already contains the required SQL code. You can simply create the table adapter and call GetData to get back a populated DataTable. What use is calling ToString on that though? All that's going to do is give you the name of the DataTable class. If you want to view the data in the DataTable then you have to traverse the fields it contains. The simplest way to do that is to bind it to a DataGridView, which displays a very similar tabular view to Access itself.

What most people start out doing is to drag and drop from the Data Sources window. If you drag a table from that window onto your form, the IDE will automatically generate a DataSet, a table adapter, a BindingSource, a DataGridView and a BindingNavigator. The appropriate DataTable in the DataSet will be automatically bound to the BindingSource, which in turn will be bound to the DataGridView and associated with the BindingNavigator. Some code will also be generated automatically, which will call Fill on the table adapter to populate the DataTable.

Thanks for the reply, what you see there is me trying to trudge through the path to my end goal. Basically i'm going through trial and error. I don't know if i'm not using the correct search terms on the web or what but i'm not finding what i want. I've tried the drag and drop and that's very helpful but i don't think i want to use it here, i don't want to actually display anything or select everything from the database.

What i want is to take three variables from the program and search their specific tables in the database to see if all three appear in the same row. Right now all i want to do is check if what these variables refer to exist within the database on a single row. What i was doing with media is trying to create code that could do a search.

Code:

---

      If source & destination & type <> "" Then

            Return True

        End If

        Return False

---

I don't know if the above helps you to understand what i'm trying to do but basically, i'm going to make sure those variables contain actual strings before making a query to the database to see if they're already there.

Looking at the code for the sql commands in the designer is what lead me to try and see if i could select all the contents of the database and then use that getData method i stumbled upon to let me see if the command worked but i got the exception before that line and became lost. I was piecing together tidits of information i got from all over the web and finally after failing decided to seek help here.

Edit: I think i may have misunderstood what the designer is for, eventually i wanted to use the designer generated tools for when i actually wanted to display data from the database. The form those tools are on wouldn't be loaded at all times, so i'm wondering if to do this background query to the database without displaying anything should i be using a typed dataset ?

If you want to be able to query the database and filter the results then you need to add a new query to your table adapter, which you can do in the DataSet designer. That will then add new methods to the table adapter that you can call in place of Fill and GetData. You should start reading here:

http://msdn.microsoft.com/en-us/library/bz9tthwx.aspx

You should also follow the SQL Tutorial and Data Walkthroughs links in my signature below.

Thanks very much for the help, i'll look into it further on msdn.

Edit: I've found what i needed through your link, i also understand working with databases more. Thanks again.