Select Query with Comma Delimited Paramater

[Frank Jovi]

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

[ADezii]

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.
   

   SELECT [LastName], [FirstName]
   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.

[Frank Jovi]

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.
   

   SELECT [LastName], [FirstName]
   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.

[ADezii]

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.

Dim varRet As Variant, intCounter As Integer
Dim strToParse As String
 
strToParse = "10021, 10027, 10034, 10019, 10054, 10055, 10007"
 
'2nd Argument to the Splpit() Function is the Delimiter
varRet = Split(strToParse, ",")
 
'Total # of Array Elements = UBound(varRet) - 1
For intCounter = 0 To UBound(varRet) - 1
  Debug.Print Trim(varRet(intCounter))
Next

OUTPUT:

10021
10027
10034
10019
10054
10055

[Frank Jovi]

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

[ADezii]

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!