Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using regular expressions in a link

Posted on 2004-08-31
21
Medium Priority
?
210 Views
Last Modified: 2013-12-24
Hi.

I have a calendar application with various showTo 'views'.  There are 2 problems: the datatypes for the primary to foreign key in the tables used are different.  The calendar table has the field- showId, but it is a varchar (and the table already has a lot of data), while the corresponding foreign key in the showTo table, for showId is an integer.  The application was originally built to insert "x" number of showTo (showId) variables in the calendar table for each entry.  However, as the showTos grow, i don't want to continue copying the relevant pages for the application and inserting in the appropriate new folder.  What are the best solutions?  I ultimately just want this one application and perhaps using links and regular expressions, display the appropriate data. I already have well over 100 showIDs and I want to get control of this before I considered the following:

1) Using like within my query to obtain the one value for the particular view:
<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId like '%10%'
ORDER BY calendar.calendardate
</cfquery>
This works, but I will still need to copy the pages of the application into various folders for all new views and change the values.  

2) Link Option with a regular expression
This seems like a good choice, but I don't know how I would construct the regular expression to find all values where showID LIKE ShowId like '%10%'.
I thought I could do: <a href="test_calendar.cfm?showId*10" class="main">View Midwest Events</a>, but this does not work either.  

Please advise.  What are my options so that I can gain control of this application?

Thank you.
0
Comment
Question by:synergeticsoul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
21 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 11944857
Why can't you do :

<a href="test_calendar.cfm?showId=10" class="main">View Midwest Events</a>

Then on the page :
<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId like '%#URL.SHowID#%'
ORDER BY calendar.calendardate
</cfquery>
0
 

Author Comment

by:synergeticsoul
ID: 11945266
The problem is the 2 different datatypes.  The application is old.  When I tried the above, I get:

"Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."

I am trying to do this from a link.   Each record has various showTos, for example, one record may have, 1, 5, 10, 30 as possible showTos.  As the application grows, I am trying to fix the problem before it gets out of hand.  I am not sure if I should be doing replacements or what..

