By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,919 Members | 1,625 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,919 IT Pros & Developers. It's quick & easy.

Select Query with Comma Delimited Paramater

P: 3
I am working on a website with ASP.NET 2.0

I started with a SQL database .mdf. After further discussions with my client, we decided to switch to an Access database.

I created a Select query that has a parameter that is a comma delimited string brought in through a paramater in to the <selectcommand=...>

The query worked perfectly before, but I ran into a problem in the switch over because:

WHERE charindex(',' + CustomerID + ',', ',' + @customers + ',') > 0

Doesn't translate to the Access server.

I know the InStr() function is intended to do the same purpose, but I can't get it to do the same thing that the charindex did in SQL.

Essentially, I need a quick and easy method to translate a comma delimited paramater in a select query. The simpler the better, its a short string derived from some user input. I'm not worried about database injection because the selections are derived from predetermined values, not typed strings.

I've been searching for hours to find a solution, but have had no luck, so I'm going to the Access PRO's on this one.

Thanks,
FB
May 12 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,628
I am working on a website with ASP.NET 2.0

I started with a SQL database .mdf. After further discussions with my client, we decided to switch to an Access database.

I created a Select query that has a parameter that is a comma delimited string brought in through a paramater in to the <selectcommand=...>

The query worked perfectly before, but I ran into a problem in the switch over because:

WHERE charindex(',' + CustomerID + ',', ',' + @customers + ',') > 0

Doesn't translate to the Access server.

I know the InStr() function is intended to do the same purpose, but I can't get it to do the same thing that the charindex did in SQL.

Essentially, I need a quick and easy method to translate a comma delimited paramater in a select query. The simpler the better, its a short string derived from some user input. I'm not worried about database injection because the selections are derived from predetermined values, not typed strings.

I've been searching for hours to find a solution, but have had no luck, so I'm going to the Access PRO's on this one.

Thanks,
FB
  1. The same functionality that CHARINDEX provides in TransactSQL can be found in the Instr() Function in Access, namely returning the Starting Position of a specific expression in a character string. The following SQL Statement will produce the Last and First Names of all Employees who have the string 'ch' contained somewhere within the [LastName] Field.
    Expand|Select|Wrap|Line Numbers
    1. SELECT [LastName], [FirstName]
    2. FROM tblEmployee Where InStr([LastName],'ch') > 0;
  2. I'm not exactly sure what you mean when you request how to
    translate a comma delimited paramater in a select query
    Please explain in more detail.
May 12 '07 #2

P: 3
Thank you for responding, first of all :) I really appreciate the time you're taking!

So, first... I am passing a series of paramaters to a SQLdataSource.

srcMarket.SelectParameters("Regions").DefaultValue = RegionStr
srcMarket.SelectParameters("DateFrom").DefaultValu e = DFV
srcMarket.SelectParameters("DateUntil").DefaultVal ue = DUV
srcMarket.SelectParameters("Item").DefaultValue = ItemV

RegionStr will look something like this, passed as a string.

10021,10027,10034,10019,10054,10055,10007,

I want the WHERE command to select each RegionID from each location in the table that matches that region. The problem is that it can be a variable number of items, so passing it as a string and then parsing it is the easiest method i know how. The charindex function did it in SQL, so I'm sure it can be done in Access. When I try different variations in Access, either I can't seem to get the formatting correct, or the InStr can't handle it for some reason? Any viable solution is an option right now.

I've tried surrounding the market.regionid with ',' (commas) so that it can parse the column data with the commas included.

(InStr(',' & [Market.RegionID] & ',', ',' & @Region & ',') > 0)

What should happen is it checks each regionID column against the @regions string and returns the ones that match the string.

However, I'm getting no difference when I add in the InStr function to the WHERE. It just returns nothing whatsoever.



  1. The same functionality that CHARINDEX provides in TransactSQL can be found in the Instr() Function in Access, namely returning the Starting Position of a specific expression in a character string. The following SQL Statement will produce the Last and First Names of all Employees who have the string 'ch' contained somewhere within the [LastName] Field.
    Expand|Select|Wrap|Line Numbers
    1. SELECT [LastName], [FirstName]
    2. FROM tblEmployee Where InStr([LastName],'ch') > 0;
  2. I'm not exactly sure what you mean when you request how to

    Please explain in more detail.
May 12 '07 #3

ADezii
Expert 5K+
P: 8,628
Thank you for responding, first of all :) I really appreciate the time you're taking!

So, first... I am passing a series of paramaters to a SQLdataSource.

srcMarket.SelectParameters("Regions").DefaultValue = RegionStr
srcMarket.SelectParameters("DateFrom").DefaultValu e = DFV
srcMarket.SelectParameters("DateUntil").DefaultVal ue = DUV
srcMarket.SelectParameters("Item").DefaultValue = ItemV

RegionStr will look something like this, passed as a string.

10021,10027,10034,10019,10054,10055,10007,

