A client at work wanted us to create a way to duplicate existing records on their website with a click of a button. These records involve multiple tables in the database. Some of the tables contained a lot of columns. We tried to do most of work using ASP but sometimes the browser would time out and Dreamweaver would crash. Then I thought, why don’t we have our SQL server do most of the heavy lifting. I created a stored procedure that query the database for the record(s) that need to be duplicated and have them duplicated. Below is a short example to get you started. First you need to create a stored procedure in SQL Server.
CREATE PROCEDURE stored_procedure_sample
@record_id INT OUTPUT
AS
BEGIN
INSERT INTO table_name (column1, column2, column3)
SELECT column1, column2, column3
FROM table_name
WHERE record_id = @record_id
END;
GONext you need to create the ASP page that process the request. You can use form variable or URL query string. For the example, I will use a form variable. Let’s imagine on page1.asp there is a form with a hidden variable that contains the record_id you want to duplicate. You submit this to page2.asp and here’s a sample of what page2.asp should look like.
<%
Dim connString, rsCmd
connString = “your connection string”
Set rsCmd = Server.CreateObject(“ADODB.Command”)
rsCmd.ActiveConnection = connString
rsCmd.CommandType = 4
rsCmd.CommandText = “stored_procedure_sample”
rsCmd.Parameters.Append rsCmd.CreateParameter("@record_id", 200, 1, 4, Request.Form("record_id"))
rsCmd.Execute
%>What page2.asp does is it takes the form variable “record_id” from page1.asp and tells the stored procedure to get the values for this record and insert it as a new record. That’s pretty much it. Just a short, simple example on how you can create a stored procedure in MSSQL Server and use it with classic ASP.
Show 11 comments
Comments
These comments were left on the original blog post. Comments are now closed — see About for how to get in touch.
↑ Back to top