none
System.Data.OleDb.OleDbException while querying access 2000 using OleDbDataReader RRS feed

  • Question

  • Hi,

    I got the exception while executing the code below. The code tries to execute a simple "select" query using MS Access 2000. But throws the error  "System.Data.OleDb.OleDbException: No value given for one or more required parameters"

    Here is the runtime sql commandtext which is captured just before the
    "sqlcmd.ExecuteReader();" command:
    "select IL, ILCE, SEMT, MAHALLE, CADDE, SOKAK, SITE, APARTMANBINA, BINANO, DAIRENO from MUSTERI where MUSTERI_ID=7199474"

    What might be the problem?, do you think using transaction can cause this?

    ------------MY CODE----------------------
    The problem is, this code runs successfully on my computer and my MS Access DB file, but throws the error above on "some" other client PCs.


    OleDbDataReader read = null;

    string strSql = "select IL, ILCE, SEMT, MAHALLE, CADDE, SOKAK, SITE, APARTMANBINA, BINANO, DAIRENO from MUSTERI where MUSTERI_ID=" + MusteriId;
    read = MainForm.accFunc.GetData(strSql, MainForm.aConn);

    ---The function that throws the exception-----

    public OleDbDataReader GetData(string sqlText, OleDbConnection aConn)
    {
        OleDbTransaction oTran = null;
        try
        {
            OpenConn(aConn);
            OleDbCommand sqlcmd = new OleDbCommand(sqlText, aConn);
            sqlcmd.CommandType = System.Data.CommandType.Text;
            oTran = aConn.BeginTransaction();
            sqlcmd.Transaction = oTran;
            OleDbDataReader reader = sqlcmd.ExecuteReader();
            oTran.Commit();
            return reader;
        }
        catch (Exception ex)
        {
            oTran.Rollback();
            HanddleError("AccFunctions", 403, sqlText, ex);
            return null;
        }
        finally
        {
            oTran.Dispose();
        }
    }

    Tuesday, May 6, 2008 9:00 AM

All replies

  • I'd recommend that you take out the transaction - you don't need it for a SELECT command, it's just adding additional overhead as well as likely causing the exception. Even if transactions were the right thing to do (which they're not), in your exception handler you'd need to wrap the rollback statement in another try/catch or you'll just throw another exception if the transaction isn't active and you try to roll it back.

     

    --Mary

     

    Tuesday, May 6, 2008 11:10 PM
  • This is common issue with Access.  It usually means one of your columns is a reserved word.  Best bet is to NOT name a column as an Access reserved word:

     

    select IL, ILCE, SEMT, MAHALLE, CADDE, SOKAK, SITE, APARTMANBINA, BINANO, DAIRENO from MUSTERI where MUSTERI_ID=7199474"

     

    My guess is SITE.

     

    Try putting square brackets around it like so:

     

    [SITE]

     

    Another possibility is you have a field name that has a typo, when this happens Access attempts to resolve it and thinks there is a missing parameter.  This is another common cause.

     

    Tuesday, May 6, 2008 11:24 PM