Class definitions: VBA vs. VB .NET

[Ed Sowell]

My books are for VBA circa late 1990s. User defined Classes are discussed,
but there is no header per se. That is, private data members and public
member functions are shown in a "class module," but there is no:

Public Class myClass
....
End Class

However, when I insert a Class in Visual Studio .Net ( ver 7) such
headers/footers appear.

Is this just a syntax change, or is ther something more subtle lurking?

TIA

Ed

 

[Scott M.]

VBA is not remotely the same as VB.NET and you should not try to compare the
two.

-Scott

 

[sloan]

vb and vb.net are like java and javascript.

Something close by name (only), but not close at all......

Dump your 1990's book.

 

[Ed Sowell]

Sloan, Scott,

Thanks. Any recommendations for a good book on .NET? I just trying to set up
a fairly simple Web app... one or two forms that access and modify a small
database that are currently set up in Excel/VBA.

Ed

 

[sloan]

You could probably learn alot if you took this c# app and converted it to
vb.net.


http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry


That's my 1.1 example of a layered application.
(DataLayer,BusinessLayer,PresentationLayer)
Read through the1.1 example. Go here:
http://sholliday.spaces.live.com/feed.rss
and find the 2.0 version of that same concept. Do NOT skip the 1.1
code.......it has more in it.


This can show you how to read excel data.
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
You say the old one is Excel, but you don't mention if you want the new one
to be in excel.
This blog entry will show you how to make your datastore less important as
far as the overall architecture.


Again, if you took the time to convert the project, line by line, (not some
webconverter that just converts the code) you could probably learn alot.

You would want to build your app like this.

New Solution
Add DAL.DataSets project.
Add DAL project.
Add BAL project.

Convert the DAL code first from C# to vb.net. With the understanding you'll
have to create a strong DataSet in DAL.DataSets from time to time.
Conver the BAL code (by referencing the DAL and DAL.DataSets projects)

Good luck.

 

[Cor Ligthert[MVP]]

http://www.vb-tips.com/FreeOnlineBooks.aspx

Sloan, Scott,

Thanks. Any recommendations for a good book on .NET? I just trying to set
up a fairly simple Web app... one or two forms that access and modify a
small database that are currently set up in Excel/VBA.

Ed

 

[Scott M.]

Hi Ed,

I really like this:
http://www.amazon.com/Professional-...=sr_1_1?ie=UTF8&s=books&qid=1239137803&sr=8-1

Also, while you certainly can use Exce as a data repository, I wouldn't
really call it a database and, as such, you are going to have to use what is
called COM InterOp to access and modify it. COM InterOp has a performance
toll that is always paid.

-Scott

 

[Ed Sowell]

Thanks, Sloan. I gave it a try, but the .sln file won't open with my 2002
version of Visual Studio .NET. Are there some particular files that I can
look at that show the code for connecting to a CVS or XLS file?

Ed

 

[Ed Sowell]

Thanks Scott. I'll take a look, but the VB I'm working with is that which is
part of Visual Studio version 7.0, circa 2002.

I realize Excel is not a database, but it seemed to be about right for the
project I have been working on, membership records for a car club I belong
to, Jaguar Owners Club of LA. It was set up in Excel by the person who had
the membership job before me, and I set out to automate the various tasks
using VBA. It works pretty well, and I have now handed over the day-to-day
to another member while I keep "improving" the AddIn package I wrote.

My current effort is to make it easier for the membership person to properly
describe the cars of new members. To that end I have set up a worksheet with
a row for each Jaguar model from 1931 to the present (fewer than 200). Each
row has a club pre-approved description, e.g., "E-Type OTS", and about 10
descriptor that apply to that car, e.g., primary Model designation (E-Type,
XJ-S, etc), Top (Soft or Hard), number of doors, number of cylinders, and of
course the first and last year for the car. The idea is the user sees a form
with all the descriptors set to "All", in which case all the Jags ever built
are shown in a listbox. As one or more descriptors are set to something
other than All, the list shrinks until a single car is in the list, which is
selected and its description is placed in the main membership worksheet. I
have implemented this in VBA and it works great. The only problem is being
sure I've got all the Jags in there, and the descriptions are correct. To
that end, I want to deploy the description selection process as a Web
application so Jag owners around the world can poke at it and give me
feedback. For this purpose I figured it would be better to implement it
independent of Excel, since I don't think Excel is made available by the
club's Web hosting service. Also, I have long wanted to learn something
about Web applications, just because I like to program.

I apreciate the pointers you and others here have given me.

Ed

 

[Scott M.]

Hi Ed,

Certainly, I understand that if an ain't broke, don't fix it. But, let me
just give you a little advice on the crossroads you face here.

