Avatar of EYoung
EYoungFlag for United States of America asked on

Help with SQL Connection and command lines in multi-form VB 2008 Windows app?

I am new to VB 2008 but am experienced with VB6.  Recently I have been asked to develop a VB2008 Windows application that allows the users to maintain (Add, Change and Delete) records in three different tables  (TableA, TableB and TableC) within the same database (Purchasing_Module).

I have developed a VB2008 Windows application that has four forms and one Module.  The first form is the main form that has a menu that allows the users to click on which table they want to maintain.  The remaining three forms are for each of the three tables.  The Module contains commonly used variables defined as Public as I have done in in the past with VB6.

In VB6 I use to do the following:
   1.  Have a Module in which I would define the connection, command strings and other variables as Public.
   2.  Open the connection in the Main form and leave it open for the duration of the user's use of the application for each of the three table forms.  I only opened and closed the connection once.
   3.  Close the connection in the Main form's exit sub routine when the user exits the application.

In VB2008 I am not clear on how or where to accomplish these same things.  I don't want to open and close the SQL Connection each time the user Adds, Changes or Deletes records in each of the three tables as that traditionally would waste resources.

Can someone tell/show me what connection/command lines to put where?
   1.  What goes into the Module1.vb?
   2.  What goes into the startup form (Main.vb) and where do the Connection.Open and .Close lines go?
   3.  What goes into each three table form?

Thank you,
EYoung
Visual Basic.NET

Avatar of undefined
Last Comment
EYoung

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Nasir Razzaq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
sirbounty

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
EYoung

OK, let me confirm what I think you are saying.  At each place in the code where the user can modify/read data, I will need to define the connection and command, open the connection, execute the command, and close the connection and command?

That means that if a user modifies/reads the database 100 times an hour for eight hours, then the connection and command strings will be created, executed and closed 800 times?  And if there are ten users, then the number will be 8000 opens, executes and closes per day against the database?

If that is what we are to do now, then I will do it, but I have been told over the years to open the connection just once, run as many modifies/reads against the database, and close the connection just once when the application ends.

Thanks for explaining this.  Regards, EYoung
Nasir Razzaq

Hi EYoung,
I am not sure what approach you are taking that you have to make edits 100 times an hour. It depends on your scenario. We may be able to suggest something better if you describe the application scenario.
You have to keep a balance between number of times connections are opened and the length of time a connection is open for.
ASKER
EYoung

I just made up the scenerio.  Typically the users edit records several times an hour, not 100 times an hour.  I just want to make sure that I am not overly wasting/tying up resourses.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Nasir Razzaq

What you can do in such scenarios is that make the changes in memory to a datatable and propagate these changes back to the database less often such as once a minute.
sirbounty

The using block will take care of all the disposing for you, regardless of the number of reads/writes needed.
I don't have a database example with 100 times per hour, but I've never come across an instance where my code showed any indication of being resource intensive....
ASKER
EYoung

I see your point but since there can be more than one user acting on or reading the same record, I think I will just update as you suggest.

Am I correct in saying that defining, opening, executing and closing the connection each time an update/read is performed is OK?  If that is correct, then that is what I will do and I will close this question.

Will spit the points as I have benefitted from both of you.

Regards, EYoung
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
EYoung

sirbounty - in your example:  "Using conn As New SqlConnection(dsn)"  are you implying that I can use a System DSN (ODBC) instead of entering the actual name of the database?  If yes, that would allow me to not have to change the location of the database each time the application is installed at a different site.
Nasir Razzaq

EYoung,
DSNs are very slow as they create a new layer between the application and the database. In my first post, i mentioned the settings class which can be used to store the connection strings. These can be modified even easily than creating or modifying a dsn. You really need to get out of the VB6 world because the VB.NET is very different and very good.
ASKER
EYoung

OK, i will try to get out of the VB6 world, but it is hard  :-(
So DSNs are very slow and add a new layer to the app.
Can you give me an example of a settings class in the config file and how I would call it from my VB.net code?  

I just bumped the points to 150 and will split.

Thanks, EYoung
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
EYoung

Thank you both for the help.  Your answers helped me get over the initial hurdle in VB 2008.  I also appreciate your sticking with me as I asked followup questions for clarification.  Regards, EYoung
sirbounty

"OK, i will try to get out of the VB6 world, but it is hard  :-("

You could be quoting me just one short year ago! :o)
You're going to love .Net though - I guarantee it!

http://visualbasic.about.com/od/usingvbnet/a/appsettings.htm should provide you a starter on appsettings.
If not, I can post what I'm using and an example on usage...
ASKER
EYoung

Thanks sirbounty.  I have learned so many languages over the years but I have to admit that .Net does seem to be a big improvement.  Regards, EYoung
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.