Estabhlishing a Connection to my Database

[Denden]

Imports System.Data.OleDb
Public Class Form1
    Dim conn As OleDbConnection
    Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Denden\Desktop\DB.mdb"  'i remove my db on desktop
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        conn = New OleDbConnection(constring)
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
                MsgBox("connected")
            Else
                MsgBox("failed")
            End If
        Catch ex As Exception
            ex.Message.ToString()
        End Try
 
        conn.Close()
 
    End Sub
End Class
 

i intentionally remove the database on my desktop just to try if my else code will execute but to my surprise the else statement didnt prompt.. Why that happens?

[Rabbit]

It's because you're checking if the state is not open. It's never going to be open before the check so it will never run the else.

[Denden]

i opened the connection before message will appear.

[Rabbit]

Yes, you open it before the message appears. The problem is you open it after you check if it's open. Therefore, it is never open before you check.

[Denden]

@Rabbit
so is this the correct way?

If conn.State = ConnectionState.Closed Then
            conn.Open()
            MsgBox("Open")
        Else
            MsgBox("Close")
 
        End If
 
 

[Rabbit]

No, what I'm saying is you're testing a condition that will only ever go down one path. The if else is pointless. Basically, what you are doing is similar to this:

x = 0
 
If x = 0 Then
   x = 1
   MsgBox(x)
Else
   MsgBox("else part of code")
End If

The code will never ever ever run the Else portion of the code because x will never not be 0. That's what your code is doing. Your connection will never be open because the only time you try to open the connection is after you do the check.

[Denden]

got it sir. i must open the connection before using the if statement :) it often see codes goes that way. Thats for enlightening my mind. Just a newbie.

Dim x As Integer
        x = 1
        If x = 0 Then
            MsgBox(x)
        Else
            MsgBox("else part of code")
        End If
 

[Frinavale]

Check out the documentation for the OleDBConnection class. It contains a lot of useful information and code examples.

For example, the suggest utilizing the using statement to help manage resources.

Like this:

 Using connection As New OleDbConnection(connectionString)
        ' The insertSQL string contains a SQL statement that 
        ' inserts a new row in the source table. 
        Dim command As New OleDbCommand(insertSQL)
 
        ' Set the Connection to the new OleDbConnection.
        command.Connection = connection
 
        ' Open the connection and execute the insert command. 
        Try
            connection.Open()
            command.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try 
        ' The connection is automatically closed when the 
        ' code exits the Using block. 
    End Using 

Notice how they have a Try...Catch block around opening the connection and executing SQL commands with it?

That's because an exception will be thrown if a connection to the database cannot be established or if something else went wrong executing the SQL commands.

The catch section is the section you are interested in...

-Frinny

[Denden]

@frinavale

i am reading the articles @home&learn website and i found it different from yours, here the way they did on how to update data on database

Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("Denden").Rows(inc).Item(1) = TextBox1.Text
        ds.Tables("Denden").Rows(inc).Item(2) = TextBox2.Text
        da.Update(ds, "Denden")
        MsgBox("Data updated")
 
 

so how it is different from yours? what would be the best option between those.

i also checked the documentation
here what it is looks like

Public Sub OpenConnection(ByVal connectionString As String)
 
        Using connection As New OleDbConnection(connectionString)
            Try
                connection.Open()
                Console.WriteLine("DataSource: {0} Database: {1}", _
                    connection.DataSource, connection.Database)
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try 
            ' The connection is automatically closed when the 
            ' code exits the Using block. 
        End Using 
    End Sub
' Define other methods and classes here

i was confused seeing that, would you mind explain this line of code

 Console.WriteLine("DataSource: {0} Database: {1}", _
                    connection.DataSource, connection.Database)

and lastly can i substitute this line

Public Sub OpenConnection(ByVal connectionString As String)

to something like this

Public Sub OpenConnection()
dim connectionString as string
 

many thanks sir.

[Frinavale]

Hi Denden,

...how it is different from yours? what would be the best option between those.

It looks like this code is using a DataSet and a DataAdapter to update the database. You don't need to use a DataSet nor do you need to use a DataAdapter to execute an insert/update/delete command; however, using these controls do have their advantages especially if you are planning on updating a whole bunch of records at the same time.

Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("Denden").Rows(inc).Item(1) = TextBox1.Text
        ds.Tables("Denden").Rows(inc).Item(2) = TextBox2.Text
        da.Update(ds, "Denden")
        MsgBox("Data updated")
 
 

The code that I posted simply creates a connection, opens the connection and executes an insert command.

 Using connection As New OleDbConnection(connectionString)
        ' The insertSQL string contains a SQL statement that 
        ' inserts a new row in the source table. 
        Dim command As New OleDbCommand(insertSQL)
 
        ' Set the Connection to the new OleDbConnection.
        command.Connection = connection
 
        ' Open the connection and execute the insert command. 
        Try
            connection.Open()
            command.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try 
        ' The connection is automatically closed when the 
        ' code exits the Using block. 
    End Using 
 

So, to answer your question, I would recommend using the OleDbDataAdapter solution if you are planning on inserting/updating/deleting a bunch of records at the same time.

... would you mind explain this line of code

 Console.WriteLine("DataSource: {0} Database: {1}", _
                    connection.DataSource, connection.Database)

This code prints the DataSource property and the Database property of the OleDBConnection object onto the screen.

and lastly can i substitute this line

Public Sub OpenConnection(ByVal connectionString As String)

to something like this

Public Sub OpenConnection()
dim connectionString as string
 

Of course you can!
You just need some way to get the connection string...It could even be stored in a .config file.



-Frinny

[Denden]

wow. thumbs up for the explanation. by the way, im using datareader in reading data, is that the best to use.