getting error on record duplication

28 May 2009 7:45 PM

Co

Hi All,

I have following code to duplicate records in a database table:
When I duplicate one record everything works but when I try to add
more records I get this error:

"Conversion from type dbnull to type string is not valid."
The code stops at the lien saying:
                    dr = ds.Tables(0).Rows(j)

Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
ByVal fileID() As String)

        Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
oldNode
        Dim strTable As String = "Bestanden"
        Dim da As New OleDb.OleDbDataAdapter(sql, conn)
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim ds As New DataSet
        Dim i As Integer
        Dim newRow As DataRow

        Try
            da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
            da.Fill(ds, strTable)

            Dim dr As DataRow

            For i = 0 To fileID.Length - 1
                For j As Integer = ds.Tables(0).Rows.Count - 1 To 0
Step -1
                    dr = ds.Tables(0).Rows(j)
                    'For Each dr In ds.Tables(0).Rows
                    If CStr(dr.Item("Id")) = fileID(i) Then
                        'create this file in the folder
                        newRow = ds.Tables(strTable).NewRow()
                        newRow("filenaam") = dr.Item("filenaam")
                        newRow("status") = FixNull(dr.Item("status"))
                        newRow("lokatie") = newNode
                        newRow("extensie") = dr.Item("extensie")
                        newRow("grootte") = FixNull(dr.Item
("grootte"))
                        newRow("datum_gemaakt") = FixNull(dr.Item
("datum_gemaakt"))
                        newRow("datum_gewijzigd") = FixNull(dr.Item
("datum_gewijzigd"))
                        newRow("expires") = FixNull(dr.Item
("expires"))
                        newRow("hddlokatie") = FixNull(dr.Item
("hddlokatie"))
                        newRow("samenvatting") = dr.Item
("samenvatting")
                        newRow("kenmerk") = FixNull(dr.Item
("kenmerk"))
                        newRow("auteur") = FixNull(dr.Item("auteur"))
                        newRow("soort") = FixNull(dr.Item("soort"))
                        newRow("inuit") = FixNull(dr.Item("inuit"))
                        ds.Tables(strTable).Rows.Add(newRow)
                    End If
                Next
            Next

                'sent the updated dataSet to the database
                da.Update(ds, strTable)

        Catch oException As OleDbException
            MessageBox.Show(oException.Message)

        Catch oException As Exception
            MessageBox.Show(oException.Message)

        End Try
        conn.Close()

    End Sub

Any thoughts?

Regards
Marco

28 May 2009 8:25 PM

nak

Hi Co,

Remember to include the Step keyword and let the compiler know that you
want to count backwards...

For j As Integer = ds.Tables(0).Rows.Count - 1 To 0 Step -1

Nick.

Show quoteHide quote

"Co" <vonclausow***@gmail.com> wrote in message
news:9b65bbb1-7b1e-423e-bdf1-fe3012b4dc45@f19g2000yqh.googlegroups.com...
> Hi All,
>
> I have following code to duplicate records in a database table:
> When I duplicate one record everything works but when I try to add
> more records I get this error:
>
> "Conversion from type dbnull to type string is not valid."
> The code stops at the lien saying:
>                    dr = ds.Tables(0).Rows(j)
>
>
> Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
> ByVal fileID() As String)
>
>        Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
> oldNode
>        Dim strTable As String = "Bestanden"
>        Dim da As New OleDb.OleDbDataAdapter(sql, conn)
>        Dim cb As New OleDb.OleDbCommandBuilder(da)
>        Dim ds As New DataSet
>        Dim i As Integer
>        Dim newRow As DataRow
>
>        Try
>            da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
>            da.Fill(ds, strTable)
>
>            Dim dr As DataRow
>
>            For i = 0 To fileID.Length - 1
>                For j As Integer = ds.Tables(0).Rows.Count - 1 To 0
> Step -1
>                    dr = ds.Tables(0).Rows(j)
>                    'For Each dr In ds.Tables(0).Rows
>                    If CStr(dr.Item("Id")) = fileID(i) Then
>                        'create this file in the folder
>                        newRow = ds.Tables(strTable).NewRow()
>                        newRow("filenaam") = dr.Item("filenaam")
>                        newRow("status") = FixNull(dr.Item("status"))
>                        newRow("lokatie") = newNode
>                        newRow("extensie") = dr.Item("extensie")
>                        newRow("grootte") = FixNull(dr.Item
> ("grootte"))
>                        newRow("datum_gemaakt") = FixNull(dr.Item
> ("datum_gemaakt"))
>                        newRow("datum_gewijzigd") = FixNull(dr.Item
> ("datum_gewijzigd"))
>                        newRow("expires") = FixNull(dr.Item
> ("expires"))
>                        newRow("hddlokatie") = FixNull(dr.Item
> ("hddlokatie"))
>                        newRow("samenvatting") = dr.Item
> ("samenvatting")
>                        newRow("kenmerk") = FixNull(dr.Item
> ("kenmerk"))
>                        newRow("auteur") = FixNull(dr.Item("auteur"))
>                        newRow("soort") = FixNull(dr.Item("soort"))
>                        newRow("inuit") = FixNull(dr.Item("inuit"))
>                        ds.Tables(strTable).Rows.Add(newRow)
>                    End If
>                Next
>            Next
>
>                'sent the updated dataSet to the database
>                da.Update(ds, strTable)
>
>        Catch oException As OleDbException
>            MessageBox.Show(oException.Message)
>
>        Catch oException As Exception
>            MessageBox.Show(oException.Message)
>
>        End Try
>        conn.Close()
>
>    End Sub
>
> Any thoughts?
>
> Regards
> Marco
>

