[EndLessMind]

Hi!

I made a program that should move all data i a table form one database to an other.
Everything works well until it reaches row nr 100, then it just stops.
The problem keeps running but nothing happens.
No Error, no nothing. Just stops.

Here is my code for that part:

Imports System.Data.OleDb
Imports System.IO
Public Class Form1
    Public ds As New DataSet
    Dim da As New OleDbDataAdapter
    Public con As New OleDbConnection
    Dim sql As String
    Dim dt As New DataTable
    Dim maxrows As Integer
    Public inc As Integer = 0
    Public ds2 As New DataSet
    Dim da2 As New OleDbDataAdapter
    Public con2 As New OleDbConnection
    Dim sql2 As String
    Dim dt2 As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        BackgroundWorker1.WorkerReportsProgress = True
        BackgroundWorker1.WorkerSupportsCancellation = True
        Label1.Text = ""
        Label2.Text = ""
        Label3.Text = ""
        Me.TopMost = True 
    End Sub

    Private Sub Form1_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
        Label2.Text = "Skapar koppling till databaserna.."
        Delay(2)
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & Application.StartupPath & "\Filmer.mdb"
        con.Open()
        sql = "SELECT * FROM Film ORDER BY Namn ASC"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Filmer")
        maxrows = ds.Tables("Filmer").Rows.Count
        con.Close()

        con2.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & Application.StartupPath & "\Movie.mdb ;Jet OLEDB:Database Password=‚y[Kznj;"
        con2.Open()
        sql2 = "SELECT*FROM Film"
        da2 = New OleDb.OleDbDataAdapter(sql2, con2)
        da2.Fill(ds2, "Movies")
        con2.Close()
        Dim col(1) As DataColumn
        ds2.Tables("Movies").Columns(0).AutoIncrement = True
        col(0) = ds2.Tables("Movies").Columns(0)
        ds2.Tables("Movies").PrimaryKey = col
        'Label2.Text = "Koppling har skapats. Flyttning av filmer påbörjas om 3 sekunder!"
        'Delay(1)
        'Label2.Text = "Koppling har skapats. Flyttning av filmer påbörjas om 2 sekunder!"
        'Delay(1)
        Label2.Text = "Koppling har skapats. Flyttning av filmer påbörjas om 1 sekunder!"
        Delay(1)
        BackgroundWorker1.RunWorkerAsync()
    End Sub

    Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim dsNewRow As DataRow
        Dim sqlsearch As String
        Dim find As String = "Exempel"
        Label2.Text = "Flyttar filmer.."
        Dim maxrows2 As Integer
        Dim i As Integer = 0
        Do Until i = maxrows
            da.SelectCommand = New OleDbCommand(sql2, con2)
            Dim cb1 As New OleDb.OleDbCommandBuilder(da2)
            dsNewRow = ds2.Tables("Movies").NewRow()
            Label1.Text = "Flyttar: " & Environment.NewLine & ds.Tables("Filmer").Rows(i).Item(1)
            dsNewRow.Item(1) = ds.Tables("Filmer").Rows(i).Item(1)
            dsNewRow.Item(2) = ds.Tables("Filmer").Rows(i).Item(2)
            dsNewRow.Item(3) = ds.Tables("Filmer").Rows(i).Item(3)
            dsNewRow.Item(4) = ds.Tables("Filmer").Rows(i).Item(4)
            dsNewRow.Item(5) = ds.Tables("Filmer").Rows(i).Item(5)
            dsNewRow.Item(6) = ds.Tables("Filmer").Rows(i).Item(6)
            dsNewRow.Item(7) = ds.Tables("Filmer").Rows(i).Item(7)
            dsNewRow.Item(8) = ds.Tables("Filmer").Rows(i).Item(8)
            dsNewRow.Item(9) = ds.Tables("Filmer").Rows(i).Item(9)
            dsNewRow.Item(10) = ds.Tables("Filmer").Rows(i).Item(10)
            dsNewRow.Item(11) = ds.Tables("Filmer").Rows(i).Item(11).ToString
            dsNewRow.Item(12) = ds.Tables("Filmer").Rows(i).Item(12).ToString
            dsNewRow.Item(13) = ds.Tables("Filmer").Rows(i).Item(13).ToString
            i += 1
            ds2.Tables("Movies").Rows.Add(dsNewRow)
            da2.Update(ds2, "Movies")
            maxrows2 = ds2.Tables("Movies").Rows.Count
            BackgroundWorker1.ReportProgress((maxrows2 / maxrows) * 100)
            Label3.Text = maxrows2 - 1 & " st filmer flyttade av " & maxrows & "st"
        Loop

