SQL Database for Visual Basic APp

It's accurate to say that you don't have any programming experience?
If so I advise you to start reading this MSDN article and come back after you perform some tests and when you have more assertive questions.

Use ADO.NET for your communications with SQL.

This link may be helpful for you:

http://www.codeproject.com/Articles/361579/A-Beginners-Tutorial-for-Understanding-ADO-NET

You can execute stored procedures, pass them parameters and populate datatables and data sets with the resulting returned data.

Thanks James

Sorry - What I mean by an App is a Windows Desktop Application.

How Complex? - Will be a Fully integrated CRM add on for a Accounting Package. (I have got almost this far with VBA - But VBA has issues when getting too complex)

This is how I connect to the Database from VBA currently.

Set SQLEx = New ADODB.Connection

    SQLEx.ConnectionString = "Provider=SQLOLEDB;Server=" & BusServer & ";Database=" & Database & "; User ID=SA;password=" & BusPass
   
    SQLEx.ConnectionTimeout = 1
    SQLEx.Open
    DoEvents
    SQLEx.CommandTimeout = 1000

Am I correct that what you are saying is connecting this way (Sightly different in Visual Basic though) will result in better performance than using Linq to SQL?

If properly done, connecting with ADO.NET requires more work, but yes, you will gain performance, and also a lot more control (easier maintenance), so it is worth it in the medium to long run if not in the short run.

However, you might want to remove the "Slightly" in front of "different" in your last paragraph.

If you were using only Connection and Command objects in classic ADO, then you're in for something very similar.

But think about the following if they were part of your VBA work: Recordsets are gone, locks are gone, permanent connections to the database for the life of the application instance are not the way to go anymore. Connections are usually handled through local variables instead of being taken care of with a global variable as was very often the case with VBA or VB6.

In ADO.NET, if you want to get the data to perform operations through your VB code instead of doing most of the job in SQL, or if you need to display or edit the data, you need to load a copy of the records that you need, work on that copy and later update if needed. During that time, you do not know if changes were made by other users. You might overwrite their work if you are not careful (or them overwrite yours).

You do not deal with multiuser conflicts by locking the records, but by performing checks when you update the data. ADO.NET has been built around that concept, so it has the necessary tools (classes/properties/methods) to help with that and make it easier than it might look at first sight. It forces you to be more careful about design prior to coding.

It requires a different application architecture, and different ways of working that takes some time getting used to. For some programmers, such as myself who had been working for countless years with classic ADO, DAO and before that stuff such as .dbf files, learning to work in the disconnected way that ADO.NET forces upon you has been my biggest challenge when I switched to .NET. To me. it seemed completely absurd when I started out at the beginning of the century.

But I would never go back to the old ways. I occasionnally have to work on old Microsoft Access code, and I hate it every single time I have to do so. The new ways are better adapted to todays environments, were databases are a lot bigger, the number of users usually a lot higher, and mobile applications fighting to get their part of the data in their own way.

But they need to take a big step on a planet that might seem alien. That is probably why so many programmers turn to LINQ and data entities. It hides the details. But the details are what makes the thing fly and give us the versatility that is required by todays complex needs and environments.

Thanks again James

I was re-Connecting to the database each time I needed to in VBA (Not maintaining the Connection)
Most of the Multi User Locks are taken care of by inserting the Record-ID into a "Record Lock" Table (The Accounting Package uses the same table so that links the two systems with the same Record Locking)
Record Sets - Yes I was using those to find the number of records and columns returned and fill Arrays with the Data (The Array would be a Global Variable that simply filled a Local Variable before it was used)
All the updating of the Data was done by compiling an SQL string and executing it against the database.

I found some examples on msdn - I'll work through them tonight and see how I go.

Just be careful with the timeouts:
    SQLEx.ConnectionTimeout = 1 --> You might want to increase this value since 1 second is a very low value for connection timeout.
    SQLEx.CommandTimeout = 1000 --> You might want to review this value since 1000 seconds is a high value for command timeout.

My recommendation is not for change those values and work with the default ones until you realize if they need to be changed or not.

Hello

I have managed to connect to the database and return some values with the following code.

 Imports System.Data.SqlClient
Imports System.Data


Public Class MainWindow
    'Inherits System.Windows.Forms.Form
    'Create ADO.NET objects.
    Private myConn As SqlConnection
    Private myCmd As SqlCommand
    Private myReader As SqlDataReader
    Private results As String


    Private Sub btnSearch_Click(sender As Object, e As RoutedEventArgs) Handles btnSearch.Click

        'Create a Connection object.
        myConn = New SqlConnection("Initial Catalog=BSTAppTest;Data Source=RON-IS\SQLEXPRESS2014;Integrated Security=SSPI;")

        'Create a Command object.
        myCmd = myConn.CreateCommand
        myCmd.CommandText = "select accno,name from dr_accs where accno in(1885,3)"

        'Open the connection.
        myConn.Open()

        myReader = myCmd.ExecuteReader()

        'Concatenate the query result into a string.
        Do While myReader.Read()
            results = results & myReader.GetValue(0) & vbTab &
                    myReader.GetValue(1) & vbLf
        Loop

        'Display results.
        MsgBox(results)

        'Close the reader and the database connection.
        myReader.Close()
        myConn.Close()

    End Sub

Select allOpen in new window

But do I have to put all this in every class where I need to connect to the Database??

What I would like to be able to do is have a Class of SQL that I can call from anywhere. - I have found the Following but can't get it to return the data.

Connection Class

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web

Public Class frmConn

    Public Shared Function ConnSQL() As SqlConnection
        Dim connectionString As String
        Dim con As SqlConnection

        connectionString = "Data Source=RON-IS\SQLEXPRESS2014;Database=BSTAppTest;Integrated Security=SSPI;"

        con = New SqlConnection(connectionString)
        Try
            con.Open()
            Return con
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        Finally
            con.Close()
        End Try
    End Function
End Class

Select allOpen in new window

SQL Class with Select Function

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Net
Imports System.Net.Mail
Imports System.IO
Imports System.Text

'The class
Public Class SqlClass
    Public Shared Function SelectRows(ByVal query As String) As DataTable
        On Error Resume Next
        Dim Conn As New SqlConnection

        Conn = frmConn.ConnSQL

        Dim da As New SqlDataAdapter
        Dim dt As New DataTable
        dt.Clear()
        da.SelectCommand = New SqlCommand(query, Conn)

        'Try
        da.Fill(dt)

        Return dt

        'Catch ex As Exception
        ' 'MsgBox(ex.Message)
        'End Try

    End Function
End Class

Select allOpen in new window

My Attempt to retrieve the Data with a Button Click (Same as the First Snippet)

Imports System.Data.SqlClient
Imports System.Data


Public Class MainWindow
	Private Sub btnSave_Click(sender As Object, e As RoutedEventArgs) Handles btnSave.Click


        Dim results As String = ""
        Dim DataTable As New DataTable

        DataTable = SqlClass.SelectRows("select accno,name from dr_accs where accno in(1885,3,3000)")

        DataTable.CreateDataReader.Read()

        'Concatenate the query result into a string.
        Do While DataTable.CreateDataReader.Read()
            results = results & DataTable.CreateDataReader.GetValue(0) & vbTab &
                    DataTable.CreateDataReader.GetValue(1) & vbLf
        Loop
        'Display results.
        MsgBox(results)

    End Sub
End Class

Select allOpen in new window

Also James - Is this what you mean by "Properly Done"? or am I on the Wrong Path?

You can create a global variable for the SQL connection and set this when the app starts and then feed this into the classes as a parameter. That will make changes to the connection string / object much easier at a later date.

I am assuming you have classes that represent the database tables or object that your working with. i.e if you have a Customer Class that corresponds to the Customer table then you can add the communication code into the Customer class. For example you would have:

Customer objCust = Customer.Fetch(CustomerID, SQLConnection);

you can also do:

Customer objCust = new Customer(1000, "Mr Jones");
and then pass it to the Customer.Add function like this:
objCust.Add(objCust, SQLConnection);

etc, etc.

If you build classes this way and ensure that all the relevant code pertaining to that type of interaction is contained within the class it will make life a lot easier.

(examples are C# but you get the idea).

I would also consider splitting the classes into Models and Presenters. I would do this:

public class Customer
{
  private _intCustomerID;

  private _string Name;
  
  public int CustomerID
  {
      get
     {
        return _intCustomerID;
     }
     set
     {
        _intCustomerID = value;
     }
  }

  public string Name
  {
      get
     {
        return _strName;
     }
     set
     {
        _strName = value;
     }
  }
  
  public Customer()
  {
      // Default Constructor
  }
  
  public Customer(int CustomerID, string Name)
  {
      this.CustomerID = CustomerID;
      this.Name = Name;
  }
}

public class CustomerPresenter
{

   public CustomerPresenter()
   {
      // Default Constructor
   }

   public Customer Fetch(CustomerID, SqlConnection SQL)
   {
      SqlDataAdapter objDataAdapter = new SqlDataAdapter();
      DataSet objDataSet = new DataSet();

      if (SQL.SQLState != ConnectionState.Open)
         {
            try
            {
               SQL.Open();
            }
            catch
            {
               throw;
            }
         }      

         objDataAdapter.SelectCommand = new SqlCommand();
         objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
         objDataAdapter.SelectCommand.CommandText = "spYourSP";
         objDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int)).Value = CustomerID;
         objDataAdapter.SelectCommand.Connection = SQL;

         try
         {
            objDataAdapter.Fill(objDataSet, "Customer");
         }
         catch 
         {
             throw;
         }
         finally
         {
            objDataAdapter = null;
         }
         
         if (objDataSet.Tables["Customer"].Rows.Count > 0)
         {
            DataRow rowCustomer = null;
            rowCustomer = objDataSet.Tables["Customer"].Rows[0];

            Customer objCustomer = new Customer();
            objCustomer.CustomerID = Convert.ToInt32(rowCustomer["CustomerID"]);
            objCustomer.Name = rowCustomer["Name"].ToString();

            objDataSet = null;
            return Customer;
        }

    }

}

Select allOpen in new window

You can then simply do this:

SQL represents a global variable that contains your SqlConnection object

CustomerPresenter objCP = new CustomerPresenter();
Customer objC = null;

try
{
objC = objCP.Fetch(1000, SQL);
}
catch (Exception er)
{
    // Do something 
}

Select allOpen in new window

If you ever move to WPF then working with Models and Presenters would be a good habit to start doing.

Really hope this helps you on the right track.

@Mr Knackered

Your Samples are in C# and this is a VB.NET Question

@James

The way I understand you Article is

Use Command for
Retrieving any Single Value.
Executing a Function that returns a Value
Executing a Stored Procedure
Executing a SQL command

Use DataReader for
Returning any Single Row of Data
(When Multiple Rows are returned does this execute against the database once? - Say I want to fill a Combobox with 50 Records is this the Best way to do it? Or would that execute on the database 50 Times?)

Use DataTable for
When I need to return multiple rows of data.
Can this be used with Multiple Tables when they are joined in the SQL String? (Your Article seems to say you need to use a DataSet if linking tables)

What is the "Correct" way to update the database?
In VBA I was simply creating a SQL command and sending it to the Database.
Is there a New way to do it with the DataTable?

The Command, with it its Connection, is at the heart of everything.

Sometimes it is used alone.
Sometimes it is used with a DataReader.
Sometimes it is used with a DataAdapter.

To summarize this, there is always a command involved whenever you access the database, be it for retrieving values or for updating, inserting or deleting records.

You use it alone when you want to retrieve only one value or want to update data in one record or perform some kind of batch processing that is better done through SQL that it is through VB code.

You use it with a DataReader when you want to sequentially read the rows of data returned by the execution of the command. The DataReader reads the records one by one. It is quite similar to sequentially reading a computer file line by line. The Command associated with the DataReader will execute only once against the database. The results are cached and then accessed one at a time by the DataReader. Everytime you call Read on the DataReader, you lose the current row, that is replaced by the next one from the cache. So you have to record each row somewhere (usually an array or a collection) if you want to accumulate the data in memory.

Because it is read only and does not do much except present you with the data in the "current" record, it is a fast way to get only one line or to loop through the results when they consist of multiple lines.

Yes, most of the time a DataReader is the best way to fill a ComboBox, or any object that is based on an array or a collection. I am an object programmer. I hold data in memory in my own classes, built into collections. I always use a DataReader to build these.

You can also use a Command object with a DataAdapter in order to fill a DataTable. The advantage of that approach over the DataReader is that the DataTable ends up containing all the rows returned, and the DataAdapter filled the DataTable for you. No loop, no need to fetch the fields one by one and store the data in a container. A simple call to the Fill method of the DataAdapter does it for you.

And as an extra, the DataTable can take care of features from the database, such as identity fields, unique columns, columns types and the likes. As I said earlier, you always work with a copy of the data, never directly on the database as was the case with the old recordset. In order to maintain integrity, you need to be able to reproduce in memory the features or a database table. That is what the DataTable is used for.

When you work with multiple tables, you have 2 approaches. If all you do is display the data, then you can simply join the tables in your query and fill the DataTable with the result.

However, if you think you will need to update the database later, working like that might make things more difficult, because you will have to work from one DataTable in order to update multiple database tables. In such a situation, a DataSet might be more convenient. Think of the DataSet as a database in memory. A DataSet is a container for multiple DataTable objects, linked together through DataRelation objects. As a database does, a DataSet can be built to know the relations between the tables and thus maintain the integrity that is necessary for a database to work, such as making sure that a CustomerID exists in the Customer table before using it in the Order tables. Or prevent you from deleting a Customer if he has Orders. When you are ready to update, you can then more easily update the tables one by one in the database.

There is no "Correct way" to do anything. It all depends on the specific needs of the operation. That holds for updates.

You can still do as you did in VBA and use a SQL command to send the update to the database. In my opinion, when this is possible, it is the best way to go. This is how I work most of the time.

But yes, there is a new way. In some situations, you might need to delay an update. The DataTable has been designed in such a way that you can change many rows (remember that you are working with a copy of the original table in memory), and then perform an Update/Delete/Insert of all the changes in one shot by using a DataAdapter and calling it's Update method.

Think of a shopping cart in a web application. The data is accumulated in memory, and the Customer, Inventory and Order tables are updated only after the order is finalized.

This approach is particularly interesting for mobile applications, where the connection to the physical database is not always available. It would enable you, as an example, with just a few line of codes, to fetch the data from the database and save it in an XML file. While the user is on the road, he works with the XML file (through DataTable and DataSet objects, no need to know anything about XML if you prefer to do so). Then, when he is back at the office, you load the XML into a DataTable or DataSet (DataSet.ReadXML or DataTable.ReadXML, one line of code only) and trigger an Update through a DataAdapter to send the changes that were made during the day to the real database.

ADO.NET is a complex library, but a very powerful one. You are not force to work in a particular way. It simply provides enought tools so that you can adapt to almost any situation or need that arise.

Thanks SO Much James

That makes it much clearer.

I have started with a simple function to return a single string with the sql command.
I have also moved the connection into the same SQL Class.

So to get a single string from the database I only have to use the following one line of code.

MsgBox(SqlClass.SelectRecord("Select accno from dr_accs where accno in (1885,3)"))

(I realise this will only return the first record)

SQL Class

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Net
Imports System.Net.Mail
Imports System.IO
Imports System.Text

'The class
Public Class SqlClass

    Public Shared Function ConnSQL() As SqlConnection
        Dim connectionString As String
        Dim con As SqlConnection

        connectionString = "Data Source=RON-IS\SQLEXPRESS2014;Database=BSTAppTest;Integrated Security=SSPI;"

        con = New SqlConnection(connectionString)
        Try
            con.Open()
            Return con
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try
    End Function

    Public Shared Function SelectRecord(ByVal query As String) As String
        Dim myCmd As SqlCommand

        myCmd = ConnSQL.CreateCommand
        myCmd.CommandText = query

        Try

            Return myCmd.ExecuteScalar()

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            ConnSQL.Close()
        End Try

    End Function
End Class

Select allOpen in new window

Does this Class look OK so far? (Is it closing the connection in the correct places)

If this Looks OK I'll start looking at Functions to return a DataTable and To fill a Combobox with a DataReader

The basics are OK, but... (there is always a but :-) )

Having a method to create the connection is worth it, because this is something you keep doing over and over, always the same exact code.

But I would question the idea a creating a method to return the result(s). If you go that route, you will end up having dozens of such methods, because there are so many little variations.

- You will need another one to return numeric values, one for dates, one for every type you can think of.
- One for a datatable, and others for stuff that needs to return only 2 or 3 value.
- Some that return a DataReader instead of a DataTable.
- Some that require parameters and others that don't.
- Some that call a stored procedure (preferred over SQL queries) and others where a query is the only way to go.
- Those who use a DataTable might require a call to FillSchema before calling Fill, others won't.
- Some that will have to run under a Transaction.
- A little XML in the pot?
- And we are still talking only about SELECT. What about UPDATE, INSERT and DELETE.
- The way to react to an Exception might be different depending on the situation. Sometimes you stop everything, sometimes you try to solve the problem through your code, sometimes you ask the user what he wants to do.
- Very often, things require more complex stuff.
- And on... and on...

If it was worth it, they would have built these in the framework. There is not much code in such a method, so it is not a big thing to go straight in the form or method where you need the data.

If you use the same database with many applications, it might make sense to have a dll with classes that simplify access to that specific database, methods such as GetCustomerID, SaveInventoryItem and the likes. It's a little more work when you create the first application and the first version of the dll, but when you start working on the second application, half of the job is already done for you. This also makes it easier to work with the application, because the code in there is simpler and deals only with what the application needs to do, not with the database itself. And if you ever make some change in the database, you make it only in the dll, you often do not have to touch the applications themselves.

But it my experience, trying to build a generic class over classes that are already generic classes is not worth the trouble and can end making things less clear when the maintenance time falls on you.

It's up to you.

Then. the way to trap exceptions, not only for ADO.Net, but in general in .NET. There are many types of exceptions, Exception being the generic one, for the exception that you did not expect. A Try Catch is like a Select Case. I have to go and I am in a hurry (sorry if I made mistake in this one, but I do not have time to review it before clicking Submit). More details about Exception handling should be the subject of another question, but try the following:

		Try

			Return myCmd.ExecuteScalar()

		Catch ex As SqlException
			'Look at all the properties specific to a SqlException

		Catch ex As Exception
			MsgBox(ex.Message)

		Finally
			ConnSQL.Close()
		End Try

Select allOpen in new window

The SqlException object gives you very precise information about what went wrong. A lot more to deal with than a simple Message.

Thanks James

That makes sense.
I've decided to go a bit of both - Use some functions for very simple requests (Such as returning a single string)

Just got my first very simple project completed - Only makes two calls to the database, retrieves two strings, the user updates one and then saves it back to the database. (Gotta Breathe before I can Crawl.. :))

I've put in another question about Classes, I'll leave this one open for a couple of days as I want to try some DataTables and DataReaders

Thanks Again

I've come up with the following to fill a combo box with a String values

SqlClass.comboboxfillstr(comboBoxfillsub, "select name from staff")

 Public Shared Sub comboboxfillstr(combobox As ComboBox, sqlstr As String)
        Dim conn As SqlConnection
        conn = ConnSQL()

        Dim myCommand As New SqlCommand(sqlstr, conn)
        Dim datareader As SqlDataReader = myCommand.ExecuteReader()

        combobox.Items.Clear()

        While datareader.Read
            combobox.Items.Add(datareader.GetString(1))
        End While

    End Sub

Select allOpen in new window

But I'm thinking I should be filling this combobox from a Class called "Staff"?
From a Method Getactivestaffnames().
I am going to need to have the staff details loaded into a Class so I can select the staff ID to update the database later so may as well load them when the application starts?

Before creating a class for something as usual as a list of strings, look in the framework. With over 10,000 classes, you can rest assure that such simple types of classes are already defined for you, with extra features through their properties and methods.

I do not have time to go into the details, but give a look at the following System.Collections.Generic Namespace. Collections are a bit like arrays, but with specialized features and easier ways to add, remove or insert elements from the list. These are all classes that are used to build list of other types, be it dates, strings or bitmaps, each with specific characteristics. And when the collection is built filling a ComboBox with it is but one line of code:

yourComboBox.DataSource=yourCollection

I had just the name as that's all that was needed for the combo box in this case - The actual staff Class will need 15 + Fields.

Also I can't get the datasource of the combobox. (I'm using WPF if that makes any difference)

This is starting to double up on my other question about Classes.
https://www.experts-exchange.com/questions/28745680/searching-a-class-instance-in-VB-Nett.html

Back on the SQL side of things - Should "Ideally" ALL the SQL coding be in an appropriate Class?
ie = Updating a Product should be from a Call to the Product Class
Inserting a New Staff member should only be done in the Staff class?
Likewise all the selecting SQL should be Located in the Associated Class?

The Collections list( of t) and dict() should be ideal for static lists of Product Groups etc where there are only a name and a Key to select and return

If your using WPF then you should be using Models and Presenters as I have outlined in my post above. The examples were in C# but the principal is the same. If your using WPF then you should be using ObservableCollections to hold collections of your Models and place the Methods for dealing with the Models into your Presenters.

Customer Class (Describes what a Customer is and the attributes that make up a Customer)
Customer Presenter Class (Holds the Methods for interacting with the Customer Class)
ObservableCollection<Customer> (is in affect a list / collection of Customer objects)

So for example you would instance your CustomerPresenter class and then call the FetchCustomer method which returns an ObservableCollection of Customer objects and then you would set the ComboBoxes.ItemsSource to hold your retrieved ObservableCollection and then bind in XAML to your Customer Classes properties.

One thing that you must do when building your Customer Class is to ensure that you are Raising the PropertyChanged Event when setting properties so that XAML knows when you have changed things in your Customer Class or the bindings wont get updated.

Your Models can inherit directly from INotifyPropertyChanged as the example below shows and you can simply call OnPropertyChanged("PropertyName") in your Set's. Example is below and has a VB version.

https://msdn.microsoft.com/en-us/library/vstudio/ms743695(v=vs.100).aspx

You can also create your own class called Notifier (for example) that inherits from INotifyPropertyChanged and defines the event etc and then you can make your Model classes inherit from your Notifier class and you wont have to repeat all the Notifier stuff in each Model.

It may be a little painful at first to get your head around this way of doing things but it really is how WPF works and once you have it working for one Model all the others will fall into place and binding from XAML will work perfectly.

Yes, WPF can make a difference. I was going on the assumption that you were in a Windows Application. But since most of our discussion is about the background work and not the user interface, most of it is good.

There are many ways to handle the mapping between your database and your classes.

Some will go with your idea of putting the SQL in the same classes that you use for your business objects.

Personally, I prefer to separate the SQL. My applications are usually built around a classic 3-tiers model (3 assemblies). The application, a business object dll (the inventory, customers and billing classes), and a data access dll. So there is not ADO.NET at all in my business layer.

The reason behind the business object dll is obvious, I can use it with different applications, and making a change in that dll automatically bring it to all the applications that use the dll.

Putting the ADO.NET code has many advantages. If you work with many customers that use different databases, you can have a data layer for SQL Server, another one for Oracle, and so on. All use the same business layer, but an optimized data access layer. It's also a godsend when a customer finally understand that he should move from Microsoft to SQL Server, or after a merger where your SQL Server database needs to be integrated in the Oracle database of the company that bought yours. You do not have to touch the application, you do not have to touch the business layer, only the data access layer.

I have also seen situations where the business layer was an option, depending on the application. A dll to handle photos, that was sometimes used only to work with photo files on the hard disk, but has to deal with a database for other applications. I was also glad when a new customer saw one of these applications and asked if I could adapt it to their database, that had a completely different structure than the one I has designed along with my classes. Having the data access layer in its own dll, I once again did not have to touch the application or the dll that deals only with photos, and was able to make it work with his database without too much work.

This being said, there are other approaches that gains popularity, specially with programmers that work in web and mobile environments.

The MVC (model-view-controller) is an architecture quite similar to the one I use, but with a slightly different philosophy that is closer to the fact that a mobile application runs on data that is on a distant server, often through a service instead of direct interaction with the server. My 3-tiers model goes more around a "network philosophy".

The Models and Presenters described by Mr Knackered is something I have seen around, but I am not familiar with that one and do not like WPF. At first sight, to me, it looks similar to MVC with other words (the same way many say that MVC is similar to 3-tiers but with different words), and might be more interesting for the way XAML (WPF and Windows 8 style apps) handles databinding, the equivalent of the DataSource I was using in my last post, thinking that you were in a Windows application.

The main common point is that the different functions (application, data manipulations and data access) are separated in different layers.

@James is correct that there are many ways to structure your approach but if using WPF then MVP (model, view, presenter) is the best place to start. Just keep the coding out of the Model as you will be binding to it and it makes more sense to keep it in a presenter or controller.

Just remember to ensure that you use or inherit from INotifyPropertyChanged in your models or the binding won't work as you would expect if the data changes in the model between your class and Xaml.

WPF is a wonderful (and frustrating) platform and it takes a while to get used to how it works compared to the old windows forms type approach but once you have switched over you won't want to go back.

A little out of the original question (tell me to drop that side discussion if you prefer so p-plater). But I am curious.

@Mr Knackered

Yes, frustrating. That is why I never made the switch. That is why I never went for Windows Store applications.

But I would like to be convinced. Why don't you want to go back^ Apart from the fact that you can design interfaces that are flashier and can more easily adapt to different screens, I cannot see the advantages of WPF.

I program business applications for the desktop, so flashy marketable interfaces and being able to run my application while the tablet is held diagonally are useless to me. What the application does and making an interface that is as easy to use as can be is my everyday work when I am not training other programmers. Apart from helping me get experience with XAML for when I will absolutely have to program a mobile application, what can WPF offer me for desktop applications? I only see headaches designing screens and limitations when dealing with events when I get out of my comfortable but very powerful Windows Application projects.

I would like to be convinced otherwise.

Once again p-plater, tell me if this side discussion annoy you.

The whole principle of the Visual Tree just makes far more sense and allows you to traverse it which is really helpful. It is far easier to build multilingual apps with WPF. The ability to use templates to completely alter any control to make some really dynamic applications. Resolution and scaling is just light years ahead of windows forms. The ability to theme your application by using styles and templates and being able to merge them is brilliant. XAML is annoying at first but once you get used to it building windows is just so much easier especially for laying out visuals that adapt to different resolutions. The way events bubble up was annoying as hell at first but once you understand how and why you just adjust and it becomes quite useful.

Don't get me wrong, I hated WPF at first when I moved over from windows forms and just didn't get why it was so different but the more you use it the more it makes sense and eventually you love it and wouldn't want to go back. Even the really simple things like using grids, stackpanels and dockpanels for laying out windows. Comboboxes being bound to observablecollections of your classes etc it's just great quick and easy to do.

It is quite easy to get memory leaks in WPF. For example if you subscribe to an event in code and don't unsubscribe it manually WPF keeps a reference to it and the garbage collector doesn't want to get rid of it. You are not supposed to have dispose methods as you don't need them (unlike windows forms) as there is only one hwnd for the window but I implement them for resources that need to be dealt with so WPF will let them go and allow them to be garbage collected. If it's done in XAML you don't get the issues but in code behind you do. Using CLR will show the leaks although it's not very friendly and it can be hard to find what is causing it. I say memory leaks, I guess they aren't really as the GC will kill them but only when it has to. It's more about managing the resources to they get collected when you want them to be collected.

As with anything you will always favour what you know and look for reasons why you have to change or reasons why not to change, I get that. Having worked extensively with Windows Forms years ago and having gone through the pain of moving to WPF I can say with absolute certainty that I wouldn't go back. It's nothing personal and I liked WF but WPF just makes more sense to me and in the end is easier to get stuff done. If I had been asked the same question four years ago the answer would be different.

Oh and I don't write windows store apps but desktop apps using WPF. It's not the case that business apps are more suited to WF and WPF is for windows store apps. Blend is written in WPF.

I do agree that just because you can doesn't mean you should. You don't want to build a crazy, lush UI for all business apps but at least you have the choice.

I have fond memories of WF and don't want you to think that I am putting it down because I am not. You should use what your most productive in and comfortable with.