28 May 2009 9:49 PM

Co

Show quote Hide quote

On 28 mei, 22:25, "nak" <a***@a.com> wrote:
> Hi Co,
>
> Remember to include the Step keyword and let the compiler know that you
> want to count backwards...
>
> For j As Integer = ds.Tables(0).Rows.Count - 1 To 0 Step -1
>
> Nick.
>
> "Co" <vonclausow***@gmail.com> wrote in message
>
> news:9b65bbb1-7b1e-423e-bdf1-fe3012b4dc45@f19g2000yqh.googlegroups.com...
>
> > Hi All,
>
> > I have following code to duplicate records in a database table:
> > When I duplicate one record everything works but when I try to add
> > more records I get this error:
>
> > "Conversion from type dbnull to type string is not valid."
> > The code stops at the lien saying:
> > dr = ds.Tables(0).Rows(j)
>
> > Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
> > ByVal fileID() As String)
>
> > Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
> > oldNode
> > Dim strTable As String = "Bestanden"
> > Dim da As New OleDb.OleDbDataAdapter(sql, conn)
> > Dim cb As New OleDb.OleDbCommandBuilder(da)
> > Dim ds As New DataSet
> > Dim i As Integer
> > Dim newRow As DataRow
>
> > Try
> > da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
> > da.Fill(ds, strTable)
>
> > Dim dr As DataRow
>
> > For i = 0 To fileID.Length - 1
> > For j As Integer = ds.Tables(0).Rows.Count - 1 To 0
> > Step -1
> > dr = ds.Tables(0).Rows(j)
> > 'For Each dr In ds.Tables(0).Rows
> > If CStr(dr.Item("Id")) = fileID(i) Then
> > 'create this file in the folder
> > newRow = ds.Tables(strTable).NewRow()
> > newRow("filenaam") = dr.Item("filenaam")
> > newRow("status") = FixNull(dr.Item("status"))
> > newRow("lokatie") = newNode
> > newRow("extensie") = dr.Item("extensie")
> > newRow("grootte") = FixNull(dr.Item
> > ("grootte"))
> > newRow("datum_gemaakt") = FixNull(dr.Item
> > ("datum_gemaakt"))
> > newRow("datum_gewijzigd") = FixNull(dr.Item
> > ("datum_gewijzigd"))
> > newRow("expires") = FixNull(dr.Item
> > ("expires"))
> > newRow("hddlokatie") = FixNull(dr.Item
> > ("hddlokatie"))
> > newRow("samenvatting") = dr.Item
> > ("samenvatting")
> > newRow("kenmerk") = FixNull(dr.Item
> > ("kenmerk"))
> > newRow("auteur") = FixNull(dr.Item("auteur"))
> > newRow("soort") = FixNull(dr.Item("soort"))
> > newRow("inuit") = FixNull(dr.Item("inuit"))
> > ds.Tables(strTable).Rows..Add(newRow)
> > End If
> > Next
> > Next
>
> > 'sent the updated dataSet to the database
> > da.Update(ds, strTable)
>
> > Catch oException As OleDbException
> > MessageBox.Show(oException.Message)
>
> > Catch oException As Exception
> > MessageBox.Show(oException.Message)
>
> > End Try
> > conn.Close()
>
> > End Sub
>
> > Any thoughts?
>
> > Regards
> > Marco

Nick,

that's already in the code.

Marco

28 May 2009 10:41 PM

nak

Hi Co,

Oh yes so it is lol! Sorry, I got fooled by the word wrapping! doh!

Nick.

Show quoteHide quote