Hope someone can find a solution to my problem.

Thanks in advance!

This post has been edited by EndLessMind: 04 January 2011 - 06:45 AM

[motcom]

Hi,

Just a question, I used to do that in a For Loop and not While Loop But should your code not look like this:

  maxrows = ds.Tables("Filmer").Rows.Count
  .....
  'then
  Dim i As Integer = 0
  Do Until i = maxrows -1     'doing -1 as you start at 0

BTW do any of the rows get imported to the other database?

[EndLessMind]

Thanks
Had missed that


Well, yes. I'm importing every row from and the other database.
(All rows from database Filmer.mdb is imported to database Movie.mdb )

[EndLessMind]

So.. i change the code a little. But still, it stops at the 100th row.

 Dim dsNewRow As DataRow
        Dim sqlsearch As String
        Dim find As String = "Exempel"
        Label2.Text = "Flyttar filmer.."
        Dim maxrows2 As Integer
        maxrows = ds.Tables("Filmer").Rows.Count
        Dim i As Integer = 0
        For i = 0 To maxrows - 1
            da.SelectCommand = New OleDbCommand(sql2, con2)
            Dim cb1 As New OleDb.OleDbCommandBuilder(da2)
            dsNewRow = ds2.Tables("Movies").NewRow()
            Label1.Text = "Flyttar: " & Environment.NewLine & ds.Tables("Filmer").Rows(i).Item(1)
            dsNewRow.Item(1) = ds.Tables("Filmer").Rows(i).Item(1)
            dsNewRow.Item(2) = ds.Tables("Filmer").Rows(i).Item(2)
            dsNewRow.Item(3) = ds.Tables("Filmer").Rows(i).Item(3)
            dsNewRow.Item(4) = ds.Tables("Filmer").Rows(i).Item(4)
            dsNewRow.Item(5) = ds.Tables("Filmer").Rows(i).Item(5)
            dsNewRow.Item(6) = ds.Tables("Filmer").Rows(i).Item(6)
            dsNewRow.Item(7) = ds.Tables("Filmer").Rows(i).Item(7)
            dsNewRow.Item(8) = ds.Tables("Filmer").Rows(i).Item(8)
            dsNewRow.Item(9) = ds.Tables("Filmer").Rows(i).Item(9)
            dsNewRow.Item(10) = ds.Tables("Filmer").Rows(i).Item(10)
            dsNewRow.Item(11) = ds.Tables("Filmer").Rows(i).Item(11).ToString
            dsNewRow.Item(12) = ds.Tables("Filmer").Rows(i).Item(12).ToString
            dsNewRow.Item(13) = ds.Tables("Filmer").Rows(i).Item(13).ToString
            If i < maxrows - 1 Then
                i += 1
            End If
            ds2.Tables("Movies").Rows.Add(dsNewRow)
            con.Open()
            da2.Update(ds2, "Movies")
            con.Close()
            maxrows2 = ds2.Tables("Movies").Rows.Count
            BackgroundWorker1.ReportProgress((maxrows2 / maxrows) * 100)
            Label3.Text = maxrows2 - 1 & " st filmer flyttade av " & maxrows & "st"
        Next

[T3hC13h]

Whats with this bit?

     
If i < maxrows - 1 Then
    i += 1
End If

You don't need that in a Do loop, not in a For loop. Adding it to a For loop will cause you to double increment on each iteration and skip over other row.

This post has been edited by T3hC13h: 04 January 2011 - 12:21 PM

[EndLessMind]

Just tried.
Had to check so that would cause a problem. But i didn't

[motcom]

Yes the i += 1 must not be there... (in the for loop)

If you debug maxrows, how many are there?

Also you can open the connection before the loop and call the update statement and close the connection once the loop is finished... (con.Open() and da2.Update() and con.Close())

