Passing parameter to stored procedure in Access (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

jdbegg -> 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



rdouglass -> 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::' )"



bobby -> RE: Passing parameter to stored procedure (7/9/2002 16:41:51)

Stored Procedure = Query

They' re the same thing, a query is what Access calls a stored procedure.

The above code should do the trick, just remember that you can only " read" a query, you can' t use it to " update" " insert" or " delete" records.

Using ASP is the same way (if you' re not using the DRW), just substitute the name of the query for the table name in your code.



jdbegg -> 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



bobby -> RE: Passing parameter to stored procedure (7/9/2002 17:22:35)

You' re going to need to create a connection to your database, unless you' ve already done that... then you need to create and open a Recordset...

ie: 
dim oMyRS
oMyRS=Server.CreateObject (" ADODB.Recordset" )
oMyRS.Open " war_board_by_cr_with_parm" , oConn


This will grab everything in your query (assuming that oConn is the name of your connection object) that matches the criteria...



bobby -> RE: Passing parameter to stored procedure (7/9/2002 17:28:13)

Uh, oops, you' re passing a parameter to this page for the search criteria, right?

Assuming you' re using v_CR_List as a variable, pulling a parameter from a form into that variable...

Then you' d want something like: 
dim oMyRS  oMyRS=Server.CreateObject (" ADODB.Recordset" ) 
sqltext = " SELECT * FROM war_board_by_cr_with_parm " 
sqltext = sqltext & "  WHERE CR = "  &  v_CR_List  & " ;" 
oMyRS.Open sqltext, oConn





jdbegg -> 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 -> 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



rdouglass -> 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 -> 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 -> 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 -> 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>




jdbegg -> 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 -> 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[:)]



huangti -> 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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.140625