string manipulation in ASP?

[taval]

I currently have a database that contains a Customer and File tables. My first question is how do I get the extension of the file (i.e last three letters of the filename). In the File table I have an entity named FileName that contains the filename. My second question is how do I get at least one of the file types the customer has to show up. So for example say the customer has 5 .exe files and 1 .img file. How would I state the query so that it gives me only 1 .exe and 1 .img, so bascally if it finds two or more file types that are the same it doesn't display all of them but just one.<br><br>Hope I was clear, thankful for any help.<br>

 

[Karl Blessing]

Assuming your filenames have no . in them other than right before the extention<br><br><FONT FACE=monospace><br>rtn = InStr(1,FileName, &quot;.&quot<br>if rtn &gt; 0 then<br>&nbsp;&nbsp;&nbsp;&nbsp;extension = Right(FileName, (Len(FileName)-rtn))<br>end if<br></font><br><br>basically rtn is the returned position of the first found &quot;.&quot;<br><br>then I do a Right(String, Length)<br>in this case I subtracted the position from the length of the string, leaving only the length from the position to the end.<br><br>oh also if you end up with a . in your new string, try this instead<br>&nbsp;&nbsp;&nbsp;&nbsp;extension = Right(FileName, (Len(FileName)-(rtn+1)))<br><br>adding one to the position to be subtracted,&nbsp;&nbsp;as for the second question, I could possibly say you can throw any new found extentions into a tempory array or table on the ASP , and if you find one you already have, dont add it to that array/table, then at the end you can list all the extensions found. <p>Karl<br><a href=mailto:kb244@kb244.com>kb244@kb244.com</a><br><a href=

http://kb244.com>

</a><br>Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)<br>

http://www.brainbench.com/transcript.jsp?pid=629151

 

[taval]

I've tried setting up an array to collect all the unique file types, this is what I have got so far:

------------------------------------------------------------