Thanks for your detailed argumentation.

But except for one point, it still does not convince me for the type of work I am doing. Either it does not apply to my situation, or I do not see the advantage. For software that you want to sell at Staples (office supply stores here in Canada and the US) maybe, but not for custom jobs that you do for specific customers.

Things like resolution and scaling are not that important in an in-house situation where the environments are usually controlled. And customers who pay for software that will be used in-house are usually not willing to pay for the extra touches that you can add through styles and templates. It's fancy useless stuff to their bank accounts. Too bad, with my background in audio-visual, photography and graphic arts, I would like to add a more personal touch to my work as a programmer.

When they need something, such as colors and fonts that are closer to the company logo, it's easy to implement with one or two base forms from which all other forms inherits. Form templates are also something you can add to your library of .NET tools.

One thing tickles me however in your arguments. How are multilingual applications easier to do in WPF than in WF. I work around Montreal, and here in Quebec province, we are in a situation where bilinguism (and sometimes trilinguism) is a big part of our world and our work as programmers. In the pre-.NET world, multilingual applications were a pain to create. But the way resources work in .NET in general, and in  the Windows Form Designer in particular, it makes so easy that I always create new programs around resources, even when the requirements does not specify a multilingual application.

You might force me to reconsider WPF if you could show to me that it's easier in that environment.

Thanks for the discussion. I always like to have my opinions and ideas (and tastes) challenged by somebody who is able to give good arguments.

In WPF you can simply bind any text to your resources files. All labels on a window for example can be bound to the resources and you don't have to even think about it. As long as you have the different culture resource files everything is automatic. It doesnt get much easier then that.

As mentioned, just because you can doesn't mean you should. It is simple to create a base style and keep a bland UI with WPF if that is what you need.

One of the reasons why I moved to WPF was because you just don't know how long it will be before MS kill off WF. They aren't exactly developing the tech. However, they killed off Silverlight so I guess nothing is safe. It's all a gamble, for example with Outlook 2016 they have removed support for connecting to Exchange 2007 which is a pain. Chances are WPF will out live WF as they are still developing the platform. Who knows what will happen but the time could come when you have little choice or maybe a new platform is just around the corner. Use what your comfortable with and write good programs. Better to have a decent WF application then a bad WPF application if you know what I mean.

@ James - Keep that side discussion as it's very relevant.

@ James and Mr Knackered  - Should I stick with WF or go to WPF?

I'm Attempting to learn vb.net from VBA at the same time as changing from WF to WPF (Obviously my VBA was all WF)
I decided to try WPF for the reason that it appeared to be the way to go. Am I trying to "Bite of more than I can Chew?"
I Need to Learn this in "My Time" then use it at work.

My Future application description is as follows
Company CRM system (For one or maybe 2 companies both on windows platform running SQL Server)
It needs to have

Second Application

I am Not a Software Developer - I work for one of these companies full time in IT and do work after hours for the second company

WPF would be a good choice if done correctly. Will be a big jump from VBA but a good challenge. Also have a look at www.syncfusion.com as they have a good WPF suite especially the Grid control. If you want and need touch then I don't see that you have much choice.

@Mr Knackered

You simply missed it in Windows Forms.

We have been able to assign resources to controls from the start, in 2002. Not only the text, but also other properties that might have to change with the language, such as the Size which, when changed, might trigger a need to also adjust the Location. I deal with them only through the Properties window, and the Form Designer handles the proper resources for me in the background.

Really easy stuff. When I switch languages from English to French-French to Canadian-French, the Text of my Label changes from "Hi gang!" to "Bonjour la compagnie!" to "Salut la gang!". Its Width automatically enlarges, and my Label automatically moves a bit more to left in French. With no code at all. All I had to do in design for that to happen was to select the language in a dropdown, and  drag a few things in on the Windows Form designer.

@Both

Mr Knackered has a very good point thinking about the future. Its clear that Windows Forms have not evolved in the last few versions of Visual Studio. Its clear that Microsoft puts its weight on XAML, which is at the base of WPF. It made the switch to Windows Store applications in Windows 8, and then if I am not mistaken, to the new Universal applications in Visual Studio 2015.

This is something that has tickled me for some time, and I am glad that I only still have a few years to work, before becoming a lazy old man who will spend more slow time with his planes and saws than frenzied time with his keyboards and mices. I do not have to seriously consider that fact in my current situation.

I do not think that Windows Forms will die soon. Hey, they tried to drop FoxPro and they are still stuck with it. And a lot less code in the world relies on FoxPro than does on Windows Forms. And look at the rollback they did with Windows 10, were the Windows Store style of application has dropped in the background instead of being in the forefront as it was in 8 and 8.1. They know an error when they see one, and are willing to bring back the good stuff when needed.

But the future is a good point that needs to be taken into consideration.

@p-plater

New info here, that might be important in your decision. Tablets you say?

First thing first. You are moving from VBA to VB.NET, quite a jump in itself, you are already aware of that.

But another big jump, because you were doing VBA in Excel, is probably the design of screens. In my opinion, creating screens is a lot more intuitive and easy to get into when working with Windows Forms. That might be due to the fact that I have worked with that stuff for so long. But I remember the fun an ease of creating my first forms a century ago, and I compare that with the frustrations I had trying to make things work and do what I wanted in the few tries I gave to WPF and Windows Store applications. Fun vs frustrations. I might be crazy, but I go for fun whenever I can.

This being said, if you ever did some HTML, the move to XAML is less painful than the move to Windows Forms. XAML shares a lot with HTML in the way screens are designed.


A third of the programmers I train are IT guys that do everything where they word. They need to program in between preparing a new installation, solving a network problem, doing their thing as the in-house DBA or showing a secretary how to send e-mail from Word or Excel. Not very different from what you say you do if I got it right. I keep in touch with many of my old students, and I know how it is for them. This .NET thing requires time to learn and master, even if only scratching the surface. It's particularly hard for those who are not at it full time. Choosing the technology that is the easiest to learn might be important in your condition.


For your first application, I could not say for WPF, but Windows Form could do anything you need. However, it's not "Touch Friendly" for the programmer. WPF might be better at that. Some would argue that it is easier to resize windows in WPF than in WF, but if you keep things to a paysage orientation, it's not the case. It's then just a question of experience and preferences in the ways it is done. If some of your users have these desktop screen than can pivot (and if they use it; I have one but its always in paysage) WPF might however be a better bet in that regard.

Your second application is what might tip the balance... and my dear Windows Forms lose on that one. Windows 8.1 and tablets.

Let's discuss Windows 8.1 first. You might not have the choice, but are you, your customer or your employer sure about that? I don't know around you, but in my world, everybody who had the bad idea of moving to Windows 8 or 8.1 is now jumping fast to Windows 10 while the upgrade is free. Personnally, I would think thrice before looking to target anything that has 8 in its name (that includes Windows 98, naturally :-) ). Why not go for something in between, like Windows 9 ( :-) again).

The word that might changes everything is "tablet". That leaves Windows Forms out. Although a Windows Form can run on a tablet that has a full version of Windows (and not only the simple Windows RT that is found on cheaper tablets), they are almost useless because of the size of the screens. I have one of the biggest tablet to come on the market, except for reading .pdf files and navigating the Internet, it's useless with standard Windows applications. Windows Store apps (what most people see as Windows 8 applications) were designed with that in mind and are, in my opinion, the only real way to go for tablets. And they use XAML in the background, the same technology that supports WPF.

So, learning WPF for your first application would give you the experience needed to more easily move to your second application.

But be sure to research the subject and make some tests before you embark on the tablets and apps bandwagon.

As you have seen, you do not run SQL Server on a tablet. And if you go for apps, you cannot access a database directly from them. System.Data is not provided with the toned down version of the framework that runs for the apps, at least, not under Windows 8.1. I did not work with apps on Windows 10, but now that everything is integrated into the same interface, they might have change that. That would have to be verified.

But under 8.1, accessing a SQL database with an app required to build a service that ran on the server. Even if SQL Server is installed on the same tablet as the app, you need to go through a service. The app calls the service, the service uses ADO.NET to deal with the database, and the service returns the data to the apps. That is one more technology to get comfortable with.

Same Specs as the Desktop but Less Functionality. How much less functionality? Got to be a lot, because you cannot hope to have complex interfaces on a tablet application. People who are used to Excel (almost everybody who uses his computer for work) expect to be able to do very similar things as what they were doing on the desktop when they are given a tablet. They want 223 pieces of information displayed at the same time.

In a Desktop application, you would use a TabControl. In apps, as far as I know, there is no TabControl. And grids à la Excel are almost impossible to create. They all want grids à la Excel. Everything has to be big, because the screen is so small. The smaller the screen, the bigger a TextBox must be, in relation to the screen size, to be usable. The bigger a TextBox is, the less space you have on the screen for other controls.

Almost every tablet I have seen developed in a business environment ended up dead after only a few weeks or even days of being deployed. There is just too little you can do in that small space and only with a touchscreen. Try entering more than very basic customer information, when the keyboard  takes half a screen that is already too small. They don't think of that beforehand, they see that only after the application is deployed.

Don't get me wrong, tablets have their place. But too many companies jump on them thinking that if it is mobile, its better.

Tablets can be OK for the guy who is at the top of a pole, repairing a phone or an electric line, when he has to look at a few pieces of data or check an item on a checklist. They might be useful for somebody who has to work and move around a shop or a steel factory and needs to receive a minimal amount of information about what happens everywhere else.

