getting error on record duplication

  • Thread starter Thread starter Co
  • Start date Start date
C

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
 
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.
 
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.
 
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.
Click to expand...

Nick,

that's already in the code.

Marco
 
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.
Click to expand...

Nick,

that's already in the code.

Marco
 
Co said:
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
Click to expand...


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
 
Co said:
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
Click to expand...


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
 
Hi Co,

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

Nick.
Click to expand...

Armin,

you were right it was the line with:

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

Marco
 
Hi Co,

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

Nick.
Click to expand...

Armin,

you were right it was the line with:

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

Marco
 
Armin,

you were right it was the line with:

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

Marco
Click to expand...

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
 
Armin,

you were right it was the line with:

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

Marco
Click to expand...

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
 
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
 
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
 
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 soI
don't do that.

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

Cor
Click to expand...

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

Marco
 
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 soI
don't do that.

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

Cor
Click to expand...

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

Marco
 
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

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
Click to expand...

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

Marco
 
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

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
Click to expand...

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

Marco
 
Back
Top