MSSQL Stored Procedures and Classic ASP

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;

GO

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

11 thoughts on “MSSQL Stored Procedures and Classic ASP

  1. Hi therei have a question, hopefully you willing to help mehow to achive sql syntax below:INSERT INTO product (Name,Description,Template,EntryTemplate,Stamp) VALUES (N’thử nghiệm’,N’thử nghiệm’,1002,0,Getdate())fromINSERT INTO product(Name, Description, Template, EntryTemplate, Stamp)VALUES (@Name, @Description, @Template, @EntryTemplate, GETDATE())orobjCmd.Parameters.Append objCmd.CreateParameter(“Name”, adVarChar, adParamInput, 100, CStr(Request.Form(“Name”)))my problem :how to add N char for unicode infront of @name parameter?how to add N char for unicode infront of CStr(Request.Form(“Name”)) ?appreciate your helpthanksHerbert

    Like

    • @herbert sorry we have migrated everything over to mysql i don’t have an environment to test things to help you. i’ll try to help with questions and ideas. is your table utf-8? here’s an article from microsoft i hope it helps http://support.microsoft.com/k…. hopefully someone else browsing the site can help as well. good luck

      Like

      • Hi Sherwinthanks for quick replyi don’t think so because when i try to save vietnamese language in the table it converted to &#…i want to store the vietname language to db not in &#.. format but the char it selfexample : thử nghiệmi understand that this can be done by using nvarchar as a data type and put N in front of the string example : N’thử nghiệm”i just don’t know how to add it if using parameterthanksherbert

        Like

      • here’s some more links to check out.http://stackoverflow.com/quest…says to usehmmm, not sure why when i post the code there’s a space in the <%@ but there shouldn't be. at least i don't think there is. it's been awhile since i coded in asp lol.the page also explains why.another link is http://stackoverflow.com/quest…sorry i can’t verify or test since i don’t have an environment that runs it anymore. let me know if that helps also

        Like

    • i was able to get a test environment going. i was able to insert and display the record. i think where you’re having issue is the CreateParameter(). i used 202 for the datatypeenum value. if you check out http://www.w3schools.com/ado/m…, 202 will give you adVarWChar.my code is as followscmd.CommandText = “INSERT INTO product(Name, Description, Template, EntryTemplate, Stamp)VALUES (?, ?, ?, ?, GETDATE())”objCmd.Parameters.Append objCmd.CreateParameter(“Name”, 202, adParamInput, 100, CStr(Request.Form(“Name”)))i tried adVarChar and it gave me an improperly defined error so that’s where i’m guessing your problem is.wow, i haven’t coded in ASP in a long time. it took a minute to get back into it. i had to dig up old code to refresh my memory. i hope this one helps. keep me posted.

      Like

      • Hi Sherwinsorry for late reply.wow you set it up in your computer again. thanks a lot man.before I already change from adVarChar to adVarWChar but it also not workingbtwmy colleague help me to solve this problem.its turn out that i missed out several linesin my case :looks like adding in tag its not enoughstill need to define code below inside asp tag at respective asp pageonce again thanks a lot for Sherwin

        Like

      • Response.ContentType = “text/html”Response.AddHeader “Content-Type”, “text/html;charset=UTF-8″Response.CodePage = 65001Response.CharSet = “UTF-8”

        Like

  2. I have recently started a website, the information you offer on this web site has helped me greatly. Thanks for all of your time & work.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s