Abby and Win’s Blog

Posts Tagged ‘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.

I recently been playing around with Windows 2003 because we are still running Windows 2000 Server at work and may be upgrading soon. Our ASP pages are using includes to our database connection page…

<!–#include file=”some_folder/db_connection_file.asp” –>

If you were to run a page with that code on a Windows 2003 server, you may get an error similar to…

Active Server Pages error ‘ASP 0131′
Disallowed Parent Path

To fix this error, I had to do the following:

1. Create a new virtual directory of the folder that contains the files. We’ll call the virtual directory “example”.
2. Update the include syntax to use virtual instead. So you’ll have something like
   <!– #include virtual=”example/some_folder/db_connection_file.asp” –>

You may get this error with javascripts as well if you have something like…

<script language=”javascript” src=”../some_folder/my_script.js”></script>

You should update that syntax to something like…

<script language=”javascript” src=”/example/some_folder/my_script.js”></script>

I’m not a big Windows user anymore so I don’t know why it has to be done this way or know of a setting so that the old method still works. But those are the 2 changes I had to make on some of our websites to make them compatible with Windows 2003.

I’ve tried in the past to get .Net installed on one of my PCs. I always got errors after the install and just gave up. It’s only to get familiar with it. I usually have a Linux box as a web server anyway and we’re still using classic ASP at work. But as more projects come my way at work, I found using PHP to create different things that classic ASP can’t do. It’s not recommended but it’s a quick fix since many of the projects need to be finished ASAP. All of the sites we develop at work is written using ASP 3.0. But things like handling emails, exporting records to Excel, and uploading files can be created with more features using PHP. The transition between an ASP to PHP page isn’t something that you should do but was necessary for us to complete the project in time.

Why don’t we just develop in PHP or .Net? Well there’s a couple of reasons for that.

• Majority of our servers are Windows. In the beginning, the IT department wouldn’t support anything but Windows servers. But since they don’t support us anyway, and we do our own support, we can use pretty much any server OS we want. We do have 3 OS X servers that can run Apache.
• It would take a longer time to re-code already working web sites running ASP and MSSQL Server 2000 to PHP5 and MySQL or .Net. Not only do we have to re-code them, the testing would take just as long. We have to ensure that the sites work on both Windows and OS X platform and their supported browsers. It pisses me off so much going to a site that doesn’t work on the browser I use. So I make sure it doesn’t happen to any of the visitors going to the sites I develop.
• No money to purchase new Windows hardware/software. Although, our current Windows servers are running fine, if we were to start using .Net, it would be best to run it using the Windows Server 2003 and more hard drive space. We currently are running Windows 2000 Server and MSSQL Server 2000.

So I started to play with .Net again. Luckily we got our hands on Parallels for our iMacs. We can now test on different OS and server environments. I finally got to install .Net 2.0. I know there’s 3.0 and 3.5 releasing in the near future. But I want to do baby steps. So far I have been impressed. I have been having trouble converting a site from ASP to .Net. Particularly with the user authentication. I still have to figure out how the login page will query the database.

For now, I may just try to convert features I wrote on PHP to .Net and add them to the existing ASP sites so that the transition between the pages will be smoother, less of a security risk, and easier to manage.