?
Solved

Access database connection error

Posted on 2007-03-27
12
Medium Priority
?
248 Views
Last Modified: 2010-04-23
I am getting an error on the code below when I try and open the database connection.  Can someone please tell me what I am doing wrong.

Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Splash\Splash.mdb;"
        Dim cn As New OdbcConnection(sConn)

        Try
            cn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Dim sSQL As String = "select * from tblmember as m inner join tblmemberhh as mhh on m.lngmemberid = mhh.lngmemberid where m.lngmemberid = " & Splash.Global.memberid & " order by lngid desc"
        Dim da As New OdbcDataAdapter(sSQL, cn)
        Dim ds As New DataSet

        Try
            da.Fill(ds)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
0
Comment
Question by:running32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18804528
what error are you getting?

AW
0
 

Author Comment

by:running32
ID: 18804605
Datasource name not found or no default driver specified.  Error IM002
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18804716
It looks to me like you should be using OleDb connections and dataadapters rather than ODBC.  That's certainly an OleDb ConnectionString.

Roger
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 2000 total points
ID: 18804955
Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Splash\Splash.mdb;"
        Dim cn As New OleDbConnection(sConn)  '<<<< note the change here

        Try
            cn.Open()


or

Dim sConn As String = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\Splash\Splash.mdb;Uid=Admin;Pwd=;"  '<<< note the change here
        Dim cn As New OdbcConnection(sConn)

        Try
            cn.Open()


one way or ther other, but either way, you can't mix the code.

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18804961
0
 

Author Comment

by:running32
ID: 18805007
That seems to work but when I got to assign the value to a textbox I get the error.  Oject instance not set to an instance to of the object.

thanks for your help    

Try
            If IsDBNull(ds.Tables("tblmember").Rows(0).Item("strlname")) = False Then
                TextBox3.Text = ds.Tables("m").Rows(0).Item("strlname")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
0
 
LVL 6

Expert Comment

by:riyazthad
ID: 18805513
Try this then

TextBox3.Text = ds.Tables("m").Rows(0).Item("strlname").ToString()
0
 
LVL 27

Expert Comment

by:planocz
ID: 18805593
Here is an easy sample on using ADO and Access.

'FORM 1

Imports System.IO
Public Class frmADOToolTip
    Inherits System.Windows.Forms.Form
    Dim sField As String = String.Empty

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call
        InitializeData()
        PopulateListFromDS(DS, "ProductName")
        PrintRows(DS)
    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents tipView As System.Windows.Forms.ToolTip
    Friend WithEvents lstBoxResults As System.Windows.Forms.ListBox
    Friend WithEvents RadioButton1 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton2 As System.Windows.Forms.RadioButton
    Friend WithEvents RadioButton3 As System.Windows.Forms.RadioButton
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Me.lstBoxResults = New System.Windows.Forms.ListBox
        Me.tipView = New System.Windows.Forms.ToolTip(Me.components)
        Me.RadioButton1 = New System.Windows.Forms.RadioButton
        Me.RadioButton2 = New System.Windows.Forms.RadioButton
        Me.RadioButton3 = New System.Windows.Forms.RadioButton
        Me.SuspendLayout()
        '
        'lstBoxResults
        '
        Me.lstBoxResults.Location = New System.Drawing.Point(8, 8)
        Me.lstBoxResults.Name = "lstBoxResults"
        Me.lstBoxResults.Size = New System.Drawing.Size(176, 82)
        Me.lstBoxResults.TabIndex = 0
        Me.tipView.SetToolTip(Me.lstBoxResults, "Click to test the tool tip")
        '
        'RadioButton1
        '
        Me.RadioButton1.Location = New System.Drawing.Point(204, 4)
        Me.RadioButton1.Name = "RadioButton1"
        Me.RadioButton1.TabIndex = 1
        Me.RadioButton1.Text = "By Unit Price"
        '
        'RadioButton2
        '
        Me.RadioButton2.Location = New System.Drawing.Point(204, 32)
        Me.RadioButton2.Name = "RadioButton2"
        Me.RadioButton2.Size = New System.Drawing.Size(120, 24)
        Me.RadioButton2.TabIndex = 2
        Me.RadioButton2.Text = "By Units In Stock"
        '
        'RadioButton3
        '
        Me.RadioButton3.Location = New System.Drawing.Point(204, 60)
        Me.RadioButton3.Name = "RadioButton3"
        Me.RadioButton3.Size = New System.Drawing.Size(120, 24)
        Me.RadioButton3.TabIndex = 3
        Me.RadioButton3.Text = "By Product Name"
        '
        'frmADOToolTip
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(332, 117)
        Me.Controls.Add(Me.RadioButton3)
        Me.Controls.Add(Me.RadioButton2)
        Me.Controls.Add(Me.RadioButton1)
        Me.Controls.Add(Me.lstBoxResults)
        Me.Name = "frmADOToolTip"
        Me.Text = "ADO ToolTip Test"
        Me.ResumeLayout(False)

    End Sub

