asked on

Access database connection error

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

Arthur_Wood

what error are you getting?

AW

running32

ASKER

Datasource name not found or no default driver specified. Error IM002

Sancler

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

Roger

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arthur_Wood

check out:

http://www.connectionstrings.com/index.asp

AW

running32

ASKER

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

riyazthad

Try this then

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

Howard Cantrell

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

Arthur_Wood

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

Sancler

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

running32

ASKER

thank you all for your help.

Arthur_Wood

glad to be of assistance.

AW