"Co" <vonclausow***@gmail.com> wrote in message
news:ce269cba-54fc-49b2-a277-ced8d392eca8@h28g2000yqd.googlegroups.com...
> On 28 mei, 22:25, "nak" <a***@a.com> wrote:
>> Hi Co,
>>
>>     Remember to include the Step keyword and let the compiler know that
>> you
>> want to count backwards...
>>
>>     For j As Integer = ds.Tables(0).Rows.Count - 1 To 0 Step -1
>>
>> Nick.
>>
>> "Co" <vonclausow***@gmail.com> wrote in message
>>
>> news:9b65bbb1-7b1e-423e-bdf1-fe3012b4dc45@f19g2000yqh.googlegroups.com...
>>
>> > Hi All,
>>
>> > I have following code to duplicate records in a database table:
>> > When I duplicate one record everything works but when I try to add
>> > more records I get this error:
>>
>> > "Conversion from type dbnull to type string is not valid."
>> > The code stops at the lien saying:
>> >                    dr = ds.Tables(0).Rows(j)
>>
>> > Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
>> > ByVal fileID() As String)
>>
>> >        Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
>> > oldNode
>> >        Dim strTable As String = "Bestanden"
>> >        Dim da As New OleDb.OleDbDataAdapter(sql, conn)
>> >        Dim cb As New OleDb.OleDbCommandBuilder(da)
>> >        Dim ds As New DataSet
>> >        Dim i As Integer
>> >        Dim newRow As DataRow
>>
>> >        Try
>> >            da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
>> >            da.Fill(ds, strTable)
>>
>> >            Dim dr As DataRow
>>
>> >            For i = 0 To fileID.Length - 1
>> >                For j As Integer = ds.Tables(0).Rows.Count - 1 To 0
>> > Step -1
>> >                    dr = ds.Tables(0).Rows(j)
>> >                    'For Each dr In ds.Tables(0).Rows
>> >                    If CStr(dr.Item("Id")) = fileID(i) Then
>> >                        'create this file in the folder
>> >                        newRow = ds.Tables(strTable).NewRow()
>> >                        newRow("filenaam") = dr.Item("filenaam")
>> >                        newRow("status") = FixNull(dr.Item("status"))
>> >                        newRow("lokatie") = newNode
>> >                        newRow("extensie") = dr.Item("extensie")
>> >                        newRow("grootte") = FixNull(dr.Item
>> > ("grootte"))
>> >                        newRow("datum_gemaakt") = FixNull(dr.Item
>> > ("datum_gemaakt"))
>> >                        newRow("datum_gewijzigd") = FixNull(dr.Item
>> > ("datum_gewijzigd"))
>> >                        newRow("expires") = FixNull(dr.Item
>> > ("expires"))
>> >                        newRow("hddlokatie") = FixNull(dr.Item
>> > ("hddlokatie"))
>> >                        newRow("samenvatting") = dr.Item
>> > ("samenvatting")
>> >                        newRow("kenmerk") = FixNull(dr.Item
>> > ("kenmerk"))
>> >                        newRow("auteur") = FixNull(dr.Item("auteur"))
>> >                        newRow("soort") = FixNull(dr.Item("soort"))
>> >                        newRow("inuit") = FixNull(dr.Item("inuit"))
>> >                        ds.Tables(strTable).Rows.Add(newRow)
>> >                    End If
>> >                Next
>> >            Next
>>
>> >                'sent the updated dataSet to the database
>> >                da.Update(ds, strTable)
>>
>> >        Catch oException As OleDbException
>> >            MessageBox.Show(oException.Message)
>>
>> >        Catch oException As Exception
>> >            MessageBox.Show(oException.Message)
>>
>> >        End Try
>> >        conn.Close()
>>
>> >    End Sub
>>
>> > Any thoughts?
>>
>> > Regards
>> > Marco
>
> Nick,
>
> that's already in the code.
>
> Marco
>

29 May 2009 6:21 AM

Co

Show quote Hide quote

On 29 mei, 00:41, "nak" <a***@a.com> wrote:
> Hi Co,
>
> Oh yes so it is lol! Sorry, I got fooled by the word wrapping! doh!
>
> Nick.
>
> "Co" <vonclausow***@gmail.com> wrote in message
>
> news:ce269cba-54fc-49b2-a277-ced8d392eca8@h28g2000yqd.googlegroups.com...
>
> > On 28 mei, 22:25, "nak" <a***@a.com> wrote:
> >> Hi Co,
>
> >> Remember to include the Step keyword and let the compiler know that
> >> you
> >> want to count backwards...
>
> >> For j As Integer = ds.Tables(0).Rows.Count - 1 To 0 Step -1
>
> >> Nick.
>
> >> "Co" <vonclausow***@gmail.com> wrote in message
>
> >>news:9b65bbb1-7b1e-423e-bdf1-fe3012b4dc45@f19g2000yqh.googlegroups.com....
>
> >> > Hi All,
>
> >> > I have following code to duplicate records in a database table:
> >> > When I duplicate one record everything works but when I try to add
> >> > more records I get this error:
>
> >> > "Conversion from type dbnull to type string is not valid."
> >> > The code stops at the lien saying:
> >> > dr = ds.Tables(0).Rows(j)
>
> >> > Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
> >> > ByVal fileID() As String)
>
> >> > Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
> >> > oldNode
> >> > Dim strTable As String = "Bestanden"
> >> > Dim da As New OleDb.OleDbDataAdapter(sql, conn)
> >> > Dim cb As New OleDb.OleDbCommandBuilder(da)
> >> > Dim ds As New DataSet
> >> > Dim i As Integer
> >> > Dim newRow As DataRow
>
> >> > Try
> >> > da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
> >> > da.Fill(ds, strTable)
>
> >> > Dim dr As DataRow
>
> >> > For i = 0 To fileID.Length - 1
> >> > For j As Integer = ds.Tables(0).Rows.Count - 1 To 0
> >> > Step -1
> >> > dr = ds.Tables(0).Rows(j)
> >> > 'For Each dr In ds.Tables(0).Rows
> >> > If CStr(dr.Item("Id")) = fileID(i) Then
> >> > 'create this file in the folder
> >> > newRow = ds.Tables(strTable).NewRow()
> >> > newRow("filenaam") = dr.Item("filenaam")
> >> > newRow("status") = FixNull(dr.Item("status"))
> >> > newRow("lokatie") = newNode
> >> > newRow("extensie") = dr.Item("extensie")
> >> > newRow("grootte") = FixNull(dr.Item
> >> > ("grootte"))
> >> > newRow("datum_gemaakt") = FixNull(dr.Item
> >> > ("datum_gemaakt"))
> >> > newRow("datum_gewijzigd") = FixNull(dr.Item
> >> > ("datum_gewijzigd"))
> >> > newRow("expires") = FixNull(dr.Item
> >> > ("expires"))
> >> > newRow("hddlokatie") = FixNull(dr.Item
> >> > ("hddlokatie"))
> >> > newRow("samenvatting") = dr.Item
> >> > ("samenvatting")
> >> > newRow("kenmerk") = FixNull(dr.Item
> >> > ("kenmerk"))
> >> > newRow("auteur") = FixNull(dr.Item("auteur"))
> >> > newRow("soort") = FixNull(dr.Item("soort"))
> >> > newRow("inuit") = FixNull(dr.Item("inuit"))
> >> > ds.Tables(strTable).Rows.Add(newRow)
> >> > End If
> >> > Next
> >> > Next
>
> >> > 'sent the updated dataSet to the database
> >> > da.Update(ds, strTable)
>
> >> > Catch oException As OleDbException
> >> > MessageBox.Show(oException.Message)
>
> >> > Catch oException As Exception
> >> > MessageBox.Show(oException.Message)
>
> >> > End Try
> >> > conn.Close()
>
> >> > End Sub
>
> >> > Any thoughts?
>
> >> > Regards
> >> > Marco
>
> > Nick,
>
> > that's already in the code.
>
> > Marco

