OleDbConnection

[madelein]

I need urgent help please!!!!

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

Here is my imports:

Imports System.IO
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Collections
Imports System.ComponentModel

Here is my connection string:

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:

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:

[Paladine]

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

:cool:

[madelein]

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

[madelein]

Could this problem occur because of an Incorrect ODBC setup?

[ralfsmith]

Can you provide me more information on this matter?

[e-mail]

[e-mail]

[Paladine]

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

[madelein]

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.

[bruce2424]

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

full src code

http://vb.net-informations.com/ado.net-dataproviders/ado.net-oledbconnection.htm

bruce

[sierrainfo]

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

[Abhay Joshi]

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