Massive Data Entry Application

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Massive Data Entry Application

Author

Topic

dimitrisX
Starting Member

7 Posts

Posted - 2005-12-10 : 11:49:47

Hello,
I am writing a web application for data entry from many clients. I am using classic ASP amd HTML to build the interface and the required scripts. The db is on SQL SERVER 2000.
The clients will be no more than 50, so performance is not an issue. They will have to Insert, Update, Delete and View records. But they will mainly Insert Data concurrently.

1. Should I use ADO or straight SQL statements?
2. How do i choose among CursorLocation, CursorType and LockType options? I am a bit confused on that matter, as, these options don't seem to work when an update is done this way:

oConnection.Execute("Update Table Set Age=27 Where Name='John'").

I have already build part of the app, but I have doubts regarding the efficiency of using SQL instead of ADO, when data entry is done from many clients and these clients may update those records. What is your oppinion?
thank you.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-12-10 : 12:32:49

quote:
but I have doubts regarding the efficiency of using SQL instead of ADO

Your doubts should be reversed. ADO is simply an additional layer that generates SQL commands to perform the actions required. The ADO Recordset .Update and .AddNew methods and not necessarily the most efficient ways to do the job, nor is ADO's transaction handling. This is especially true when handling large batches of data.

Generally speaking, it is better to use ADO Recordsets only for retrieving data, and to use SQL stored procedures via ADO Command objects to perform insert, update, and delete operations. SQL Server will handle concurrency and locking for you automatically, and you can use its normal transaction handling within a stored procedure for more complex operations. The ADO CursorType and CursorLocation will be less of an issue, since the Recordset would essentially be read-only.

Kristen
Test

22859 Posts

Posted - 2005-12-10 : 13:47:13

What Rob said

Kristen

dimitrisX
Starting Member

7 Posts

Posted - 2005-12-10 : 13:59:09

Ok, I got it. thank you.
Now, I have a small example and I would like to ask if it could cause any problems, as it is.

In one case, a transaction involves 3 operations:
1. Get a value from a web form field and insert it into table 'Translations'.
2. Get the ID of the new record
3. put the ID in another column of the same record

This is how I do it (in vbscript):


'*** form value
	FormValue = Request.Form("txtComments")
	
	on error resume next

'*** begin transaction
	oConn.BeginTrans
	
	set cmd = server.CreateObject("ADODB.Command")
	cmd.ActiveConnection = oConn  
					
'*** 1,	Insert value	
	cmd.CommandText = "Insert Into Translations(TextData) Values('"& FormValue &"')" 
	cmd.Execute
	if err.number<>0 then
		response.Write "<h2>Entry Failed</h2>"
		oConn.RollBackTrans
		response.End
	end if	
	
'*** 2, Get last inserted record id	
	set rs = server.CreateObject("ADODB.Recordset")  	
	rs.Open "Select IDENT_CURRENT('Translations')", oConn,adOpenForwardOnly,adLockReadOnly
	if err.number<>0 then
		response.Write "<h2>Entry Failed</h2>"
		oConn.RollBackTrans
		response.End
	end if		
	idcode = rs.Fields.item(0)	
	rs.Close	

'*** 3, update a column with the id we just got
	cmd.CommandText = "Update Translations Set IdCode="& idcode &" Where IdTrans="& idcode
	cmd.Execute
	if err.number<>0 then
		response.Write "<h2>Entry Failed</h2>"
		oConn.RollBackTrans
		response.End
	end if	      	
			
	oConn.CommitTrans
		
	set cmd = nothing
	set rs = nothing
	set d = nothing

The above seems to work fine, but I can not be absolutely sure that it will do it's job as a standard insert operation of my application (the above code will be executed in a for loop).
I would appreciate any comments.

Kristen
Test

22859 Posts

Posted - 2005-12-11 : 02:45:01

[code]
CREATE PROCEDURE dbo.Translations_Insert
@TextData TEXT, -- or is this a VARCHAR()??
AS
DECLARE @intID int

BEGIN TRANSACTION
INSERT INTO dbo.Translations(TextData)
VALUES(@TextData)
IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 GOTO Translations_Insert_EXIT
SELECT @intID = scope_identity()

UPDATE dbo.Translations
SET IdCode = @intID
WHERE IdTrans = @intID
IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 GOTO Translations_Insert_EXIT

COMMIT
RETURN 0

Translations_Insert_EXIT:
ROLLBACK
RETURN 1
GO
[/code]
Then

cmd.CommandText = "EXEC dbo.Translations_Insert '" & FormValue & "')"

from your application.

Your VB approach has got three round trips to the server, plus the opportunity to leave a BEGIN TRANSACTION "dangling" if there is an error in your application code between the
oConn.BeginTrans
and the
oConn.RollBackTrans / oConn.CommitTrans

Kristen

dimitrisX
Starting Member

7 Posts

Posted - 2005-12-11 : 12:16:29

thank you very much!

dimitrisX
Starting Member

7 Posts

Posted - 2005-12-11 : 14:59:02

quote:

Then

cmd.CommandText = "EXEC dbo.Translations_Insert '" & FormValue & "')"

from your application.

Your VB approach has got three round trips to the server, plus the opportunity to leave a BEGIN TRANSACTION "dangling" if there is an error in your application code between the
oConn.BeginTrans
and the
oConn.RollBackTrans / oConn.CommitTrans

Ok. But, what if the proc parameter 'FormValue' is an array in a statement like:

for i=0 to ubound(FormValue)
  cmd.CommandText = "EXEC dbo.Translations_Insert '" &  FormValue(i) & "')"
  cmd.Execute
next

In the above case there would be as many round trips to the server as the size of the array.
I don't think it's possible to pass an array to a stored procedure.
Could there be another way?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-12-11 : 16:11:22

These might help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12538
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19565
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13935

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources