Very Computer

I'm stumped.

This doesn't work:

UPDATE PEOPLE SET LNAME='Doe', FNAME='John', MI='P',
STUDENT_ID='123123123', EMPLOYEE_ID='123456789' WHERE HID=100008

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E31)
Timeout expired

This does work:

SET NOCOUNT ON; INSERT INTO PEOPLE(LNAME, FNAME, MI, STUDENT_ID,
EMPLOYEE_ID) VALUES('Smith', 'John', 'R', '432432432', '654654654');

Win2k server, sql 2000, iis5

====================

Set cnxn = Server.CreateObject("ADODB.Connection")
    ' open connection
    Set Cnxn = Server.CreateObject("ADODB.Connection")
    strCnxn = "Provider='sqloledb';Data Source=<myIP>;" & _
            "Integrated Security='SSPI';Initial Catalog='DB'"

Cnxn.Open strCnxn

' create and open second Recordset using recordset - open
Set rs1 = Server.CreateObject("ADODB.Recordset")

if(len(hid) < 2) then hid=0
strSQL = "SELECT * FROM PEOPLE WHERE HID=" & hid
rs1.Open strSQL, Cnxn, adOpenStatic, adLockPessimistic, adCmdText
if(rs1.eof AND rs1.bof) then
        strSQL = "SET NOCOUNT ON; " & _
                "INSERT INTO " & _
                "PEOPLE(LNAME, FNAME, MI, STUDENT_ID, EMPLOYEE_ID) " & _
                "VALUES(" & lname & ", " & fname & ", " & mi & ", " & _
                student_id &  ", " & employee_id & "); " & _

        'Set rs1 = Cnxn.Execute(strSQL)
        'hid = rs1.Fields("NewID").value
else
        strSQL = "UPDATE PEOPLE " & _
                        "SET LNAME=" & lname & ", FNAME=" & fname & ", MI=" & mi & _
                        ", STUDENT_ID=" & student_id & ", EMPLOYEE_ID=" & employee_id & " "
& _
                        "WHERE HID=" & hid
        Set rs1 = Cnxn.Execute(strSQL)
end if

rs1.close
cnxn.close

It could be that there is no useful index on the People table, and the
lookup to find the correct row to update takes longer than 15 secs ( default
ADO timeout.)
An insert has a trivial plan that doesn't require lookups... Check to see if
there is an index on HID.

--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), C*te, NC
www.computeredservices.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org