|
| |
|
|
jdbegg
Posts: 29
Joined: 6/19/2002
From: Huntsville AL USA
Status: offline
 |
Passing parameter to stored procedure in Access - 7/9/2002 15:39:30
Does anyone know how to go about passing a parameter from a form into a stored procedure in Access?
I have the form designed and the stored procedure done, but don' t know how to hook them up. At the moment, the stored procedure is just a query (it doesn' t accept passed parameters).
Thanks much,
Jim
< Message edited by Spooky -- 1/11/2003 7:54:02 PM >
|
|
|
|
rdouglass
Posts: 9279
From: Biddeford, ME USA
Status: offline
|
RE: Passing parameter to stored procedure - 7/9/2002 16:34:51
You can use Access queries with the DRW in FP just like tables - at least for viewing info. They are read-only IIRC. However, you can pass parameters to queries just like tables; just select the query in Step 2 of the DRW. Your SQL line should look very similar to one using tables:
fp_sQry=" SELECT * FROM myQuery WHERE (ID = ' ::ID::' )"
vs.
fp_sQry=" SELECT * FROM myTable WHERE (ID = ' ::ID::' )"
| |
|
|
|
jdbegg
Posts: 29
Joined: 6/19/2002
From: Huntsville AL USA
Status: offline
|
RE: Passing parameter to stored procedure - 7/9/2002 16:57:53
I' m not using the DRW in FP. I had originally written the SQL query in an ASP page thus:
dim strCRSQL
strCRSQL = " war_board_by_cr_with_parm"
' strCRSQL = " Select * from WAR_BOARD"
' strCRSQL = strCRSQL & " Where CR = " & v_CR_List &
' strCRSQL = strCRSQL & " Order by SPR; "
But, with further reading have decided to try and make a stored procedure in Access itself. From what I' ve read the strCRSQL assigment should call the stored procedure in Acess. The line of code that' s not a comment should call the procedure that' s sitting in the Access DB. The procedure is identical to the Select statement above with the exception of the procedue in Access asking for or waiting on a value being passed to it... i.e v_CR_List.
I hope this makes sense.
Jim
| |
|
|
|
jdbegg
Posts: 29
Joined: 6/19/2002
From: Huntsville AL USA
Status: offline
|
RE: Passing parameter to stored procedure - 7/9/2002 17:42:17
I do have a connection string to the DB thus:
dim objConn
objConn = Server.CreateObject(" ADODB.Connection" )
objConn.ConnectionString = " DSN=war_board"
objConn.Open
Although the page quit working because of the code change...grrrr..... I don' t see anything wrong this this statement....
I do have a Select statement which should accept a passed value. It looks like this:
dim strCRSQL
strCRSQL = " war_board_by_cr_with_parm"
' strCRSQL = " Select * from WAR_BOARD"
' strCRSQL = strCRSQL & " Where CR = " & v_CR_List &
' strCRSQL = strCRSQL & " Order by SPR; "
The " war_board_by_cr_with_parm" is a stored procedure within my DB. Calling it would get everything in the recordset. I' m wanting to take a variable set in a form and pass it to " war_board_by_cr_with_parm" with the result set that is returned only containing those records that meet the passed criteria.
I don' t know...it might just be better for me at the moment to go with the Select statement embedded in the page. On the other hand, I' m trying to design this thing with a view towards ease of maintenance later on....
| |
|
|
|
ASPPlayground
Posts: 122
Joined: 6/25/2002
From: Toronto, Canada
Status: offline
|
RE: RE: Passing parameter to stored procedure - 7/9/2002 20:59:38
There are a few things you need to know about Access' s Stored Procedure, and please take the following code as an example:
Dim objRS, strSP, strDb, intVar, strVar, arrAllRec
intVar = CInt (request(" integerValue" ))
strVar = CStr (request(" StringValue" ))
strDb = " Northwind" [color=#666600]' Assume that you have a DSN entry called Northwind[/color]
strSP = " spSelectSomething " &intVar&" , ' " &strVar&" ' " [color=#666600]' Don' t wrap parameteres with parentheses, and spSelectSomething is a Stored Query in your Access DB[/color]
set objRS = Server.CreateObject(" ADODB.RECORDSET" )
With objRS
.open strSP, strDb, , , adCmdStoredProc
if not .EOF then
arrAllRec = .getrows
end if
.close
End With
Set objRS = nothing
< Message edited by ASPPlayground -- 7/8/2002 9:01:07 PM >
_____________________________
Samuel Chou
ASPPlayground.NET Developer
| |
|
|
|
rdouglass
Posts: 9279
From: Biddeford, ME USA
Status: offline
|
RE: RE: Passing parameter to stored procedure - 7/10/2002 14:19:30
quote:
ORIGINAL: jdbegg
......I do have a Select statement which should accept a passed value. It looks like this:
dim strCRSQL
strCRSQL = " war_board_by_cr_with_parm"
' strCRSQL = " Select * from WAR_BOARD"
' strCRSQL = strCRSQL & " Where CR = " & v_CR_List &
' strCRSQL = strCRSQL & " Order by SPR; "
....
Did you try something like this:
dim strCRSQL
strCRSQL = " Select * from war_board_by_cr_with_parm"
strCRSQL = strCRSQL & " Where CR = " & v_CR_List &
strCRSQL = strCRSQL & " Order by SPR; "
| |
|
|
|
jdbegg
Posts: 29
Joined: 6/19/2002
From: Huntsville AL USA
Status: offline
|
RE: Passing parameter to stored procedure - 7/10/2002 14:34:17
For the time being I have the select statement looking like this:
dim strCRSQL
strCRSQL = " Select * from WAR_BOARD "
strCRSQL = strCRSQL & " Where CR = ' " & v_CR_List & " ' ;"
The " war_board_by_cr_with_parm" is a stored procedure I' ve written that does the same as the SQL above with the exception that I haven' t gotten the coding written to pass the parm v_CR_List to it yet. I found the chapter in a book that deals with that.
| |
|
|
|
jdbegg
Posts: 29
Joined: 6/19/2002
From: Huntsville AL USA
Status: offline
|
RE: Passing parameter to stored procedure - 7/10/2002 16:26:58
This is really getting to be irritating!!! After I solve one problem another seems to pop up. This one has left me completely in the dust. I had gotten to the point where I retrieved disticnt column values from my table, passed them on to a form, was able to select the value. I had a debugging statement in the code that showed me the SQL statement I had built. Well, I dug into the books a little more and found how to set up my AccessDB to accept a parameter. Then with everyone' s help here and more reading I put together code I thought would work. And I think it has, except, now the page comes back as a blank...but no errors. I can' t tell what it' s doing as all I have is a blank page. Could somebody look at this code and tell me why I' m getting a blank page and what to do to fix it?
Thanks in advance... Jim
<%@ Language=VBScript %>
<% Option Explicit %>
<% Response.Buffer = True %>
<%
' This script will retrieve all Change Requests from the
' War Board where CR is not null and is unique for loading
' into a list box.
'
' The CRs are to be sorted in ascending order.
'
' All the CRs will be loaded in to a list box from which the
' user can select the one he/she chooses. Upon making a choice
' all rows that match the CR are to be retrieved from the War
' Board database and displayed to the user.
%>
<html>
<head>
<title>CR Query Form</title>
</head>
<body>
<%
On Error Resume Next
' Open connection to the database
dim objConn
Set objConn = Server.CreateObject(" ADODB.Connection" )
objConn.Open " DSN=war_board"
If objConn.Errors.Count > 0 Then
Set objErr = Server.CreateObject(" Error" )
dim fndCriticalError
For Each objErr In objConn.Errors
If objErr <> 0 Then
Response.Write " Number: " & objErr.Number & " <P>"
Response.Write " Description: " & objErr.Description & " <P>"
Response.Write " Source: " & objErr.Source & " <P>"
Response.Write " SQLState: " & objErr.SQLState & " <P>"
Response.Write " NativeError: " & objErr.NativeError & " <P>"
fndCriticalError = True
End If
Next
Set objErr = Nothing
If fndCriticalError Then
Response.End
End If
End If
' Create SQL statment to retrieve all CRs that are not null
dim strSQL
strSQL = " war_board_by_cr"
' Debugging SQL statement
Response.Write strSQL
' Now we open the record set
dim rs_cr_wb
set rs_cr_wb=Server.CreateObject(" ADODB.recordset" )
rs_cr_wb.Open strSQL, objConn
If Not rs_cr_wb.EOF then
rs_cr_wb.MoveFirst
%>
<%
' Here we are building the list box from data found in
' the War Board database.
%>
<form action=" cr_form.asp" method=" post" >
<select name=" CR_List" >
<% Do until rs_cr_wb.EOF
Response.Write " <option value=' " & rs_cr_wb(" CR" ) & " ' >"
Response.Write rs_cr_wb(" CR" )
rs_cr_wb.MoveNext
Loop
%>
</select>
<input type=" submit" value=" Go" />
</form>
<%
' Close the cursor for form data
rs_cr_wb.Close
Set rs_cr_wb = Nothing
End If
dim v_CR_List
v_CR_List = Request.Form(" CR_List" )
Set objCmd = Server.CreateObject(" ADODB.Command" )
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = " {CALL war_board_by_cr_with_parm (' " & CStr(v_CR_List) & " ' )}"
Set rs_cr_wb2 = Server.CreateObject(" ADODB.recordset" )
Set rs_cr_wb2 = objCmd.Execute
If objConn.Errors.Count > 0 Then
Set objErr = Server.CreateObject(" Error" )
dim fndCriticalError2
For Each objErr In objConn.Errors
If objErr <> 0 Then
Response.Write " Number: " & objErr.Number & " <P>"
Response.Write " Description: " & objErr.Description & " <P>"
Response.Write " Source: " & objErr.Source & " <P>"
Response.Write " SQLState: " & objErr.SQLState & " <P>"
Response.Write " NativeError: " & objErr.NativeError & " <P>"
fndCriticalError2 = True
End If
Next
Set objErr = Nothing
If fndCriticalError2 Then
Response.End
End If
End If
Do Until rs_cr_wb2.EOF
Response.Write rs_cr_wb2 (" CR" ) & " <P>"
rs_cr_wb2.MoveNext
Loop
' Clean up our ADO objects
rs_cr_wb2.Close
Set rs_cr_wb2 = Nothing
%>
</body>
</html>
| |
|
|
|
ASPPlayground
Posts: 122
Joined: 6/25/2002
From: Toronto, Canada
Status: offline
|
RE: RE: Passing parameter to stored procedure - 7/10/2002 17:08:18
I am going to simplify your code.
try the following:
<%@ Language=VBScript %>
<% Option Explicit %>
<% Response.Buffer = True %>
<html>
<head>
<title>CR Query Form</title>
</head>
<body>
<%
dim objConn,rs_cr_wb
Set objConn = Server.CreateObject(" ADODB.Connection" )
objConn.Open " DSN=war_board"
set rs_cr_wb=Server.CreateObject(" ADODB.recordset" )
rs_cr_wb.Open " war_board_by_cr" , objConn, , , adCmdStoredProc
If Not rs_cr_wb.EOF then
%>
<form action=" cr_form.asp" method=" post" >
<select name=" CR_List" >
<% While not rs_cr_wb.EOF %>
<option value=" <%= rs_cr_wb(" CR" ) %>" ><%= rs_cr_wb(" CR" ) %></option>
<% rs_cr_wb.MoveNext %>
<% wend %>
</select>
<input type=" submit" value=" Go" />
</form>
<%
End If
if rs_cr_wb.state <> adStateClosed then rs_cr_wb.Close
rs_cr_wb.open " war_board_by_cr_with_parm ' " & CStr(" " &Request.Form(" CR_List" )) & " ' " , objConn, , , adCmdStoredProc
Do Until rs_cr_wb.EOF
Response.Write rs_cr_wb (" CR" ) & " <P>"
rs_cr_wb.MoveNext
Loop
if rs_cr_wb.state <> adStateClosed then rs_cr_wb.Close
Set rs_cr_wb = Nothing
%>
</body>
</html>
_____________________________
Samuel Chou
ASPPlayground.NET Developer
| |
|
|
|
jdbegg
Posts: 29
Joined: 6/19/2002
From: Huntsville AL USA
Status: offline
|
RE: Passing parameter to stored procedure - 7/10/2002 17:22:52
Samuel,
I tried it and am getting this error message back...
Error Type:
Microsoft VBScript runtime (0x800A01F4)
Variable is undefined: ' adCmdStoredProc'
/test_group/cr_form2.asp, line 18
I' ll see if I can figure out what to do about it.
Thanks for taking the time to go over my code. This time last year my family and I were there in Toronto on vacation. It' s quite a different place than Alabama. We enjoyed ourselves immensely.
| |
|
|
|
ASPPlayground
Posts: 122
Joined: 6/25/2002
From: Toronto, Canada
Status: offline
|
RE: RE: Passing parameter to stored procedure - 7/10/2002 18:52:09
It is because you don' t have reference to ADO constants, try this:
<!-- METADATA TYPE=" typelib" UUID=" 00000200-0000-0010-8000-00AA006D2EA4" -->
<% Option Explicit %>
<% Response.Buffer = True %>
<html>
<head>
<title>CR Query Form</title>
</head>
<body>
<%
dim objConn,rs_cr_wb
Set objConn = Server.CreateObject(" ADODB.Connection" )
objConn.Open " DSN=war_board"
set rs_cr_wb=Server.CreateObject(" ADODB.recordset" )
rs_cr_wb.Open " war_board_by_cr" , objConn, , , adCmdStoredProc
If Not rs_cr_wb.EOF then
%>
<form action=" cr_form.asp" method=" post" >
<select name=" CR_List" >
<% While not rs_cr_wb.EOF %>
<option value=" <%= rs_cr_wb(" CR" ) %>" ><%= rs_cr_wb(" CR" ) %></option>
<% rs_cr_wb.MoveNext %>
<% wend %>
</select>
<input type=" submit" value=" Go" />
</form>
<%
End If
if rs_cr_wb.state <> adStateClosed then rs_cr_wb.Close
rs_cr_wb.open " war_board_by_cr_with_parm ' " & CStr(" " &Request.Form(" CR_List" )) & " ' " , objConn, , , adCmdStoredProc
Do Until rs_cr_wb.EOF
Response.Write rs_cr_wb (" CR" ) & " <P>"
rs_cr_wb.MoveNext
Loop
if rs_cr_wb.state <> adStateClosed then rs_cr_wb.Close
Set rs_cr_wb = Nothing
%>
</body>
</html>
Hope this can help
< Message edited by aspplayground -- 7/9/2002 6:53:20 PM >
_____________________________
Samuel Chou
ASPPlayground.NET Developer
| |
|
|
|
huangti
Posts: 1
Joined: 7/10/2006
Status: offline
|
RE: RE: Passing parameter to stored procedure - 7/10/2006 21:17:18
What about reports that are using store proc that accept parameters ?
I am trying to use QueryDef.Parameters() in ASP but to no avail.
It doesn't work at all.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