Next, have you defined the insert statement ("Insert for the database") for da2. (da2.InsertCommand)? Are there any records that get inserted into the Movie.mdb file at all?

see this code for an example

       Dim dsNewRow As DataRow
       Dim sqlsearch As String
       Dim find As String = "Exempel"
       Label2.Text = "Flyttar filmer.."
       maxrows = ds.Tables("Filmer").Rows.Count
       Dim i As Integer
       If maxrows > Then
          For i = 0 To maxrows - 1
              da.SelectCommand = New OleDbCommand(sql2, con2)
              Dim cb1 As New OleDb.OleDbCommandBuilder(da2)
              dsNewRow = ds2.Tables("Movies").NewRow()
              Label1.Text = "Flyttar: " & Environment.NewLine & ds.Tables("Filmer").Rows(i).Item(1)
              dsNewRow.Item(1) = ds.Tables("Filmer").Rows(i).Item(1)
              dsNewRow.Item(2) = ds.Tables("Filmer").Rows(i).Item(2)
              dsNewRow.Item(3) = ds.Tables("Filmer").Rows(i).Item(3)
              dsNewRow.Item(4) = ds.Tables("Filmer").Rows(i).Item(4)
              dsNewRow.Item(5) = ds.Tables("Filmer").Rows(i).Item(5)
              dsNewRow.Item(6) = ds.Tables("Filmer").Rows(i).Item(6)
              dsNewRow.Item(7) = ds.Tables("Filmer").Rows(i).Item(7)
              dsNewRow.Item(8) = ds.Tables("Filmer").Rows(i).Item(8)
              dsNewRow.Item(9) = ds.Tables("Filmer").Rows(i).Item(9)
              dsNewRow.Item(10) = ds.Tables("Filmer").Rows(i).Item(10)
              dsNewRow.Item(11) = ds.Tables("Filmer").Rows(i).Item(11).ToString
              dsNewRow.Item(12) = ds.Tables("Filmer").Rows(i).Item(12).ToString
              dsNewRow.Item(13) = ds.Tables("Filmer").Rows(i).Item(13).ToString
         
              ds2.Tables("Movies").Rows.Add(dsNewRow)

              BackgroundWorker1.ReportProgress((i / maxrows) * 100)
              Label3.Text = i - 1 & " st filmer flyttade av " & maxrows & "st"
       Next
    da2.Update(ds2, "Movies")
    con.Close()
    End If

[EndLessMind]

So, i tried to change to code to

 con.Open()
        For i = 0 To maxrows - 1
            da.SelectCommand = New OleDbCommand(sql2, con2)
            Dim cb1 As New OleDb.OleDbCommandBuilder(da2)
            dsNewRow = ds2.Tables("Movies").NewRow()
            Label1.Text = "Flyttar: " & Environment.NewLine & ds.Tables("Filmer").Rows(i).Item(1)
            dsNewRow.Item(1) = ds.Tables("Filmer").Rows(i).Item(1)
            dsNewRow.Item(2) = ds.Tables("Filmer").Rows(i).Item(2)
            dsNewRow.Item(3) = ds.Tables("Filmer").Rows(i).Item(3)
            dsNewRow.Item(4) = ds.Tables("Filmer").Rows(i).Item(4)
            dsNewRow.Item(5) = ds.Tables("Filmer").Rows(i).Item(5)
            dsNewRow.Item(6) = ds.Tables("Filmer").Rows(i).Item(6)
            dsNewRow.Item(7) = ds.Tables("Filmer").Rows(i).Item(7)
            dsNewRow.Item(8) = ds.Tables("Filmer").Rows(i).Item(8)
            dsNewRow.Item(9) = ds.Tables("Filmer").Rows(i).Item(9)
            dsNewRow.Item(10) = ds.Tables("Filmer").Rows(i).Item(10)
            dsNewRow.Item(11) = ds.Tables("Filmer").Rows(i).Item(11).ToString
            dsNewRow.Item(12) = ds.Tables("Filmer").Rows(i).Item(12).ToString
            dsNewRow.Item(13) = ds.Tables("Filmer").Rows(i).Item(13).ToString
            ds2.Tables("Movies").Rows.Add(dsNewRow)
            BackgroundWorker1.ReportProgress((i / maxrows) * 100)
            Label3.Text = i + 1 & " st filmer flyttade av " & maxrows & "st"
        Next
        BackgroundWorker1.ReportProgress(0)
        da2.Update(ds2, "Movies")
        con.Close() 

