Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing Vairables to a stored procedure

Status
Not open for further replies.

iainmc

Programmer
Dec 2, 2002
53
GB
I am pretty much a beginner with frontpage and web site design/building and have what will hopefully be a basic query-

I have a page (page1) with a text box where i am asking for input, the next page (page2) has a table based on a stored procedure in SQL Server (which i have a connection to). I want to use the value in the text box on page1 as the input parameter used in the stored proc for page2.

Where do i set in the tables html/asp (not sure which one it is) that the procedure has an input parameter that of the value in page1?

Any idea?

 
I am also learning to work with .asp and SQL Server. However, I am just finishing my first project. So perhaps I am a few steps ahead of you. I will be happy to share my limited knowledge with you.

I can break down your post into three questions:

Q1) Is this primarily an .asp issue or an .htm issue?
A1) You need to process data, NOT just show off pretty pictures. Your pages should have the .asp extension, and you should do the coding in .asp.

Q2) How do I move the data from the text box on page one to page two?
A2) I know of four ways to do this. First, you could have a table with one row in SQL Server. You could store the variable there. This method would be rather awkward (it would require extra coding), but it is an option (and its easy to understand on a conceptual level). Second, you could store the variable on a cookie. This would be an ideal solution if you are CERTAIN that all users will accept cookies. Third, you could store the value in a session (global) variable. I find session variables a little hard to understand, but they do give you a global value without using cookies. Fourth, you could use local variables and pass them among the pages. This is easy to understand, but it requires LOTS of code to implement. My advice would be to choose one of these methods and then use it throughout the application. Don't select a method because it is the only one you can write. It only takes a day or two to learn a new method.

Q3) How do a use the variable as a parameter on page two?
A3) I cannot offer any advice on this until I know how the table is created. Do you use SQL to make the table, or some other technique?
 
OhioSteve,

Appreciate the passing on of your knowledge, i had considered holding the value in a table but was not sure if there were better ways to do so, so thanks on that front.

My table is a table on the web page itself, it was created by using the database results wizard in frontpage (prob not the most versatile way i'm sure) and then changing the HTML to use a stored procedure instead of the table as the data source. My lines now say s-sql="procedurename", b-procedure="TRUE"
and i was wondering if there was a line to specify the parameter?

Thanks
 
My boss has created a library of VB functions. We can call these functions to perform certain tasks, including running SQL statements. The SQL statements start out as string variables in .asp pages. Then we can use other functions to pass them into SQL server and run them. After reading your post I looked at the wizard you mentioned. I think that it works in a similar fashion.

Lets say that you have code that says something like this:

SQL="SELECT dbo.company.*, companyname FROM dbo.company WHERE(companyname = 'IBM')"

That code initializes a string variable called "SQL". When you pass in that variable (from the page to the db), it will run a query that returns the IBM record. Now, lets say that instead of "IBM", you want the parameter to be a local variable in your .asp page. The variable is called selectedName. In my environment, the code would look like this:

SQL="SELECT dbo.company.*, companyname FROM dbo.company WHERE(companyname = '" & selectedCompany & "')"

I suspect that you would do something similar if you were using the wizard to establish the connection. Perhaps other posters can clarify this.

Incidentally, in my last post I forgot one method of transfering variables among pages. You can put them into the url itself. The tek-tips web site is a great example of this. The strange stuff at the end of your address bar is really a set of variables and their values.
 
Unfortunately because i am trying to run a Stored Procedure and not an SQL statement the page does not accept "SELECT * FROM ProcName WHERE @InputParamName = Value". I need to type "EXEC ProcName 'InputParamValue'" to call and run a procedure and this is not accepted/recognised by the s-Sql section of my page.

This is becoming a bit of a 'mare really! Thanks for your continued help tho.
 
We have our own, custom-made way of doing programs. So we don't use many stored procedures. However, it can't be too hard to learn how to pass in values. You might start by reviewing books online. It should be included in your SQL Server installation. The topic "SQLOLEDB/stored procedures/calling a stored procedure (ole db)" might be useful to you.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top