Visual Basic.Net - Updating MySql Tables

[Keith Austin]

I am writing my first "large" app in Visual Studio (VB.net) 2005 with the .net connector. Have used MySQL with ADO in VB6 and VBA but new to .net

Very simple algorithm.

1. Select table with column.date= whatever
2. Using loop through dataset and print whatever.
3. Prompt User - FLAG (OK Cancel)
4. If yes update print.date column in each record

When I first prototyped a month ago no problem. Problem appears to be with running out of memory. There is no bad data as the rows already exist and it is the same data written to each row. Small datasets, no problems (<50). I think I am using the wrong approach.

I think I am wrong in the call I make for an sql execute:
(details are below if you want/need more)
***************'Code where I update the data
Public Shared Function CmdQuery(ByVal strSQL As String) As Integer
Dim objConnection As MySqlConnection = Lookup.getConnection()
Dim iRet As Integer
iRet = MySqlHelper.ExecuteNonQuery(objConnection, strSQL, Nothing)
Return iRet
End Function
'**Lookup.getConnection reads app.config

From the Immediate window,
we see that everything is running fine up to 88 records by the Debug.Print
.
.
.
.
.
UPDATE IGNORE ads SET ad_print_flag ='Y', ad_print_flag_DT ='2007-01-30 19:39:02' WHERE ad_id = 533606 ;
UPDATE IGNORE ads SET ad_print_flag ='Y', ad_print_flag_DT ='2007-01-30 19:39:02' WHERE ad_id = 533607 ;
UPDATE IGNORE ads SET ad_print_flag ='Y', ad_print_flag_DT ='2007-01-30 19:39:02' WHERE ad_id = 533608 ;
UPDATE IGNORE ads SET ad_print_flag ='Y', ad_print_flag_DT ='2007-01-30 19:39:02' WHERE ad_id = 533609 ;
UPDATE IGNORE ads SET ad_print_flag ='Y', ad_print_flag_DT ='2007-01-30 19:39:02' WHERE ad_id = 533610 ;
UPDATE IGNORE ads SET ad_print_flag ='Y', ad_print_flag_DT ='2007-01-30 19:39:02' WHERE ad_id = 533611 ;
UPDATE IGNORE ads SET ad_print_flag ='Y', ad_print_flag_DT ='2007-01-30 19:39:02' WHERE ad_id = 533612 ;

and then

A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'System.ApplicationException' occurred in FOSCMD.dll
A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
*** press <enter> at ex msgbox
*** and repeats error for any subsequent UPDATE command.

UPDATE IGNORE ads SET ad_print_flag ='Y', ad_print_flag_DT ='2007-01-30 19:39:02' WHERE ad_id = 533613 ;
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'System.ApplicationException' occurred in FOSCMD.dll
A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
etc.. etc,,, etc...

This is the gist of how I am attempting to update.
I have a dataset, I have looped through it and produced desired output and the operator has input OK. I loop through the dataset again, read the id create an UPDATE sql statement which is passed to this function

Code call by looping through dataset
Public Shared Function UpdateClassifiedAdPF(ByVal strID As String, _
ByVal strPrintFlag As String, _
ByVal strPrintFlagDT As String, _
ByVal ErrorMsgDataSet As System.Data.DataSet) As Boolean

Dim objSQLTransaction As SqlTransaction = Nothing
Dim objSQLConnection As SqlConnection = Nothing
Dim bRET As Boolean = True
Try
Dim strSQL As String
strSQL = "UPDATE IGNORE ads SET "
strSQL += "ad_print_flag ='" & strPrintFlag & "', "
strSQL += "ad_print_flag_DT ='" & strPrintFlagDT & "' "
strSQL += "WHERE ad_id = " & strID & " ;"
Debug.Print(strSQL)
Dim iRet As Integer = AD.CmdQuery(strSQL)
If iRet = 0 Then
bRET = False
Return bRET
Stop
Exit Function

End If
'Return bRET

Catch ex As Exception
If Not objSQLTransaction Is Nothing Then
objSQLTransaction.Rollback()
End If
ex.Source = ex.Source
Throw New ApplicationException("Failed in Execute method of UPDATE for Ad.", ex)

Finally
If Not objSQLConnection Is Nothing Then
objSQLConnection.Close()
End If
End Try
Return bRET
End Function

***************'Code where I update the data
Public Shared Function CmdQuery(ByVal strSQL As String) As Integer
Dim objConnection As MySqlConnection = Lookup.getConnection()
Dim iRet As Integer
iRet = MySqlHelper.ExecuteNonQuery(objConnection, strSQL, Nothing)
Return iRet
End Function
'**Lookup.getConnection reads app.config