I'm new to VB.net and I am trying to upgrade a VB6 program using VB2005.net. One of my problems is that I have to parse a excel spreadsheet and write the end result row by row to a SQL Server2000 BD Table. The following the following is the code I used to do this in VB6 using ADO. I would appreciate it if anyone could show me what I have to do to modify my code to work with ado.net.

Public Sub ImportCity_of_NO()
Dim excel_app As Object
Dim excel_sheet As Object
Dim counter As Integer
Dim addCounter As Integer
Dim editcounter As Integer
Dim x As Integer
Dim tmpTXT As String
counter = 0
addCounter = 0
editcounter = 0
x = 0
TOTALREMITTANCE = 0
D_COUNT = 0
Dim new_value1 As String 'RemitterID
Dim new_value2 As String 'Column 2 MemberID
Dim new_value3 As String 'Column 3 LASTNAME
Dim new_value4 As String 'Column 4 FIRSTNAME
Dim new_value5 As String 'Column 5 MI
Dim new_value6 As String 'Column 6 +-
Dim new_value7 As String 'Column 7 PREM
Dim new_value8 As String 'Column 10 FACTOR

Dim row As Integer

    Screen.MousePointer = vbHourglass
    DoEvents

    ' Create the Excel application.
    Set excel_app = CreateObject("Excel.Application")
    ' Open the Excel spreadsheet.
    excel_app.Workbooks.Open FileName:=txtExcelFile.Text
    ' Check for later versions.
    If Val(excel_app.Application.Version) >= 8 Then
        Set excel_sheet = excel_app.ActiveSheet
    Else
        Set excel_sheet = excel_app
    End If
    ' Get data from the Excel spreadsheet and insert
    ' it into the TestValues table.
    row = 3

   ' Adodc2.Refresh
    Do
        ' Get the next value.
        new_value2 = Trim$(excel_sheet.Cells(row, 2))
            ' See if it's blank.
        If Len(new_value2) = 0 Then
            Exit Do
        Else
            new_value3 = Trim$(excel_sheet.Cells(row, 3))
            new_value4 = Trim$(excel_sheet.Cells(row, 4))
            new_value5 = Trim$(excel_sheet.Cells(row, 5))
            new_value6 = Trim$(excel_sheet.Cells(row, 7))
            new_value7 = Trim$(excel_sheet.Cells(row, 8))
            new_value8 = Trim$(excel_sheet.Cells(row, 11))
            x = CInt(new_value8)
            If x > 0 Then
                deducted = CDbl(new_value7) - (0.14 * x)
            Else
                deducted = CDbl(new_value7) - 0.14
            End If
            tmpAMOUNT = CStr(deducted)
            remitterID = "006925658"

            If CInt(new_value8) > 0 Then x = CInt(new_value8)
            With adoPrimaryRS

                'With Adodc2.Recordset
                    'If Not Adodc2.Recordset.BOF Then .MoveLast
                    If Not .BOF Then .MoveLast
                    GetDeduction
                    DEDUCTION = FormatDeduction(tmpDEDUCTION)
                            .AddNew
                                    !remitterID = remitterID
                                    !MemberID = new_value2
                                    'new_value3 = ReplacementText new_value3, "-", " ")

                                    tmpTXT = new_value3 & ", " & new_value4 & " " & new_value5
                                    !Name = Left(tmpTXT, 22)
                                    !DeductionAMOUNT = DEDUCTION
                                    !DeductionDATE = Format$(Now, "yyyymmdd")
                                .UpdateBatch adAffectCurrent
                                addCounter = addCounter + 1
                                D_COUNT = addCounter
                                TOTALREMITTANCE = TOTALREMITTANCE + deducted
                                GetTOTALREMITTANCE
                End With
            End If
            row = row + 1
            counter = row - 2

        Loop
        tmpHEADER = " A" + " " + remitterID + " " + remitterNAME + " " + Format$(Now, "yyyymmdd")
        tmpTRAILER = " Z" + " " + remitterID + " " + CStr(D_COUNT) + " " + FormatDeduction(remitterTotal) + "+"
    ' Close the workbook without saving.
    excel_app.ActiveWorkbook.Close False
    ' Close Excel.
    excel_app.Quit
    Set excel_sheet = Nothing
    Set excel_app = Nothing
    Screen.MousePointer = vbDefault
End Sub

Here is another way to open an Excel file, then you could use the
dataset to update your SQLServer table:

Here is one method. Others will have a different take I'm sure.

'* Create your Connection Object
Dim cn As New System.Data.OleDb.OleDbConnection
With cn
    '* Notice the double quotes ("") before and after the Extended Properties
    '* portion of the connection string.
    '* This connection string attempts to open C:\Book1.xls - replace
    '* with your full path file name.
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
        & "C:\Book1.xls;Extended Properties=""Excel 8.0;HDR=YES"";"
    .Open()
End With

'* Create the Command Object
Dim cmd As New System.Data.OleDb.OleDbCommand
With cmd
    .Connection = cn
    .CommandType = CommandType.Text
    '* The worksheet that you want to open is enclosed in brackets
    '* and ends with a $ sign.
    .CommandText = "SELECT * FROM [Sheet1$]"
    Dim da As New System.Data.OleDb.OleDbDataAdapter(cmd)
    Dim ds As New DataSet
    da.Fill(ds)
    '* The line below assumes you have a DataGridView named dg
    '* placed on your form.
    dg.DataSource = ds.Tables(0)
End With

cn.close()

****************************************
Now you can retrieve your data as follows:

For intX as integer = 0 to ds.tables(0).rows.count - 1
    '* Create one variable as below for each column
    dim ColumnValue1 as string = ds.tables(0).rows(intx).item("Your field name here")

'* Sample Update String
'* Assumes the "ColumnValue" varaibles are strings
dim strSQL as string = "UPDATE myTable SET myValue1 = '" & ColumnValue1 & "'",myValue2 = '" & ColumnValue2 & "'"

    '* Create an SQLCommand object with a connection and update
    Dim Cmd as new SQLCommand
    With Cmd
        .Connection = cn '* A connection object you create with a valid connection string
        .CommandType = CommandType.Text
        .CommandText = strSQL
        .ExecuteNonQuery
        .Dispose
    End With

Next intX



Best Regards,
Earl Francis