Want to create a page to pass Select statement with variables to SQL7 1

[uncleroydee]

It shouldn't be this darn complicated! %-(

New to Interdev. Have had a couple of exchanges with pete, for which I am greatful, but this Visual Interdev is starting to get under my skin.

What I want to do seems like a simple concept to me. I have a SQL 7 database, within which I want to construct all of the data manipulation statements as stored procedures using SQL Query Analyzer. I then would execute the procedures with Interdev ASPs including the submission of parameters. My simplest example would be to create one ASP with a form containing a text box for the record number and a submit button. When submitted (onclick?) this form would pass the record number as a parameter to a detail page which would contain a recordset bound to the stored procedure and a grid to display the results.

In FrontPage this would be a simple matter. I would create a form with appropriately named testboxes and a submit button. The Method would be POST and the ACTION would be &quot;FBOverview.asp?VU_NAME=<%If Not IsEmpty(fp_rs) And Not IsNothing(fp_rs) Then Response.Write CStr(fp_rs(&quot;%%VU_NAME%%&quot)%>&quot; This would call up a second ASP containing a table (grid in VI) bound to a database object by the following query statement: &quot;SELECT [SERIAL NR], UIC, NSN, POC, Number, CommonName, RECORD_NBR, FL_ALTERNATE, SYSNAME, TBLNAME, datemod, usermod, basenm3, f2bnbr
FROM %%tblname%%
WHERE [SERIAL NR] like nullif('%%SN%%','') OR LIN like nullif('%%LIN%%','') OR [G-NOMEN] LIKE nullif('%%NOMEN%%','') OR [NSN] = nullif('%%NSN%%','') OR ACQ_DATE like nullif('%%ACQ_DATE%%','') OR ACQ_COST like nullif('%%ACQ_COST%%','')
ORDER BY F2BNBR DESC&quot;

This would retrieve a recordset containing an overview of every record that meets the query criteria. One field in every record would be configured as a hyperlink that would call a detail view of the record; users could then edit the record and submit it to yet another ASP containing the database region wizard that would actually update the database. Trust me, the process works well in FrontPage.

Thus far, in Interdev I have been able to connect to my database and display records from a table using a static query but have not been able to master the mechanics of submitting a SQL statement (Execute sp_*** with parameters) to retrieve a recordset. I figure (hope) that once I've overcome this hurdle it will be a simple matter to adapt the process to other stored procedures for inserting and updating records.

There are two main reasons I want to use V-I for this project: the first is that I thought I should learn it to round out my database skillset (though now I'm not so sure), the second is that the project will have several drop down listboxes that I intend to populate from lookup tables in the database, but that's another matter....

While I'm venting, let me add that I've not found a single book on V-I that has been worthwhile. Does anyone out there have any recommendations?

Thanks for the opportunity to let it out.

Roy

 

[palbano]

> Trust me, the process works well in FrontPage.

Of course, once you know how. The same is true for VID.

Check this out:
HOWTO: Get Records and Return Value from Stored Proc Using DTCs

http://support.microsoft.com/support/kb/articles/Q192/6/66.ASP

Hope this helps
-pete

 

[uncleroydee]

Pete,

You are DA MAN! I have a new lease on life, my life with V-Interdev anyway.

Thank you very much.

BTW, Have you ever heard of Capt. Lou Albano? A wrestler from the late 20th century. Any relation?

Now, if I may be so bold, in FrontPage you can use hidden fields to pass data from form to form without the user seeing it, as in my daisy chain example above. What is the counterpart in VID?

 

[palbano]

> What is the counterpart in VID?

You can use the same technique in VID. Also there are some DTC state managment features built in to the 'Page' object and of course there is the 'PageTransition' object. I just roll my own using hidden form variables so I'm not real familiar with the DTC's.

> Capt. Lou Albano? A wrestler from the late 20th century. Any relation?

Boy if I had a nickle for every time I've been asked that, I would have about $6.35 ! B-)

No, no relation.

-pete