I want the WHERE command to select each RegionID from each location in the table that matches that region. The problem is that it can be a variable number of items, so passing it as a string and then parsing it is the easiest method i know how. The charindex function did it in SQL, so I'm sure it can be done in Access. When I try different variations in Access, either I can't seem to get the formatting correct, or the InStr can't handle it for some reason? Any viable solution is an option right now.

I've tried surrounding the market.regionid with ',' (commas) so that it can parse the column data with the commas included.

(InStr(',' & [Market.RegionID] & ',', ',' & @Region & ',') > 0)

What should happen is it checks each regionID column against the @regions string and returns the ones that match the string.

However, I'm getting no difference when I add in the InStr function to the WHERE. It just returns nothing whatsoever.
The best I can do right now is hopefully point you in the right direction since I am really pressed for time and heading out for a wedding. I've listed the Syntax below for parsing a String based on a specific Delimiter and putting its values into an Array. Let me know how you make out.
Expand|Select|Wrap|Line Numbers
  1. Dim varRet As Variant, intCounter As Integer
  2. Dim strToParse As String
  3.  
  4. strToParse = "10021, 10027, 10034, 10019, 10054, 10055, 10007"
  5.  
  6. '2nd Argument to the Splpit() Function is the Delimiter
  7. varRet = Split(strToParse, ",")
  8.  
  9. 'Total # of Array Elements = UBound(varRet) - 1
  10. For intCounter = 0 To UBound(varRet) - 1
  11.   Debug.Print Trim(varRet(intCounter))
  12. Next
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. 10021
  2. 10027
  3. 10034
  4. 10019
  5. 10054
  6. 10055
May 12 '07 #4

P: 3
It took me a at least 8 hours, but I solved it... and the answer isn't anything very enlightening.

I had problems when I was working with the InStr feature as we discussed. I don't understand why at all, but it wasn't returning anything when I used the @Regions variable. I took it into Access Query Designer and worked with it from there. It kept giving me a Compile Error when I switched to the Datasheet view. Repeatedly. I saved it to take a break and went to the ASP.NET code. I worked with the ASP to no avail. When I came back, I don't know why, but it stopped giving me the Compile error and would pull up the data properly. I directly copied this to my ASP.NET page and it worked. Now, at first I thought this was magical, and amazing and absolutely puzzling. But, it gets even more confusing. I copied the string to a backup datasource in an old file to see what had changed. It wouldn't work again. I remembered I had adjusted the Paramaters default value, since it seemed like the paramaters weren't being passed correctly... I set the Default Value for Regions to one of the RegionIDs... This, for some reason, which I don't understand, fixed the problem. It accepted not only the default value, but also a string of values and worked properly. I've never had to set a default value before, and the other paramaters I have aren't set to any default value.

I don't know why when I saved the query and then reopened it the compile error disappeared. On top of that, I don't know why a default value had to be set to make the query work, so I I don't see the relevance. I had a confirmation label.text to ensure that the variable was being set correctly, which it was. However, setting the default value seems to have fixed the problem, so perhaps its some kind of rare bug.

Thanks for your help very much! I really appreciate your time, and I'll definitely spread the word this is the place to get some fast and good answers. :)

F
May 12 '07 #5

ADezii
Expert 5K+
P: 8,628
It took me a at least 8 hours, but I solved it... and the answer isn't anything very enlightening.

I had problems when I was working with the InStr feature as we discussed. I don't understand why at all, but it wasn't returning anything when I used the @Regions variable. I took it into Access Query Designer and worked with it from there. It kept giving me a Compile Error when I switched to the Datasheet view. Repeatedly. I saved it to take a break and went to the ASP.NET code. I worked with the ASP to no avail. When I came back, I don't know why, but it stopped giving me the Compile error and would pull up the data properly. I directly copied this to my ASP.NET page and it worked. Now, at first I thought this was magical, and amazing and absolutely puzzling. But, it gets even more confusing. I copied the string to a backup datasource in an old file to see what had changed. It wouldn't work again. I remembered I had adjusted the Paramaters default value, since it seemed like the paramaters weren't being passed correctly... I set the Default Value for Regions to one of the RegionIDs... This, for some reason, which I don't understand, fixed the problem. It accepted not only the default value, but also a string of values and worked properly. I've never had to set a default value before, and the other paramaters I have aren't set to any default value.

I don't know why when I saved the query and then reopened it the compile error disappeared. On top of that, I don't know why a default value had to be set to make the query work, so I I don't see the relevance. I had a confirmation label.text to ensure that the variable was being set correctly, which it was. However, setting the default value seems to have fixed the problem, so perhaps its some kind of rare bug.

Thanks for your help very much! I really appreciate your time, and I'll definitely spread the word this is the place to get some fast and good answers. :)

F
I am just as befuddled as you are - I see absolutley no reason why setting a Default Value would have corrected the problem and eliminated the Compilation Error. Could it possibly have been the Default Value implying a different Data Type that the Variable was coerced into? Who knows - soemtimes in this business we never find the answer. Congratulations on solving the problem and thanks for spreading the word!
May 13 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.