VB6 - Help is Welcomed

[happyday09]

Hello All. Can someone please help me? I have been at this code for two weeks. I am a newbie at creating the following code in VB6. I want to open a database (SQL), read from the data tables, and write the data into an existing excel spreadsheet cell by cell. Can someone view the code below? It does have errors, because I do not know where to start to correct them and or to figure out what’s missing. Thank you in advance for your help.

'*************Imports - classes used from other namespaces ******
'****************************************************************
Imports System
Imports System.Xml
Imports System.Data
Imports System.Data.OleDb
Imports System.Diagnostics.FileVersionInfo
Imports System.IO
Imports System.Drawing
Imports System.Windows.Forms
Imports System.ComponentModel
Imports System.Text
Imports Excel

'*************** Declarations - Main Form **********************
'***************************************************************
Public Main
Inherits System.Windows.Forms.Form
 Dim myDataSet As DataSet
    Dim myRow As DataRow
    Dim myCol As DataColumn
    Dim myConnection As OleDbConnection
    Dim mySet1 As DataSet
        
Public Sub Main_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    '*************** Mouse Cursor ***********************
    'Me.MousePointer = 0
    'Me.Mouse
    
'*************** LSpec input on Commandline *******************
'**************************************************************
Dim arguments As [String]()= Environment.GetCommandLineArgs()
If arguments.Lenth > 1 Then
    Fnumbers.Text = Trim$(UCase$(CommandLineArguments(1)))
    Me.Show() 'Shows the form currently excuting code
    Me.Cursor = Cursors.Default
    TabControl1.SelectedIndex = 1   'Show the Log File Tab
    Call Go
    End
End If
End Sub
'Do I really need GotFocus and LostFocus?

Private Sub Go()
'***********ADODB Connection Object&Recordset Obj.& "Store SQL Commands"**********
Dim myConnection As New OleDbConnection
Dim myRecSet As New OleDbConnection
Dim SQLstr As String

'MyConnObj.Open_
 Dim sString As String
 sString = "ServerName=SQLNCI;" & _
    "DatabaseName= VB6Code;" & _
    "UserID= VBUser;" & _
    "Password= codingisfun;"
    


'**************Read Number of Turns Per Section***********************************************
'*********************************************************************************************
SQLstr = "SELECT RegionID, count(*) As TerritoryCoun;"
SQLstr = SQLstr & "FROM Terriories;"
SQLstr = SQLstr & "GROUP BY RegionID;"

Dim myCommand As New OleDbCommand(SQLstr, myConnnection)
Dim myAdapter As New OleDbDataAdapter(myCommandILV)
Dim myCommandBuilder As New OleDbCommandBuilder(myAdapterILV)
myConnection.Open()
myDataSet = New DataSet

Try
            myDataAdpCCA.Fill(myDataSet, "RegionID")
        Catch ex As Exception
            WriteStatusMessage(ex.ToString(), "Black", 12)
            Exit Sub
        End Try
        
   End Sub
   
   If myDataSet.Tables("RegionID").Rows.Count() = 0 Then
            WriteStatusMessage (Trim$(UCase$(Fnumbers.Text)) & " Not Found in List")
            MyExcel = Nothing
            myWorkBooks = Nothing
            myWorkBook = Nothing
            sh = Nothing
            myworksheet = Nothing
            myWorkSheetCore = Nothing
            myWorkSheetSummary = Nothing
            Exit Sub
        End If

'******** Write - Number of Turns Per Section **************************************************
'***********************************************************************************************
myworksheet.Name = "Region"
        myworksheet = myWorkBook.Worksheets.Add()
        For Each myworksheet In sh
            If UCase(Mid(myworksheet.Name.ToString, 1, 5)) = "SHEET" Then
                Try
                    myWorkSheet.Delete()
                Catch ex As Exception
                End Try
            End If
        Next
        myWorkBook.Sheets("Region").Select()
        System.Windows.Forms.Application.DoEvents()
        
        For Each myDataRow In myDataSet.Tables("RegionID").Rows
            If SummaryAndDetails.Checked = True Then 'double check
            
            myworksheet.Cells(3, 1) = "Country"
            myworksheet.Cells(3, 6) = "State"
            myworksheet.Cells(4, 1) = "County"
            myworksheet.Cells(4, 4) = "Address"
            myworksheet.Cells(4, 6) = "City"
            myworksheet.Cells(5, 2) = "Zip"
            myworksheet.Cells(7, 2) = "FirstName1"
            myworksheet.Cells(8, 2) = "FirstName2"
            myworksheet.Cells(7, 4) = "LastName1"
            myworkshhet.Cells(8, 4) = "LastName2"
            myworksheet.Cells(4, 3) = myDataRow("RegionID")
            
            End If
            End Function

