[Last Call] Learn how to a build a cloud-first strategyRegister Now


Convert VB6 ADO to vs 2008 ADO.NET

Posted on 2009-02-11
Medium Priority
Last Modified: 2012-05-06
I have an old VB6 application which works well.
For performance it runs on the server that where the database resides.
The server OS is Windows Server 2003 R2 x64
It processes log files and imports the data into an SQL 2005 database after validating the data in each log file. There can be over 50 files per day and each log file can contain more than 5000 records. The database grows at approx 5GB every six months. There are approx 45 tables in the database.
I connect to the database via code below.
The database is interogated by opening recordsets as needed and all insertions are done by building the sql statement in a string varaible and issuing a gcnADO.Execute command to insert/update a record on any given table table.
I want to convert this application to VS2008, visual basic.
What is the best practice to do this ?
Public gcnADO As ADODB.Connection
Public cmd As New ADODB.Command
Public Function ConnectToDatabase(ByVal strServer As String, ByVal strPassword As String, ByVal sDBName As String) As Boolean
On Error GoTo Error_ConnectToDatabase
   Dim strConnect    As String
   Dim strError      As String
   ConnectToDatabase = False
   Set gcnADO = New ADODB.Connection
   strConnect = "Data Source = " & strServer
   gcnADO.Provider = "SQLOLEDB.1"
   gcnADO.Open strConnect, gstrUserID, strPassword
   gcnADO.DefaultDatabase = sDBName
   ConnectToDatabase = True
   Exit Function
   strError = "Cannot Open Database Error No :" & Err.Number & " Description :" & Err.Description
   MsgBox strError, vbCritical, "Critical Error"
   Resume Exit_ConnectToDatabase
End Function

Open in new window

Question by:josephwalsh
  • 2
LVL 70

Accepted Solution

Éric Moreau earned 2000 total points
ID: 23610869

Expert Comment

ID: 23614868
What information are you looking for? There are many different ways to do what your asking. You could use a strongly typed dataset that contains all your tables. Then add rows to the tables and call the tables TableAdapter's update method to perform all the inserts for you. Or pass the datasets table to a SQLBulkCopy method.

Author Comment

ID: 23615134
This is my first app. in the .NET environment.
I would like to do it right, and am open to suggestions.
I think that Bulkcopy might not be the way as there is an internal transactionID per transaction and there would be entries in other related tables per transaction. The transactionID does no come from the log file. Do you need more info ?
LVL 70

Expert Comment

by:Éric Moreau
ID: 23615289
the link I have provided gives you different ways of doing it

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

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

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

Join & Ask a Question