please advise... thank you...
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11946156
Oh then you can use IN
<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId IN (#URL.ShowID#)
ORDER BY calendar.calendardate
</cfquery
0
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 
LVL 1

Expert Comment

by:lxdev
ID: 11946460
If you need to have single quotes around each value, then you can use the ListQualify() function:

<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE calendar.calendardate between #begofweek# and #endofweek#
AND ShowId IN (#ListQualify(URL.ShowID, "'")#)
ORDER BY calendar.calendardate
</cfquery>

Note that the function's second argument is a single quote surrounded by double quotes.
0
 

Author Comment

by:synergeticsoul
ID: 11952891
what is the ListQualify() function?

Neither solution works because of the data type difference.  i still get : "Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."

Or I get the calendar minus the event data.  I also tried casting, but still get the same errors.

Are there other options to explore that can be done immediately?  I have a lot of data in the table now.  Each record has various showTo variables as varchar, from the showTo table (int).

??Help.  

0
 

Author Comment

by:synergeticsoul
ID: 11952910
FYI, there are currently 3943 total records in the table.
0
 

Author Comment

by:synergeticsoul
ID: 11952918
Could I possibly use a combination of "Like" and "In" in my query?
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11953673
ListQualify will not work since the data type is int.  It is for strings.

The problem is that you are sending it in in quotes which makes it see it as a varchar instead of a string of ints.

Try this :

<cfset tempqry = "SELECT * FROM calendar WHERE calendar.calendardate between #begofweek# and #endofweek# AND ShowId IN (#URL.ShowID#) ORDER BY calendar.calendardate">


<cfoutput>#tempqry#</cfoutput>
<cfabort>

And see exactly what gets printed to the screen.

It should look like this (I filled in dates since I don't know what those will be):

SELECT * FROM calendar WHERE calendar.calendardate between 3/4/2004 and 3/11/2004 AND ShowId IN (1,2,6,10,40) ORDER BY calendar.calendardate
0
 

Author Comment

by:synergeticsoul
ID: 12006904
I am still getting the same error-

"Syntax error converting the varchar value '1,2,6,10,40' to a column of data type int."

Please advise.  What are my options besides rebuilding the table and datatypes which will be quite time-consuming?
0
 

Author Comment

by:synergeticsoul
ID: 12008302
I also tried the following:

<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
      '#form.ShowID#'
      <cfelse>'#URL.ShowID#'</cfif>
AND ShowId like
<cfif isDefined("Form.ShowID")>
      '%#Form.ShowID#%'
      <cfelse>'%#URL.ShowID#%'</cfif>
AND CAST(showId AS int)
Order BY calendarDate ASC
</cfquery>

I get the following error: Incorrect syntax near '20'.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12045346
I do not think it should be deleted as the IN option will work.

I am sorry I did not respond after the post on the 8th, but I was out of town for a week.

 synergeticsoul  - the reason you got an error with incorrect syntax is that you left out the parenthesis.  Look again at my example at what the SQL should look like.  Then to test what yours looks like you do this :

<cfoutput>
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
     '#form.ShowID#'
     <cfelse>'#URL.ShowID#'</cfif>
AND ShowId like
<cfif isDefined("Form.ShowID")>
     '%#Form.ShowID#%'
     <cfelse>'%#URL.ShowID#%'</cfif>
AND CAST(showId AS int)
Order BY calendarDate ASC
</cfoutput>

You will see that you do not have the ( ) around the IN clause and that is causing your syntax problem.
0
 

Author Comment

by:synergeticsoul
ID: 12101271
Thank You.  MRichmon.  I have been trying to test this and should have a response soon.

Thanks.
0
 

Author Comment

by:synergeticsoul
ID: 12138241
I am still getting an error.  This time the error is not a list, but invalid.  Not sure what I am doing wrong.  I am passing from a link.  So on one page, the link may be: calendar.cfm?showid=1, but upon clicking on this, there is an error.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12138643
what is the error?

Can you give a URL so we can see it?
0
 

Author Comment

by:synergeticsoul
ID: 12160122
Here is the actual error:

<ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '1'.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (39:1) to (39:58).

>
=================================
This is the code for the test link:

<a href="test_calendar.cfm?showId=1" class="main">Sample Calendar View By Link Only</a>
====================================
This is the actual code for the page now:

<cfquery name="getweekcal" datasource="#application.dsn#">
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
     '#form.ShowID#'
     <cfelse>'#URL.ShowID#'</cfif>
AND ShowId like
<cfif isDefined("Form.ShowID")>
     '%#Form.ShowID#%'
     <cfelse>'%#URL.ShowID#%'</cfif>
AND CAST(ShowID AS int)
</cfquery>
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 2000 total points
ID: 12162510
The problem is that you still do not have the correct syntax.

Again I recommend that you place the following on the page when you get problems like this so you can see exactly the query you are creating :

<cfoutput>
SELECT * FROM calendar
WHERE ShowId IN
<cfif isDefined("Form.ShowID")>
     '#form.ShowID#'
     <cfelse>'#URL.ShowID#'</cfif>
AND ShowId like
<cfif isDefined("Form.ShowID")>
     '%#Form.ShowID#%'
     <cfelse>'%#URL.ShowID#%'</cfif>
AND CAST(ShowID AS int)
</cfouput>
<cfabort>

You would then notice that the query created is :

SELECT * FROM calendar WHERE ShowId IN '1' AND ShowId like'%1%' AND CAST(ShowID AS int)

This is not valid syntax.

First of all if the column showID is an int then you should NOT have the single quotes.

Second, please carefully review all of the previous comments and you will see that you NEED parenthesis around andy IN clause

Also I am not sure what exactly you are trying to select.  I am guessing you want any ID with a 1 anywhere in the ID?

But re-reading the original post you say that showID in the calendar table is a varchar so the following should give you all those results:

SELECT * FROM calendar WHERE ShowID LIKE '%1%'

Then you don't need the IN or the CAST portions (I am not really sure why the CAST portion is there anyways).  If you then need to connect to a table that has showID as an int then please explain what you need and we can help from there.
0
 

Author Comment

by:synergeticsoul
ID: 12363434
Sorry for the late response, I have had no luck with this and have been trying to figure out what's wrong.  MRichmon, thanks for your help.  Points go to MRICHMON.  
0

Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question