Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update one table from an other table in classic ASP

Posted on 2014-07-17
14
Medium Priority
?
343 Views
Last Modified: 2014-07-17
Hi Experts,
I'm adding records to the New_Employees table from the Employees table, but only if the Emp_Id is not already present in the New_Employees table. I am using a button to execute:

<form action="UpdateEmployees.asp">
    <input type="submit" value="Update Employee List">
</form>

The problem is that I have thousands of rows and it times out in the middle of the process, after transferring a couple of hundred records. The button has to be pressed several times in order to transfer all missing employees. I was wondering if there is a better method to perform this task. Maybe using stored procedure would be more efficient? If yes, may I ask for your help to write the stored procedure and call it from my classic asp page? I don't have too much experience with writing stored procedures.
This is my code:
<%
rs1="select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from New_Employees order by ID"
set rs1= Conn.execute(rs1)

rs2="select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from Employees order by ID"
set rs2= Conn.execute(rs2)

rs2.movefirst

do while not rs2.eof

New_Emp_Id =  rs2("Emp_Id")

rsSFO= "select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from New_Employees where Emp_Id='" & New_Emp_Id & "'"
set rsSFO= Conn.execute(rsSFO)

sEmp_Id=rs2("Emp_Id")
sLast_Name=replace(rs2("Last_Name"),"'","''")
sFirst_Name=replace(rs2("First_Name"),"'","''")
if not isnull(rs2("Email_Id")) then
sEmail_Id=replace(rs2("Email_Id"),"'","''")
else
sEmail_Id=""
end if
sWork_Loc_Cd=rs2("Work_Loc_Cd")
sWork_Phone_Num=rs2("Work_Phone_Num")
sDivision_Dscr=replace(rs2("Division_Dscr"),"'","''")
sPosition_Title=replace(rs2("Position_Title"),"'","''")
sMail_Cd_Dscr=rs2("Mail_Cd")
sAdj_Svc_Dt=rs2("Adj_Svc_Dt")

if rsSFO.eof then

strSQL = "INSERT INTO New_Employees (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt) VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

conn.Execute(strSQL)
end if

rs2.movenext
loop

'Redirect to the UPDATED page
Response.Redirect "New_Employees.asp"
%>

Open in new window

0
Comment
Question by:romsom
  • 7
  • 6
14 Comments
 
LVL 4

Assisted Solution

by:Philip Portnoy
Philip Portnoy earned 400 total points
ID: 40202558
I know nothing about ASP, but from the SQL standpoint you're actually doing it in a completely strange way.
Stored procedure is a good idea.

I'm not going to write the whole thing for you, but here's main thing, make sure that datatypes are right and list all the fields:

CREATE PROC sp_add_new_employee
@Emp_ID int,
@Last_Name nvarchar(max),
@First_Name nvarchar(max),
...
@Adj_Svc_Dt nvarchar(max)
AS
IF @Emp_ID NOT IN (SELECT DISTINCT EMP_ID FROM NEW_EMPLOYEES)
BEGIN
INSERT INTO New_Employees (@Emp_ID, @Last_Name..., @Adj_Svc_Dt)
END

Open in new window


And use following code to call your stored proc from the ASP app:

strSQL = "INSERT INTO New_Employees VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

Open in new window

0
 

Author Comment

by:romsom
ID: 40202582
Thanks for the quick response. I'm working on it. I'll be back with the results.
0
 
LVL 34

Accepted Solution

by:
Big Monty earned 1600 total points
ID: 40202592
hello again :)

i always try to use stored procedures when doing DB intensive actions, however in this case, let's see how fast the code below runs. if it still has poor performance, we can easily convert it over.

I changed the logic a bit, have a look and ask questions on anything you don't understand

<%
sql = "select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt " _
	& "from New_Employees " _
	& "where Emp_ID not in ( select Emp_ID order by ID from Employees )"
set rs1= Conn.execute(sql)

'-- don't need these 3 lines, comment them out
'rs2="select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from Employees order by ID"
'set rs2= Conn.execute(rs2)
'rs2.movefirst

do while not rs.eof

	sEmp_Id=rs("Emp_Id")
	sLast_Name=replace(rs("Last_Name"),"'","''")
	sFirst_Name=replace(rs("First_Name"),"'","''")
	if not isnull(rs("Email_Id")) then
	sEmail_Id=replace(rs("Email_Id"),"'","''")
	else
	sEmail_Id=""
	end if
	sWork_Loc_Cd=rs("Work_Loc_Cd")
	sWork_Phone_Num=rs("Work_Phone_Num")
	sDivision_Dscr=replace(rs("Division_Dscr"),"'","''")
	sPosition_Title=replace(rs("Position_Title"),"'","''")
	sMail_Cd_Dscr=rs("Mail_Cd")
	sAdj_Svc_Dt=rs("Adj_Svc_Dt")

	strSQL = "INSERT INTO New_Employees (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt) VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

	conn.Execute(strSQL)

	rs.movenext
loop

'Redirect to the UPDATED page
Response.Redirect "New_Employees.asp"
%>

Open in new window


