was there a better way to code this?...

[Andy]

I wrote the following code to do this:

1) read a table inside an Access .mdb file
2) format the data to match the formatting on a SQL 2000 db
3) write the newly formatted data into the SQL 2000 db

there are four control not shown here: sqlConnection, sqlCommand, oleDBConnection, oleDBCommand.

The code works but I was wondering if there was a more efficient way of doing it. maybe with datasets and the such?

in particular: could (should) I have used Datasets? the arrays---whats a better way? I KNEW ahead of time there were just under 1000 rows. What if the row count is unknown?

Thanks ahead everyone!!

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        AccessConn.Open()
 
        Dim reader As OleDb.OleDbDataReader
 
        reader = AccessCMD.ExecuteReader
 
        Dim strTmp As String
        Dim artist(1000) As String
        Dim song(1000) As String
        Dim mag(1000) As String
        Dim month(1000) As String
        Dim year(1000) As String
 
        Dim intCount As Integer = 0
 
        While reader.Read()
 
            artist(intCount) = reader.Item("Artists")
            artist(intCount) = artist(intCount).Replace("'", "º")
 
            song(intCount) = reader.Item("Song")
            song(intCount) = song(intCount).Replace("'"c, "º"c)
 
            mag(intCount) = reader.Item("Magazine")
 
            strTmp = reader.Item("Month/Year")
 
            If strTmp = "1995" Then
                strTmp = "Jan/1995"
            End If
 
            year(intCount) = strTmp.Substring(strTmp.IndexOf("/") + 1)
 
            month(intCount) = strTmp.Substring(0, strTmp.IndexOf("/"))
 
            Select Case month(intCount)
 
                Case "Jan"
                    month(intCount) = "January"
                Case "Feb"
                    month(intCount) = "February"
                Case "Mar"
                    month(intCount) = "March"
                Case "Apr"
                    month(intCount) = "April"
                Case "May"
                    month(intCount) = "May"
                Case "Jun"
                    month(intCount) = "June"
                Case "Jul"
                    month(intCount) = "July"
                Case "Aug"
                    month(intCount) = "August"
                Case "Sep"
                    month(intCount) = "September"
                Case "Oct"
                    month(intCount) = "October"
                Case "Nov"
                    month(intCount) = "November"
                Case "Dec"
                    month(intCount) = "December"
 
            End Select
 
            Select Case mag(intCount)
                Case "Guitar(fpm)"
                    mag(intCount) = "Gftpm"
            End Select
 
            intCount += 1
 
        End While
 
        reader.Close()
        AccessConn.Close()
 
        SqlConn.Open()
 
        For c As Integer = 0 To intCount
 
            SqlCMD.CommandText = "Insert Into Tabs " & _
                "(Artist, Song, Magazine, Month, Year) Values('" & _
                artist(c) & "', '" & song(c) & "', '" & mag(c) & _
                "', '" & month(c) & "', '" & year(c) & "')"
 
            SqlCMD.ExecuteNonQuery()
 
        Next
 
        MessageBox.Show("Done")
 
    End Sub