2 Replies - 1121 Views - Last Post: 16 October 2012 - 06:41 AM Rate Topic: -----

#1 Gadge  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 08-April 09

ODBC Database - Where in (?)

Posted 10 October 2012 - 10:05 AM

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;
        }
    }
}


Is This A Good Question/Topic? 0
  • +

Replies To: ODBC Database - Where in (?)

#2 Gadge  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 08-April 09

Re: ODBC Database - Where in (?)

Posted 16 October 2012 - 05:59 AM

No one fancy's a crack at this? :)
Was This Post Helpful? 0
  • +
  • -

#3 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 486
  • Joined: 13-June 12

Re: ODBC Database - Where in (?)

Posted 16 October 2012 - 06:41 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1