But as a tool to Add and Edit Products, Add and Edit Customers with more than the very basic informations, Enter Orders, and prepare Invoice Orders... No way. The salesman on the road (do they still to their job on the road as my father did?) might have some use for the orders entry. But give that tablet application to any of the clerks that do the other stuff, and after less than a week you will see a protest with people yelling "My Desktop! My Desktop! My Desktop!".

The best one I heard came from one of my students. It was of his boss, an old man fond of new technologies. His most prized possession what his dashboard. Not a thing, a piece of custom software. You know, these little applications that tell you the past, the present, the future and everything in between, month by month in only one screen that takes 2 minutes to build from millions of lines of data. Naturally, he wanted it in a Windows 8 interface, on his brand new Surface Pro bought on Amazon 3 months before it was available. "Don't change anything. I like it the way it is fullscreen on my 36" desktop. They have fullscreen on Windows 8, don't they". "Yes sir! They have only that by the way" (not completely true, but close enough).

The old man paid quite a sum of cash, training his best .NET IT guy so that he learned to program tablets, then to program a service. Then more in salary, while the guy spent the usual time that is  required, even after a good training, getting used to the thing, making many mistakes along the way, finally getting it, fussing about installing the result (you do not install tablet applications the same way you install Windows applications), and finally, proudly, telling his good old boss that the thing was ready.

Full of expectations, the old man clicked on the nice tile with a logo of the company. And they waited, and waited. "What is happening?" "It takes a little longer sir, about 15 minutes, because the tablet is less powerful and the information needs to go through a service that talks only XML, so the data is about 5 times as big as it used to be. And it's slower through the WiFi than it was directly on the network." Then, the 15 minutes mark was crossed, the screen lighted, looking just the same as on the desktop. "Its too small, I can't read a damn thing!". He switched his desktop on and had his dashboard full size on in a little less than 4 minutes, including the time it took to boot Windows. He switched his tablet off, and cancelled the order he had for tablets for all the managers in the company. The legend goes that he never used his tablet afterward.

These things have to be evaluated properly if you do not want to lose time and money for something useless or that nobody wants to use.

So, at first sight, specially if you are sure that tablets are good for your needs, according to your requirements, WPF seems a better choice.

But from the little I know of your background, consider that it might be a lot easier to move to Windows Forms.

And if you have your say in it, be sure that a tablet application is a good solution for what you need to do. If it is, I would definitively go for WPF. But if you eventually go all the way to the desktop, then the decision is up to you. I like blue toward aqua, while Mr Knackered seems to prefer blue toward purple. I still have a long way to go with the color I am living it, that suits very well what is required of me. Mr Knackered see what the actual trends are and would rather follow them. At some point, this very interesting discussion is just a question of different taste.

I have decided to go with Windows Forms for the moment, Attempting to learn WPF at the same time as VB.Net will take more time than I have.

I will look to some 3rd party controls later to make it touch Friendly.

So what you mean with the 3 Tiers is

Application User Interface makes calls to an external dll for all the data/business Classes
The dll with the Data/Business Classes asks the SQL dll for the data

Can I do this by keeping all the Business Classes in separate Files and all the SQL in separate files while I Code the Application then moving them to dll's when things are working?
(Just seems that it would be harder to learn working with 3 separate Projects)

Also on that Note - is it better to have a Number of smaller applications or one large one?
(For operations that are only done monthly or less should they be there all the time or a second application? - Does it slow things to have rarely used features with the ones that are constantly used? Like the Staff Class - It only needs 4 Fields 99% of the time - the other 15 are rarely used)

Hello James - Not sure if this is allowed but I would appreciate it if you could have a quick look at another question.
https://www.experts-exchange.com/questions/28745161/convert-vba-utility-to-vb-net-object-classes.html
Not sure on what to do about the Companies and would like to start setting them up - It is Explained on the Question.

What I mean with 3-tiers is:

- Application that uses classes in...
- ... a dll that contains the business classes. This calls data access classes in ...
- ... a third dll that handles the data Access.

The following code is typed straight in the Expert-Exchange interface, it has not been tested, but it will give you the feeling for the thing.

The application would use the second tier with code such as:

Dim customer As New Customer(id)
Dim customer as New Customer(name)
customer.Phone="450-647-1234"
customer.Save()

Dim customers as New CustomerCollection
Dim customers as New CustomerCollection("Montreal")
Dim customers as New CustomerCollection(paiementDue)
Dim customers as New CustomerCollection(#2015-10-11#)
'Get the list of customers that bought something on the given date
For Each customer As Customer in customers
    Debug.WriteLine (customer.Company)
Next
... and so on

Select allOpen in new window

Heres is the start of the business class used for the collection of customers, with the contructor that retrieves the customers by city, you would have code like the following that would call the data access class:

Public Class CustomerCollection
    Inherits BindingListSortable(Of Customer) 

Public Sub New (city As String)
    Dim drd As New SqlDataReader
    drd = DataAccessClass.GetCustomersByCity (city)
    While drd.Read
       Dim cust As New Customer
       cust.ID = drd.GetInt32(0)
       cust.Company = drd.GetString(1)
       cust.LastOrderDate=drd.GetDateTime(2)
       ...
       Me.Add(cust)
    End While
End Sub

End Class

Select allOpen in new window

And in the data access layer, the method that fetch the data from the database:

Shared Function GetCustomersByCity (city As String) As System.Data.Common.DbDataReader
		Using cmd As New SqlCommand("CustomersByCity", CreateConnection)

			cmd.CommandType = Data.CommandType.StoredProcedure
			cmd.Parameters.AddWithValue("@city", city)
			Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

		End Using	'cmd
End Function

Select allOpen in new window

Sorry, no time to revise this morning. I hope that I did not make too many mistakes.

Sorry - I'm totally lost..

Here is my Form code (Application Code)

I'm trying to fill a combobox and print the list of staff members

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim stafflist As New StaffClass(False, False)

        ComboBox1.DataSource = stafflist
        ComboBox1.DisplayMember = Name
        ComboBox1.ValueMember = staffno

        'Get the list of customers that bought something on the given date
        For Each staff As staff In stafflist
            Debug.WriteLine(staff.name)
        Next
    End Sub
End Class

Select allOpen in new window

This is the Business Class that is called from the Application

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports StaffClass1

Public Class StaffClass

    Inherits List(Of StaffClass)

    Public Property staffno As Int16
    Public Property name As String

    Public Sub New(Optional ByRef inactive As Boolean = False, Optional ByRef systemnames As Boolean = False)
        Dim drd As DbDataReader
        drd = StaffDataClass.GetStaffNames(inactive, systemnames)
        While drd.Read
            Dim staff As New StaffClass
            staff.staffno = drd.GetInt32(0)
            staff.name = drd.GetString(1)
            Me.Add(staff)
        End While
    End Sub
End Class

Select allOpen in new window

And Lastly the Data Access Code

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports StaffClass1.SqlClass

Public Class StaffDataClass
    Shared Function GetStaffNames(Optional ByRef inactive As Boolean = False, Optional ByRef systemnames As Boolean = False) As System.Data.Common.DbDataReader
        Dim sqlstring As String

        sqlstring = " Select staffno,name from staff "

        If inactive = False Then
            If systemnames = False Then
                sqlstring &= " where isactive = 'Y' and staffno not in (1,2)"
            Else
                sqlstring &= " where isactive = 'Y'"
            End If
        Else
            If systemnames = False Then
                sqlstring &= " where staffno not in (1,2)"
            End If
        End If

        Using cmd As New SqlCommand(sqlstring, ConnSQL)

            Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

        End Using   'cmd
    End Function
End Class

Select allOpen in new window

I Think that data access code might be OK but the others are wrong.
(Note that the Connsql is calling the SQL Connection string that is working on the other tests)


The Data access code is selecting the data from the database and returning a dbdatareader
I don't think I'm getting the data out of the reader in the business layer though. - I seem to be declaring New Classes to many times?
Then the Application code is totally wrong. :(

First of all, the name of your class is a little overboard. Because almost everything we work with the framework is a class, it becomes repetitive and useless to add a Class prefis at the end of a class. It would be like adding Variable at the end of each of your variable names. I would rather call you class StaffMember.

Your problem is that you forgot something, the Collection.

My code was not complete, I did not give you the code for the Customer class (equivalent to your StaffClass). I thought you were OK with that part. If you look at my second piece of code, its a CustomerCollection, in which I add individual Customer objects. These are 2 objects. Note that for the collection, the convention holds that you add the word Collection at the end, as a way to see right there that it is a Collection of Customer objects.

You do the job of both classes inside one. So everytime that you create a StaffClass, you fill it with the whole collection of all the StaffClass objects.

Your second piece of code should thus be separated in 2 classes. The one for the Staff and the one for the Collection:

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports StaffClass1

Public Class StaffMember

    Public Property staffno As Int16
    Public Property name As String

End Class

Public Class StaffCollection

    Inherits List(Of StaffMember)

    Public Sub New(Optional ByRef inactive As Boolean = False, Optional ByRef systemnames As Boolean = False)
        Dim drd As DbDataReader
        drd = StaffDataClass.GetStaffNames(inactive, systemnames)
        While drd.Read
            Dim staffMember As New StaffMember
            staff.staffno = drd.GetInt32(0)
            staff.name = drd.GetString(1)
            Me.Add(staffMember)
        End While
    End Sub
End Class

Select allOpen in new window

And a little question. Why do you define your StaffNo property as a Short (int16), but use GetInt32 to read it from the database. Things like that can cause problems down the line. Because your source is the database, you sould usually stick to that type and its equivalent in .NET for all your manipulations.

Last note, note how I spelled your StaffNo property, and not staffno. And in the Read, I declare the variable staffMember. You see the differences in capitalisation. This might seem insignificant, but they help you a lot when you reread our code. And I know companies where stuff like that would be taken into consideration if you ever applied for a job and had to compete against other programmers in a skill test.

These are but conventions, but very useful ones. Almost all professional programmer you will encounter use the sames, the ones recommended by Microsoft by the way. Look for Naming conventions on MSDN if you want a complete list of the conventions.

But basically, local variable names should start with a lowercase letter. Names of classes, properties, methods and Events starts with Uppercase. That way, just by looking at the capitalization, you can see at a glance whether it is local or not.

When a name is composed of 2 words (or more), you put an uppercase at the beginning of each Extra word. easiertoread is hard to understand at fisrt glance. But EasierToRead is.

Thanks again James

I didn't realise that the Integer from SQL was the int32, I'll have to be more careful with the Data Types.

Thanks for the Naming Conventions tips.

I've got the staff Class accessing the database and a Combobox filling with the Staff Names.
When you select a Name the Textboxes fill with the class properties.
You can edit these then Pressing the save button is saving back to the database.
All Good :) - But :)

