By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,500 Members | 2,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,500 IT Pros & Developers. It's quick & easy.

Estabhlishing a Connection to my Database

P: 33
Expand|Select|Wrap|Line Numbers
  1. Imports System.Data.OleDb
  2. Public Class Form1
  3.     Dim conn As OleDbConnection
  4.     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
  5.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  6.  
  7.         conn = New OleDbConnection(constring)
  8.         Try
  9.             If conn.State <> ConnectionState.Open Then
  10.                 conn.Open()
  11.                 MsgBox("connected")
  12.             Else
  13.                 MsgBox("failed")
  14.             End If
  15.         Catch ex As Exception
  16.             ex.Message.ToString()
  17.         End Try
  18.  
  19.         conn.Close()
  20.  
  21.     End Sub
  22. End Class
  23.  
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?
Sep 21 '14 #1
Share this Question
Share on Google+
10 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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.
Sep 21 '14 #2

P: 33
i opened the connection before message will appear.
Sep 22 '14 #3

Rabbit
Expert Mod 10K+
P: 12,366
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.
Sep 22 '14 #4

P: 33
@Rabbit
so is this the correct way?
Expand|Select|Wrap|Line Numbers
  1. If conn.State = ConnectionState.Closed Then
  2.             conn.Open()
  3.             MsgBox("Open")
  4.         Else
  5.             MsgBox("Close")
  6.  
  7.         End If
  8.  
  9.  
Oct 12 '14 #5

Rabbit
Expert Mod 10K+
P: 12,366
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:
Expand|Select|Wrap|Line Numbers
  1. x = 0
  2.  
  3. If x = 0 Then
  4.    x = 1
  5.    MsgBox(x)
  6. Else
  7.    MsgBox("else part of code")
  8. 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.
Oct 12 '14 #6

P: 33
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.

Expand|Select|Wrap|Line Numbers
  1. Dim x As Integer
  2.         x = 1
  3.         If x = 0 Then
  4.             MsgBox(x)
  5.         Else
  6.             MsgBox("else part of code")
  7.         End If
  8.  
Oct 12 '14 #7

Frinavale
Expert Mod 5K+
P: 9,731
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:
Expand|Select|Wrap|Line Numbers
  1.  Using connection As New OleDbConnection(connectionString)
  2.         ' The insertSQL string contains a SQL statement that 
  3.         ' inserts a new row in the source table. 
  4.         Dim command As New OleDbCommand(insertSQL)
  5.  
  6.         ' Set the Connection to the new OleDbConnection.
  7.         command.Connection = connection
  8.  
  9.         ' Open the connection and execute the insert command. 
  10.         Try
  11.             connection.Open()
  12.             command.ExecuteNonQuery()
  13.         Catch ex As Exception
  14.             Console.WriteLine(ex.Message)
  15.         End Try 
  16.         ' The connection is automatically closed when the 
  17.         ' code exits the Using block. 
  18.     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
Oct 16 '14 #8

P: 33
@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

Expand|Select|Wrap|Line Numbers
  1. Dim cb As New OleDb.OleDbCommandBuilder(da)
  2.         ds.Tables("Denden").Rows(inc).Item(1) = TextBox1.Text
  3.         ds.Tables("Denden").Rows(inc).Item(2) = TextBox2.Text
  4.         da.Update(ds, "Denden")
  5.         MsgBox("Data updated")
  6.  
  7.  
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
Expand|Select|Wrap|Line Numbers
  1. Public Sub OpenConnection(ByVal connectionString As String)
  2.  
  3.         Using connection As New OleDbConnection(connectionString)
  4.             Try
  5.                 connection.Open()
  6.                 Console.WriteLine("DataSource: {0} Database: {1}", _
  7.                     connection.DataSource, connection.Database)
  8.             Catch ex As Exception
  9.                 Console.WriteLine(ex.Message)
  10.             End Try 
  11.             ' The connection is automatically closed when the 
  12.             ' code exits the Using block. 
  13.         End Using 
  14.     End Sub
  15. ' Define other methods and classes here

i was confused seeing that, would you mind explain this line of code
Expand|Select|Wrap|Line Numbers
  1.  Console.WriteLine("DataSource: {0} Database: {1}", _
  2.                     connection.DataSource, connection.Database)

and lastly can i substitute this line
Expand|Select|Wrap|Line Numbers
  1. Public Sub OpenConnection(ByVal connectionString As String)
to something like this

Expand|Select|Wrap|Line Numbers
  1. Public Sub OpenConnection()
  2. dim connectionString as string
  3.  
many thanks sir.
Oct 18 '14 #9

Frinavale
Expert Mod 5K+
P: 9,731
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.
Expand|Select|Wrap|Line Numbers
  1. Dim cb As New OleDb.OleDbCommandBuilder(da)
  2.         ds.Tables("Denden").Rows(inc).Item(1) = TextBox1.Text
  3.         ds.Tables("Denden").Rows(inc).Item(2) = TextBox2.Text
  4.         da.Update(ds, "Denden")
  5.         MsgBox("Data updated")
  6.  
  7.  
The code that I posted simply creates a connection, opens the connection and executes an insert command.
Expand|Select|Wrap|Line Numbers
  1.  Using connection As New OleDbConnection(connectionString)
  2.         ' The insertSQL string contains a SQL statement that 
  3.         ' inserts a new row in the source table. 
  4.         Dim command As New OleDbCommand(insertSQL)
  5.  
  6.         ' Set the Connection to the new OleDbConnection.
  7.         command.Connection = connection
  8.  
  9.         ' Open the connection and execute the insert command. 
  10.         Try
  11.             connection.Open()
  12.             command.ExecuteNonQuery()
  13.         Catch ex As Exception
  14.             Console.WriteLine(ex.Message)
  15.         End Try 
  16.         ' The connection is automatically closed when the 
  17.         ' code exits the Using block. 
  18.     End Using 
  19.  
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
Expand|Select|Wrap|Line Numbers
  1.  Console.WriteLine("DataSource: {0} Database: {1}", _
  2.                     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
Expand|Select|Wrap|Line Numbers
  1. Public Sub OpenConnection(ByVal connectionString As String)
to something like this

Expand|Select|Wrap|Line Numbers
  1. Public Sub OpenConnection()
  2. dim connectionString as string
  3.  
Of course you can!
You just need some way to get the connection string...It could even be stored in a .config file.



-Frinny
Oct 20 '14 #10

P: 33
wow. thumbs up for the explanation. by the way, im using datareader in reading data, is that the best to use.
Oct 21 '14 #11

Post your reply

Sign in to post your reply or Sign up for a free account.