But still stops as the 100th record.



What should the InsertCommand be?

And yes. Every record from database "filmer.mdb" is getting inserted into database "Movie.mdb"

[motcom]

Ok,

in this line

 For i = 0 To maxrows - 1

what value is at maxrows? is it 100? if yes, then threre are 100 records only...

[EndLessMind]

Right now, there is around 270 records
And maxrows = ds.table("Filmer").Rows.Count

[motcom]

try putting the code between a try catch and see if an error happens, maybe dbnull error, etc...

try
       con.Open()
       For i = 0 To maxrows - 1
           da.SelectCommand = New OleDbCommand(sql2, con2)
           Dim cb1 As New OleDb.OleDbCommandBuilder(da2)
           dsNewRow = ds2.Tables("Movies").NewRow()
           Label1.Text = "Flyttar: " & Environment.NewLine & ds.Tables("Filmer").Rows(i).Item(1)
           dsNewRow.Item(1) = ds.Tables("Filmer").Rows(i).Item(1)
           dsNewRow.Item(2) = ds.Tables("Filmer").Rows(i).Item(2)
           dsNewRow.Item(3) = ds.Tables("Filmer").Rows(i).Item(3)
           dsNewRow.Item(4) = ds.Tables("Filmer").Rows(i).Item(4)
           dsNewRow.Item(5) = ds.Tables("Filmer").Rows(i).Item(5)
           dsNewRow.Item(6) = ds.Tables("Filmer").Rows(i).Item(6)
           dsNewRow.Item(7) = ds.Tables("Filmer").Rows(i).Item(7)
           dsNewRow.Item(8) = ds.Tables("Filmer").Rows(i).Item(8)
           dsNewRow.Item(9) = ds.Tables("Filmer").Rows(i).Item(9)
           dsNewRow.Item(10) = ds.Tables("Filmer").Rows(i).Item(10)
           dsNewRow.Item(11) = ds.Tables("Filmer").Rows(i).Item(11).ToString
           dsNewRow.Item(12) = ds.Tables("Filmer").Rows(i).Item(12).ToString
           dsNewRow.Item(13) = ds.Tables("Filmer").Rows(i).Item(13).ToString
           ds2.Tables("Movies").Rows.Add(dsNewRow)
           BackgroundWorker1.ReportProgress((i / maxrows) * 100)
           Label3.Text = i + 1 & " st filmer flyttade av " & maxrows & "st"
       Next
       BackgroundWorker1.ReportProgress(0)
       da2.Update(ds2, "Movies")
       con.Close() 
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

[EndLessMind]

Aha. the PrimaryKey gave me that problem.
It said that the number "101" already exited in the database, and therefor not unique.
But i don't understand why, because my PrimaryKey is a AutoNumber, and i wasn't trying to change that.

Now i added "dsNewRow.Item(0) = ds.Tables("Filmer").Rows(i).Item(0)" so the PrimaryKey would be the same as for the old database.
And i it goes all the way up to row 133, then i get the same error again.
"Column 0 is restricted to unique numbers only. Value 101 does already exist" ( or something like that, translated from Swedish )

[motcom]

Yes, auto number type will generate a number automatically...

So unless you need to have the id's exactly the same, you can add the record, but don't set the ID as it will generate a number for you.

I know in SQL you can do a "hard insert" so to say. That is by doing and identity insert, I'm not to sure about Access...

EDIT:

oh and you can reference the column names directly so you will not get the columns messed up, so instead of this:

dsNewRow.Item(1) = ds.Tables("Filmer").Rows(i).Item(1)

you can use something like this

dsNewRow.Item("FilmName") = ds.Tables("Filmer").Rows(i).Item("FilmName")

maybe that is a safer route to go anyway...

This post has been edited by motcom: 07 January 2011 - 06:31 AM

[EndLessMind]

Well, i don't know why i would need to have the ID's exactly the same, because i don't use them, nor then transferring or in my program.

[motcom]

well they should be there to identify a unique row in the table, if they relate to some other table, you might get trouble later on... if not (and make sure) you can ignore them.