Add new record in SQL Server database

Shaily · #1 (**permalink**) 09-10-03, 19:06

Hi,

I am getting an error:

Error Type:
Active Server Pages, ASP 0113 (0x80004005)
The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.

At the same time whenever I try to open SQL SERVER I am getting the message: SQL SERVER IS NOT KNOWN TO BE RUNNING. ARE YOU SURE YOU WANT TO CONNECT
I have installed SQL SERVER DESKTOP edition on Windows XP

I wrote this script in ASP to ADD new record to SQL SERVER DATABASE

FileName: sha_BankConnection.asp

<html>
<body>
<%
dim str
str = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Bank;Data Source=SHAILY"
%>
</body>
</html>

FileName: AddnewRecord.asp


<html>
<body>
<%
dim conn,rec, ID

set rec = server.createobject("ADODB.Recordset")
rec.open "select * from Account", str

do while not rec.EOF
response.write rec("AccountID")
response.write "<br>"
loop

ID = 1234-5678
rec.MoveLast
rec.addnew

rec("AccountID") = ID
rec("HolderName") = "Sally S. Bhat"
rec.update
rec.close

rec.open "select * from Account where AccountID = " & ID ,str

if rec.EOF then
response.write "New record not found"
else
response.write "New record is added"
end if

rec.close
set rec = nothing
%>
</body>
</html>

Seppuku · #2 (**permalink**) 09-11-03, 12:36

You need to move the cursor in the recordset to the next record for each iteration of your loop.

Quote:

Originally posted by Shaily
do while not rec.EOF
response.write rec("AccountID")
response.write "<br>"

rec.MoveNext

loop

Shaily · #3 (**permalink**) 09-11-03, 15:25

Thanks, I wrote rec.MoveNext

and I deleted rec.MoveLast because with rec.MoveLast I was getting the error:
Microsoft OLE DB Provider for SQL Server (0x80040E24)
Rowset does not support fetching backward.

But now again I am getting an error
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

In the SQL Server database I enabled the permission to INSERT, UPDATE, SELECT for Account table

Seppuku · #4 (**permalink**) 09-11-03, 15:40

Change your "rec.Open" statement to be:

Code:

rec.Open "select * from Account", str, adOpenKeySet, adLockOptimistic, adCmdText

If you're not using the ADO VBS Constants, you'll need to put these before your "Open" statement

Code:

Const adOpenKeySet = 1
Const adLockOptimisitc = 3
Const adCmdText = &H0001

adOpenKeySet opens the recordset to be updatable. The default is "adOpenStatic" which is a disconnected recordset. Records added/modified/deleted to the DB at the same time by another user/process are not accessible in this cursor type. Use "adOpenDynamic" with a value of "2" if that is needed.

adLockOptimistic locks the record only when it's being updated.

adCmdText provides a hint to the Open method that the string being executed is SQL

Shaily · #5 (**permalink**) 09-11-03, 18:30

Thanks, Program is now working

Internet Services

Web Development

Digital Marketing

Consumer Tech