Using ASP & SQL Stored Procedures to Insert new Records

Rexolio · Oct 19, 2001

I have 2 books...SQL for Dummies and Active Server Pages Bible..so far both are great. However, I have a problem with inserting new records from my web site via a registration form to a table in my SQL database. I use a similar ADO insert script for another part of my site, and it inserts very quickly. But this one has a problem and seems to take between 10 and 30 seconds to insert. I've tried everything and used this site to try and figure out what it is. I can't isolate it to any one thing.

My ASP Bible says to create a stored procedure and then use it from my web site to utilize it. SQL for Dummies doesn't tell me anything and the ASP Bible book says to refer to SQL documentation.

My question: can someone give me an example of a script, include connection and close, that will insert from a web site form to a SQL database via a stored procedure? I have the stored procedure created already. I've tried creating a script...but it keeps saying that I'm not using the right parameters (@FirstName_1)...the actual form field name and table field name is simply FirstName. The name @FirstName_1 was the name given my the Create Stored Procedure Wizard.

Thanks in advance!

dbrom · Oct 19, 2001

Here is my ASP page that submits to itself,l and then inserts using sproc.

<html>
<body>
<%
var tviConnStr = "Driver={SQL Server}; Server=122.133.144.155; Database=<DATABASE NAME>; UID=<USER ID>; PWD=<PASSWORD>"