Armin,

you were right it was the line with:

If CStr(dr.Item("Id")) = fileID(i) Then

Marco

29 May 2009 7:11 AM

Co

On 29 mei, 08:21, Co <vonclausow***@gmail.com> wrote:
Show quoteHide quote

> On 29 mei, 00:41, "nak" <a***@a.com> wrote:
>
>
>
> > Hi Co,
>
> > Oh yes so it is lol! Sorry, I got fooled by the word wrapping! doh!
>
> > Nick.
>
> > "Co" <vonclausow***@gmail.com> wrote in message
>
> >news:ce269cba-54fc-49b2-a277-ced8d392eca8@h28g2000yqd.googlegroups.com....
>
> > > On 28 mei, 22:25, "nak" <a***@a.com> wrote:
> > >> Hi Co,
>
> > >> Remember to include the Step keyword and let the compiler know that
> > >> you
> > >> want to count backwards...
>
> > >> For j As Integer = ds.Tables(0).Rows.Count - 1 To 0 Step -1
>
> > >> Nick.
>
> > >> "Co" <vonclausow***@gmail.com> wrote in message
>
> > >>news:9b65bbb1-7b1e-423e-bdf1-fe3012b4dc45@f19g2000yqh.googlegroups.com...
>
> > >> > Hi All,
>
> > >> > I have following code to duplicate records in a database table:
> > >> > When I duplicate one record everything works but when I try to add
> > >> > more records I get this error:
>
> > >> > "Conversion from type dbnull to type string is not valid."
> > >> > The code stops at the lien saying:
> > >> > dr = ds.Tables(0).Rows(j)
>
> > >> > Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
> > >> > ByVal fileID() As String)
>
> > >> > Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
> > >> > oldNode
> > >> > Dim strTable As String = "Bestanden"
> > >> > Dim da As New OleDb.OleDbDataAdapter(sql, conn)
> > >> > Dim cb As New OleDb.OleDbCommandBuilder(da)
> > >> > Dim ds As New DataSet
> > >> > Dim i As Integer
> > >> > Dim newRow As DataRow
>
> > >> > Try
> > >> > da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
> > >> > da.Fill(ds, strTable)
>
> > >> > Dim dr As DataRow
>
> > >> > For i = 0 To fileID.Length - 1
> > >> > For j As Integer = ds.Tables(0).Rows.Count - 1 To 0
> > >> > Step -1
> > >> > dr = ds.Tables(0).Rows(j)
> > >> > 'For Each dr In ds.Tables(0).Rows
> > >> > If CStr(dr.Item("Id")) = fileID(i) Then
> > >> > 'create this file in the folder
> > >> > newRow = ds.Tables(strTable).NewRow()
> > >> > newRow("filenaam") = dr.Item("filenaam")
> > >> > newRow("status") = FixNull(dr.Item("status"))
> > >> > newRow("lokatie") = newNode
> > >> > newRow("extensie") = dr.Item("extensie")
> > >> > newRow("grootte") = FixNull(dr.Item
> > >> > ("grootte"))
> > >> > newRow("datum_gemaakt") = FixNull(dr.Item
> > >> > ("datum_gemaakt"))
> > >> > newRow("datum_gewijzigd") = FixNull(dr.Item
> > >> > ("datum_gewijzigd"))
> > >> > newRow("expires") = FixNull(dr.Item
> > >> > ("expires"))
> > >> > newRow("hddlokatie") = FixNull(dr.Item
> > >> > ("hddlokatie"))
> > >> > newRow("samenvatting") = dr.Item
> > >> > ("samenvatting")
> > >> > newRow("kenmerk") = FixNull(dr.Item
> > >> > ("kenmerk"))
> > >> > newRow("auteur") = FixNull(dr.Item("auteur"))
> > >> > newRow("soort") = FixNull(dr.Item("soort"))
> > >> > newRow("inuit") = FixNull(dr.Item("inuit"))
> > >> > ds.Tables(strTable)..Rows.Add(newRow)
> > >> > End If
> > >> > Next
> > >> > Next
>
> > >> > 'sent the updated dataSet to the database
> > >> > da.Update(ds, strTable)
>
> > >> > Catch oException As OleDbException
> > >> > MessageBox.Show(oException.Message)
>
> > >> > Catch oException As Exception
> > >> > MessageBox.Show(oException.Message)
>
> > >> > End Try
> > >> > conn.Close()
>
> > >> > End Sub
>
> > >> > Any thoughts?
>
> > >> > Regards
> > >> > Marco
>
> > > Nick,
>
> > > that's already in the code.
>
> > > Marco
>
> Armin,
>
> you were right it was the line with:
>
> If CStr(dr.Item("Id")) = fileID(i) Then
>
> Marco