Is the Saving back though the Classes correct?
(It seems it will be a lot of work when there are 20+ Properties - Is there another way I should / Could be doing it?)

Application Code

Public Class Form1
    Private Sub LoadButton_Click(sender As Object, e As EventArgs) Handles LoadButton.Click

        Dim staffList As New StaffCollection(False, False)

        StaffMemberCombobox.DataSource = staffList
        StaffMemberCombobox.DisplayMember = "Name"
        StaffMemberCombobox.ValueMember = "staffno"

        'Get the list of customers that bought something on the given date
        For Each staff As StaffMember In staffList
            Debug.WriteLine(staff.name)
        Next
    End Sub

    Private Sub StaffMemberCombobox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles StaffMemberCombobox.SelectedIndexChanged
        Dim person As StaffMember = StaffMemberCombobox.SelectedItem
        StaffNoLabel.Text = StaffMemberCombobox.SelectedValue.ToString
        NameTextbox.Text = person.name
        JobTitleTextbox.Text = person.jobTitle
        PhoneTextbox.Text = person.phone
        HomePhoneTextbox.Text = person.homePhone
        IsActiveCheckbox.Checked = person.isActive
    End Sub

    Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        StaffMember.SaveStaffMember(Convert.ToInt32(StaffNoLabel.Text), NameTextbox.Text, JobTitleTextbox.Text, PhoneTextbox.Text, HomePhoneTextbox.Text, IsActiveCheckbox.Checked)
    End Sub
End Class

Select allOpen in new window

Business Class Code

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports StaffClass1

Public Class StaffMember

    Public Property staffNo As Int16
    Public Property name As String
    Public Property jobTitle As String
    Public Property phone As String
    Public Property homePhone As String
    Public Property isActive As String

    Shared Sub SaveStaffMember(ByRef staffNo As Int32, ByRef name As String, ByRef jobTitle As String, ByRef phone As String, ByRef homePhone As String, ByRef isActive As Boolean)
        StaffDataClass.SaveStaff(staffNo, name, jobTitle, phone, homePhone, isActive)
    End Sub

End Class

Public Class StaffCollection

    Inherits List(Of StaffMember)

    Public Sub New(Optional ByRef inActive As Boolean = False, Optional ByRef systemNames As Boolean = False)
        Dim drd As DbDataReader
        drd = StaffDataClass.GetStaffNames(inActive, systemNames)
        While drd.Read
            Dim staffMember As New StaffMember
            staffMember.staffNo = drd.GetInt32(0)
            staffMember.name = drd.GetString(1)
            staffMember.jobTitle = drd.GetString(2)
            staffMember.phone = drd.GetString(3)
            staffMember.homePhone = drd.GetString(4)
            staffMember.isActive = Convert.ToBoolean(drd.GetString(5))
            Me.Add(staffMember)
        End While
    End Sub
End Class

Select allOpen in new window

Data Access Class Code

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports StaffClass1.SqlClass

Public Class StaffDataClass
    Shared Function GetStaffNames(Optional ByRef inActive As Boolean = False, Optional ByRef systemNames As Boolean = False) As System.Data.Common.DbDataReader
        Dim sqlString As String

        sqlString = " Select staffno,isnull(name,''),isnull(jobtitle,''),isnull(phone,''),isnull(homephone,''),case isnull(isactive,'Y') when 'Y' then 'true' else 'false' end from staff "

        If inActive = False Then
            If systemNames = False Then
                sqlString &= " where isactive = 'Y' and staffno not in (1,2)"
            Else
                sqlstring &= " where isactive = 'Y'"
            End If
        Else
            If systemNames = False Then
                sqlString &= " where staffno not in (1,2)"
            End If
        End If

        Using cmd As New SqlCommand(sqlString, ConnSQL)

            Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

        End Using   'cmd
    End Function

    Shared Sub SaveStaff(ByRef staffNo As Int32, ByRef name As String, ByRef jobTitle As String, ByRef phone As String, ByRef homePhone As String, ByRef isActive As Boolean)
        Dim isActiveString As String
        If isActive = True Then
            isActiveString = "Y"
        Else
            isActiveString = "N"
        End If
        Dim sqlString As String
        sqlString = "name = '" & name & "', jobtitle ='" & jobTitle & "', phone ='" & phone & "', homephone = '" & homePhone & "', isactive = '" & isActiveString & "'"
        SqlClass.SQL_Update_Basic("staff", sqlString, "staffno = " & staffNo.ToString)
    End Sub
End Class

Select allOpen in new window

You will Notice I am checking for Null Values at the SQL Level and replacing with empty strings - Is this the correct way/place to do it?

I am also changing the "Y" and "N" values to a Boolean and back again at the SQL level -Is there a better way to do it? (The accounting database uses Y and N for True and False)

First, the name of your methods. Just like naming conventions, their role is to make things easier to read and understand.

A method name should be descriptive, so at first sight something like SaveStaffMember might seem appropriate, but your not creating a generic methods that will be called from a module. This is used with an object that is a StaffMember. It can only be used with a StaffMember. And when you use it, you know that you are using a StaffMember. If you don't, it's probably because the name of the variable is not descriptive enough. Your method should simply be called Save.

Look at the framework for examples of how to do different things. Does if has a CloseForm, CloseConnection or a CloseFile methods. No, they are all called simply Close. When you use Close, because it is always through an object, it's very clear what your are closing.

Another thing before answering your question. Drop the ByRef. It was the default in VBA for different reasons, mainly because when it was created, VB was slow and computers were slow and did not have as much memory as today. The default in VB.NET is ByVal, and with good reasons that would be too long to explain here. There are still a few cases that come in from time to time that might require ByRef, but most of the time you see ByRef in VB.NET code, it's because the programmer lacks in his knowledge of how to use VB.NET or object oriented programming in general.

A couple of comments about your SaveStaff method... and you database design if you have control over it.

Database first. If your isactive field can hold only 2 values, it would be better defined as a bit (boolean) field. It's more efficient than any type of text field, both in the code and in the database, would not require a conversion as you are doing here and whenever you want to use that value with a CheckBox.

And although it might just be a question of preference, I think I have already discussed that, I do not find that you gain much by having a generic class to to these things. If they were that useful, they would have been built into the framework.

Finally, although it might be something that you might want to handle later, building SQL strings code is not efficient, can lead to security problems, and is hard to debug and maintain. You might put on your "Technologies to learn" checklist an entry on learning how to work with ideally stored procedures, or at least parametized queries.

Thanks James

No - I don't have any control over the database design as it's linked to the accounting system.
I use Stored Procedures with many things but not for simple things like this update.


Quick Explanation of how I "Search" for a Product in my VBA project.

User types some text into a Textbox and when they exit the Textbox a SQL string is sent to the database.
If only ONE product is found then the required data is returned
If MORE than one product is found then another Form opens with a list containing all the returned records.
The User can then either

Is this basically the way to go in vb.net?

Or should I

Is keeping data in memory for searching a good idea?
Is it FASTER with the data in memory (Over calls to a SQL server on the Network)

How much Data?

These are searched constantly all day and searching must be FAST, As fast as possible.

I was thinking of only loading the "Search Data" into memory and always selecting the current data from the database once a selection has been made. This "Search Data" would be relevantly Static - So a daily update (On application start) would be fine

Would the speed improvement (If any) be worth the effort?

The Application will have about 30 Users using it most of the time

Actually thinking it over further - Keeping data in memory won't work as there are some fields that will need to have the current data displayed when searched (Product stock levels for instance)

But is the way I attempted to explain the searching the best way?
Or is there a New way in vb.net?

Keeping data in memory is usually not a good way to go. You are just using a bunch of resources for stuff that is never used by the user.

This is specially true with some data containers such as DataTable objects and the different types of grids that you encounter in .NET, because the advantages they might offer require a lot of overhead.

With the speed of modern databases and data transmissions, round trips to the database are usually a snap, specially if you request as little the data as possible. So, in most situations, you try to limit what you bring into memory to what the user really needs. Go back to the database only when you need more.

So your way of thinking is the right one. It's just a little more work on your part, and a lot less than what it could have been in the previous systems when you look at all the tools that .NET provides you with. But it usually enhance performance in a big way. One of the questions that is most often asked is something like "How can I get 10,000 rows in my DataGridView. It's so slow.". Look like you understand why.

What is the Best way to create a SQL String with multiple like causes on multiple columns?

