Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: Add new record in SQL Server database

    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

    <!-- #include file = "sha_BankConnection.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>

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721

    Re: Add new record in SQL Server database

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

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

    rec.MoveNext

    loop
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    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

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    Sep 2003
    Posts
    5
    Thanks, Program is now working

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •