0

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

Ňɏssa Pøngjǣrdenlarp
  • 38,411
  • 12
  • 59
  • 178
Hynch
  • 69
  • 1
  • 8
  • a) this cant really be VB6 can it? b) you should show the whole query string and procedure because it looks like you are creating invalid SQL (pretty much the point of Parameterized queries is so you dont have to build query string like that). – Ňɏssa Pøngjǣrdenlarp May 20 '14 at 22:10
  • if you just want to filter the current results, you do not need to build a new query, you can use a `DataView` to apply a filter. These can be applied to the DGV or a simple SQL WHERE clause fed to the `DefaultView.RowFilter` of a datatable. – Ňɏssa Pøngjǣrdenlarp May 20 '14 at 22:35
  • Well that would certainly help. I'll look into DataView, then I won't have to worry about this parameter problem. Thank you very much for your help Plutonix! (How do I upvote you?) – Hynch May 20 '14 at 22:39
  • At first: It is not possible. You may pass only values as parameters, not other parts of the query. At second: OleDb doesn't use named parameters, [see this answer](http://stackoverflow.com/a/1476818/2369384). – Roman Plischke May 26 '14 at 13:53
  • Okay, so there's just no way to pass the column name based on what I find at runtime? That's a shame. Thanks for the link about OleDb parameters, in my research I'd seen both named and just question marks, so I wasn't sure what to use =P – Hynch May 26 '14 at 14:11

1 Answers1

0

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).

Roman Plischke
  • 1,042
  • 7
  • 14
  • I was trying to do this to prevent accidental sql injection from changing field names in excel =P But it's okay. What does {0} stand for? It looks to me as if %House% would replace the ? – Hynch May 27 '14 at 15:03
  • _String.Format_ is pure string replacing. _Parameters.AddWithValue_ not, however it looks similar. It is more complex, it puts values in the query later with some checks. So it is not possible make sql injection attack by strange value of parameter. You can try yourself, see an [example](http://www.w3schools.com/sql/sql_injection.asp). – Roman Plischke May 27 '14 at 19:05
  • I've got parameterized queries working, this is great. Thank you for your explanations Roman – Hynch May 27 '14 at 22:27