Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Implement a function to connect to a DB

Posted on 2006-11-28
8
Medium Priority
?
235 Views
Last Modified: 2010-04-23
using vb.net I would like to implement a function that would connect my project to a MS Access database.
using the upgrading tool VB6 to VB.net from my software I got this
Public function ADO_Connect() as boolean
dim connSDB as new adodb.connection
      'error handler to exit function and return false if error occurs
      on error goto Err_handler
      ADO_Connect= true
      connSDB.open "Driver={Microsoft Access Driver (*.mdb)}:DBQ=Path to the database"
      Exit function
'label for error handler
Err_handler:
      ADO_Connect=false
End function


The ADO_Connect procedure will be added to the form load event, and in case the connection failed to be established an message box would be displayed and the form unload
0
Comment
Question by:Arnaud241
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 250 total points
ID: 18032363
Are you using VS2005? If so,
Imports System.Data
Imports System.Data.Oledb

Dim dt as new Datatable
Using conn as new OleDbConnection("Your connectino string here")
Using cmd as new oledbCommand("your query here",conn)
Using da as New Oledbdataadapter(cmd)
da.fill(dt)
End Using
End Using
End Using

Your data will now be in the datatable dt.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18032366
Major paradigm shift in ADO .NET is to disconnected dadtasets -- you connect, grab the data and disconnect. Gone are the days of persistent connections.
0
 

Author Comment

by:Arnaud241
ID: 18032991
I'm using  VB 2005
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Arnaud241
ID: 18033048
ok, the days of persistent connections are over.
how can I implement a public function that I could can on any form I have in my project.
Actually I'm trying to implement a function that would execute all my SQL queries, such as insert, delete and update.


public function ADO_Execute (byval strQuery as string) as Boolean
on error goto err_handler
Execute strQuery
ADO_Execute=true
exit function
Err_handler:
ADO_Execute=false
end function
 thanks for the help
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18033360
The equivalent code with VB.NET would be something like (coded onto the screen, so not tested)

public function ADO_Execute (byval strQuery as string) as Boolean
Dim result As Boolean = True
Try
   myCommand.CommandText = strQuery
   myConnection.Open
   myCommand.ExecuteNonQuery
Catch
   result = False
   'some error reporting here if you want it
Finally
   If myConnection.State = ConnectionState.Open Then
       myConnection.Close
End Try
Return result
end function

That assumes that there is some declaration and initiation, with scope accessible to this function, of (a) an OleDbConnection and (b) and OleDbCommand - with the OleDbConnection as its .Connection property.

But - while there is no rule against executing your insert, delete and update piecemeal, with a function such as this -the disconnected approach of the ADO.NET model offers other possibilities.  Such as saving them all up (automatically) in the datatable and using a dataadapter to do them all at once.

Roger
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18033683
Sancler,

This is what happens while I'm driving home LOL

The one scenario you didn't cover (which my original code does, so maybe we can forgive you) is the SELECT statement :)

Chaosian
0
 

Author Comment

by:Arnaud241
ID: 18034013
ok, actually I'm kind of new with VB.Net. Can you tell me to implement this function do I have to create a new class just for the functions cause I have to implement more public functions, and be specific on the select statement implementation.
thanks
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18035495
It depends how "pure" you want to be.  The main alternatives are a Class or a Module.  The former is more consistent with the object-oriented approach, the latter is (in my view) slightly easier to code.

Whichever you choose, it sounds as though you want to put all your data objects (dataset/datatable/s, connection, command, dataadapter) and methods (such as the function you proposed) in it.  Then, if you use a Class, you can expose public properties and methods to return such objects/information as you wish to other parts of your app or, if you use a Module, you can just call (and refer to) its public methods and objects from any other part of your app.

For specifics on the select statement implementation, this is a good site for connection strings

   http://www.connectionstrings.com/

You would want the appropriate one from under the heading "OLE DB, OleDbConnection (.NET)".

The query string would be in standard sql format - like

   "SELECT * FROM MyTable"

or

   "SELECT ThisField, ThatField FROM MyTable WHERE ThisField < 100"

Roger
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question