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;
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.
I dont think it work properly
what doesn’t work properly?
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
@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
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
here’s some more links to check out.http://stackoverflow.com/quest…says to use
hmmm, 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 alsosorry that one also not work for me
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.
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
Response.ContentType = “text/html”Response.AddHeader “Content-Type”, “text/html;charset=UTF-8″Response.CodePage = 65001Response.CharSet = “UTF-8”
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.