#End Region
    '/ <summary>
    '/ The main entry point for the application.
    '/ </summary>
    <STAThread()> Public Shared Sub Main()
        Application.Run(New frmADOToolTip)
    End Sub
    Private Sub InitializeData()

        Cursor.Current = Cursors.WaitCursor
        sDBName = "D:\Visual Studio Projects\TestArea\Northwind.mdb"    '<---- A standard access database from Microsoft
        sTableName = "Products"

        OpenDBADOConnection()
 
        sSql = ""
        sSql = "SELECT * "
        sSql += "FROM " & sTableName & " "
        sSql += "ORDER BY ProductName"
        Try
            'TblAdapter = New SqlDataAdapter(sSql, Connect)            'FOR SQL Server
            ADOAdapter = New OleDb.OleDbDataAdapter(sSql, Con)         'FOR ADO Access database
            ADOAdapter.Fill(DS)

        Catch Exp As Data.OleDb.OleDbException
            MsgBox("FillDataSet Procedure Error", MsgBoxStyle.Critical, "Load Report Error")
        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try
        Cursor.Current = Cursors.Default
    End Sub
    Private Sub lstboxResults_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstBoxResults.Click
        Dim sCost As String
        Dim sUnits As String
        Dim str As String
        Select Case True
            Case RadioButton1.Checked      'By Unit Price
                sCost = DS.Tables(0).Rows(sender.selectedindex)("ProductName").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Product Name: " & sCost & vbCr & "Unit In Stock: " & sUnits
            Case RadioButton2.Checked      'By Units In Stock
                sCost = DS.Tables(0).Rows(sender.selectedindex)("ProductName").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                str = "Product Name: " & sCost & vbCr & "Cost: " & Format(CDbl(sUnits), "$###.00")
            Case RadioButton3.Checked      'By Product Name
                sCost = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Cost: " & Format(CDbl(sCost), "$###.00") & vbCr & "Unit In Stock: " & sUnits
            Case Else
                sCost = DS.Tables(0).Rows(sender.selectedindex)("Unitprice").ToString()
                sUnits = DS.Tables(0).Rows(sender.selectedindex)("UnitsInStock").ToString()
                str = "Cost: " & Format(CDbl(sCost), "$###.00") & vbCr & "Unit In Stock: " & sUnits
        End Select

        tipView.SetToolTip(Me.lstBoxResults, str)
    End Sub
    Private Sub PopulateListFromDS(ByVal DS As DataSet, ByVal sFieldName As String)
        ' A table in a dataset is used to populate a list box with
        ' product name from the products table.
        ' With the dataset the name of the table and field must be known
        ' at design time. If they are misspelled or mistyped
        ' an error will be generated only at runtime.

        Dim s As String
        Dim i As Integer

        lstBoxResults.Items.Clear()

        For i = 0 To DS.Tables(0).Rows.Count - 1
            ' Check to see if row is flagged as deleted.
            If Not DS.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ' Get the product name for each record.
                s = DS.Tables(0).Rows(i)(sFieldName).ToString()
                ' Add product name to the list box
                lstBoxResults.Items.Add(s)
            End If
        Next i
    End Sub
   
    Private Sub PrintRows(ByVal DS As DataSet)
        Dim i As Integer
        Dim ProductName As String
        Dim SupplierID As String
        Dim CategoryID As String
        Dim UnitPrice As String
        Dim UnitsInStock As String
        Dim UnitsOnOrder As String
        Dim ProductID As String

        Dim objWriter As StreamWriter = New StreamWriter("C:/Products.txt", False)
        objWriter.Write("Products as of " & Today & vbNewLine & vbNewLine)
        For i = 0 To DS.Tables(0).Rows.Count - 1
            ' Check to see if row is flagged as deleted.
            If Not DS.Tables(0).Rows(i).RowState = DataRowState.Deleted Then
                ' Get the  name for each record.
                ProductName = DS.Tables(0).Rows(i)("ProductName").ToString()
                SupplierID = DS.Tables(0).Rows(i)("SupplierID").ToString()
                CategoryID = DS.Tables(0).Rows(i)("CategoryID").ToString()
                UnitPrice = DS.Tables(0).Rows(i)("UnitPrice").ToString()
                UnitsInStock = DS.Tables(0).Rows(i)("UnitsInStock").ToString()
                UnitsOnOrder = DS.Tables(0).Rows(i)("UnitsOnOrder").ToString()
                ProductID = DS.Tables(0).Rows(i)("ProductID").ToString()
                objWriter.Write("ProductName: " & ProductName & ", Supplier ID: " & SupplierID & vbNewLine)
                objWriter.Write("Category ID: " & CategoryID & vbNewLine)
                objWriter.Write("Unit Price: " & UnitPrice & vbNewLine)
                objWriter.Write("Units In Stock: " & UnitsInStock & ", Units On Order: " & UnitsOnOrder & ", Product ID: " & ProductID & vbNewLine)
            End If
        Next
        objWriter.Close()
        objWriter = Nothing
    End Sub
    Private Sub RadioButtonChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged, RadioButton2.CheckedChanged, RadioButton3.CheckedChanged
        Select Case sender.text
            Case "By Unit Price"
                sField = "Unitprice"
            Case "By Units In Stock"
                sField = "UnitsInStock"
            Case "By Product Name"
                sField = "ProductName"
        End Select
        PopulateListFromDS(DS, sField)
    End Sub
