Running Stored Queries in Access Database

Running Stored Queries in an ASP page
Ok we are now ready to move on to running this stored query in the ASP page. Copy the following code and paste it into a new file and save it as storedproc.asp :

Explanation
The only line of interest to us is where

con.Execute "exec
InsertProc 'New Name'"

lies. Now place both storeproc.asp and StoredProc.mdb files in the same place. Then run storedproc.asp page in your browser. You will see nothing happening. Then go and check your database, double click 'Names' table and you will see New Name listed there.

Well done! I must say. We created an Insert stored query in Access database and first ran it from within Access and then called it from an ASP page and it ran fine.

More Stored Queries
We will create now create some more stored queries in our Access database. I will not recall all the steps of creating a stored query, that you can see in earlier pages of this article. I will only tell you what SQL statement to use in that query and then how to call it from within ASP page.

Select Stored Query
This query will select all the records from the database. Create a new query in 'Design view' and copy paste the following code into it and then save it as SelectProc :

Explanation
Just selects all the records from the table Names. To call it from within ASP page, edit the storedproc.asp page so that it looks like following :

Update Stored Query
This query will update record in the Names table. Create a new query in 'Design view' and copy paste the following code into it and then save it as UpdateProc :

Explanation
Updates the Name field to a new name in the Names table with given ID. Thus this stored query asks for two arguments, first new name, second the ID of the Name you want updated. To call it from within ASP page, edit the storedproc.asp page so that it looks like following :

Notice that the only one new line of code has been added to the storedproc.asp page. This is a con.Execute statement which runs the


UpdateProc

stored query and changes the second name in the Names table to 'Changed Name'.

Names - Table

Congratulations! you have just entered a record using stored query.

	<%
		' -- StoredProc.asp --
	%>
	<html>
	<head>
		<title>Running Stored Queries in Access Database</title>
		<style>p { font-family:verdana,arial; font-size:10pt;
			font-weight:bold; }</style>
	</head>
	<body><p>
	<%
		' Connection String
		' Provide relative path to your StoredProc.mdb
		' database
		Dim connStr
			connStr = "Provider=Microsoft.Jet.OLEDB.4.0;
			Data Source=" & Server.MapPath("StoredProc.mdb")

		' Connection Object
		Dim con
			Set con = Server.CreateObject("ADODB.Connection")

		' connecting to database
		con.Open connStr

		' executing stored procedure
		con.Execute "exec InsertProc 'New Name'"

		' closing connection and freeing resources
		con.Close
		Set con = Nothing
	%>
</p></body>
   </html>

Explanation
The only line of interest to us is where con.Execute "exec InsertProc 'New Name'" lies. Now place both storeproc.asp and StoredProc.mdb files in the same place. Then run storedproc.asp page in your browser. You will see nothing happening. Then go and check your database, double click 'Names' table and you will see New Name listed there.

Names - Table

Well done! I must say. We created an Insert stored query in Access database and first ran it from within Access and then called it from an ASP page and it ran fine.

Select Stored Query
This query will select all the records from the database. Create a new query in 'Design view' and copy paste the following code into it and then save it as SelectProc :

	SELECT * FROM [Names];

Explanation
Just selects all the records from the table Names. To call it from within ASP page, edit the storedproc.asp page so that it looks like following :

	<%
		' -- StoredProc.asp --
	%>
	<html>
	<head>
		<title>Running Stored Queries in Access Database</title>
		<style>p { font-family:verdana,arial; font-size:10pt;
				font-weight:bold; }</style>
	</head>
	<body><p>
	<%
		' Connection String
		' Provide relative path to your StoredProc.mdb
		' database
		Dim connStr
			connStr = "Provider=Microsoft.Jet.OLEDB.4.0;
			Data Source=" & Server.MapPath("StoredProc.mdb")

		' Connection Object
		Dim con
			Set con = Server.CreateObject("ADODB.Connection")

		' Recordset Object
		Dim rs

			' connecting to database
			con.Open connStr

			' executing stored procedure
			Set rs = con.Execute ("exec SelectProc")

			' showing all records
			While Not rs.EOF
				Response.Write rs(0) & " " & rs(1) & "<br>"
				rs.MoveNext
			Wend

			' closing connection and freeing resources
			con.Close

			Set rs = Nothing

			Set con = Nothing
	%>
	</p></body>
	</html>

When you run storedproc.asp, it should look like following in your browser:

storedproc.asp

Next we will be creating an Update stored procedure ( Query ).

Update Stored Query
This query will update record in the Names table. Create a new query in 'Design view' and copy paste the following code into it and then save it as UpdateProc :

	UPDATE [Names] SET Name = [@newName] WHERE ID=[@IDis];

<%
		' -- StoredProc.asp --
%>
<html>
<head>
	<title>Running Stored Queries in Access Database</title>
	<style>p { font-family:verdana,arial; font-size:10pt;
			font-weight:bold; }</style>
</head>
<body><p>
<%
	' Connection String
	' Provide relative path to your StoredProc.mdb
	' database
	Dim connStr
		connStr = "Provider=Microsoft.Jet.OLEDB.4.0;
		Data Source=" & Server.MapPath("StoredProc.mdb")

	' Connection Object
	Dim con
		Set con = Server.CreateObject("ADODB.Connection")

	' Recordset Object
	Dim rs

		' connecting to database
		con.Open connStr

		' executing stored procedure
		con.Execute "exec UpdateProc 'Changed Name', 2"
		Set rs = con.Execute ("exec SelectProc")

		' showing all records
		While Not rs.EOF
			Response.Write rs(0) & " " & rs(1) & "<br>"
			rs.MoveNext
		Wend

		' closing connection and freeing resources
		con.Close
		Set rs = Nothing
		Set con = Nothing
%>
</p></body>
</html>

Notice that the only one new line of code has been added to the storedproc.asp page. This is a con.Execute statement which runs the UpdateProc stored query and changes the second name in the Names table to 'Changed Name'.

When you ran storedproc.asp before it looked like this :

storedproc.asp

Now it should look like this :

storedproc.asp