Click here to Skip to main content
15,887,392 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am connecting a VB Form Program to an access database. 3 of the 4 tables in the database are connected and working in their proper datagridviews but the last one is not connecting properly. the code for all connections is the same.

What I have tried:

so i traced the issue with msgbox outputs to when i am populating my variables.
the settings.DA.fill just above the commented out lines. if i comment out the fill line the program will run and give both loc messages but with it active the code gets stuck and wont proceed with, but instead ignores the rest of the code keeps the database open.
Imports System.Data.OleDb
Public Class Admin

    Dim provider As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source =")
    Dim dataFile As String = ("Filelocaty.mdb")
    Dim connString As String = provider & dataFile
    Dim myConnection As OleDbConnection = New OleDbConnection
    Dim con As OleDbConnection = New OleDbConnection(connString)

    Dim infoDA As OleDbDataAdapter
    Dim infoDS As DataSet
    Dim cmd1 As New OleDbCommand("SELECT * FROM Info", con)

    Dim RoomDA As OleDbDataAdapter
    Dim RoomDS As DataSet
    Dim cmd2 As New OleDbCommand("SELECT * FROM Rooms", con)

    Dim userDA As OleDbDataAdapter
    Dim userDS As DataSet
    Dim cmd3 As New OleDbCommand("SELECT * FROM Logins", con)

    Dim settingDA As OleDbDataAdapter
    Dim settingDS As DataSet
    Dim cmd4 As New OleDbCommand("SELECT * FROM General", con)

    Private Sub Admin_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        con.Open()

        infoDA = New OleDbDataAdapter(cmd1)
        Dim builder1 As OleDbCommandBuilder = New OleDbCommandBuilder(infoDA)
        infoDS = New DataSet
        Me.infoDA.Fill(infoDS, "Info")
        InfoDG.DataSource = infoDS.Tables("Info").DefaultView
        InfoDG.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill


        RoomDA = New OleDbDataAdapter(cmd2)
        Dim builder2 As OleDbCommandBuilder = New OleDbCommandBuilder(RoomDA)
        RoomDS = New DataSet
        Me.RoomDA.Fill(RoomDS, "Rooms")
        RoomDG.DataSource = RoomDS.Tables("Rooms").DefaultView
        RoomDG.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

        userDA = New OleDbDataAdapter(cmd3)
        Dim builder3 As OleDbCommandBuilder = New OleDbCommandBuilder(userDA)
        userDS = New DataSet
        Me.userDA.Fill(userDS, "Logins")
        UserDG.DataSource = userDS.Tables("Logins").DefaultView
        UserDG.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

        settingDA = New OleDbDataAdapter(cmd4)
        Dim builder4 As OleDbCommandBuilder = New OleDbCommandBuilder(settingDA)
        settingDS = New DataSet
        MsgBox("Loc A")
        settingDA.Fill(settingDS, "General")
        'SettingsDG.DataSource = settingDS.Tables("General").DefaultView
        'SettingsDG.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        MsgBox("Loc b")
        con.Close()
    End Sub


if anyone knows where i went wrong please let me know! from what i can tell the code is fine and i have checked my database thoroughly.
Posted
Updated 19-Sep-17 6:44am
Comments
eddieangel 19-Sep-17 11:32am    
Depending on how your dataset are generated, it could be pluralizing General as Generals. Check that. Also, it is never a bad idea to use delimiters around table names. [General] [Room] [Info] and such. Are all these failures on the same computer?
eddieangel 19-Sep-17 11:39am    
Is the failure occurring on the same computer? General could cause you issues, you might consider using delimiters on your table names. [General] [Info] [Rooms] etc...

Also, something to be wary of for your future development is that the ACE and Jet drivers are really flaky. Any time you can use a driver that isn't tied to something installed on the machine, the better off you will be.
Member 13416420 19-Sep-17 12:11pm    
That did the trick!

Im not sure what you mean with the drivers if you can link me to a reference that would help!
Member 13416420 19-Sep-17 12:11pm    
That did the trick!

Im not sure what you mean with the drivers if you can link me to a reference that would help!
eddieangel 19-Sep-17 12:19pm    
I am not sure I can find a link right off hand, but I can give you a brief rundown.

Each version of Office comes with a dll (driver in simple terms) that enables programs to access it's documents through a common interface. Prior to 2000 the OLEDB driver (That is the format that Access and Excel store data in) in use was the JETDB provider. After 2000 or so they phased in the ACEDB driver that you are referencing in your connection string above.

The problem is that these drivers require that Office or at least the Office compatability pack be installed on the target computer. That is probably not an issue since you are access (I assume) a local Access database. However, when deployed to different machines with different versions of Office, you may find that your connection string will cause you problems. You will likely get an error on the Connection.Open() line. This is especially common when you have multiple computers with different processor architectures (32bit vs 64bit).

It is not anything to be afraid of, just something to be aware of if you start encountering errors when you deploy to different computers.


1 solution

Another problem I see. You're repeating the same code over and over again. This is Clue #1 (tm) that you need to put that kind of code in it's own method and call it from where you need it.
 
Share this answer
 
Comments
Member 13416420 19-Sep-17 13:56pm    
after i got everything working properly i will be condensing my code to specific module blocks for looped application but since i am adding and removing features i like to have it all laid out during my design stages. but yes this a very valid point, someone could have also pointed out my lack of try catch use but i like to complete that as i review and reedit my code.
Dave Kreskowiak 19-Sep-17 14:02pm    
That's cute and all, but it just adds more opportunity to introduce bugs, kind of like what you're facing now where one query doesn't work but the others do. Condensing the code now helps narrow down the problem between the code executing the query and the query itself.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900