Forum Stats

  • 3,826,755 Users
  • 2,260,705 Discussions


Parameterized Query does not find any rows


I have a problem retrieving results using a parameterized query using an MSDAOra provider in VB.NET against a 10g database. I am trying to simply retrieve a value from a row based on a match of 3 fields. The code below does not return any rows, even though a matching record exists. If I build the SQL string directly (by commenting in the fourth line below, and commenting out the third line and the .AddWithValue lines), the query returns the expected results.

sSQL = "SELECT s.EndGLBal "
sSQL = sSQL & " FROM AcctSum s "
sSQL = sSQL & " WHERE s.CoCode = ? And s.AcctNumb = ? And s.YearMonth = ?"
'sSQL = sSQL & " WHERE s.CoCode = " & SQLString(CoCode) & " And s.AcctNumb = " & SQLString(sLiabiltyGL) & " And s.YearMonth = " & SQLString(EndOfLastFYYearMonth)

cmdEndGLBal = New OleDbCommand(sSQL, conn)
cmdEndGLBal.Parameters.AddWithValue("@coCode", CoCode.PadRight(6))
cmdEndGLBal.Parameters.AddWithValue("@acctNumb", sLiabiltyGL.PadRight(24))
cmdEndGLBal.Parameters.AddWithValue("@yearMonth", EndOfLastFYYearMonth)

drEndGLBal = cmdEndGLBal.ExecuteReader()

1. The AcctSum tables have fields on the CHAR; therefore, I am padding the CoCode and AcctNumb fields to the width of the fields. (The EndOfLastFYYearMonth is always 6 characters, which matches the YearMonth field width, so I don't have to pad it).
2. The SQLString() function (used in the non-parameterized query) is my own function to escape the string and return a single-quoted value (I don't have any special characters in the values for my test case, so it effectively just wraps the string in single quotes).
3. The parameterized query works correctly against Oracle 9 databases; it fails against both Oracle 10 databases I tried.

Thanks in advance for any ideas.

This discussion has been closed.