If you use .NET to access a COM object (like Excel), you will be including a
bottleneck into your application as you introduce Runtime Callable Wrappers
(RCW's) into the deisgn, these wrappers help your objects persist via
interfaces that they were not initially created to emulate. This is usually
accompanied by performance slowdowns AND opportunites for memory leaks, if
this situation is not properly cleaned up (there are special concerns that
must be addressed when cleaning up COM objects utilized in .NET
applications).

Secondly, making your Excel data available to multiple people via a web
interface is likely to cause file locking scenarios that prevent more than
one user from accessing the data at the same time and it will require your
web ISP to have the MS Office Primary InterOp Assemblies (PIA's) available
on the server (which you may not be in control of). You wouldn't need Excel
on the web server, but you would need these PIA's.

With that in mind, you may want to seriously take a look at porting the data
to SQL Server, which would elimiate both of the issues above.

Good luck with your project!

-Scott

 

[Ed Sowell]

Thanks, Scott. If I understand you correctly, you are recommending leaving
Excel behind for the Web app. I agree. My plan is to to keep the good and
true data in an Excel workbook that is used only by two people, me and the
membership person. I will export the Model Table worksheet as CSV and move
it to the Web server, where I will read it into a dataset used by the Web
app. As to having conflicts with multiple users, I should be so lucky!
Anyway, is someone write me and says "Your model locater doesn't correctly
identify my car," then I'll modify the Excel worksheet, export it again and
put it on the Web server.

This afternoon I did succeed in loading the CSV file into a VB DataSet and
displaying it in a DataGrid, which is progress. Does that qualify as porting
the data into an SQL server? Now I have to learn how to manipulate the
DataSet, extracting subsets etc. Well, I wanted to learn something new, and
I am. With a little help from online friends. Thanks!

Ed

 

[Scott M.]

Hi Ed,

I think you still may find that using an Excel to .csv model will cause
problems for you.

You don't need to have high volume to have problems with file locks. Even
with just one user accessing your data on your web server, the files (excel
and the .csv) can become locked and/or corrupted easily. This is why I
suggest you leave that behind and move to a more robust data store like SQL
Server or MySQL (if your web ISP supports it).

-Scott

 

[Ed Sowell]

Thanks, Scott. But, I can't see how the file lock problem could develop if I
use the method suggested by Cor Ligthert (see my thread on CVS file as
database):

http://www.vb-tips.com/CSVDataSet.aspx

This code works except for a couple small problems. It reads the .CSV file
and stuffs it all into a DataSet. For the Web app I have no need to update
the .CVS file. I plan to extract various subsets of the table from the
DataSet and display it in a WebForm. That's it. Users of the Web app will
never see the .CVS file so i don't see how it could be corrupted. Or, what
am I missing?

On the other hand, I would not mind converting the .CVS file to a .MDB or
..MDF file if I could do it easily without getting too deeply into SQL. I did
a Google and found lots of CSV to SQL converters, but they all presume that
one is very familiar with SQL or PHP or whatever, which I am not. I played
with one or two of these tools ang could not get them to emit anything I can
replate to, e.g., a file that could point to in the Visual Studio .NET
connection dialog.

Ed

 

[Scott M.]

Hi Ed,

The moment a file, such as a .csv, is accessed, a lock is put on the file.
If that lock is not released, the next user won't be able to acess the file.
All it would take is for two users to attempt to access your page
simultaneously and that would do it. Or, it can happen without user
intervention if the web server were to be restarted while the file was being
accessed.

For this reason it is generally not a good idea to use a file as a data
store when accessed via the web. If you do decide to go to a database, I
would urge you NOT to go with an .mdb (Access) database as Access is
notorious for breaking down when more than just a couple of connections are
open. You don't have to know much about MySql or SQL Server to work with
basic tables and queries and you wind up having the data in a repository
that is made to support multiple simultaneous connections.

-Scott

 

[Tom Shelton]

Hi Ed,

The moment a file, such as a .csv, is accessed, a lock is put on the file.
If that lock is not released, the next user won't be able to acess the file.

That's true, by default. But, I must point out that if you know the file is
readonly - then it is perfectly safe to open it in shared mode, so that NO
lock is placed on the file... This would negate all of the other concerns.
Again, ONLY if the file is readonly, is this a practicle option.

Of course, looking further down the thread, the OP is using the OLEDB
dataprovider to read the data, and I'm completely unsure about how it handles
file locking...

Personallly, if I were the op, I would get rid of the csv completely, and use
the Office 2003 XML format for the worksheet. In fact, I do do this for some
stuff I've done recently. Once you understand the SpreadsheetML format, it's
not all that hard to read all of the data using an xpath query... It's even
easier in 2008 using LINQ to XML.

 

[Scott M.]

Agreed Tom.

My point here is that now would be the best time to think about a good data
store strategey going forward for the OP. IMHO, porting the data to SQL or
MySql would be the simplest thing to do.

-Scott

 

[Cor Ligthert[MVP]]

Disagreed Tom,

I once started with Net like Ed and that was with using an XML file to do
things (which became very quick a dataset).
Horrible to get that well because that you have to check that it is always
completely right rewritten.

I checked this thread because I have given Ed a solution how to use a CSV
file. But that has the same problems as an XML file or any serial file.

I would use in Ed's place direct an SQL Server Express database like the
advice of Scott especially now that this is freeware.

I direct this to you, but it is more for the OP that I write it like this.



Cor

 

[Tom Shelton]

Disagreed Tom,

I once started with Net like Ed and that was with using an XML file to do
things (which became very quick a dataset).
Horrible to get that well because that you have to check that it is always
completely right rewritten.

I guess I should say, that I would actually use a database solution
for this as well. I was only commenting on Scott's comments on file
locking.

I checked this thread because I have given Ed a solution how to use a CSV
file. But that has the same problems as an XML file or any serial file.

I mentioned the office xml, because I think for simple readonly data
as he's talking about is a much simpler solution. Especially when
using LINQ to XML. I would post code, but all of my stuff is in C#.
I have code to generate xml spreadsheets as well as read them. I also
have some code I've been working on to convert Microsoft Word XML
documents to WPF FlowDocuments.

Of course, xml has the same problems if you are having to lock the
file - what I was pointing out is that the OP is using the data in a
read only fashion. But, using the OLEDB reader could introduce
problems IF it performs file locking. Simply having two vistors hit
the site at the sametime can cause issues in that case. Using simple
file io, you can control that by opening the file in shared mode -
but, I personally like to work with XML over csv if you go in that
direction

I would use in Ed's place direct an SQL Server Express database like the
advice of Scott especially now that this is freeware.

I direct this to you, but it is more for the OP that I write it like this..

I agree that overall, the database is the best solution. But, that
really depends on his hosting provider as to what is available - and
what the OP is willing to learn, do etc. But, given the choice
between working with CSV and XML - I would take the XML.

 

[Cor Ligthert[MVP]]

Now we agree again (completely)



Cor

Disagreed Tom,

I once started with Net like Ed and that was with using an XML file to do
things (which became very quick a dataset).
Horrible to get that well because that you have to check that it is always
completely right rewritten.

I guess I should say, that I would actually use a database solution
for this as well. I was only commenting on Scott's comments on file
locking.

I checked this thread because I have given Ed a solution how to use a CSV
file. But that has the same problems as an XML file or any serial file.

I mentioned the office xml, because I think for simple readonly data
as he's talking about is a much simpler solution. Especially when
using LINQ to XML. I would post code, but all of my stuff is in C#.
I have code to generate xml spreadsheets as well as read them. I also
have some code I've been working on to convert Microsoft Word XML
documents to WPF FlowDocuments.

Of course, xml has the same problems if you are having to lock the
file - what I was pointing out is that the OP is using the data in a
read only fashion. But, using the OLEDB reader could introduce
problems IF it performs file locking. Simply having two vistors hit
the site at the sametime can cause issues in that case. Using simple
file io, you can control that by opening the file in shared mode -
but, I personally like to work with XML over csv if you go in that
direction

I would use in Ed's place direct an SQL Server Express database like the
advice of Scott especially now that this is freeware.

I direct this to you, but it is more for the OP that I write it like this.

I agree that overall, the database is the best solution. But, that
really depends on his hosting provider as to what is available - and
what the OP is willing to learn, do etc. But, given the choice
between working with CSV and XML - I would take the XML.

 

[Ed Sowell]

I much appreciate the discussion and am getting to know the problems, no
matter what the direction. After the tech at my hosting service told me I
couldn't read csv (not sure that is true, but it's what he said) with MySQL
(which is what they provide) I decided to convert my cvs file to MySQL on my
Windows machine. I then spent a couple days installing MySQL, reading the
manual, and posting questions to the MySQL Newbies Forum. It seems that
MySQL keeps all databases in what appears to be binary form, so it's not
simply a matter of moving my Web app files and a data file from my machine
to the hosting server after getting my Web app working locally. I'm advised
I have to export the database from MySQL on my machine, ftp it to the Web
host server, and then use MySQl_import on the host. I'm sure all of this can
be done, but the complexity builds. One thing I don't understand at this
point is how to do the import at the Web host server. The hosting service
does not allow telnet access, so apparently I can run MySQL in a command
window. How am I to do that? Guess that's a question for the Weh hosting
tech...

Thanks again.

Ed