End Class


'MODULE

Imports System.Data
Imports System.Data.OleDb
Module ModADO
#Region " MY Global Vars "
    'Const adOpenStatic = 3
    'Const adLockOptimistic = 3
    'Const adCmdText = &H1
    '********* database Var's ************
    Public sSql As String
    Public sTableName As String
    Public iCounter As Short
    Public myDataRow As DataRow
    Public DS As New DataSet
    Public ADOAdapter As OleDb.OleDbDataAdapter  '(ADO Access)

    '**** Server Connection Vars *********
    Public sDBServer As String               '<--- Server Name
    Public sUSERid As String                 '<--- User Id
    Public sDBName As String                 '<--- Database Name
    Public sDBPass As String                 '<--- Password
    Public Con As OleDb.OleDbConnection      '<--- for Access

    '******* Standard Var's *************
    Public sAppPath As String = System.Windows.Forms.Application.StartupPath
    Public bLogin As Boolean
#End Region
#Region " MY Server Connections "

    '***Sample for a Access Database***
    'Provider=Microsoft.Jet.OLEDB.4.0;
    'User ID=Admin;
    'Data Source=D:\MyDatabase.mdb;
    'Mode=Share Deny None;
    'Extended Properties="";
    'Locale Identifier=1033;
    'Persist Security Info=False;
    'Jet OLEDB:System database=D:\SYSTEM.MDW;
    'Jet OLEDB:Registry Path="";
    'Jet OLEDB:Database Password="";
    'Jet OLEDB:Engine Type=5;         'Type=4 is for Access 95
    'Jet OLEDB:Database Locking Mode=1;
    'Jet OLEDB:Global Partial Bulk Ops=2;
    'Jet OLEDB:Global Bulk Transactions=1;
    'Jet OLEDB:New Database Password="";
    'Jet OLEDB:Create System Database=False;
    'Jet OLEDB:Encrypt Database=False;
    'Jet OLEDB:Don't Copy Locale on Compact=False;
    'Jet OLEDB:Compact Without Replica Repair=False;
    'Jet OLEDB:SFP=False

    Public Sub OpenDBADOConnection()
        Try
            Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBName & ";password=" & sDBPass & ";")
            Con.Open()
            Con.Close()
        Catch Ex As Exception
            MsgBox("Exception: " & Ex.Message & "  " & Ex.ToString, MsgBoxStyle.Critical)
            Cursor.Current = Cursors.Default
        End Try
    End Sub
#End Region
End Module
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18807051
in this code:

Try
            If IsDBNull(ds.Tables("tblmember").Rows(0).Item("strlname")) = False Then
                TextBox3.Text = ds.Tables("m").Rows(0).Item("strlname")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

which line is rainsing the error?

AW
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18807327
If the .Fill code remains as it was in the earlier posts (after deciding whether to go down the OleDb or ODBC route) this

          ds.Tables("tblmember")

is going to be Null.   And so is ds.Tables("m").

           da.Fill(ds)

will give the first table the default name "Table": it won't pull a name out of the Select statement to give it.  It probably needs

            If IsDBNull(ds.Tables(0).Rows(0).Item("strlname")) = False Then

But, having said that, the question seems to have drifted rather from "Access database connection error"

Roger
0
 

Author Comment

by:running32
ID: 18807825
thank you all for your help.  
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18810773
glad to be of assistance.

AW
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question