VB.NET

VB.NET RSS

OleDbConnection

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: Oct 2005

Posts: 4

Reputation: madelein is an unknown quantity at this point

Solved Threads: 0

madelein

Offline

Newbie Poster

OleDbConnection

Oct 19th, 2005

I need urgent help please!!!!

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

Here is my imports:

 Help with Code Tags 
 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:

 Help with Code Tags 
 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:

 Help with Code Tags 
 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 **************

•

Join Date: Feb 2003

Posts: 793

Reputation: Paladine has a spectacular aura about

Solved Threads: 26

Paladine

Offline

Master Poster

Re: OleDbConnection

Oct 21st, 2005

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

Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code

•

Join Date: Oct 2005

Posts: 4

Reputation: madelein is an unknown quantity at this point

Solved Threads: 0

madelein

Offline

Newbie Poster

Re: OleDbConnection

Nov 23rd, 2005

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

•

Join Date: Oct 2005

Posts: 4

Reputation: madelein is an unknown quantity at this point

Solved Threads: 0

madelein

Offline

Newbie Poster

Re: OleDbConnection

Nov 23rd, 2005

Could this problem occur because of an Incorrect ODBC setup?

•

Join Date: Nov 2005

Posts: 1

Reputation: ralfsmith is an unknown quantity at this point

Solved Threads: 0

ralfsmith

Offline

Newbie Poster

Re: OleDbConnection

Nov 28th, 2005

Can you provide me more information on this matter?

johntvery@operamail.com

johntvery@hotmail.com

•

Join Date: Feb 2003

Posts: 793

Reputation: Paladine has a spectacular aura about

Solved Threads: 26

Paladine

Offline

Master Poster

Re: OleDbConnection

Dec 2nd, 2005

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

Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code

•

Join Date: Oct 2005

Posts: 4

Reputation: madelein is an unknown quantity at this point

Solved Threads: 0

madelein

Offline

Newbie Poster

Re: OleDbConnection

Dec 6th, 2005

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.

•

Join Date: Jun 2008

Posts: 23

Reputation: bruce2424 is an unknown quantity at this point

Solved Threads: 0

bruce2424

Offline

Newbie Poster

Re: OleDbConnection

Nov 8th, 2008

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

•

Join Date: May 2008

Posts: 144

Reputation: sierrainfo is an unknown quantity at this point

Solved Threads: 9

sierrainfo sierrainfo is offline

Offline

Junior Poster

Re: OleDbConnection

Nov 18th, 2008

•

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

•

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

•

Join Date: Feb 2009

Posts: 1

Reputation: Abhay Joshi is an unknown quantity at this point

Solved Threads: 0

Abhay Joshi Abhay Joshi is offline

Offline

Newbie Poster

Re: OleDbConnection

#10

Feb 20th, 2009

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