Why does this code not generate an error?

D

Daniel

Hi Group,

Why does the below code not generate an error? I have a table with x
number of records. Each record is read from the DB into a hashtable.
Then I add to the hashtable additional keys. From here, i run through
each key in the hashtable and change the associated value. Now, when I
update the DB with the keys and values from the hashtable, all works
well. However, I also added to the hashtable extra items. So, as far
as I am concerned, when the code tries to do an update for a record that
does not exist in the table it should generate an error because it could
not find it. However, it just ignores it and continues as if everything
is great. I have not added any error handling to the code so in theory
it should complain.

Here is the code.

Dim FileTable as new HashTable

ScanDA = New SqlDataAdapter
ScanConStr = "server=localhost;uid=SA;password=123;database=myDB"
ScanConn = New SqlConnection(ScanConStr)
ScanQuery = "SELECT FilePath,Comment FROM myTable"
ScanCMD = New SqlCommand(ScanQuery, ScanConn)

ScanDA.SelectCommand = ScanCMD
ScanConn.Open()


For Each HashLine As DictionaryEntry In FileTable

UpdateMD5Cmd = "UPDATE myTable SET " & Chr(34) & "newMD5" & Chr(34) &
"='updated' WHERE " & Chr(34) & "FilePath" & Chr(34) & "='" &
HashLine.Key & "'"

ScanCMD.CommandText = UpdateMD5Cmd
ScanCMD.ExecuteNonQuery()

Next

ScanConn.Close()



So, it loads all the file names and comments into the hashtable
(filename is the key and the comment is the value). Then it processes
the comments. After that, it uploads the keys back into the FilePath
column and the values back into the comments column.
So, it should throw an error because it cannot find the new files i
added to the hashtable.

I hope this is clear! Thanks.
 
R

Rick Mogstad

Daniel said:
Hi Group,

Why does the below code not generate an error? I have a table with x
number of records. Each record is read from the DB into a hashtable. Then
I add to the hashtable additional keys. From here, i run through each key
in the hashtable and change the associated value. Now, when I update the
DB with the keys and values from the hashtable, all works well. However,
I also added to the hashtable extra items. So, as far as I am concerned,
when the code tries to do an update for a record that does not exist in
the table it should generate an error because it could not find it.
However, it just ignores it and continues as if everything is great. I
have not added any error handling to the code so in theory it should
complain.

Here is the code.

Dim FileTable as new HashTable

ScanDA = New SqlDataAdapter
ScanConStr = "server=localhost;uid=SA;password=123;database=myDB"
ScanConn = New SqlConnection(ScanConStr)
ScanQuery = "SELECT FilePath,Comment FROM myTable"
ScanCMD = New SqlCommand(ScanQuery, ScanConn)

ScanDA.SelectCommand = ScanCMD
ScanConn.Open()


For Each HashLine As DictionaryEntry In FileTable

UpdateMD5Cmd = "UPDATE myTable SET " & Chr(34) & "newMD5" & Chr(34) &
"='updated' WHERE " & Chr(34) & "FilePath" & Chr(34) & "='" & HashLine.Key
& "'"

ScanCMD.CommandText = UpdateMD5Cmd
ScanCMD.ExecuteNonQuery()

Next

ScanConn.Close()



So, it loads all the file names and comments into the hashtable (filename
is the key and the comment is the value). Then it processes the comments.
After that, it uploads the keys back into the FilePath column and the
values back into the comments column.
So, it should throw an error because it cannot find the new files i added
to the hashtable.

I hope this is clear! Thanks.
Click to expand...


You are allowed to update 0 records, so if it cannot find the record, it
will simply not update anything.

Perhaps I am not understanding, but it seems that this is the case.
 
A

alantolan

1) You might try using string.format for creating the command string -
makes things a touch more readable

e.g.
private const CMD_STRING as string = "UPDATE myTable SET newMD5 =
'updated' WHERE FilePath = '{0}'"

UpdateMD5Cmd= string.Format(CMD_STRING, cstr(HashLine.Key))


2) Debug.writeline() the command strings and them try to run them by
hand to see if they update anything - it is possible that they are
finding no records to update (say a case comparison problem) and thus
are not updating anything but not giving any errors.


Alan.
 
D

Daniel

Rick said:
You are allowed to update 0 records, so if it cannot find the record, it
will simply not update anything.

Perhaps I am not understanding, but it seems that this is the case.
Click to expand...
Hmmm, so then, how can I get VB to throw an error if there is nothing to
update?
 
C

Cor Ligthert

Daniel,

Why don't you just try it with a complete update command (nothing variable),
from what you are sure that it is incorrect and sees what it does. Now you
keeping to much dependencies.

Just my thought,

Cor
 
D

Daniel

Cor said:
Daniel,

Why don't you just try it with a complete update command (nothing variable),
from what you are sure that it is incorrect and sees what it does. Now you
keeping to much dependencies.

Just my thought,

Cor
Click to expand...

How would that differ from what I have written? I know that the code is
correct so this seems to be something by design. But I need VB to throw
an error if it cannot find the record. Any ideas?
 
C

C-Services Holland b.v.

Daniel said:
Hi Group,

Why does the below code not generate an error? I have a table with x
number of records. Each record is read from the DB into a hashtable.
Then I add to the hashtable additional keys. From here, i run through
each key in the hashtable and change the associated value. Now, when I
update the DB with the keys and values from the hashtable, all works
well. However, I also added to the hashtable extra items. So, as far
as I am concerned, when the code tries to do an update for a record that
does not exist in the table it should generate an error because it could
not find it. However, it just ignores it and continues as if everything
is great. I have not added any error handling to the code so in theory
it should complain.

Here is the code.

Dim FileTable as new HashTable

ScanDA = New SqlDataAdapter
ScanConStr = "server=localhost;uid=SA;password=123;database=myDB"
ScanConn = New SqlConnection(ScanConStr)
ScanQuery = "SELECT FilePath,Comment FROM myTable"
ScanCMD = New SqlCommand(ScanQuery, ScanConn)

ScanDA.SelectCommand = ScanCMD
ScanConn.Open()


For Each HashLine As DictionaryEntry In FileTable

UpdateMD5Cmd = "UPDATE myTable SET " & Chr(34) & "newMD5" & Chr(34) &
"='updated' WHERE " & Chr(34) & "FilePath" & Chr(34) & "='" &
HashLine.Key & "'"

ScanCMD.CommandText = UpdateMD5Cmd
ScanCMD.ExecuteNonQuery()

Next

ScanConn.Close()



So, it loads all the file names and comments into the hashtable
(filename is the key and the comment is the value). Then it processes
the comments. After that, it uploads the keys back into the FilePath
column and the values back into the comments column.
So, it should throw an error because it cannot find the new files i
added to the hashtable.

I hope this is clear! Thanks.
Click to expand...

This is expected behaviour.. You'll have to check for the existance of
hashline.key and if it exists, update it otherwise add it. A stored
procedure could do it for you so you only have to call the sp with all
the relevant parameters.
 
D

Daniel

C-Services Holland b.v. said:
This is expected behaviour.. You'll have to check for the existance of
hashline.key and if it exists, update it otherwise add it. A stored
procedure could do it for you so you only have to call the sp with all
the relevant parameters.
Click to expand...

OK, i'll try this. The trouble with checking to see if hashline.key
exists is that if the hashtable has 5000 keys it will take a long time
to query the DB for each hashline.key.

Also, I can't use stored procedures as I am connecting to MSDE.
 
A

Armin Zingler

Daniel said:
OK, not an alternative but a solution!!!
Click to expand...

Call it alternative or solution, the question is still, why? A solution for
what? Check the return value. If it's 0, nothing's been updated.

Armin
 
D

Daniel

Armin said:
Call it alternative or solution, the question is still, why? A solution
for what? Check the return value. If it's 0, nothing's been updated.

Armin
Click to expand...

ahhhh, so i will get a value returned?!!! Therefore is this what you'd
suggest?

Dim UpdateVal as Integer

UpdateVal = ExecuteNonQuery

If UpdateVal = 0 Then ......
 
A

Armin Zingler

Daniel said:
ahhhh, so i will get a value returned?!!! Therefore is this what
you'd suggest?

Dim UpdateVal as Integer

UpdateVal = ExecuteNonQuery

If UpdateVal = 0 Then ......
Click to expand...


I thought, writing "ExecuteNonQuery is a function" is sufficient to put you
on the right track. :)

Armin
 
C

Cor Ligthert

Daniel,
How would that differ from what I have written? I know that the code is
correct so this seems to be something by design. But I need VB to throw
an error if it cannot find the record. Any ideas?
Click to expand...
It would probably led that you see earlier the problem. Most people are
blind for the code they have checked and checked. Than there should be taken
another approach.

I hope this helps,

Cor
 

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

Why does this code not work....... 4
why does this code not produce an error? 2
Why is my code returning an error?. 6
Macro Help... why does this error out? 1
Why does this fail with an invalid type error 3
Why does x.ToString() throw an error if x == null? 16
Why this code does not cause error in .NET 2 & 3.5 (bug?) 13
Update Rank List - Why does this code not work?! 1

Top