Search code examples
vb.netexceloledbdataadapter

VisualBasic OleDb accessing Excel spreadsheet, can't set column in query using parameter?

I'm working in Visual Basic and using OleDb to access an Excel spreadsheet. I'm importing the data from the sheet into my DataGridView, and that works fine, but now I'm working on filtering. For the most part it works great, but I'm trying to use parameters ("@p1" and so on), and I'm getting a very strange issue.

I can have the following (excluding a bunch of irrelevant stuff before, in between, and after)

query = query & "Project" & " LIKE @Gah1"
...
MyCommand.SelectCommand.Parameters.AddWithValue("@Gah1", "%House%")

and it gives me the results I'm looking for. But I can't seem to get a parameter for the name of the column itself, for example

query = query & "@Gah1" & " LIKE @Gah2"
...
MyCommand.SelectCommand.Parameters.AddWithValue("@Gah1", "Project")
MyCommand.SelectCommand.Parameters.AddWithValue("@Gah2", "%House%")

does not work (and I've tried enclosing Project in different brackets and stuff in different ways, can't get it to work). I've found plenty of examples on using parameters, but none that use them to give the column name.

I'm guessing the parameter changes how the string is represented, seeing as you don't need to have the ' ' around string literals. Is it not possible to give column names in parameter? If you can, what do I need to do?

Well it won't let me post comment, so here

a) Oops, no, I guess not b) The string query that I end up sending in my test query here is 

"select * from [Bid Summary$] where @Gah1 LIKE @Gah2"

I can post the procedure if absolutely need be, but it isn't the problem because the whole thing works perfectly fine if I replace @Gah1 with Project or [Project], so I just showed the lines that I change. I'm very new to parameterized queries, can you explain how to avoid query strings using it? If there's a better way to do what I'm doing I'm happy to use it =) And thanks for response and edit

Solution

  • I use combination of string methods and parameters, like this:

    //replace field name in a query template
query = String.Format("select * from [Bid Summary$] where {0} LIKE ?", "@Gah1");

//set value (name is in OleDb parameter ignored, so it could be null)
MyCommand.SelectCommand.Parameters.AddWithValue(null, "%House%");

    Note: There is possibility of a sql injection, so be sure about origin of field name (not from user input).