none
Can't connect to Access from Visual Studio Academic Edition RRS feed

  • Question

  • What's wrong with my initial load event. When loaded members should be automatically displayed.

     

    Private Sub frmCities_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     

    'Get data from the database, put it into the DataTable object dt,

     

    'and display the initial record's data in text boxes

     

     

    Dim connStr As New OleDbConnection

     

    Dim sqlStr As String = "SELECT * FROM Cities"

     

    Dim command As New OleDbCommand(sqlStr)

    command.Connection =

    New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = MEGACITIES.MDB")

     

    Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)

    dataAdapter.Fill(dt)

    dataAdapter.Dispose()

    UpdateTextBoxes()

     

    End Sub

     

    Friday, August 13, 2010 1:07 PM

All replies

  • There is not enough info here to answer your question. Could you indicate what type of problem you are encountering (e.g. errors)?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, August 13, 2010 2:22 PM
  • Paul

    This is a simple form which when loaded should display elements from the database MEGACITIES.MDB. The code in the load event is as fellows. When I debug the problem I believe lies in the String Connection ConnStr which is closed and fails to establish the connection. The dataAdapter is not responding to the connStr.

     

    Dim connStr As New OleDbConnection

     

    Dim sqlStr As String = "SELECT * FROM Cities"

     

    Dim command As New OleDbCommand(sqlStr)

    command.Connection =

    New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = MEGACITIES.MDB")

     

    Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)

    dataAdapter.Fill(dt)

    dataAdapter.Dispose()

    UpdateTextBoxes()

     

    Sunday, August 15, 2010 10:48 AM
  • Try to change it to following code

     

    Dim connStr As New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = MEGACITIES.MDB") 

    connStr.Open() 

     

    Dim sqlStr As String = "SELECT * FROM Cities"

     

     

     

    dataAdapter.Fill(dt)

    dataAdapter.Dispose()

    connStr.Close()

    UpdateTextBoxes()

    Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
    Val Mazur (MVP) http://www.xporttools.net
    Monday, August 16, 2010 10:29 AM
    Moderator
  • Sorry, but I didn't get the information I was requesting. Does the application hang? Are there errors? If so, what are they?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, August 16, 2010 4:18 PM
  • Hi JwasWell,

    Please check your connection string to make sure that you are able to connect to that database first. It seems that the path of your Access database is not correct. You can refer to the following link to get some information about Connection strings for Access. http://www.connectionstrings.com/access

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, August 19, 2010 7:43 AM
    Moderator
  • To : VMazur, I did as you suggested and the textboxes still do not fill on the load event from elements of the database........ I going to insert the entire code block for your kind attention, as I can't see whats wrong here. It is a simple form with three buttons called Find, Next and Previous which scroll through various entries from the MEGACITIES database and display them in the textboxes under city, country, Population 2005, Population 2015.

    Thanks

    JwasWell

    Imports System.Data
    Imports System.Xml
    Imports System.Data.OleDb
    
    Public Class frmCities
      Dim dt As New DataTable()
      Dim rowIndex As Integer = 0
    
      Private Sub frmCities_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Get data from the database, put it into the DataTable object dt,
        'and display the initial record's data in text boxes
    
    
        Dim connStr As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\Programs JM\Programming\VBwork\VB2005SchneiderEX\AccessDatabase\AccessDatabase\MEGACITIES.MDB")
        connStr.Open()
        Dim sqlStr As String = "SELECT * FROM Cities"
        'Dim command As New OleDbCommand(sqlStr)
        'command.Connection = New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = MEGACITIES.MDB")
        'connStr.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = MEGACITIES.MDB"
        'connStr.Open()
        Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
        dataAdapter.Fill(dt)
        dataAdapter.Dispose()
        connStr.Close()
        UpdateTextBoxes()
      End Sub
    
      Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
        'Search through each row looking for the requested city
        'Update the fields if that city is found
        'Otherwise display a message box
        Dim cityName As String
        Dim cityFound As Boolean = False
        cityName = InputBox("Enter the name of the city to search for.")
        For i As Integer = 0 To (dt.Rows.Count - 1)
          If CStr(dt.Rows(i)("city")) = cityName Then
            cityFound = True
            rowIndex = i
            UpdateTextBoxes()
          End If
        Next
        If (Not cityFound) Then
          MsgBox("Cannot find the requested city", 0, "Not in Table")
        End If
      End Sub
    
      Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        'Show the next record if the current one is not the last
        If (rowIndex < dt.Rows.Count - 1) Then
          rowIndex += 1 'Increase the rowIndex by 1
          UpdateTextBoxes()
        End If
      End Sub
    
      Sub UpdateTextBoxes()
        'Display the contents of the row specified by the rowIndex variable
        txtCity.Text = CStr(dt.Rows(rowIndex)("city"))
        txtCountry.Text = CStr(dt.Rows(rowIndex)("country"))
        txtPop2005.Text = CStr(dt.Rows(rowIndex)("pop2005"))
        txtPop2015.Text = CStr(dt.Rows(rowIndex)("pop2015"))
      End Sub
    End Class
    

    asWell

    Thursday, August 19, 2010 8:59 AM
  •  

     

    To : Alex Liang, The application doesn't hang and there are no errors........ I going to insert the entire code block for your kind attention, as I can't see whats wrong here. It is a simple form with three buttons called Find, Next and Previous which scroll through various entries from the MEGACITIES database and display them in the textboxes under city, country, Population 2005, Population 2015.

    Thanks

    JwasWell

    Thursday, August 19, 2010 9:02 AM
  • If you look at the code I have included the full address and name of the database in the connection string

    Dim

     

    connStr As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\Programs JM\Programming\VBwork\VB2005SchneiderEX\AccessDatabase\AccessDatabase\MEGACITIES.MDB")

    Included full code too.....

     

    Imports System.Data
    Imports System.Xml
    Imports System.Data.OleDb
    
    Public Class frmCities
     Dim dt As New DataTable()
     Dim rowIndex As Integer = 0
    
     Private Sub frmCities_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      'Get data from the database, put it into the DataTable object dt,
      'and display the initial record's data in text boxes
    
    
      Dim connStr As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\Programs JM\Programming\VBwork\VB2005SchneiderEX\AccessDatabase\AccessDatabase\MEGACITIES.MDB")
      connStr.Open()
      Dim sqlStr As String = "SELECT * FROM Cities"
      'Dim command As New OleDbCommand(sqlStr)
      'command.Connection = New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = MEGACITIES.MDB")
      'connStr.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = MEGACITIES.MDB"
      'connStr.Open()
      Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
      dataAdapter.Fill(dt)
      dataAdapter.Dispose()
      connStr.Close()
      UpdateTextBoxes()
     End Sub
    
     Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
      'Search through each row looking for the requested city
      'Update the fields if that city is found
      'Otherwise display a message box
      Dim cityName As String
      Dim cityFound As Boolean = False
      cityName = InputBox("Enter the name of the city to search for.")
      For i As Integer = 0 To (dt.Rows.Count - 1)
       If CStr(dt.Rows(i)("city")) = cityName Then
        cityFound = True
        rowIndex = i
        UpdateTextBoxes()
       End If
      Next
      If (Not cityFound) Then
       MsgBox("Cannot find the requested city", 0, "Not in Table")
      End If
     End Sub
    
     Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
      'Show the next record if the current one is not the last
      If (rowIndex < dt.Rows.Count - 1) Then
       rowIndex += 1 'Increase the rowIndex by 1
       UpdateTextBoxes()
      End If
     End Sub
    
     Sub UpdateTextBoxes()
      'Display the contents of the row specified by the rowIndex variable
      txtCity.Text = CStr(dt.Rows(rowIndex)("city"))
      txtCountry.Text = CStr(dt.Rows(rowIndex)("country"))
      txtPop2005.Text = CStr(dt.Rows(rowIndex)("pop2005"))
      txtPop2015.Text = CStr(dt.Rows(rowIndex)("pop2015"))
     End Sub
    End Class
    
    
    Thursday, August 19, 2010 9:05 AM