I found a way around, I wouldn't say solution but:

Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
ByVal fileID() As String)
        'this code copies a file from one folder to another
        'there's a problem when finding a record and adding it to the
dataset
        'because the dataset is not updated yet the new record will
not have an "Id"
        'that's why we have to exclude it for the search

        Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
oldNode & " ORDER BY Lokatie DESC"
        Dim strTable As String = "Bestanden"
        Dim da As New OleDb.OleDbDataAdapter(sql, conn)
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim ds As New DataSet
        Dim i As Integer
        Dim iniet As Integer = 1   'when a record is add we become 1
bigger
        Dim newRow As DataRow
        Dim bfound As Boolean      'when we found the record move on
to the next
        Try
            da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
            da.Fill(ds, strTable)

            Dim dr As DataRow

            For i = 0 To fileID.Length - 1
                For j As Integer = ds.Tables(0).Rows.Count - iniet To
0 Step -1
                    bfound = False
                    dr = ds.Tables(0).Rows(j)
                    If CStr(dr.Item("Id")) = fileID(i) Then
                        bfound = True
                        iniet += 1
                        newRow = ds.Tables(strTable).NewRow()
                        newRow("filenaam") = dr.Item("filenaam")
                        newRow("status") = FixNull(dr.Item("status"))
                        newRow("lokatie") = newNode
                        newRow("extensie") = dr.Item("extensie")
                        newRow("grootte") = FixNull(dr.Item
("grootte"))
                        newRow("datum_gemaakt") = FixNull(dr.Item
("datum_gemaakt"))
                        newRow("datum_gewijzigd") = FixNull(dr.Item
("datum_gewijzigd"))
                        newRow("expires") = FixNull(dr.Item
("expires"))
                        newRow("hddlokatie") = FixNull(dr.Item
("hddlokatie"))
                        newRow("samenvatting") = dr.Item
("samenvatting")
                        newRow("kenmerk") = FixNull(dr.Item
("kenmerk"))
                        newRow("auteur") = FixNull(dr.Item("auteur"))
                        newRow("soort") = FixNull(dr.Item("soort"))
                        newRow("inuit") = FixNull(dr.Item("inuit"))
                        ds.Tables(strTable).Rows.Add(newRow)
                    End If
                    If bfound = True Then Exit For
                Next
            Next

            'sent the updated dataSet to the database
            da.Update(ds, strTable)

        Catch oException As OleDbException
            MessageBox.Show(oException.Message)

        Catch oException As Exception
            MessageBox.Show(oException.Message)

        End Try
        conn.Close()

    End Sub

Marco

29 May 2009 11:55 AM

Armin Zingler

Co wrote:

>>>> Marco
>>
>> Armin,

If you want to address me, you should reply to my post.

>> you were right it was the line with:
>>
>> If CStr(dr.Item("Id")) = fileID(i) Then
>>
>> Marco
>
> I found a way around, I wouldn't say solution but:

Do you expect me to compare what you've changed now? Please say what you did
and/or show few lines of code. (few=not many) I only see that the line that
caused the error is still unchanged. And why wouldn't you say solution?

Armin

29 May 2009 12:48 PM

nak

Easy tiger! Manners are free you know and not everyone uses newsgroups as
much as yourself!

29 May 2009 2:32 PM

Cor Ligthert[MVP]

Nick, you are teasing...............and hopes Armin will become mad.

:-)

Cor

Show quoteHide quote

"nak" <a@a.com> wrote in message
news:9C14D5CB-E574-4148-AFA9-3BE29F4A7863@microsoft.com...
> Easy tiger! Manners are free you know and not everyone uses newsgroups as
> much as yourself!
>
>

29 May 2009 3:51 PM

nak

lol! Well actually I thought he was a little harsh ;)

Show quoteHide quote

