Using SQL for order lookup on the web

[Guest_imported]

<br>
Hello, everyone! Yes, this is actaully a question, rather than an answer or tip! Here is the problem. I am using MSSQL 7 to lookup up information for clients, on the web. Everything works fine as far as it finiding the information and returning it to the client (based on the criteria the client establishes). However, there is just one little thing I can't seem to get to work no matter what I do. <br>
<br>
I would like to allow the customer to specify how the results are going to be ordered. I uses a SELECT with OPTIONS, and return the value ("order") to the idc. However, it seems no matter what I do, SQL just outputs the file in the same order... unless I specifically use an<br>
ORDER BY PO_NUM (Or whatever field I wasnt to order by). I guess my question is, Can you use a user variable to specify the ORDER BY operator, in the idc? <br>
<br>
Here is the idc file as it now stands. As I said everything works fine, BUT the Order By<br>
<br>
<br>
SQLStatement:<br>
+SELECT *<br>
+FROM BBWOPNPO<br>
+Where COMPANY LIKE '%Company%'<br>
+OR TT_NDC LIKE '%TT_NDC%'<br>
+OR PO_NUM LIKE '%PO_NUM%'<br>
+OR DEPT Like '%DEPT%'<br>
+OR BBW_SKU Like '%BBW_SKU%'<br>
+OR DES Like '%DES%'<br>
+ORDER BY '%Order%'<br>
<br>
And the HTML code to get the field (%Order%) is:<br>
&lt;SELECT NAME="Order" SIZE="1"&gt;<br>
&lt;OPTION VALUE="TT_NDC" SELECTED&gt;TT_NDC<br>
&lt;OPTION VALUE="PO_NUM"&gt;PO_NUM<br>
&lt;OPTION VALUE="DEPT"&gt;DEPT<br>
&lt;OPTION VALUE="BBW_SKU"&gt;BBW_SKU <br>
&lt;/SELECT&gt;<br>
<br>
<br>
(and the htx file, doesn't really matter)<br>
<br>
Any help here will be so greatly appreciated. The only way around this, that I can see, if I can't do it this way, is to write four seperate idc files, each one being basically the same, except for the ORDER BY, then call the correct idc based on the clients criteria, but this is sloppy and creates even MORE files to keep track of (like I don't have neough already)!

 

[Guest_imported]

Was this too hard of a question, or did I just not clarify things enough? I am really interested in finding ANYBODY who knows ANYTHING about writing HTML code for transferring data to and from SQL! ANYBODY?

 

[calahans]

Is the % part of the program language you or are using or the SQL wildcard identifier? If its the latter, that is your problem. <br>
<br>
The following are not the same:<br>
<br>
SELECT * FROM TAB WHERE NAME LIKE 'THER%'<br>
ORDER BY NAME<br>
<br>
and<br>
<br>
SELECT * FROM TAB WHERE NAME LIKE 'THER%'<br>
ORDER BY 'NAME'<br>
<br>
You will not get an error but your sort will not work correctly.<br>
<br>
Is this any use?

 

[Guest_imported]

Actually, the % are used by the Internet Connector Database with SQL to signify a users input that is carried into the idc file. For instance in the above code the ORDER would be carried over to the idc. using a form you carry the value to the idc file (method=get action="mydata.idc?order") and then you can use the variable by using %order%. I am thinking, maybe if I just change the word ORDER to something like CustOrder maybe the idc is getting confused by my using a keyword as a variable? Gotta check that out today... thanks for replying to my post!

 

[jnicho02]

Hi jvogel, looks like nobody else is using idc...<br>
<br>
You know that idc only allows a single SQL statement, why not make that statement a call to a stored procedure passing on your htx parameters. You can then handle your SQL coding in a more orderly fashion and you'll be able to do more complicated things with your single statement. For example, allow the query to be called with some empty parameters.<br>
<br>
e.g. SQLStatement: sp_EmployeeDetails %EmployeeName% %EmployeeSalary%<br>
<br>
[By the way, why do you have LIKE statements and not = in your query?]

 

[bigfoot]

&gt;&gt; Hi jvogel, looks like nobody else is using idc... <br>
<br>
That's because IDC is old technology. Why not use ASP????<br>
You can have as much code as you want in the page.<br>