was there a better way to code this?...
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!!
:wave:
VB Code:
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