"Cor Ligthert[MVP]" <Notmyfirstn***@planet.nl> wrote in message
news:#3RonpG4JHA.1512@TK2MSFTNGP05.phx.gbl...
> Nick, you are teasing...............and hopes Armin will become mad.
>
> :-)
>
> Cor
>
> "nak" <a@a.com> wrote in message
> news:9C14D5CB-E574-4148-AFA9-3BE29F4A7863@microsoft.com...
>> Easy tiger! Manners are free you know and not everyone uses newsgroups
>> as much as yourself!
>>
>>
>
>

29 May 2009 5:29 PM

Co

Show quote Hide quote

On 29 mei, 17:51, "nak" <a***@a.com> wrote:
> lol! Well actually I thought he was a little harsh ;)
>
> "Cor Ligthert[MVP]" <Notmyfirstn***@planet.nl> wrote in message
>
> news:#3RonpG4JHA.1512@TK2MSFTNGP05.phx.gbl...
>
> > Nick, you are teasing...............and hopes Armin will become mad.
>
> > :-)
>
> > Cor
>
> > "nak" <a***@a.com> wrote in message
> >news:9C14D5CB-E574-4148-AFA9-3BE29F4A7863@microsoft.com...
> >> Easy tiger! Manners are free you know and not everyone uses newsgroups
> >> as much as yourself!

As long as we are honoust to each other.

Marco

29 May 2009 3:05 PM

Co

Show quote Hide quote

On 29 mei, 13:55, "Armin Zingler" <az.nos***@freenet.de> wrote:
> Co wrote:
> >>>> Marco
>
> >> Armin,
>
> If you want to address me, you should reply to my post.
>
> >> you were right it was the line with:
>
> >> If CStr(dr.Item("Id")) = fileID(i) Then
>
> >> Marco
>
> > I found a way around, I wouldn't say solution but:
>
> Do you expect me to compare what you've changed now? Please say what you did
> and/or show few lines of code. (few=not many) I only see that the line that
> caused the error is still unchanged. And why wouldn't you say solution?
>
> Armin

I know sometimes reading is difficult but it was there all the time:

'there's a problem when finding a record and adding it to the dataset
'because the dataset is not updated yet the new record will not have
an "Id"
'that's why we have to exclude it for the search.

I don't think it's a solution because one of you guys will probably
say it's sh*t.
But it's working as far as I can see.

Marco

29 May 2009 3:39 PM

Armin Zingler

Co wrote:

>> Do you expect me to compare what you've changed now? Please say what
>> you did and/or show few lines of code. (few=not many) I only see
>> that the line that caused the error is still unchanged. And why
>> wouldn't you say solution?
>>
>> Armin
>
> I know sometimes reading is difficult but it was there all the time:
>
> 'there's a problem when finding a record and adding it to the dataset
> 'because the dataset is not updated yet the new record will not have
> an "Id"
> 'that's why we have to exclude it for the search.

I didn't intend to find this information myself inside a long full quote and
even more code.

> I don't think it's a solution because one of you guys will probably
> say it's sh*t.
> But it's working as far as I can see.

If you're looking for an ID it's ok to search only within the records that
already have an ID, IMO.

Armin

28 May 2009 10:37 PM

Armin Zingler

Co wrote:
Show quoteHide quote

> Hi All,
>
> I have following code to duplicate records in a database table:
> When I duplicate one record everything works but when I try to add
> more records I get this error:
>
> "Conversion from type dbnull to type string is not valid."
> The code stops at the lien saying:
>                    dr = ds.Tables(0).Rows(j)

>                    dr = ds.Tables(0).Rows(j)
>                    'For Each dr In ds.Tables(0).Rows
>                    If CStr(dr.Item("Id")) = fileID(i) Then

Are you sure it is not in the next line ("If...")? I don't know how the
error can happen in the line before.

If it's the last line, the field "id" obviously contains Null
(DBNull.Value). Check the table content.

Armin

29 May 2009 7:57 AM

Cor Ligthert[MVP]

Hi Co,

Helping you is no problem, but you can make it more easy for us.

It is not needed for us to see your complete program. As it is the time for
that you will be asked for sure.

Give us the relevant parts, then helping you is much easier and more
effective

Something in general about programming, do all data names in English or do
them in Dutch, now it looks so amateuristic.

There is an advance for using Dutch names, but I find it awful reading so I
don't do that.

What is the reason you do it bottom up, that should be done when you are
deleting?

Cor

Show quoteHide quote

