Ad:

VB.NET Discussion Thread
Unsolved
Views: 28862

Oct 19th, 2005

OleDbConnection

Expand Post »

I need urgent help please!!!!

This is my problem:
I want to connect to a Access database with VB.Net.

Here is my imports:

VB.NET Syntax (Toggle Plain Text)
Imports System.IO
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Collections
Imports System.ComponentModel
Imports System.IO
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Collections
Imports System.ComponentModel

Here is my connection string:

VB.NET Syntax (Toggle Plain Text)
Public pDB As New OleDbConnection("Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source='F:\relayMaster.mdb';Jet OLEDB:Engine Type=5;Provider='Microsoft.Jet.OLEDB.4.0';Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1")
 
Public pDataAdapt As New OleDbDataAdapter
Public pDB As New OleDbConnection("Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source='F:\relayMaster.mdb';Jet OLEDB:Engine Type=5;Provider='Microsoft.Jet.OLEDB.4.0';Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1")

Public pDataAdapt As New OleDbDataAdapter

Here is my code where i want to access the database:

VB.NET Syntax (Toggle Plain Text)
    Private Sub GetTodayScd(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem5.Click
 
        pDSet1.Clear()
        pDSet2.Clear()
 
        Dim switchOnceSQL As String
 switchOnceSQL = "SELECT switchOnce.day, switchOnce.month, switchOnce.year, switchOnce.hour, switchOnce.min, switchOnce.port, switchOnce.state "
        switchOnceSQL &= "FROM(switchOnce) "
        switchOnceSQL &= "WHERE(((switchOnce.day) = "
        switchOnceSQL &= Now().ToString("dd")
        switchOnceSQL &= ") And ((switchOnce.month) = "
        switchOnceSQL &= Now().ToString("MM")
        switchOnceSQL &= ") And ((switchOnce.year) = "
        switchOnceSQL &= Now().ToString("yyyy")
switchOnceSQL &= ")) ORDER BY switchOnce.day, switchOnce.month, switchOnce.year, switchOnce.hour, switchOnce.min, switchOnce.port;"
 
        Try
 
            pDataAdapt.SelectCommand = New OleDbCommand(switchOnceSQL, pDB)
            pDataAdapt.Fill(pDSet1, "OnceOff")
 
        Catch ex As OleDbException
 
            MessageBox.Show("Connection to Database Failed", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
 
        End Try
 
        Dim scheduleSQL As String
 scheduleSQL = "SELECT schedule.day, 0 AS [month], 0 AS [year], schedule.hour, schedule.min, schedule.port, schedule.state "
        scheduleSQL &= "FROM(schedule) "
        scheduleSQL &= "WHERE (((schedule.day)="
        scheduleSQL &= Now().DayOfWeek()
        scheduleSQL &= ")) ORDER BY schedule.day, schedule.hour, schedule.min, schedule.port;"
 
        Try
 
            pDataAdapt.SelectCommand = New OleDbCommand(scheduleSQL, pDB)
            pDataAdapt.Fill(pDSet2, "Schd")
 
        Catch ex As OleDbException
 
            MessageBox.Show("Connection to Database Failed", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
 
        End Try
 
        schDGrid1.SetDataBinding(pDSet1, "OnceOff")
        schDGrid2.SetDataBinding(pDSet2, "Schd")
 
 
    End Sub
    Private Sub GetTodayScd(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem5.Click

        pDSet1.Clear()
        pDSet2.Clear()

        Dim switchOnceSQL As String
 switchOnceSQL = "SELECT switchOnce.day, switchOnce.month, switchOnce.year, switchOnce.hour, switchOnce.min, switchOnce.port, switchOnce.state "
        switchOnceSQL &= "FROM(switchOnce) "
        switchOnceSQL &= "WHERE(((switchOnce.day) = "
        switchOnceSQL &= Now().ToString("dd")
        switchOnceSQL &= ") And ((switchOnce.month) = "
        switchOnceSQL &= Now().ToString("MM")
        switchOnceSQL &= ") And ((switchOnce.year) = "
        switchOnceSQL &= Now().ToString("yyyy")
switchOnceSQL &= ")) ORDER BY switchOnce.day, switchOnce.month, switchOnce.year, switchOnce.hour, switchOnce.min, switchOnce.port;"

        Try

            pDataAdapt.SelectCommand = New OleDbCommand(switchOnceSQL, pDB)
            pDataAdapt.Fill(pDSet1, "OnceOff")

        Catch ex As OleDbException

            MessageBox.Show("Connection to Database Failed", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return

        End Try

        Dim scheduleSQL As String
 scheduleSQL = "SELECT schedule.day, 0 AS [month], 0 AS [year], schedule.hour, schedule.min, schedule.port, schedule.state "
        scheduleSQL &= "FROM(schedule) "
        scheduleSQL &= "WHERE (((schedule.day)="
        scheduleSQL &= Now().DayOfWeek()
        scheduleSQL &= ")) ORDER BY schedule.day, schedule.hour, schedule.min, schedule.port;"

        Try

            pDataAdapt.SelectCommand = New OleDbCommand(scheduleSQL, pDB)
            pDataAdapt.Fill(pDSet2, "Schd")

        Catch ex As OleDbException

            MessageBox.Show("Connection to Database Failed", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return

        End Try

        schDGrid1.SetDataBinding(pDSet1, "OnceOff")
        schDGrid2.SetDataBinding(pDSet2, "Schd")


    End Sub

and last, here is my error:
Connection to Database Failed

I am stil new to programming and need all the help I can get. Tx a mill :cry:

Last edited by Paladine; Oct 21st, 2005 at 1:25 am. Reason: **** PLEASE USE CODE BLOCKS **************

Similar Threads

VB: Connect to Access database via ODBC datasource name in Visual Basic 4 / 5 / 6
Populating & Retrieving Data in a listbox : ASP.NET (w/ VB.NET) in ASP.NET
Sharing object instances between applications in VB.NET
Registration and Login scripts using VB and Oledbconnection to Access Database in ASP.NET
Simple ASP.Net Login Page (Using VB.Net) in ASP.NET

madelein

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Oct 2005

Permalink

Oct 21st, 2005

Re: OleDbConnection

Just off the top of my head your connection string to the DB is far to long , and complicated for what you are doing.

Replace it with this :

"Provider = Microsoft.Jet.OLEDB.4.0;Data Source=F:\relayMaster.mdb;User Id=Admin;Password=;"

And second, you dont put single quotes inside the double quotes, and your may work.

Hope this helps

Paladine

Team Colleague

Reputation Points: 211

Solved Threads: 27

Master Poster

Offline

793 posts
since Feb 2003

Permalink

Nov 23rd, 2005

Re: OleDbConnection

Sorry but it still does not work. Still get the same error

madelein

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Oct 2005

Permalink

Nov 23rd, 2005

Re: OleDbConnection

Could this problem occur because of an Incorrect ODBC setup?

madelein

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Oct 2005

Permalink

Nov 28th, 2005

Re: OleDbConnection

Can you provide me more information on this matter?

johntvery@operamail.com

johntvery@hotmail.com

ralfsmith

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Nov 2005

Permalink

Dec 2nd, 2005

Re: OleDbConnection

If the database (the Access DB) set with a password? Meaning when you double click on the file, does it request a password?

Paladine

Team Colleague

Reputation Points: 211

Solved Threads: 27

Master Poster

Offline

793 posts
since Feb 2003

Permalink

Dec 6th, 2005

Re: OleDbConnection

No, there is no password. The thing is I now get a problem with this statement:

OleDbDataAdapter1.Fill(DsSwitchOnce, "switchOnce")

DsSwitchOnce is the dataset and switchOnce is the tablename

Here is the error message:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Fill: SelectCommand.Connection property has not been initialized.

madelein

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Oct 2005

Permalink

Nov 8th, 2008

Re: OleDbConnection

connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourdatabasename.mdb;"
connection = New OleDbConnection(connetionString)

full src code

http://vb.net-informations.com/ado.n...connection.htm

bruce

bruce2424

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

23 posts
since Jun 2008

Permalink

Nov 18th, 2008

Re: OleDbConnection

Quote ...

1st Method

Imports System.Data

Module Test

Sub Main()
Dim sConnectionString, sSQL As String

'SQL Connection String
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\yourdatabase.mdb"

sSQL = "SELECT Title FROM yourTable"

Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
Dim cmd As New System.Data.OleDb.OleDbCommand(sSQL, conn)
Dim dr As System.Data.OleDb.OleDbDataReader
conn.Open()

dr = cmd.ExecuteReader()

Do While dr.Read()
System.Console.WriteLine(dr.Item("Title"))
Loop

dr.Close()
conn.Close()
End Sub
End Module

2nd Method

Quote ...

Dim pagedData As New PagedDataSource

Sub Page_Load(ByVal obj As Object, ByVal e As EventArgs)
doPaging()
End Sub

Function getTheData() As DataTable
Dim DS As New DataSet()
Dim strConnect As New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source=App_Data/ArtDatabase.mdb")
Dim objOleDBAdapter As New OleDbDataAdapter("SELECT ArtID, FileLocation, Title, UserName, ArtDate FROM Art ORDER BY Art.ArtDate DESC", strConnect)
objOleDBAdapter.Fill(DS, "Art")

Return DS.Tables("Art").Copy
End Function

Sub doPaging()
pagedData.DataSource = getTheData().DefaultView
pagedData.AllowPaging = True
pagedData.PageSize = 2

Try
pagedData.CurrentPageIndex = Int32.Parse(Request.QueryString("Page")).ToString()
Catch ex As Exception
pagedData.CurrentPageIndex = 0
End Try

btnPrev.Visible = (Not pagedData.IsFirstPage)
btnNext.Visible = (Not pagedData.IsLastPage)

pageNumber.Text = (pagedData.CurrentPageIndex + 1) & " of " & pagedData.PageCount

ArtRepeater.DataSource = pagedData
ArtRepeater.DataBind()
End Sub

sierrainfo

Reputation Points: 10

Solved Threads: 9

Junior Poster

Offline

144 posts
since May 2008

Permalink

Feb 20th, 2009

Re: OleDbConnection

Hi,

Try by not opening the connection in declaration.

===================================
dim conn as OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = sConnectionString
conn.Open()
===================================

Some time opening connection in declaration works but sometimes you need to open it seperately. I suppose this is a bug in OldDb but not sure why and when it fails.

Regards

Abhay

Abhay Joshi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Feb 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: add, save records using binding navigator

Next Thread in VB.NET Forum Timeline: data extraction

DaniWeb Message

Cancel Changes

User Name
Password