for now, we'll build a sql string to make sure the process is working properly. If it is, I recommend switching it over to a parameterized query, which will help ensure proper data is passed over. for now, let's see if this works.

Before executing any asp code, can you run the sql statement on line 2 directly in SSMS and make sure you get the proper records?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:romsom
ID: 40202621
Thanks Big Monty, now I get this error on line 6:

Incorrect syntax near the keyword 'from'
0
 
LVL 34

Expert Comment

by:Big Monty
ID: 40202624
can you post lines 1-6?
0
 

Author Comment

by:romsom
ID: 40202640
I did this, but now it won't transfer any data
<%
sql = "select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from New_Employees where Emp_ID not in ( select Emp_Id from Employees )"
set rs= Conn.execute(sql)

do while not rs.eof

	sEmp_Id=rs("Emp_Id")
	sLast_Name=replace(rs("Last_Name"),"'","''")
	sFirst_Name=replace(rs("First_Name"),"'","''")
	if not isnull(rs("Email_Id")) then
	sEmail_Id=replace(rs("Email_Id"),"'","''")
	else
	sEmail_Id=""
	end if
	sWork_Loc_Cd=rs("Work_Loc_Cd")
	sWork_Phone_Num=rs("Work_Phone_Num")
	sDivision_Dscr=replace(rs("Division_Dscr"),"'","''")
	sPosition_Title=replace(rs("Position_Title"),"'","''")
	sMail_Cd_Dscr=rs("Mail_Cd")
	sAdj_Svc_Dt=rs("Adj_Svc_Dt")

	strSQL = "INSERT INTO New_Employees (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt) VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

	conn.Execute(strSQL)

	rs.movenext
loop

'Redirect to the UPDATED page
Response.Redirect "SFO_Employees.asp"
%>

Open in new window

0
 
LVL 34

Expert Comment

by:Big Monty
ID: 40202644
if you run the sql on line 2 directly in SSMS like I suggested originally, do you get any records returned?
0
 

Author Comment

by:romsom
ID: 40202670
I ran it in SSMS but no rows were returned
0
 
LVL 34

Expert Comment

by:Big Monty
ID: 40202692
that indicates there are no new Emp_ID's, meaning it can't find any any Emp_ID's in the new table that is not already in the original table.

Make sure you have some unique Emp_ID's in the new employee table
0
 

Author Comment

by:romsom
ID: 40202730
I think the table names have to be reversed. I'd like to insert From Employees to New_Employees, but only if the Emp_Id is not in New_Employees already. So I made the following change and ran it. About 3830 records were inserted, the it timed out giving me an error message:
Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
0
 

Author Comment

by:romsom
ID: 40202745
<%
sql = "select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from Employees where Emp_ID not in ( select Emp_Id from New_Employees )"
set rs= Conn.execute(sql)

do while not rs.eof

	sEmp_Id=rs("Emp_Id")
	sLast_Name=replace(rs("Last_Name"),"'","''")
	sFirst_Name=replace(rs("First_Name"),"'","''")
	if not isnull(rs("Email_Id")) then
	sEmail_Id=replace(rs("Email_Id"),"'","''")
	else
	sEmail_Id=""
	end if
	sWork_Loc_Cd=rs("Work_Loc_Cd")
	sWork_Phone_Num=rs("Work_Phone_Num")
	sDivision_Dscr=replace(rs("Division_Dscr"),"'","''")
	sPosition_Title=replace(rs("Position_Title"),"'","''")
	sMail_Cd_Dscr=rs("Mail_Cd")
	sAdj_Svc_Dt=rs("Adj_Svc_Dt")

	strSQL = "INSERT INTO New_Employees (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt) VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

	conn.Execute(strSQL)

	rs.movenext
loop

'Redirect to the UPDATED page
Response.Redirect "SFO_Employees.asp"
%>

Open in new window

0
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1600 total points
ID: 40202810
you're right, I had the table logic backwards...

you have a few options here, depending on what you're looking to do....

Are you concerned about it taking a long time (assuming we can get it to finish without an error)? if the answer is no, then you can increase the Server.ScriptTimeOut property by doing something like:

Line 1: Server.ScriptTimeOut = 600

this would set the script timeout to 600 seconds, which is 10 minutes. During this time, the page will just look like it's doing nothing, it'll just look like the page is loading and loading and loading and....

not a good solution if a lot of people are going to access the page, and there are ways to display to display some kind of message stating the page is loading, please wait... if only a handful of people are using the page, this would be the easiest.

if not, we can move it over to a stored procedure...I'm going to be wrapped up for awhile, but could possibly get to it later if no one else chimes in
0
 

Author Closing Comment

by:romsom
ID: 40202839
I guess the stored procedure would be the best solution. Your code is much better than the one I used before, thank you so much for your help. I'm trying to write the stored procedure, and I'll post another question while I'm doing it.
0
 
LVL 34

Expert Comment

by:Big Monty
ID: 40202852
glad I could help :)

if you switch over to a SP, you'll want to load all of the ID's into a temp table, along with the other columns you want to do the insert on, and then loop through each record to do the insert. Below is a very basic example on how to do that:

Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question