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

[squirrelly1]

I don't think so if you're using a Data Environment... and it seems to me that you are.

That probably is the best way to transfer data between two database formats...

I would personally break it down into a few different functions just to make it look a little neater, but that's just me ...

Nice...

~Squirrelly1

[Pirate]

I agree with squirrelly1 about breaking this method into more specific methods or properites (like artist , songs ...etc) . And you could alwasy make use of the "Imports" statements to shorten your code . Lastly , ArrayList is much more faster than the the regular array you're using .

[squirrelly1]

Definately right again Pirate...

BTW... What object uses the Imports statement?

Or method rather...

Squirrelly1

[Kt3]

Truthfully, I don't know. I gotta ask why you think you have to format all those values though. If you are controlling the databases on both ends, can't you make them compatible from the getgo? And just use the SqlAdapter to update the server, or fill a local dataset.

Oh, as far as the 1000 rows thing, you don't HAVE to declare how many entries you are going to have max in your string arrays, do you? I mean, I'm honestly asking, because I don't know.

[Andy]

great suggestions!!

the 1000 I declared was necessary because I kept being told by the compiler that I didn't have a reference to the object.(can't remember the exact error). I really don't want to hard-code 1000 in there but that's what I knew was enough. Any ideas?

[Pirate]

Originally posted by Andy
great suggestions!!

the 1000 I declared was necessary because I kept being told by the compiler that I didn't have a reference to the object.(can't remember the exact error). I really don't want to hard-code 1000 in there but that's what I knew was enough. Any ideas?

Use the dynamic array object (ArrayList) or (SortedArray or something like this) . By using this object you don't have to specify number of elements you will be using .

[Pirate]

Originally posted by squirrelly1
Definately right again Pirate...

BTW... What object uses the Imports statement?

Or method rather...

Squirrelly1

You mean :
Imports System.Data.OleDB

sorry if I missed your point !

[squirrelly1]

lol...

[Kt3]

Originally posted by Pirate
Use the dynamic array object (ArrayList) or (SortedArray or something like this) . By using this object you don't have to specify number of elements you will be using .

...and when you're done, don't forget to copy it to a strongly typed array object. It's a method that you can call from the ArrayList object.

[Pirate]

Originally posted by Kt3
...and when you're done, don't forget to copy it to a strongly typed array object. It's a method that you can call from the ArrayList object.

You don't need to do this . You can have a property that checks the object that you want to add before you add it to the arraylist collection .