getting error on record duplication

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
 
N

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.
 
N

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.
 
C

Co

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
 
C

Co

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
 
A

Armin Zingler

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
 
A

Armin Zingler

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
 
C

Co

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
 
C

Co

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
 
C

Co

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
 
C

Co

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
 
C

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
 
C

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
 
C

Co

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
 
C

Co

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
 
C

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

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
 
C

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

error on adding record to database 2
create dynamic html with vb.net 8
How do I fill a Datagridview? 4
problem reading array data from structure 20
add record to Access Database 5
deleted row information cannot be accessed... 5
Add record to Access Databases 3
Add Record to MS Access Database 11

Top