Question Help with Database class

onestarpro · #1 (**permalink**) 11-22-2008, 11:38 PM

ok I am trying to create a database class to access an access database (2000 Sorry).

Now i get an error in my class stating Keywork provider is not a valid key word can someone help me here is my code.

Code:

Imports System.Data.SqlClient

Public Class clsDB
    Private database_ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydb.mdb;"
    Private database_Connection As New SqlConnection()
    Private database_Sql_Statement As String = ""
    Public Reader As SqlDataReader

    Public Sub OpenDatabaseConnection()
        Try
            database_Connection.ConnectionString = database_ConnectionString
            database_Connection.Open()
        Catch ex As Exception
            Addlvitems("DataBase Connection Error: " + ex.Message.ToString(), "System")
        End Try
    End Sub
    Public Sub CloseDatabaseConnection()
        Try
            Reader.Close()
            If database_Connection.State <> Data.ConnectionState.Closed Then
                Reader.Close()
                database_Connection.Close()
            End If
        Catch ex As Exception
            Addlvitems("DataBase Close Error: " + ex.Message.ToString(), "System")
        End Try
    End Sub

    Public Function RunSqlStatement(ByVal SQL_Statement As String, ByVal where As String) As String
        RunSqlStatement = ""
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand(SQL_Statement, database_Connection)
            Reader = cmd.ExecuteReader
            If Reader.HasRows Then
                Do While Reader.Read()
                    RunSqlStatement = Reader.Item(where)
                Loop
            Else
                RunSqlStatement = " "
            End If

            Return RunSqlStatement
        Catch ex As Exception
            Addlvitems("Sql Error: " + ex.Message.ToString(), "System")
        End Try
    End Function

End Class

ps if someone has a easy db class i can use I would love to try it.

Troy · #2 (**permalink**) 11-23-2008, 1:05 PM

Hi, ok for starters The first thing you need to do is change a few lines.

To open an access DB with code you you will need to specify it as OLEDB

This line:

Quote:

Imports System.Data.SqlClient

Should be:

Code:

Imports System.Data.OleDB

this line:

Quote:

Private database_Connection As New SqlConnection()

should be this:

Code:

Private database_Connection As New OleDbConnection()

Next you will need to change this line:

Quote:

Dim cmd As New System.Data.SqlClient.SqlCommand(SQL_Statement, database_Connection)

to this:

Code:

Dim cmd As New OleDbCommand(SQL_Statement, database_Connection)

That should get you working

.

onestarpro · #3 (**permalink**) 11-23-2008, 3:42 PM

thanks for the help and the code for everyone else

Dim vbnoob() as string{"youranoob", "Imanoob", "wearevbnoobs"}

Code:

Imports System.Data


Public Class clsDB
    Private database_ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\smartftp.mdb;"
    Private database_Connection As New System.Data.OleDb.OleDbConnection()
    Private database_Reader As System.Data.OleDb.OleDbDataReader


    Public Sub OpenDatabaseConnection()
        Try
            database_Connection.ConnectionString = database_ConnectionString
            database_Connection.Open()
        Catch ex As Exception
            Addlvitems("DataBase Connection Error: " + ex.Message.ToString(), "System")
        End Try
    End Sub
    Public Sub CloseDatabaseConnection()
        Try
            database_Reader.Close()
            If database_Connection.State <> Data.ConnectionState.Closed Then
                database_Reader.Close()
                database_Connection.Close()
            End If
        Catch ex As Exception
            Addlvitems("DataBase Close Error: " + ex.Message.ToString(), "System")
        End Try
    End Sub

    Public Function RunSqlStatement(ByVal SQL_Statement As String, ByVal where As String) As String
        RunSqlStatement = ""

        Try
            Dim cmd As New System.Data.OleDb.OleDbCommand(SQL_Statement, database_Connection)
            database_Reader = cmd.ExecuteReader
            If database_Reader.HasRows Then
                Do While database_Reader.Read()
                    RunSqlStatement = database_Reader.Item(where)
                Loop
            Else
                RunSqlStatement = " "
            End If

            Return RunSqlStatement
        Catch ex As Exception
            Addlvitems("Sql Error: " + ex.Message.ToString(), "System")
        End Try
    End Function

End Class

cjard · #4 (**permalink**) 11-24-2008, 8:16 AM

If you want to make a proper, modern class (set of classes) for accessing a database, read the DW2 link in my signature. The datasets you will generate are the database access layer

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)