ASP/SQL Server Problem, INSERT Works, UPDATE Doesn't

ASP/SQL Server Problem, INSERT Works, UPDATE Doesn't

Post by Jim Baye » Wed, 03 Mar 2004 06:10:02



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

 
 
 

ASP/SQL Server Problem, INSERT Works, UPDATE Doesn't

Post by Wayne Snyde » Wed, 03 Mar 2004 06:42:15


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


> 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


 
 
 

1. Porting ASP code from access to SQL Server - DSN doesn't work

We created a system DSN for an Access database and it works fine. We used
ASP code with embedded SQL statements. We ported the application to SQL
server and now get the following error:  

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed

/TechSsecurity.inc, line 60

Any ideas on how to make it work would be greatly appreciated.

-Ben and Jennifer

2. vb3 + odbc + SQL 6.5 = ODBC Timeout

3. SQL Server, Visual InterDev and ADO/ASP: one login works, another doesn't

4. database machines

5. ASp's rs.Filter doesn't work for SQL

6. Delete a field/column from a table???

7. insert doesn't work from subscriber in immediate updating in SQL2K

8. Passing a 'variable' Variable

9. SQL Mail doesn't work but SQLAgentMail works on prod server

10. ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES DOESN'T WORK

11. sql enterprise manger's rigester server doesn't work in a nt workstation

12. Registering server doesn't work with SQL Server 7.0

13. msolap.asp - doesn't want to work