1

I have a simple SQL command that wont work in certain giving parameters.
For example if TweetID = 59 and UserID = 1 it will return the value;
but if TweetID = 8 and UserID = 1 it will not return the value.
Can some one find the reason for that?

public static int GetReTweetIdFromReTweetByUserIdAndTweetId(int TweetID,int UserID)
{
    string sql = "SELECT [ReTweetID] FROM [ReTweet] WHERE [TweetID] = [@TID] AND [UserID] = [@UID]";
    OleDbConnection conn = ConnectToDb();
    OleDbCommand com = new OleDbCommand(sql, conn);
    com.Parameters.Clear();
    OleDbParameter objParamater;
    objParamater = com.Parameters.Add("[@UID]", OleDbType.Integer);
    objParamater.Direction = ParameterDirection.Input;
    objParamater.Value = UserID;
    objParamater = com.Parameters.Add("[@TID]", OleDbType.Integer);
    objParamater.Direction = ParameterDirection.Input;
    objParamater.Value = TweetID;
    OleDbDataAdapter da = new OleDbDataAdapter(com);
    DataTable dt = new DataTable();
    int id=0;
    try
    {
        conn.Open();
        da.Fill(dt);
        id = int.Parse(dt.Rows[0][0].ToString());
    }
    catch (Exception err)
    {
        throw err;
    }
    finally
    {
        da.Dispose();
        dt.Dispose();
        com.Dispose();
        conn.Close();
        conn.Dispose();
    }
    return id;
}

database picture

Lews Therin
  • 3,707
  • 2
  • 27
  • 53
Rokni
  • 67
  • 13
  • Off topic: look into the [using statement](http://www.dotnetperls.com/using). It could clean up your code a lot and save the hassle of calling `Dispose()` on `IDisposable` objects. – Lews Therin May 17 '16 at 13:59
  • 1
    don't `throw err` in your catch block since that resets the stacktrace. Either just do a `throw`, or get rid of the catch block altogether as you are wasting your time. – user1666620 May 17 '16 at 14:03
  • if you run the script directly against the database, does it return results? – user1666620 May 17 '16 at 14:04
  • @Sylverac Can yo explain more i really want to know what you meant. – Rokni May 17 '16 at 16:06
  • @user1666620 I dont know why not to throw the error can you explain better? and it does get the results good. – Rokni May 17 '16 at 16:07
  • 1
    @Rokni http://stackoverflow.com/a/881489/1666620 – user1666620 May 17 '16 at 16:15
  • 1
    @Rokni Did you take a look at the link I posted? You can replace `try...catch...finally` blocks with a one-line `using` statement for instances of classes that implement the `IDisposable` interface. A `using` statement is just shorthand for a `try...catch...finally` block that calls `Dispose()` in the `finally` block for instances specified in the `using` statement. Here's the link again: http://www.dotnetperls.com/using – Lews Therin May 17 '16 at 17:26
  • @Sylverac I did thank you. – Rokni May 18 '16 at 06:59

1 Answers1

4

When using OleDb with an Access database, parameter names are ignored. You must supply the parameter values in the order Access expects. If you run this query from the Access query designer ...

SELECT [ReTweetID] FROM [ReTweet] WHERE [TweetID] = [@TID] AND [UserID] = [@UID]

... you will see that Access asks you to supply a value for [@TID] first and then a value for [@UID].

But in your c# code, you're supplying the parameter values in the opposite order.

However there may be something else which confuses the issue because I don't see why it succeeded with TweetID = 59 and UserID = 1. That would only make sense to me if the table actually includes a row with TweetID = 1 and UserID = 59. And perhaps it does. But either way, I urge you to first supply parameter values in the order Access expects, and then see whether you need to make additional code changes.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thank you it worked didnt know that access works that way, and i dont know also why it worked with the first parameters. – Rokni May 17 '16 at 16:03
  • Actually Access doesn't always work that way. For example, the parameter names are meaningful when a query is executed from DAO. I think the parameter names are only ignored when an Access query is executed from OleDb or from ADO (which is based on OleDb). – HansUp May 17 '16 at 16:08
  • i did use Oledb and ADO but it worked with a different set of parameters and that is weird. – Rokni May 17 '16 at 16:13
  • 1
    Hm. I'm surprised. I may need to revise my thinking on this, but I'll leave that for another day. :-) – HansUp May 17 '16 at 16:15