Error: "Specified Cast Not Valid"

[erezrave]

Hello...

I have an error "Specified cast not valid" when I try updating a database using SQL line.

the SQL line is:
"UPDATE tblTableName SET fldFieldName='" & String & "' WHERE fldID=" & Integer

it looks like something is killing the connection because with error traping once the error occurs, it never works since.

please help me find the source for the problem.

[gknierim]

Are you actually using 'String' and 'Integer' as your variable names? If you are, you need to rename them as they are object types. Hopefully you are not.

Do you have Option Strict On ? If so, you may need to convert your variables to the appropiate types.

Other than that, the sql statement looks correct provided that fldFieldName is defined as a string(varchar/text) in your database and fldID is defined as an integer. The error may be related to something else in your code.

[erezrave]

ofcourse I am using other variable names !!!

I wrote "string" because it shouldn't interest you what the name of the variable is...

the SQL as it is:

UPDATE tblImages SET fldImageName='" & mImageName & "' WHERE fldID=" & mImageID

when I step-debug the program, the SQL recieves a value of:

UPDATE tblImages SET fldImageName='BG.jpg' WHERE fldID=12
(for example)

what's weird is that it works good for about 5 images and after a few, it gives that error. "Specified cast not valid" and since that moment, every attempt to use the connection to the DB is failed by the same error (like it damaged the connection !!)

[gknierim]

I wrote "string" because it shouldn't interest you what the name of the variable is...

Don't get snippy buddy....:D ! You'd be surprised what some people would use!! Just asking the obvious here!

Now, are you performing this update in a loop? Are you using Access or SQL Server? How about posting some more of your code.

[erezrave]

ok...

sorry .... I just thought it's obvious !!


I can't post more code for now but it has nothing to do with the code...

I have some threads parallel...
every thread that finish it's work raises event "TaskFinished" in the main program (not a thread)
in this function there is a semafore (SyncLock Me). (for preventing 2 tasks to end at exact same time)
inside the SyncLock there is a loop that updates some images and the task in the access database. everytime a task ends it runs one time for the task and one time for every image in the task. after about 5 of 6 (sometimes 1, sometimes 100) tasks that works good, suddenly it "LOCKS" the connection and every attempt to INSERT / DELETE / UPDATE the DB has this error: "Specified case not valid".
I am using OLEDB connection with Jet 4.0
I am using MS-Access XP
...

Please Help.

[gknierim]

Well, I'm not sure about your SyncLock stuff because I think that function is uneccessary. I have written several applications (with vb.net that perform loops and update the database and haven't had any problems. This is even with updating 1000's of records. So...........

there is a semafore (SyncLock Me). (for preventing 2 tasks to end at exact same time)

Why are you concerned with this? Do you have multiple connections to the same database? Why should 2 tasks ending at the same time have anything to do with you executing your UPDATE statement? I'm not questioning your judgement on how you are accomplishing this task, just wondering why you are concerned with the above that you have this SyncLock whatever function.

You say its not in your code. I think it is. Post your code when you can and we'll take a look at it then unless someone else has an idea of what is going on.

[erezrave]

Hey.

The problems I have are not in the semafore....
the reason why I have several connections and sevral threads at the same time is because I have a exe file that scans the Internet and look for some things, because an avarage speed of this exe using the internet is 10 k/s, I execute about 10 at the same time and the speed is always at my maximum connection speed at the office (750 kb/s - 96 k/s).
using object oriented programming, I have an object that update the database before, during and after the exe is running.
if 2 or more exe ended at the same time, the connections will collapse. that's why I am using semafore.
the problem is not with the semafore, the problem is with the connection...

I am updating 50000 lines in the DB and suddenly it stops working with the error (exception) "Specified cast not valid".

even if I put it outside the semafore - the same...
using an other connection - the same...
deleting the problematic lines - the same (will get stuck on the next access to the DB)..
it looks like some action I made have caused an error in the DB and this error locks the DB for other connections.

here is some code...
mDB is an object for connecting the DB.
the While is for Retries defined by the Setup Object (SetupO)




Private Sub TaskEnded(ByVal CrwID As Integer, ByRef HandledTask As Task)

Dim Itm As ListViewItem
Dim Rty As Integer = 0
Dim DtSet As DataSet
Dim DtRow As DataRow
Dim SqlStr As String
Dim ScanTimeStr As String

SyncLock Me

ScanTimeStr = Now.Date & " " & Now.TimeOfDay.ToString

Rty = 0
While Rty <= SetupO.DBRetries
Try
SqlStr = "UPDATE tblURLs SET fldLastScaned='" & ScanTimeStr & "' WHERE fldTaskID=" & HandledTask.TaskID
mDB.Execute(SqlStr)
Rty = 0
Exit While
Catch Ex As Exception
Thread.Sleep(100 * (CrwID Mod 10))
Rty += 1
LogObj.WriteLine("Retry " & Rty & " for finishing task " & HandledTask.TaskID)
If Rty = SetupO.DBRetries Then
ErrObj.HandleError(Ex)
Exit While
End If
End Try
End While

While Rty <= SetupO.DBRetries
Try
mDB.Execute("UPDATE tblTasks SET fldStatus=" & Task.TaskStatus.Completed & " WHERE fldID=" & HandledTask.TaskID)
Exit While
Catch Ex As Exception
Thread.Sleep(100 * (CrwID Mod 10))
Rty += 1
LogObj.WriteLine("Retry " & Rty & " for finishing task " & HandledTask.TaskID)
If Rty = SetupO.DBRetries Then
ErrObj.HandleError(Ex)
Exit While
End If
End Try
End While

Try
For Each Itm In dlTasks.Items
If Itm.Text = HandledTask.TaskID Then
Itm.SubItems(3).Text = "Compelted"
Exit For
End If
Next
Catch Ex As Exception
ErrObj.HandleError(ex)
End Try

End SyncLock


End Sub

[gknierim]

First, where did you pick up the term Semafore(I think its supposed to be semaphore but thats irrelevant)? Never heard that one before....:)

Second, Access in the past has had several issues with concurrent connections from the same caller. Search MSDN and you will find several articles. I know that this is especially true when updating the database from an application. The caller I am referring to is the same program from the same PC...not the same program from different PCs. You will get unexpected results (i.e. sometimes 1 row sometimes 10000). It all depends at that moment.

Not sure what else to do for here bud. Maybe someone else can help you.

[erezrave]

well...

thank you,

I tried looking for the solution in MSDN,
I tried asking VB masters and online microsoft help...
No solution !!

for now I managed to skip the problem, Instead of updating the DB with "UPDATE.... WHERE .... = ..."... for 10-20 lines together,
I read to a data set all the fldID I need o update and I update them one by one....
a little slower but it works.

thank you all for now, If anyone have the solution for the problem, please contact me.

E.R, Israel.