[Minolwen]

I know this is probably not what you want to hear, but as you are still learning obviously, why not learn using current technology?

You can learn to program VB.NET just as easy as VB6 and older, if you are still in the early stages. You can get Visual Studio Express Edition for free at http://www.microsoft.com/Express/VB/

[happyday09]

You are right Minolwen... vb.net will be more to update to use. however, I stuck using VB6 because the sofware that's supporting this code is pretty old and only supports vb6. Since, I am using such an outdated version, do you think there's any way I can get this code to work using vb6?

[Comatose]

that code isn't VB6 at all. It's vb.net. If you want to port it to vb6, you should simply re-write it. The languages/code are that different. If you need help on a re-write, we can help you.

[ryan311]

i agree that is vb.net :)

[happyday09]

Thank you Comatose and Ryan 311's for your feedback. I have taken your advice and re-wrote the code in VB6 not VB.net. You are correct there are major syntax differences. Below is my rework, however, I have been moved to vb.net forum. Therefore, I may have to resubmit in the vb6 forum. Thanks again for all of your help.
Option Explicit

Public Sub Main()
Dim myDataSet As DataSet
Dim myRow As DataRow
Dim myCol As DataColumn
Dim myConnection As OleDbConnection
Dim mySet1 As DataSet
End Sub

Public Sub frmMain_Load(ByVal Text As String)
Dim arguments As String
If arguments.Lenth > 1 Then
Fnumbers.Text = Trim$(UCase$(CommandLineArguments(1)))
frmMain.Show
frmMain.Cursor = Cursors.Default
TabControl1.SelectedIndex = 1 'Show the Log File Tab
Call Go
End
End If
End Sub

Private Sub cmdGo_click()
'***********ADODB Connection Object&Recordset Obj.& "Store SQL Commands"**********
Dim myConnection As New ADODB.Connection
Dim myRecSet As New ADODB.Connection
Dim SQLstr As String

'MyConnObj.Open_
Dim sString As String
sString = "ServerName=SQLNCI;" & _
"DatabaseName= VB6Code;" & _
"UserID= VBUser;" & _
"Password= codingisfun;"

myRecSet.Open sqlstr, myConnection, AsOpenKeyset

'**************Read ***********************************************
'*********************************************************************************************
SQLstr = "SELECT RegionID, count(*) As TerritoryCoun;"
SQLstr = SQLstr & "FROM Terriories;"
SQLstr = SQLstr & "GROUP BY RegionID;"

Dim myCommand As New ADODB.Command
Dim myAdapter As New ADODB.Adapter
Dim myCommandBuilder As New ADODB.CommandBuilder
myConnection.Open()
myDataSet = New DataSet

'Try
' myDataAdpRegionID.Fill(myDataSet, "RegionID")
' Catch ex As Exception
' WriteStatusMessage("Incorrect data")
' Exit Sub
' End Try

'End Sub

If myDataSet.Tables("RegionID").Rows.Count() = 0 Then
WriteStatusMessage (Trim$(UCase$(Fnumbers.Text)) & " Not Found in List")
MyExcel = Nothing
myWorkBooks = Nothing
myWorkBook = Nothing
sh = Nothing
myworksheet = Nothing
Exit Sub
End If

'******** Write - **************************************************
'***********************************************************************************************
myworksheet.Name = "Region"
myworksheet = myWorkBook.Worksheets.Add()
For Each myworksheet In sh
If UCase(Mid(myworksheet.Name.ToString, 1, 5)) = "SHEET" Then
' Try
' myWorkSheet.Delete()
' Catch ex As Exception
'End Try
' End If
'Next
'myWorkBook.Sheets("Region").Select()
'System.Windows.Forms.Application.DoEvents()
'Dim oExcel As Object
'Dim oBook As Object
'Dim oSheet As Object

For Each myDataRow In myDataSet.Tables("LAYER").Rows
If SummaryAndDetails.Checked = True Then 'double check

myworksheet.Range("A4").Value = "ILV"
myworksheet.Range("B8").Value = "I Code"
myworksheet.Range("C10").Value = "Layer Insulation"
myworksheet.Range("F4").Value = "ILV Finish Turns"
myworksheet.Range("G3").Value = "ILV Layer I Code"
myworksheet.Range("E4").Value = "Reset Turns Counter"
myworksheet.Range("A7").Value = "Turns"
myworksheet.Range("B3").Value = "Turns"
myworksheet.Range("H6").Value = "ILV Duct 1"
myworkshhet.Range("I4").Value = "ILV Duct 2"
myworksheet.Range("J4").Value = myDataRow("LAYER")

End Function

End Sub