var lastName = String(Request.Form("lastName&quot

);
var firstName = String(Request.Form("firstName&quot

);

var phone1 = String(Request.Form("phone1&quot

);
var phone2 = String(Request.Form("phone2&quot

);
var phone3 = String(Request.Form("phone3&quot

);
var phone4 = String(Request.Form("phone4&quot

);

if (phone1 != "&quot

var phoneNumber = "(" + phone1 + &quot

" + phone2 + "-" + phone3 + " ext. " + phone4;
else
var phoneNumber = "";
var emailAddress = String(Request.Form("emailAddress&quot

);
var webpage = String(Request.Form("webpage&quot

);
var customPage = String(Request.Form("customPage&quot

);
var location = String(Request.Form("location&quot

);
var comments = String(Request.Form("comments&quot

);
var bannerFilename = String(Request.Form("bannerFilename&quot

);
var memory = String(Request.Form("memory&quot

);

//See if memory function is set to "Yes"
var remember = (memory == "Yes&quot

;

var updateOK;

if (location != "undefined&quot

{
//Creates ADO Connection object and opens it
var connTviDB = Server.CreateObject("ADODB.Connection&quot

;
connTviDB.Open(tviConnStr,"USERNAME","PASSWORD&quot

;

//Creates ADO Command object
var cmdInsertPhot = Server.CreateObject("ADODB.Command&quot

;

with (cmdInsertPhot)
{
ActiveConnection = connTviDB; //Active connection set to connTviDB
CommandText = "uspInsertPhotographer"; //Name of stored procedure
CommandType = 4;

//Appending new parameters
Parameters.Append(CreateParameter ("RETURN_VALUE",3,4) );
Parameters.Append(CreateParameter ("@lastName",200,1,50,lastName) );
Parameters.Append(CreateParameter ("@firstName",200,1,50,firstName) );
Parameters.Append(CreateParameter ("@phoneNumber",200,1,50,phoneNumber) );
Parameters.Append(CreateParameter ("@location",200,1,2,location) );
Parameters.Append(CreateParameter ("@bannerFilename",200,1,50,bannerFilename) );
Parameters.Append(CreateParameter ("@webpage",200,1,100,webpage) );
Parameters.Append(CreateParameter ("@emailAddress",200,1,100,emailAddress) );
Parameters.Append(CreateParameter ("@comments",200,1,1000,comments) );
Parameters.Append(CreateParameter ("@customPage",200,1,50,customPage) );

Execute();
updateOK = Parameters("RETURN_VALUE&quot

;
}

//If record was added successfully, updateOK is 1, otherwise it is set to 0
if (updateOK == 1)
{
res = "<center>";
res += "<img src='../images/noproblem.gif' border=0 align=absmiddle>   ";
res += "<font color=#007172>";
res += "Photographer <i><b><font color='#0066FF'>" + firstName + " " + lastName + "</font></i></b> has been added successfully. ";
res += "</font>";
}
else
{
res = "<center>";
res += "<img src='../images/problem.gif' border=0 align=absmiddle>   ";
res += "<font color=#007172>";
res += "Update failed. ";
res += "The casting notice <i><b><font color='#0066FF'>" + cnTitle + "</font></i></b> already exists in the database. ";
res += "</font>";
}

Response.Write(res);

} //end if (cnTitle != "undefined&quot

%>
<form name=addForm action=addPhotographer.asp method=post>
<TABLE width="50%" border=0 cellPadding=5 cellSpacing=5 align=center bgcolor=#99ccff >

<TR>
<TD>Last Name:</TD>
<TD><INPUT name=lastName></TD>
</TR>
<TR>
<TD>FirstName:</TD>
<TD><INPUT name=firstName></TD>
</TR>

<TR>
<TD>Phone Number:</TD>
<TD>
(<INPUT name=phone1 size=2 >)
<INPUT name=phone2 size=2 >
<INPUT name=phone3 size=3>  ext. <INPUT name=phone4 size=3>
</TD>
</TR>

<TR>
<TD> Location:</TD>
<TD>
<SELECT name=location>
<OPTION selected value="">-- Select City --</OPTION>
<OPTION value=LA>Los Angeles</OPTION>
<OPTION value="NY">New York</OPTION>
</SELECT>
</TD>
</TR>

<TR>
<TD>E-mail address:</TD>
<TD><INPUT name=emailAddress ></TD>
</TR>

<TR>
<TD> Webpage:</TD>
<TD><INPUT name=webpage></TD>
</TR>

<TR>
<TD> Comments:</TD>
<TD><TEXTAREA name=comments></TEXTAREA>
</TD>
</TR>

<TR>
<TD> Banner Filename:</TD>
<TD><INPUT name=bannerFilename></TD>
</TR>

<TR>
<TD> Custom Page:</TD>
<TD><INPUT name=customPage></TD>
</TR>

<TR>
<TD> </TD>
<TD><INPUT type="submit" value="Add">
 
<INPUT type=checkbox value=Yes name=memory>
 Remember Fields
</TD>
</TR>

</TABLE>
</form>
</body>
</html>

Here's my Sproc:

CREATE PROCEDURE uspInsertPhotographer

@lastName varchar(50),
@firstName varchar(50),
@phoneNumber varchar(40),
@location varchar(2),
@bannerFIlename varchar(50),
@webpage varchar(100),
@emailAddress varchar(100),
@comments varchar(1000),
@customPage varchar(50)

AS

IF NOT EXISTS (SELECT dateAdded
FROM tviPhotographers
WHERE firstName = @firstName
AND lastName = @lastName
AND location = @location
AND phoneNumber = @phoneNumber)

BEGIN

INSERT INTO tviPhotographers
(firstName, lastName, phoneNumber, location, bannerFilename, webpage, emailAddress, comments, customPage)
VALUES
(@firstName, @lastName, @phoneNumber, @location, @bannerFilename, @webpage, @emailAddress, @comments, @customPage)

RETURN 1

END

ELSE
RETURN 0

Enjoy...

Tell me if you got any questions.
Hope this helps. <Dmitriy>
dbrom@crosswinds.net

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Using ASP & SQL Stored Procedures to Insert new Records

Rexolio

Technical User

dbrom

Programmer

Similar threads

Part and Inventory Search

Sponsor

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Using ASP &amp; SQL Stored Procedures to Insert new Records

Rexolio

Technical User

dbrom

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor

Using ASP & SQL Stored Procedures to Insert new Records