[Gadge]

Hi,

I'm having a play with an ODBC connection and having a little bit of trouble which hopefully someone can help me with please.

I cycle through a list of variables which I obtain from a text file and pass this into an array. Using the data gathered I then attempt to pass this into my 'where in' statement in my SQL query to filter on multiple values.

Looking at my variables they do correctly obtain the information so I think the problem is likely here:

DbCommand.Parameters.Add(new OdbcParameter("@Users" + i, UserList[i]));

I have done this type of thing using a SQLDataReader in which I would name the parameters directly however in ODBC I believe I need to use question marks?
If I did something like:

Where column1 in (?,?,?)

it does seem to pull the details over.

Thanks for looking

        public OdbcDataReader InitateConnection_DataGrid1_SQL()
        {
            CreateConnection ConnectionObject = new CreateConnection();
            OdbcConnection OdbcConnection = new OdbcConnection("DSN=ODBC");

            AddUsers AddUsers = new AddUsers();
            List<string> UserList = AddUsers.CreateUserList();

            OdbcConnection.Open();

            SQLQuery SQLQuery = new SQLQuery();

            OdbcCommand DbCommand = new OdbcCommand();

            string[] paramArray = UserList.Select((x, i) => "@Users" + i).ToArray();
            DbCommand.CommandText = string.Format(SQLQuery.PendingQuerys(), string.Join(",", paramArray));

            for (int i = 0; i < UserList.Count; ++i)
            {
                DbCommand.Parameters.Add(new OdbcParameter("@Users" + i, UserList[i]));
            }

            DbCommand.Connection = OdbcConnection;
            OdbcDataReader reader = DbCommand.ExecuteReader();

            return reader;
        }

Basic SQL Example:

  class SQLQuery
    {
        public string PendingQuerys()
        {

            string TopConnection =
@"
SELECT column1, column2, column3
FROM table 
Where column1 in (?)
";
            return TopConnection;
        }
    }
}

[Gadge]

No one fancy's a crack at this?

[MrShoes]

Try this:

DbCommand.Parameters.AddWithValue("@Users" + i.ToString(), UserList[i]);

The problem could be that you're creating a parameter name (as a string) without converting the integer in i to string - meaning you're probably getting the ASCII character mapped to that number.