Database project

[slbreaul]

Hello :)

I'm working a project for class that involves a database connection. I have an Acess database called "Libraries" that contains two tables called Libraries and Books. I have to create a form that has two DataGridViews(one that contains the Libraries and their ID #'s, the other that has the media from the selected library). I have to be able to save changes made to the database. There are also two listboxes. One has the types of media (book, DVD, etc). The user can select a media type and the second box will show the items from all libraries that are that type of media. I'm really stuck on how to go about this, but I have some pieces of code. Would someone be willing to help walk me through this?

I have the form and the database connection done. This is the first part that I have:

Dim connstr As String = "Provider=Microsoft.ACE.OLEBD.12.0;Data Source=C:\Libraries.accdb"
    Dim Shareddt As New DataTable()
    Dim sqlStr As String = "SELECT * FROM Libraries"
    Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connstr)

I also have a sub to fill the list box with the types of media:

Sub populateListBoxWithMedia()
        lstMediaType.Items.Clear()
        Dim strSQL As String = "SELECT DISTICT Media Type FROM Libraries"
        Dim dt As New DataTable()
        Dim dataAdapter As New OleDb.OleDbDataAdapter(strSQL, connstr)
        dataAdapter.Fill(dt)
        lstMediaType.DataSource = dt

        For i As Integer = 0 To (dt.Rows.Count - 1)
            lstMediaType.Items.Add(dt.Rows(i)("Media Type"))
        Next
        dataAdapter.Dispose()
        lstMediaType.SelectedIndex = 0
    End Sub

And I have the save changes button:

Private Sub btnSaveChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveChanges.Click
        Dim strSQL As String
        Dim dataAdapter As New OleDb.OleDbDataAdapter(strSQL, connstr)
        Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)
        Dim changes As Integer
        changes = dataAdapter.Update(Shareddt)
        dataAdapter.Dispose()
        MsgBox("Changes saved to database!")
    End Sub

Nothing shows up when I run the form. Any ideas?

Thank you in advance!

[Oxiegen]

You misspelled DISTINCT in "SELECT DISTICT Media Type FROM Libraries".

[Reverend Jim]

There are a few things you have to do before you even start writing code.

Determine the structure of your database

Determine what tables you need and what fields will appear in each table. One of the tables will be the libraries table and will contain at least two fields, the library name and the library ID. The library ID will be unique and can be the primary key. You will need one table for media. This table will contain fields such as name, media type, library code (Dewey decimal perhaps - something to uniquely identify an item) and a library ID. A third table will define the media types and a unique code for each. For example, media type "DVD" may have the code 1, "BOOK" may have the code 2, etc.

Create the database and tables and populate with sample data

Determine the functions you want to make available to the user

Create a user interface to reflect that functionality. Write out exactly what happens for each action the user can execute. Use that document to create the comments for your code for each event. Events would be

user selects a new library from the drop down list
user selects a new media type (possibly more than one type if that is an option)

Create stubs (empty procedures) for those events using the IDE

Once you have the stubs defined, populate them with the comments from the previous section.

Now you can start coding

Use the immediate window in Access to try the queries that you will be using in your application. It's easier to debug them there than while running the code. A couple of tips:

Don't use blanks in your field names in the database.

Name your controls for their function. names like Button1, Button2, Listbox1, etc are NOT going to make your code easier to read. If the person who marks your assignment finds your code hard to read (assuming that markers even do that anymore) I guarantee you will lose points (I used to be one of those dreaded markers).

Use whitespace to block your code. Code that is pleasing to the eye automatically puts your work in a good light in the eyes of the marker.

Do the groundwork and get back to us with questions if you get stuck.

Good luck.