What I need is
User inputs "one two -three-"
I'm searching on Columns Col1 Col2

SQL Statement
Select Col3,Col4 from table where
        (Col1 like '%one%' or col2 like '%one%' )
and
        (Col1 like '%two%' or col2 like '%two%' )
and
        (Col1Not like '%three%' or col2 Not like '%three%' )

(Not if the work is in negative signs the SQL string changes to NOT Like)

The way I did it in VBA was
I had a function that took the Search Text, Search Columns as parameters (Note these are in one string separated with a ; )
This looped through the search words, and depending if it was negative or not called another function that looped through the columns and returned the string.

Below is the Functions
(Note they have some extra parameters these are only used when the user ticks an "Extended Search" Check box that then uses additional columns in the search.

Do I follow these same lines in vb.net or some new way?

Would this kind of function slow things down over hard coding looping through each column in each class?

Public Sub Full_Text_Search(searchtext, searchfields As String, extsch As Boolean, extschfields As String)

    'Declare Variables
    Dim Extfields As Variant
    Dim Nooffields As Integer
    Dim cuttext As String
    Dim textpos As Variant
    Dim trimed As String
    Dim NoofParam As Integer
    Dim f As Integer
    Dim p As Integer
    Dim partsearchtext As String
    
    On Error GoTo trapped
    ErrPos = "Full_Text_Search"
    
    'Clear Variables
    fullsearchtext = ""
    textpos = 0

    cuttext = Application.WorksheetFunction.Trim(searchtext)

    If extsch = True And extschfields <> "" Then
        
        'Remove Excess Spaces then count the ";" to find the number of fields
        cuttext = Trim(extschfields)
        Nooffields = (Len(cuttext) - Len(Replace(cuttext, ";", "")))
        
        'Re declare the Extfields array to the number of fields
        ReDim Extfields(Nooffields)

        'Loop through the search fields adding them to the fields array
        For f = 0 To Nooffields
            'Continue on error (No ";" found)
            On Error Resume Next
            textpos = Application.WorksheetFunction.Search(";", cuttext)
            On Error GoTo trapped
                
            'Select search field
            trimed = Trim(Replace(Left(cuttext, textpos), ";", ""))

            'Add the search field to the array
            Extfields(f) = trimed
            
            'Remove search field from cuttext variable
            cuttext = Application.WorksheetFunction.Replace(cuttext, 1, textpos, "")
        Next f
        
        'Remove Excess Spaces then count the spaces to find the number of search words
        cuttext = Trim(searchtext)
        NoofParam = (Len(cuttext) - Len(Replace(cuttext, " ", "")))
        fullsearchtext = "("
        
        For p = 0 To NoofParam
            'Continue on error (No space found)
            On Error Resume Next
                textpos = Application.WorksheetFunction.Search(" ", cuttext)
            On Error GoTo trapped
    
                'if this is the last Parameter then don't cut the search string
                If p = NoofParam Then
                    trimed = cuttext
                Else
                    'Select search word
                    trimed = Trim(Left(cuttext, textpos))
                End If

                'Loop through every search field
                For f = 0 To Nooffields
                    'if this is the first search field don't add "Or"
                    If f = 0 Then

                    'Add search field and search word to fullsearchtext variable
                        fullsearchtext = fullsearchtext & Extfields(f) & " like '%" & trimed & "%'"
                    Else
                        fullsearchtext = fullsearchtext & " Or " & Extfields(f) & " like '%" & trimed & "%'"
                    End If
                Next f
                
                'If this is the last search word then don't add "And"
                If p = NoofParam Then
                    fullsearchtext = fullsearchtext & ")"
                Else
                    fullsearchtext = fullsearchtext & ") And ("
                End If
            
            'Remove search word from cuttext variable
            cuttext = Application.WorksheetFunction.Replace(cuttext, 1, textpos, "")
            
        Next p
        
    Else
            
        Do Until textpos = ""
            textpos = ""
            
            partsearchtext = "(("

            'Continue on error (No space found)
            On Error Resume Next
                textpos = Application.WorksheetFunction.Search(" ", cuttext)
            On Error GoTo trapped
            
            'If space found
            If textpos <> "" Then

                'Select search word
                trimed = Trim(Left(cuttext, textpos))

                'Check for searchword enclosed in - (Exclude from search)
                If Left(trimed, 1) = "-" And Right(trimed, 1) = "-" Then
                
                    'Remove the - from the searchword
                    trimed = Replace(trimed, "-", "")
                    

                    Call SearchFieldLoopNotLike(searchfields, trimed, partsearchtext)
                
                    'Add search word to fullsearchtext variable
                    fullsearchtext = fullsearchtext & partsearchtext
                
                Else
                    
                    Call SearchFieldLoopLike(searchfields, trimed, partsearchtext)
                    
                    fullsearchtext = fullsearchtext & partsearchtext
                
                End If

                'Remove search word from cuttext variable
                cuttext = Application.WorksheetFunction.Replace(cuttext, 1, textpos, "")

            End If
            
        Loop

            'Check for the last searchword enclosed in - (Exclude from search)
            If Left(cuttext, 1) = "-" And Right(cuttext, 1) = "-" Then

                'Remove the - from the searchword
                cuttext = Replace(cuttext, "-", "")

                fullsearchtext = fullsearchtext & " (("
                'Add last search word to fullsearchtext variable
                Call SearchFieldLoopNotLike(searchfields, cuttext, fullsearchtext)
                
                fullsearchtext = Left(fullsearchtext, (Len(fullsearchtext) - 4))

            Else
            
                'Add last search word to fullsearchtext variable
                fullsearchtext = fullsearchtext & " (("
                'Add last search word to fullsearchtext variable
                Call SearchFieldLoopLike(searchfields, cuttext, fullsearchtext)
                
                fullsearchtext = Left(fullsearchtext, (Len(fullsearchtext) - 4))

            End If
    End If
    On Error GoTo 0
    Exit Sub
trapped:
    Call InsertError("ModuleSearch", ErrPos, Error)
    On Error GoTo 0
End Sub
Public Sub SearchFieldLoopNotLike(searchfields As String, SearchKey As Variant, partsearchtext As String)

    'Declare Variables
    Dim cuttext As String
    Dim textpos As Variant
    Dim trimed As String
    textpos = 0

    cuttext = Application.WorksheetFunction.Trim(Replace(searchfields, ";", ","))
    
    Do Until textpos = ""
        textpos = ""

        'Continue on error (No Comma found)
        On Error Resume Next
            textpos = Application.WorksheetFunction.Search(",", cuttext)
        On Error GoTo 0
        
        'If Comma found
        If textpos <> "" Then
            
            'Select search word
            trimed = Replace(Trim(Left(cuttext, textpos)), ",", "")
            
            'Add search word to fullsearchtext variable
            partsearchtext = partsearchtext & trimed & " not like '%" & SearchKey & "%') AND ("
            
            'Remove search word from cuttext variable
            cuttext = Application.WorksheetFunction.Replace(cuttext, 1, textpos, "")
            
        Else
            'Add search word to fullsearchtext variable
            partsearchtext = partsearchtext & cuttext & " not like '%" & SearchKey & "%')) And "
        End If
    Loop
End Sub
Public Sub SearchFieldLoopLike(searchfields As String, SearchKey As Variant, partsearchtext As String)

    'Declare Variables
    Dim cuttext As String
    Dim textpos As Variant
    Dim trimed As String
    textpos = 0

    cuttext = Application.WorksheetFunction.Trim(Replace(searchfields, ";", ","))
    
    Do Until textpos = ""
        textpos = ""

        'Continue on error (No Comma found)
        On Error Resume Next
            textpos = Application.WorksheetFunction.Search(",", cuttext)
        On Error GoTo 0

        'If Comma found
        If textpos <> "" Then

            'Select search word
            trimed = Replace(Trim(Left(cuttext, textpos)), ",", "")
            
            'Add search word to fullsearchtext variable
            partsearchtext = partsearchtext & trimed & " like '%" & SearchKey & "%') OR ("
            
            'Remove search word from cuttext variable
            cuttext = Application.WorksheetFunction.Replace(cuttext, 1, textpos, "")
            
        Else
            'Add search word to fullsearchtext variable
            partsearchtext = partsearchtext & cuttext & " like '%" & SearchKey & "%')) And "
        End If

    Loop
End Sub

Select allOpen in new window

Your code is too long for the little time I need to analyze it.

Let just me say that you should do as much work as you can at the database level. This means learn to work with stored procedures. Stored procedures are like procedures, with variables, IF and SELECT CASE statements, mix with more standard SQL statements. They cannot do everythings, but as long has to do with the basic data in the database, nothing beats them for security and maintenance. And because they run on the server, they are more efficient that anything you could do from your application. And since the job is done at the database level, there is less data to transmit to the application, so it moves faster across the network.

You call them with a Command object, just as you do for SQL that you build in the code. You also handle the result exactly the same way.

They are the recommended way to go for almost everything, no matter if it is .NET or something else.

Earlier you commented not to make a Custom Class for something as simple as a list of Strings - How do I go about getting the values to populate a list from the database without a Custom Class?
(I am using the 3 Tier Application Model)

The framework contains a lot of different Collection classes that can easily be used to prepare a list of any type of object. Each has a specific purpose, but something like the System.Collections.Generics.List (Of T) is a good basic choice if you do not have special needs and only require a simple list.

If need be, you can always inherits from any of these collections to build your own. This could be useful if you need to have different constructors for different purposes, or add properties and methods that are not already provided in the basic collections. But if all you want to do is add and remove objects in a list, no need to create your own class for such a purpose.

If you need to display the list, almost all of the built-in collections can fill a control such as a ComboBox of a Grid with only one line of code, by setting it to the DataSource property of the control.

Getting somewhere (I Think)

When do I need to Dispose of an Object?

I have a Form that has some List (of T) collections declared at the beginning that are bound to Combo boxes.
Do I need to Dispose of these Objects or will they be automatically disposed of when the form closes?

Public Class ActivityQuickAdd

    Dim StaffNameList As New List(Of StaffMemberName)
    Dim ActivityTypeList As New List(Of ActivityType)
    Dim ActivityStatusList As New List(Of ActivityStatus)
    Dim NewActivity As New Activity

    Private Sub ActivityQuickAdd_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'MsgBox("Before Staff")
        StaffNameList = New StaffNameCollection(False, False)

        With AssignToStaffComboBox
            .DataSource = StaffNameList
            .ValueMember = "ID"
            .DisplayMember = "Name"
            .SelectedIndex = -1
        End With
        'MsgBox("Before Type")
        ActivityTypeList = New ActivityTypeCollection()

        With ActivityTypeComboBox
            .DataSource = ActivityTypeList
            .ValueMember = "ID"
            .DisplayMember = "Name"
            .SelectedIndex = -1
        End With
        'MsgBox("Before Status")
        ActivityStatusList = New ActivityStatusCollection()

        With ActivityStatusComboBox
            .DataSource = ActivityStatusList
            .ValueMember = "ID"
            .DisplayMember = "Name"
            .SelectedIndex = -1
        End With

    End Sub


End Class

Select allOpen in new window

The Following code is to search the database when the user exits the Textbox after entering some Text.

Is this the Best way to add data to the Database? - I'm creating a New Object (NewActivity) when the form is created then when a Control is updated I am updating the NewActivity Object to the New Value, Then when The user presses the save button I just send the NewActivity Object to the Save in the BLL DLL.
Or should I be LINKING the Controls directly to the Object?

Private Sub CompanyNameTextBox_Leave(sender As Object, e As EventArgs) Handles CompanyNameTextBox.Leave

        If CompanyNameTextBox.Text = "" Then
            NewActivity.CompanyID = ""
            Exit Sub
        End If

        Dim companySearchList As New CompanyNameCollection(CompanyNameTextBox.Text, False)

        If companySearchList.Count = 1 Then
            Dim company As CompanyName = companySearchList.Item(0)
            CompanyNameTextBox.Text = company.Name
            NewActivity.CompanyID = company.ID
            company = Nothing
        Else
            Using CompanySearchForm = New CompanySearch()
                With CompanySearchForm
                    .SearchTextBox.Text = CompanyNameTextBox.Text
                    .SearchResultDataGridView.DataSource = companySearchList
                    .ShowDialog()
                End With
                NewActivity.CompanyID = CompanySearchForm.valueID
                CompanyNameTextBox.Text = CompanySearchForm.valueName
            End Using
        End If
        companySearchList = Nothing
    End Sub

Select allOpen in new window

Sorry for the delay in answering, I was in the training room all day (and will be all week next week), so I have only a few hours in the evening to answer questions.

Dispose is not called automatically when you leave a method or even close an application. It will eventually be called by the Garbage Collector, the object that manages the destruction of objects in the framework.

So it is suggested that whenever a class has a Dispose method, you call it yourself before you lose the variable. This could release external stuff as soon as it is needed, and can solve some problems such as files that stays in a lock state.

An alternative to calling Dispose is to declare the variable with a Using block. This will call Dispose automatically at the end of the block.

-----

Leave is OK if you do not need to validate the user's input. Validating is better if you need to validate and cancel the exit from the control when the stuff that was typed in is not valid. You can also prevent the event from occuring in some circumstances, such as the user clicking on a Help button.

-----

If what you call Notes means comments in the code, you should put it everywhere. What seems obvious now might be hard to understand if you have to work again on that code in a few weeks, months or even years.

-----

I see that you already know about Using. Yes, that is the recommended way.

Thanks very much.

I Didn't know what the Using was for - It was just in the code from another Question..
I'll use Using around the form code to auto dispose the collections that are bound to the combo boxes.

To get the Code from the Controls to the Save method in the DLL I am

When the User Presses Save

    Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        Activity.Save(NewActivity)
        Call ClearForm()
    End Sub
    Private Sub ClearForm()
        NewActivity = Nothing
        NewActivity = New Activity
        With Me
            .CompanyNameTextBox.Text = ""
            .ContactNameTextBox.Text = ""
            .ActivityStatusComboBox.SelectedIndex = -1
            .ActivityTypeComboBox.SelectedIndex = -1
            .AssignToStaffComboBox.SelectedIndex = -1
            .DetailsTextBox.Text = ""
            .StartDateTimePicker.Value = Date.Today
        End With

    End Sub

Select allOpen in new window

I just want to make sure this is correct?
Is it OK/Correct to set an object to Nothing (To Clear the Data) then set it to a New Instance of a Class?
Or is this leaving the Old instance floating somewhere?
(This form will be staying open for hours and this will be cycling through the save sub constantly)

There are very few cases in .NET where setting to Nothing is necessary, because it is done automatically for you when you reach the End Sub.

Setting a variable to Nothing used to be necessary up to VB3 (End Sub did not always do it), before the constructor (New) came into the language. But this changed in VB4 (around 1995). And we are now 20 years later at VB14. But old habits are hard to lose for many programmers, and they transmit them to younger ones. So you still see a lot of programmers setting variables to Nothing all over the place, but it is most often useless to do so.

From VB4 to VB6, it used to be useful to release memory when you were working with big objects and still had work to do that had a need for a lot of memory before reaching the End Sub.

But in .NET, setting a variable to Nothing does not clear the memory as many still think it does. It simply disconnect the variable from the block of memory that holds the properties of the object, and marks that block as being available for collection by the garbage collector. Memory will be freed only when the garbage collector decides that it needs that block of memory for something else.

There is nothing bad in setting the variable to Nothing, but in your case, since the following line that assigns the variable to another object does the same thing, so it is only redundant. And if you did not have that following line, you would hit the End Sub so fast that setting that variable to Nothing has absolutely no impact.

Hello James

I was going to use "Using" to dispose of the variables that are declared at the start of the Class, But I can't as Using can only be used inside a Method.

Do I need to dispose of these 4 Variables when the Form Closes? If So then How can I do it?

Public Class ActivityQuickAdd

    Dim StaffNameList As New List(Of StaffMemberName)
    Dim ActivityTypeList As New List(Of ActivityType)
    Dim ActivityStatusList As New List(Of ActivityStatus)
    Dim NewActivity As New Activity

    Private Sub ActivityQuickAdd_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'MsgBox("Before Staff")
        StaffNameList = New StaffNameCollection(False, False)

        With AssignToStaffComboBox
            .DataSource = StaffNameList
            .ValueMember = "ID"
            .DisplayMember = "Name"
            .SelectedIndex = -1
        End With
        'MsgBox("Before Type")
        ActivityTypeList = New ActivityTypeCollection()

        With ActivityTypeComboBox
            .DataSource = ActivityTypeList
            .ValueMember = "ID"
            .DisplayMember = "Name"
            .SelectedIndex = -1
        End With
        'MsgBox("Before Status")
        ActivityStatusList = New ActivityStatusCollection()

        With ActivityStatusComboBox
            .DataSource = ActivityStatusList
            .ValueMember = "ID"
            .DisplayMember = "Name"
            .SelectedIndex = -1
        End With

    End Sub


End Class

Select allOpen in new window

You dispose of a variable when you have finished working with it, so it can be anywhere. But since members of a class (a form is a class) are usually used throughout the form, FormClosing is often the best place.

And the way to do that is simply to call the Dispose method. This is the heart of the thing. Classes that are better to be disposed all have a Dispose method. It's that method that does the job. Using is simply a way to make sure that Dispose will be called, no matter what. If you have an Exception, Dispose will be called. If you have a complex Select Case with imbricated If blocks in it, it can be easy to forget one or two calls to Dispose here and there. Using insure that it will be called.

So in the Previous Code example how do I dispose of the Variable StaffNameList?

StaffNameList.dispose
Dispose(StaffNameList)

Both give Errors

Does it not having a dispose method mean it doesn't need to be disposed?

Yes. Not all classes have a Dispose method. And those who do not will not work with Using either.

If all the world was .NET, there would not be a need for a Dispose method. But underneath .NET, we still have Windows and COM. They all deal with memory cleanup in a different way. .NET is able to clean up things in its own memory space. But it cannot take care efficiently of COM objects and memory allocated directly by Windows. That is the role of Dispose.

A Form has a Dispose because the basic controls are not provided by .NET, but by Windows. The .NET class that you use for a Button for instance gets its Button from Windows. A StreamWriter also has a Dispose method, because the file handles are also provided by Windows.

But classes that deal only with basic types such as Integer, Strings and Dates do not require a Dispose. These types of objects are completely handled by the framework, in the memory block managed by .NET (you have seen the term "managed code" in the documentation, didn't you?). So .NET knows when an object is not needed anymore and also knows how to dispose of it in the most efficient way possible.

The framework thus automatically dispose of its own objects, but needs help with stuff that lies outside of its own memory structure.

Absolutely Top help and Advise.
Can't thank you enough James for the Time you have put into this - I very much doubt I would have got started without it.

Thanks again

Your question where good ones and you evolved rapidly through all of this. It is always a pleasure to help somebody in such a situation.