form_CustID =request.querystring(&quot;id&quot

strSQL = &quot;Select FileName from FileDescription where CustId = &quot; & form_CustID
Set rsFiles = Server.CreateObject (&quot;ADODB.Recordset&quot
rsFiles.Open strSQL ,&quot;DSN=Architron; UID=sa; PWD=&quot;

If rsFiles.eof then
%>
<table align= &quot;center&quot; cellpadding=&quot;1&quot; cellspacing=&quot;1&quot; BORDER=&quot;0&quot; WIDTH=&quot;800&quot; >
<tr valign=center><td align=center BGCOLOR=&quot;#99FF99&quot; ><h4> | Customer ID: <% response.write rsCustomer(&quot;CustID&quot %> | &nbsp; | Customer Name: <% response.write rsCustomer(&quot;CustName&quot %> | &nbsp; Customer has no backed up files </h4><td></tr>
</table>
<%
Else
rsFiles.MoveFirst

<<< this counts how many records have been found >>>
connectme=&quot;DSN=Architron;uid=sa;pwd=&quot;
sqltemp=&quot;Select FileName from FileDescription where CustId = &quot; & form_CustID
set rstemp=Server.CreateObject(&quot;adodb.Recordset&quot
rstemp.open sqltemp, connectme, adopenstatic
howmanyrecs=rstemp.recordcount
response.write howmanyrecs & &quot; records in<br>&quot; & sqltemp
rstemp.close
set rstemp=nothing

<<< pointer to increment array >>>
arrayinc=0

%>

<table align= &quot;center&quot; cellpadding=&quot;1&quot; cellspacing=&quot;1&quot; BORDER=&quot;0&quot; WIDTH=&quot;800&quot; >
<tr><td align= &quot;center&quot; width=&quot;70&quot; bgcolor=#CCCCCC >File Types</td></tr>
<%

While not rsFiles.EOF


<<< finds the file extension >>>
file = rsFiles(&quot;FileName&quot
rtn = InStr(1,file, &quot;.&quot
if rtn > 0 then
extension = Right(file, (Len(file)-rtn+1))
end if

<<< loops used to compare two srings together >>>
for l=1 to howmanyrecs

<<< if extension tyoe already in array then exit loop >>>
if extArray(l) = extension then
exit-loop <<< code to exit the loop >>>
else
<<< otherwise add extension to array >>>
arrayinc= arrayinc+1
extArray(arrayinc)= extension
exit-loop <<< code to exit the loop >>>
end if



next l

rsFiles.MoveNext


wend
%>
------------------------------------------------------------


Most of the code is in pseudocode and needs to be put into asp code, I was wondering if someone could help me do this and correct any problems they see with the code?

Grateful for any help.
Thanks.

Taha

 

[Guest_imported]

Taval,
I'm not sure if that's what you wanted, but I tried the following query in SQL Server and it worked just fine.
All you need to do is adjust it to your needs and execute it from your .asp as usually:

select distinct right(file_name,(len(file_name)- charindex(&quot;.&quot;,file_name,1))+ 1)from table_test

As you can see this query will give you a recordset, which contains all file extensions without repetition because it's select distinct; only it search entire table, but you can narrow search with where clause; and you don't any arrays.

 

[taval]

guestg,

I tried the above in SQL server changing &quot;file_name&quot; to the entity &quot;FileName&quot; in my table and table_test to relate to my table. When I run it in SQL server I get the message that 1 row is affected with nothing else shown. Do you know what could be wrong?

Thanks.
Taha

 

[Guest_imported]

Taval,
if only you could post an output from simple &quot;select * from&quot; your file table, so I could figure out what data you have there.

 

[Guest_imported]

Taval,
this query works either:

select distinct file_name from table_test where uid = 2

It returns the correct amount of file names for a paticular user without duplicates

 

[taval]

here is what some of the output I get from
SELECT * FROM FileDescription

------------------------------------
FileID FileName Size LastModified
------ --------------------------------------------- ------ -----------------------
724 d:\Pioneer 2\ChangePassword.cpp 2333 1998-01-26 15:45:32.000
725 d:\Pioneer 2\ChangePassword.h 1474 1998-01-26 15:39:42.000
726 d:\Pioneer 2\CntrItem.cpp 4200 1997-12-09 08:28:50.000
727 d:\Pioneer 2\CntrItem.h 1855 1997-11-17 10:56:20.000
728 d:\Pioneer 2\DefaultPane1.cpp 1423 1998-01-23 11:16:20.000
729 d:\Pioneer 2\DefaultPane1.h 1675 1998-01-23 11:16:20.000
730 d:\Pioneer 2\DefaultPane2.cpp 1423 1998-01-23 11:18:08.000
731 d:\Pioneer 2\DefaultPane2.h 1674 1998-01-23 11:18:08.000
732 d:\Pioneer 2\MainFrm.cpp 20815 1998-03-05 10:50:54.000
733 d:\Pioneer 2\MainFrm.h 2632 1998-01-28 09:01:42.000
739 c:\My Documents\Trollies to the resue.RBS 234 1999-06-18 19:32:48.000
740 c:\My Documents\JAM.txt 1973 1998-12-14 13:34:08.000
741 c:\My Documents\Rout.txt 38876 1998-11-27 16:50:48.000

-----------------------------------------------------------

you can now create a table of a simliar kind and see if the query works on SQL server.

Grateful for you help.
Thanks
Taha

 

[Guest_imported]

Taha,
there's still one thing that I don't understand:
what are you really concerned about?
Do you want to find out how many different types(extensions) of files the user has, or do you want to know how many different file names the user has?

 

[taval]

I wanna find all the different types of files extension each user has, so if a user has a files

CntrItem.cpp
ChangePassword.cpp ChangePassword.h

then the query will return

.cpp
.h

hope that clears things up.

 

[Guest_imported]

In this case, the query that I've already posted does exactly what you need:

select distinct right(file_name,(len(file_name)- charindex(&quot;.&quot;,file_name,1))+ 1)from table_test where userId = some_id

 

[taval]

I've tried this:

select distinct FileName , right(FileName,(len(FileName)- charindex(&quot;.&quot;,FileName,1))+ 1)from FileDescription where CustId = 5

in sql sever and I only get the the first FileName bit, but not the extension ( right(FileName,(len(FileName)- charindex(&quot;.&quot;,FileName,1))+ 1) ).

why does it work on your server and not on mine, puzzled?

 

[Guest_imported]

Taval,
you need just to scroll a little bit to the right in your query result window of Query Analizer and you'll see the extensions, and else,if you want to retrieve file names also, distinct won't work and you'll get all file names plus their extensions.
So, in this case don't use distinct, because it's useless.
If you want just file extensions only and without duplicates, you have to use distinct as in query that you've already seen.

 

[Guest_imported]

And sorry for the delays.
I'm in New York
Good luck

 

[taval]

I've looked inside the whole query result window but still this thing just doesn't want to work.

Oh well I'll have to do it the long way. Thanks for all your help

. Taha

 

[Guest_imported]

Taval,
I'm affraid you're tired of my trying to figure this problem out, but give it the last shot. If you still won't get any results, I think you'd better start thinking about reinstalling your SQL Server, 'cause there's definitely something wrong with it.

Here's what my test table look like:

fileID file_name UID
------- ------------------ ---
1 abcd.exe 1
2 bcdee.asp 1
3 cdefff.exe 2
4 defgggg.asp 2
5 efghhhhh.html 2
6 qqqqqqqqq.asp 2
7 qqqqqqqqq.asp 2
8 qqqqqqqqq.asp 2


Query1
-------------
select File_Name , extension = right(File_Name,(len(File_Name)- charindex(&quot;.&quot;,File_Name,1))+ 1)from table_test where uId = 2

Result1
File_Name extension
-------------- -----------
cdefff.exe .exe
defgggg.asp .asp
efghhhhh.html .html
qqqqqqqqq.asp .asp
qqqqqqqqq.asp .asp
qqqqqqqqq.asp .asp

6 row(s)affected
And it's true

Query2
----------
select distinct right(file_name,(len(file_name)- charindex(&quot;.&quot;,file_name,1))+ 1)from table_test where uId = 2

Result2
-------------

extension
---------
.asp
.exe
.html

(3 row(s) affected)

And it's true.

Try it again

 

[taval]

theres one thing I've noticed guestg, its that when I paste your Query1 (as in yor post) into sql server the text &quot;File_Name&quot; comes out pink, when I change this to &quot;FileName&quot; (as called in my table) it turns blue. On top of this if I change &quot;File_Name&quot; to anthing else the text remains blue.

This is how my query looks in sql server:
the red is actually pink in sql server.

select distinct FileName , right ( FileName ,(len ( FileName )- charindex (&quot;.&quot;, FileName ,1))+ 1) from FileDescription where CustId [/color] = 5

could this be causing the problem some-how?

 

[Guest_imported]

Taval,
you're right and I've noticed that too earlier and recalled that File_Name is an intrinsic function in T-SQL.
But this is not the reason because the query works fine even with file_name for the name of the field.
I changed it to filename and later - to f and it still worked.