"Co" <vonclausow***@gmail.com> wrote in message
news:9b65bbb1-7b1e-423e-bdf1-fe3012b4dc45@f19g2000yqh.googlegroups.com...
> Hi All,
>
> I have following code to duplicate records in a database table:
> When I duplicate one record everything works but when I try to add
> more records I get this error:
>
> "Conversion from type dbnull to type string is not valid."
> The code stops at the lien saying:
>                    dr = ds.Tables(0).Rows(j)
>
>
> Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
> ByVal fileID() As String)
>
>        Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
> oldNode
>        Dim strTable As String = "Bestanden"
>        Dim da As New OleDb.OleDbDataAdapter(sql, conn)
>        Dim cb As New OleDb.OleDbCommandBuilder(da)
>        Dim ds As New DataSet
>        Dim i As Integer
>        Dim newRow As DataRow
>
>        Try
>            da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
>            da.Fill(ds, strTable)
>
>            Dim dr As DataRow
>
>            For i = 0 To fileID.Length - 1
>                For j As Integer = ds.Tables(0).Rows.Count - 1 To 0
> Step -1
>                    dr = ds.Tables(0).Rows(j)
>                    'For Each dr In ds.Tables(0).Rows
>                    If CStr(dr.Item("Id")) = fileID(i) Then
>                        'create this file in the folder
>                        newRow = ds.Tables(strTable).NewRow()
>                        newRow("filenaam") = dr.Item("filenaam")
>                        newRow("status") = FixNull(dr.Item("status"))
>                        newRow("lokatie") = newNode
>                        newRow("extensie") = dr.Item("extensie")
>                        newRow("grootte") = FixNull(dr.Item
> ("grootte"))
>                        newRow("datum_gemaakt") = FixNull(dr.Item
> ("datum_gemaakt"))
>                        newRow("datum_gewijzigd") = FixNull(dr.Item
> ("datum_gewijzigd"))
>                        newRow("expires") = FixNull(dr.Item
> ("expires"))
>                        newRow("hddlokatie") = FixNull(dr.Item
> ("hddlokatie"))
>                        newRow("samenvatting") = dr.Item
> ("samenvatting")
>                        newRow("kenmerk") = FixNull(dr.Item
> ("kenmerk"))
>                        newRow("auteur") = FixNull(dr.Item("auteur"))
>                        newRow("soort") = FixNull(dr.Item("soort"))
>                        newRow("inuit") = FixNull(dr.Item("inuit"))
>                        ds.Tables(strTable).Rows.Add(newRow)
>                    End If
>                Next
>            Next
>
>                'sent the updated dataSet to the database
>                da.Update(ds, strTable)
>
>        Catch oException As OleDbException
>            MessageBox.Show(oException.Message)
>
>        Catch oException As Exception
>            MessageBox.Show(oException.Message)
>
>        End Try
>        conn.Close()
>
>    End Sub
>
> Any thoughts?
>
> Regards
> Marco

29 May 2009 9:19 AM

Co

Show quote Hide quote

On 29 mei, 09:57, "Cor Ligthert[MVP]" <Notmyfirstn***@planet.nl>
wrote:
> Hi Co,
>
> Helping you is no problem, but you can make it more easy for us.
>
> It is not needed for us to see your complete program. As it is the time for
> that you will be asked for sure.
>
> Give us the relevant parts, then helping you is much easier and more
> effective
>
> Something in general about programming, do all data names in English or do
> them in Dutch, now it looks so amateuristic.
>
> There is an advance for using Dutch names, but I find it awful reading so I
> don't do that.
>
> What is the reason you do it bottom up, that should be done when you are
> deleting?
>
> Cor
>
> "Co" <vonclausow***@gmail.com> wrote in message
>
> news:9b65bbb1-7b1e-423e-bdf1-fe3012b4dc45@f19g2000yqh.googlegroups.com...
>
> > Hi All,
>
> > I have following code to duplicate records in a database table:
> > When I duplicate one record everything works but when I try to add
> > more records I get this error:
>
> > "Conversion from type dbnull to type string is not valid."
> > The code stops at the lien saying:
> > dr = ds.Tables(0).Rows(j)
>
> > Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
> > ByVal fileID() As String)
>
> > Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
> > oldNode
> > Dim strTable As String = "Bestanden"
> > Dim da As New OleDb.OleDbDataAdapter(sql, conn)
> > Dim cb As New OleDb.OleDbCommandBuilder(da)
> > Dim ds As New DataSet
> > Dim i As Integer
> > Dim newRow As DataRow
>
> > Try
> > da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
> > da.Fill(ds, strTable)
>
> > Dim dr As DataRow
>
> > For i = 0 To fileID.Length - 1
> > For j As Integer = ds.Tables(0).Rows.Count - 1 To 0
> > Step -1
> > dr = ds.Tables(0).Rows(j)
> > 'For Each dr In ds.Tables(0).Rows
> > If CStr(dr.Item("Id")) = fileID(i) Then
> > 'create this file in the folder
> > newRow = ds.Tables(strTable).NewRow()
> > newRow("filenaam") = dr.Item("filenaam")
> > newRow("status") = FixNull(dr.Item("status"))
> > newRow("lokatie") = newNode
> > newRow("extensie") = dr.Item("extensie")
> > newRow("grootte") = FixNull(dr.Item
> > ("grootte"))
> > newRow("datum_gemaakt") = FixNull(dr.Item
> > ("datum_gemaakt"))
> > newRow("datum_gewijzigd") = FixNull(dr.Item
> > ("datum_gewijzigd"))
> > newRow("expires") = FixNull(dr.Item
> > ("expires"))
> > newRow("hddlokatie") = FixNull(dr.Item
> > ("hddlokatie"))
> > newRow("samenvatting") = dr.Item
> > ("samenvatting")
> > newRow("kenmerk") = FixNull(dr.Item
> > ("kenmerk"))
> > newRow("auteur") = FixNull(dr.Item("auteur"))
> > newRow("soort") = FixNull(dr.Item("soort"))
> > newRow("inuit") = FixNull(dr.Item("inuit"))
> > ds.Tables(strTable).Rows..Add(newRow)
> > End If
> > Next
> > Next
>
> > 'sent the updated dataSet to the database
> > da.Update(ds, strTable)
>
> > Catch oException As OleDbException
> > MessageBox.Show(oException.Message)
>
> > Catch oException As Exception
> > MessageBox.Show(oException.Message)
>
> > End Try
> > conn.Close()
>
> > End Sub
>
> > Any thoughts?
>
> > Regards
> > Marco

