Visual Basic.Net OleDBConnection and Class Module

InvisibleMan
InvisibleMan used Ask the Experts™
on
Could someone please give me a code example of creating a class module in VB.Net for a database connection.  I am having trouble figuring this out.  I want a class module in which anywhere in my application I can open a recordset using the DataSet option etc.  Could you please provide me with a class module that I could edit.  I am using SQL Server as my database.  Also maybe an example of using the DataSet to fill a listbox or combo box.  I am used to the ADODC control and binding it to a datagrid or listbox.  Thanks everyone.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:

Author

Commented:
That looks good.  I will read alot more into that.  The thing I dont want to do is create a connection string everytime I want to call a stored procedure etc.  I need an example on creating the connection string in a class module so I can reuse it.  I dont want to use this below example from that article over and over again.  In VB6 I had a class module that created a gConn connection string in which I could user anywhere in the application.  

After the namespace has been imported, you can call any of the Execute* methods, as illustrated in the following code sample.

[Visual Basic]
Dim ds As DataSet = SqlHelper.ExecuteDataset( _
   "SERVER=(local);DATABASE=Northwind;INTEGRATED SECURITY=True;",
     _
   CommandType.Text, "SELECT * FROM Products")

Author

Commented:
I will check out the sample documentation from the download but not sure it will have what I am looking for with the class module.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

RexxSenior IT Auditor

Commented:
Here is some basic code that you can get some ideas from.  Save as an aspx file and check it out.

<%@ Page Language="VB" Debug="true" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ import Namespace="System.Data.sqlClient" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Drawing" %>
<%@ import Namespace="System.Web.HttpCookie" %>
<%@ import Namespace="System.datetime" %>

<script language="VB" runat="server">

Public Sub Page_Load(Source as Object, E As EventArgs)
     If Not IsPostBack Then

     Dim optselarray as New arraylist
          optselarray.add("")
          optselarray.add("Corporation")
          optselarray.add("LLC")
          optselarray.add("LP")
          optselarray.add("All")          
     optsel.DataSource = optselarray
     optsel.DataBind()

     Dim pnlPanel as Panel
     Dim strPanelName as String
     'hide previous panel
     strPanelName = "SelectPanel"
     pnlPanel = FindControl( strPanelName )
     pnlPanel.Visible = true
     End if

End Sub
</script>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Company Print Options</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>


<Form runat="server" id="form">
<asp:panel ID="SelectPanel" runat="server" Visible="false">
     <table width="100%" border="0" cellpadding="0" cellspacing="0" class="apartmenttable">
      <tr bordercolor="0">
        <td width="30%" class="apartmentinside">&nbsp;</td>
        <td width="70%" class="apartmentinside"></td>
      </tr>
      <tr bordercolor="0">
        <td class="apartmentinside">&nbsp; Select Option to print Company records:</td>
        <td class="apartmentinside"><asp:dropdownlist ID="optsel" runat="server" TextMode="SingleLine" />
         <asp:ListItem Text="Corporation" Value="Corporation"/><asp:ListItem Text="LLC" Value="LLC" /><asp:ListItem Text="LP" Value="LP" /><asp:ListItem Text="All" Value="All" /></asp:ListBox>
      </tr>
      <tr bordercolor="0">
        <td class="apartmentinside">&nbsp;</td>
        <td class="apartmentinside"></td>
      </tr>
       <tr>
        <td class="apartmentinside">&nbsp;</td>
        <td class="apartmentinside">
          <asp:button ID="btnprint" Text="Print" OnClick="print_click" runat="server" />
          <input type="button" value="Return" onclick="history.go(-1)" />
          </td>
       </tr></table>
</asp:panel>
</form>
</body>
</html>
RexxSenior IT Auditor

Commented:
Remove the <asp:button ID ... since I did not include that sub print_click
Zlatin ZlatevSenior Technical Architect, Salesforce Commerce Cloud

Commented:
@InvisibleMan,
Check out http://www.asp.net for Asp.Net WebMatrix download.
It has built-in templates for data access pages, moreover it is great *free* tool from microsoft for building ASP.NET applications.

Kind Regards,
Zlatin Zlatev, MCSD(VS6)
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
You could open the connection in the constructor of the class if you want to have it open all the time.
Zlatin ZlatevSenior Technical Architect, Salesforce Commerce Cloud

Commented:
@InvisibleMan, you can have connection string in your web.config file under appSettings section.

HTH

Author

Commented:
Thanks everyone for all the information.  I am only at this time working with Visual Basic though building a client/server application not a web application.  But that is helpful for the future. Thanks.

Author

Commented:
I just installed the Application Block.  I open the sample projects and there is a little yellow warning sign next to the reference of the data application block reference.  Why is this?  It also fails when I load it with build errors saying The referenced component 'Microsoft.ApplicationBlocks.Data' could not be found.

Author

Commented:
Ok I started playing with the Application Block.  Very cool.  If I want to use the connection string globally where I can I set that up?  You say the constructor.  Can you give me an example please.  I see the following in the sqlhelper.vb

#Region "private utility methods & constructors"

In there?  Can you give me an example using the Northwind DB etc?  Thanks
Zlatin ZlatevSenior Technical Architect, Salesforce Commerce Cloud

Commented:
@InvisibleMan, again you can have your connection string in appSettings section in your config file.
If your application is named Myapp.exe your config file will be Myapp.exe.config.

See the following resources from .NET Framework SDK for more info:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconfiguringnetframeworkapplications.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstep1examiningconfigurationfiles.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemConfigurationConfigurationSettingsClassAppSettingsTopic.asp

HTH
Zlatin ZlatevSenior Technical Architect, Salesforce Commerce Cloud

Commented:
The reason for making this in config file is flexibility - you can always edit the config file, if you need to change the connection string.

Author

Commented:
Ok sounds good that would be great.  So what your saying is have a config file sort of like an INI file for the database connection.  Then use the Applications Block for SQL Data access updates etc.  Is this a very good way to build .Net applications?  It seems it.  So I went to the configuration option inder the project properties.  I see Start Action, Start Options etc.  Where do I place in the config file.  Do I just create an INI file?

Author

Commented:
I have alot to learn.  I will read all the information.  Thanks to all.  zlatev I am just trying to start playing with the application block set.  I was able to get a project I have working with it.  The only thing is I am using a textbox that has my connection string in it to a local database.   I want to rid that and create a global connection.  thanks
Zlatin ZlatevSenior Technical Architect, Salesforce Commerce Cloud

Commented:
The config file is placed in the same directory as your application. You have access to the config file via the
System.Configuration.ConfigurationSettings
class.
check for AppSettings member of this class.

HTH

Author

Commented:
I dont see a config file under that directory.  I see all my .vb files and a file called AssemblyInfo.vb.  Thats it.  Nothing wih a .config extension to it.

Author

Commented:
I created a new config file in my application that looks like XML.  I have the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
     <appSetting>
          <add key="BillConn"
               value="server=MikeW;Integrated Security=SSPI;database=Billing"/>
     </appSetting>
</configuration>

Now what?  How do I use the BillConn connection?
Zlatin ZlatevSenior Technical Architect, Salesforce Commerce Cloud

Commented:
@InvisibleMan, use some kind of the following syntax:

' VB.NET code:
System.Configuration.ConfigurationSettings.AppSettings("BillConn")

// C# code:
System.Configuration.ConfigurationSettings.AppSettings["BillConn"]

You need not to use the Item property of AppSettings NameValueCollection class, because it is the indexer property of the class.
(See: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemcollectionsspecializednamevaluecollectionmemberstopic.asp
)

HTH

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial