VB.NET Executereader out of memory when switch to Chinese language

Question

I have this code that works very well in different languages but when I change the regional setting number format to "Chinese Traditional" Out of memory exception happens on Executereader! What to do? Thank you!

Dim dv As DataView
Dim dt As DataTable = New DataTable
dv = New DataView(dt)
Dim dr As DataRow
Dim myreader As OleDbDataReader
dbobj = New dboperation("2")
dbobj.objcmd.CommandType = CommandType.Text
dbobj.objcmd.CommandText = "SELECT [geonameid], [name], [asciiname], [alternatenames], [latitude], [longitude], [timezone], [admin1 code], [code] , [Field2], [Field3] FROM atlas_view a left join Admin1CodesASCII d on a.code=d.Field1 WHERE (@Country Is NULL OR [country code]=@Country) and (@City Is NULL OR [alternatenames] like '%'+@City+'%')" ' OR [Alt] like '%'+@City+'%')"

dbobj.objcmd.Parameters.AddWithValue("@Country", IIf(Country = "" Or Country Is Nothing, DBNull.Value, mMdl.FixString(Country)))
dbobj.objcmd.Parameters.AddWithValue("@City", IIf(City = "" Or City Is Nothing, DBNull.Value, City))

If mMdl.SQlInjection(dbobj) Then
    Return Nothing
End If

If dbobj.objcmd.Connection.State = ConnectionState.Closed Then
    dbobj.objcmd.Connection.Open()
End If

myreader = dbobj.objcmd.ExecuteReader

Firstly, please don't post code that has the leading whitespace removed from the first line but not the rest. You're just making your code harder to read to save yourself a tiny effort. Removing all the unnecessary whitespace is easy in either VS or this site, so please do us the courtesy of making your code as easy to read as possible. — jmcilhinney, Feb 12 '20 at 01:03
As for the issue, I don't know whether this will fix it or not but you should not be using `AddWithValue` like that. Use `Add`, specify the data type and, if appropriate, the size of the parameter explicitly, then set the `Value`. Apart from the possibility of inferring invalid data types and sizes, you can't infer a type at all from `DBNull.Value`. Also, don't use `IIf`. It's been outdated for over a decade. Use the `If` operator. — jmcilhinney, Feb 12 '20 at 01:07
Perhaps the problem might be the OleDb driver or the data store. Neither of which you've given us any details of. Is there any information on their support for traditional Chinese? — Hursey, Feb 12 '20 at 01:43
I'm also curious what the `FixString()` and `SQLInjection()` methods are doing, because on face value they look like the kind of method someone builds before they know the correct way to handle sql injection, and should _stop using_ once you have parameterized queries (as we do in this case). — Joel Coehoorn, Feb 12 '20 at 02:46

Joel Coehoorn · Answer 1 · 2020-02-12T02:53:40.323

This isn't a direct answer, but I wanted to address several concerns in the original code and needed more space than would fit in a comment. Some of the changes are stylistic, but a few of these changes fix actual serious bugs in the original code. It also amazes me how often just fixing these kinds of things to use better patterns will fix the original issue in a question.

Dim dt As New DataTable()
Dim sql As String = "
    SELECT [geonameid], [name], [asciiname], [alternatenames], [latitude], [longitude], [timezone], [admin1 code], [code] , [Field2], [Field3] 
    FROM atlas_view a 
    LEFT JOIN Admin1CodesASCII d on a.code=d.Field1 
    WHERE 1=1"

If Not String.IsNullOrEmpty(Country) Then
     sql += " AND [country code]= @Country"
End If
If Not String.IsNullOrEmpty(City) Then
    sql += " AND ([alternatenames] like '%'+@City+'%' OR [Alt] like '%'+@City+'%')"
End If

' If the dboperation type manages your connections, it should implement IDisposable!
' Also, the official MS style guide now explicitly recommends NOT to use "obj" prefixes. That behavior died with vb6, more than 15 years ago.
Using db As New dboperation("2")

    If Not String.IsNullOrEmpty(Country) Then      
        ' Don't use AddWithValue! It can cause HUGE performance issues.        
        ' I had to guess at parameter type and length. You should use actual types and length from the database
        db.cmd.Parameters.Add("@Country", OleDbType.VarWChar, 3).Value = mMdl.FixString(Country)
    End IF
    If Not String.IsNullOrEmpty(City) Then
        db.cmd.Parameters.Add("@City", OleDbType.VarWChar, 30).Value = City
    End If

    db.cmd.Connection.Open()
    Using rdr As OleDbDataReader = db.cmd.ExecuteReader()
       dt.Load(rdr)
    End Using
End Using
Dim result As New DataView(dt)

Is it worth it to move both the SQL WHERE clause concatenation to variable `sql` and the optional parameter adding, together within the same String.IsNullOrEmpty() check? So it would end up as `If Not String.IsNullOrEmpty(Country) Then sql += " AND [country code]= @Country" db.cmd.Parameters.Add("@Country", OleDbType.VarWChar, 3).Value = mMdl.FixString(Country) End If` Yes, this is ugly in a comment! — HardCode, Feb 12 '20 at 17:31
@HardCode Once upon a time, I worked with a provider where changing the commandtext cleared the Parameters collection. So if both conditions are supplied, the first parameter would get cleared. That was back in like 2006, and was probably a bug in that old provider, but I've been paranoid ever since. Of course, it'd be easy to enough to test for in your own code if you wanted to try that. — Joel Coehoorn, Feb 12 '20 at 17:43

VB.NET Executereader out of memory when switch to Chinese language

1 Answers1