locked
Return count from access table RRS feed

  • Question

  • User605367422 posted

     Hi,

    Can anyone shows me the way to get the count from the access table?  I'm going to pass in 2 values to match up the values in 2 fields and return the number of count. I have the code below for the count but it always return 1.  Anyone guides me through this?  thanks

    mySelectQuery = "Select COUNT(*) from points where StrComp(center_long, " + "'" + center_long + "', 0) = 0" + " and StrComp(center_lat, " + "'" + center_lat + "', 0) = 0";

    myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConfigurationManager.AppSettings.Get("RootDir") + username + ConfigurationManager.AppSettings.Get("DbPoints");

    myConnection = new OleDbConnection(myConnString);myCommand = new OleDbCommand(mySelectQuery, myConnection);

    myConnection.Open();

    myCommand.CommandType = System.Data.
    CommandType.Text;

    myCommand.ExecuteNonQuery();

    count = Convert.ToInt16(myCommand.CommandType);

    return count;

    Thursday, February 12, 2009 4:31 PM

Answers

  • User-1199946673 posted

    First of all, concatenating an SQL string is not good practice, you should use parameterized queries instead. Also, in an ASP.NET environment, you're actually not using an Access database, but a Jet database. I don't think that StrComp is a valid Jet Function

    I'm not sure how exactly you're storing your connectionstring, see http://www.mikesdotnetting.com/Article.aspx?ArticleID=78

    Finally, since you're only returning 1 record with only 1 value, you should use ExecuteScalar instead of ExecuteNonQuery:

    myConnection.Open();
    myCommand.CommandType = System.Data.CommandType.Text;
    <STRIKE>myCommand.ExecuteNonQuery();
    count = Convert.ToInt16(myCommand.CommandType);
    return count;</STRIKE>
    return myCommand.ExecuteScalar;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 12, 2009 5:13 PM
  • User-821857111 posted

    I have the code below for the count but it always return 1
     

    As Hans said, you should use ExecuteScalar() or ExecuteReader() to return single or multiple values respectively.  The only thing that ExecuteNonQuery() returns is the number of rows that have been affected by the query, which in your case will always be 1.

    A minor amendment to Hans' final line of code:

    return (int)myCommand.ExecuteScalar();

    ExecuteScalar() returns an object, so it needs to be cast to the correct type.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 13, 2009 3:06 AM
  • User-821857111 posted

    I don't see where you are closing the connection.  You must do that when you are finished with it.  Instantiate the connection object in a "using" block.

    Oh, and that's not a parameterised query, so check the actual values you are trying to pass.

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 13, 2009 3:05 PM

All replies

  • User-1199946673 posted

    First of all, concatenating an SQL string is not good practice, you should use parameterized queries instead. Also, in an ASP.NET environment, you're actually not using an Access database, but a Jet database. I don't think that StrComp is a valid Jet Function

    I'm not sure how exactly you're storing your connectionstring, see http://www.mikesdotnetting.com/Article.aspx?ArticleID=78

    Finally, since you're only returning 1 record with only 1 value, you should use ExecuteScalar instead of ExecuteNonQuery:

    myConnection.Open();
    myCommand.CommandType = System.Data.CommandType.Text;
    <STRIKE>myCommand.ExecuteNonQuery();
    count = Convert.ToInt16(myCommand.CommandType);
    return count;</STRIKE>
    return myCommand.ExecuteScalar;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 12, 2009 5:13 PM
  • User-821857111 posted

    I have the code below for the count but it always return 1
     

    As Hans said, you should use ExecuteScalar() or ExecuteReader() to return single or multiple values respectively.  The only thing that ExecuteNonQuery() returns is the number of rows that have been affected by the query, which in your case will always be 1.

    A minor amendment to Hans' final line of code:

    return (int)myCommand.ExecuteScalar();

    ExecuteScalar() returns an object, so it needs to be cast to the correct type.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 13, 2009 3:06 AM
  • User-821857111 posted

    I don't think that StrComp is a valid Jet Function
     

    It's about whether the functions are available in Sandbox mode or not, which by default is on when you connect to an mdb file using Jet OleDb (ie, outside of an Access application). StrComp is available in Sandbox mode.  The one that catches people out most is that Replace() isn't.

     

    Friday, February 13, 2009 3:14 AM
  • User-1199946673 posted

    It's about whether the functions are available in Sandbox mode or not, which by default is on when you connect to an mdb file using Jet OleDb (ie, outside of an Access application). StrComp is available in Sandbox mode.  The one that catches people out most is that Replace() isn't.

    That's a good one. I remember that I've seen this page before, I just couldn.t find it anymore, the term 'Sandbox mode' didn't stuck in my mind, but now it does [:)]

    Friday, February 13, 2009 6:32 AM
  • User605367422 posted

    Thank you hans and Mike for replying,

    Instead of using the StrComp for concatenating in the SQL string, I use the parameterized queries and the ExecuteScalar() for returning an object.  I works great and return the count but up until it reads the last 9 rows in the table, I fails at the connection.open(); for "Unspecified Error".  I added the try and catch in the method to see if it returns a specific error, but no specific error displays, just "Unspecified Error".  Any ideas why?  Thank you so much for your help.

     

    try

    {

    mySelectQuery = "Select COUNT(*) from points Where center_long =" + center_long + "And center_lat = " + center_lat ;

    myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConfigurationManager.AppSettings.Get("RootDir") + username + ConfigurationManager.AppSettings.Get("DbPoints");

    myConnection = new OleDbConnection(myConnString);myCommand = new OleDbCommand(mySelectQuery, myConnection);

    myConnection.Open();   //Unspecified error

    myCommand.CommandType = System.Data.CommandType.Text;

    retVal = Convert.ToString((int)myCommand.ExecuteScalar());

    }

    catch (Exception err)

    {

    retVal = err.Message.ToString();

    }

    Friday, February 13, 2009 9:52 AM
  • User-821857111 posted

    I don't see where you are closing the connection.  You must do that when you are finished with it.  Instantiate the connection object in a "using" block.

    Oh, and that's not a parameterised query, so check the actual values you are trying to pass.

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 13, 2009 3:05 PM
  • User605367422 posted

    Thank you Mike,

    I forgot to add the closing connection at the end.  Thank for your help [:)]

    Friday, February 13, 2009 4:40 PM