You are right I should do them in English.
Is there a difference in going bottom up?

Marco

29 May 2009 10:15 AM

Cor Ligthert[MVP]

I don't know, but it is a uncommon, while at the moment the code you show us
is not direct inviting to look for the problem.

Therefore why not first try it in a normal way?

Cor

Show quoteHide quote

"Co" <vonclausow***@gmail.com> wrote in message
news:bbcfff25-d156-42a2-a7fa-d6f380e7bea7@m19g2000yqk.googlegroups.com...
On 29 mei, 09:57, "Cor Ligthert[MVP]" <Notmyfirstn***@planet.nl>
wrote:
> Hi Co,
>
> Helping you is no problem, but you can make it more easy for us.
>
> It is not needed for us to see your complete program. As it is the time
> for
> that you will be asked for sure.
>
> Give us the relevant parts, then helping you is much easier and more
> effective
>
> Something in general about programming, do all data names in English or do
> them in Dutch, now it looks so amateuristic.
>
> There is an advance for using Dutch names, but I find it awful reading so
> I
> don't do that.
>
> What is the reason you do it bottom up, that should be done when you are
> deleting?
>
> Cor
>
> "Co" <vonclausow***@gmail.com> wrote in message
>
> news:9b65bbb1-7b1e-423e-bdf1-fe3012b4dc45@f19g2000yqh.googlegroups.com...
>
> > Hi All,
>
> > I have following code to duplicate records in a database table:
> > When I duplicate one record everything works but when I try to add
> > more records I get this error:
>
> > "Conversion from type dbnull to type string is not valid."
> > The code stops at the lien saying:
> > dr = ds.Tables(0).Rows(j)
>
> > Sub FileDuplicator(ByVal oldNode As Integer, ByVal newNode As Integer,
> > ByVal fileID() As String)
>
> > Dim sql As String = "SELECT * FROM Bestanden WHERE Lokatie=" &
> > oldNode
> > Dim strTable As String = "Bestanden"
> > Dim da As New OleDb.OleDbDataAdapter(sql, conn)
> > Dim cb As New OleDb.OleDbCommandBuilder(da)
> > Dim ds As New DataSet
> > Dim i As Integer
> > Dim newRow As DataRow
>
> > Try
> > da.SelectCommand = New OleDb.OleDbCommand(sql, conn)
> > da.Fill(ds, strTable)
>
> > Dim dr As DataRow
>
> > For i = 0 To fileID.Length - 1
> > For j As Integer = ds.Tables(0).Rows.Count - 1 To 0
> > Step -1
> > dr = ds.Tables(0).Rows(j)
> > 'For Each dr In ds.Tables(0).Rows
> > If CStr(dr.Item("Id")) = fileID(i) Then
> > 'create this file in the folder
> > newRow = ds.Tables(strTable).NewRow()
> > newRow("filenaam") = dr.Item("filenaam")
> > newRow("status") = FixNull(dr.Item("status"))
> > newRow("lokatie") = newNode
> > newRow("extensie") = dr.Item("extensie")
> > newRow("grootte") = FixNull(dr.Item
> > ("grootte"))
> > newRow("datum_gemaakt") = FixNull(dr.Item
> > ("datum_gemaakt"))
> > newRow("datum_gewijzigd") = FixNull(dr.Item
> > ("datum_gewijzigd"))
> > newRow("expires") = FixNull(dr.Item
> > ("expires"))
> > newRow("hddlokatie") = FixNull(dr.Item
> > ("hddlokatie"))
> > newRow("samenvatting") = dr.Item
> > ("samenvatting")
> > newRow("kenmerk") = FixNull(dr.Item
> > ("kenmerk"))
> > newRow("auteur") = FixNull(dr.Item("auteur"))
> > newRow("soort") = FixNull(dr.Item("soort"))
> > newRow("inuit") = FixNull(dr.Item("inuit"))
> > ds.Tables(strTable).Rows.Add(newRow)
> > End If
> > Next
> > Next
>
> > 'sent the updated dataSet to the database
> > da.Update(ds, strTable)
>
> > Catch oException As OleDbException
> > MessageBox.Show(oException.Message)
>
> > Catch oException As Exception
> > MessageBox.Show(oException.Message)
>
> > End Try
> > conn.Close()
>
> > End Sub
>
> > Any thoughts?
>
> > Regards
> > Marco

You are right I should do them in English.
Is there a difference in going bottom up?

Marco