Add new record in SQL Server database

bhatnagar_shaily · September 10th, 2003, 05:57 PM

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 RUNNING. ARE YOU SURE YOU WANT TO CONNECT

I wrote this script in ASP to ADD new record in 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>

Yehuda · September 17th, 2003, 10:48 AM

The first error reflects that the script is taking a long time to process. IIS has a script time out processing that cuts off ASPs if they are taking to long. This can be expanded either programatically from the response object or as a whole in IIS (the default I think is 90 seconds).

The secod error appears to be the main cause. You appear to have a connection issue. Here are some things to check:
1. user name and password (are they correct. Try to log in manually through Enterprise mnager or query analyzer).
2. Is the database running and online.
3. Is ado 2.6 (I think this installs the sqloleddb.1 drivers) or higher installed on the server processing the request.
4. Is it taking a long time to connect and the script limit is cutting you off.

Yehuda

DaveGerard · September 24th, 2003, 01:15 AM

First of all the "SQL SERVER IS NOT RUNNING. ARE YOU SURE YOU WANT TO CONNECT" message is normal. It happens when you try to open the MMC doesn't it? Happens to me all the time on versions of SQL 7.0.

Try this...

FileName: sha_BankConnection.inc
<%
set db = server.createobject("adodb.connection")
db.connectionString = "provider=sqloledb.1; user id=MyUserId; password=MyPassword; data source=MyServer; initial catalog=MyDatabase"
db.mode=3
db.cursorlocation=3
db.open
%>

FileName: AddNewRecord.asp

<%
    Value1 = "1234-5678"
    Value2 = "Sally S. Bhat"
' (Remember the quotes for the string values.)

    set rs = server.createobject("adodb.recordset")
    sql = "select * from account"
    rs.open sql, db, 3, 3
    rs.addnew
    rs("AccountId") = Value1
    rs("HolderName") = Value2
    rs.update
%>

You don't even need the HTML unless you plan on displaying something as the record is being processed.

DaveGerard · September 24th, 2003, 01:16 AM

By the way. Let me know how that works for you. I'm curious.