updating rows

[derwent]

I have a quick script that updates some columns of my MSSQL database, running it on my machine updates the fields perfectly, no problems.

However if I alter my connection script for our webserver and run the same script, the script times out and in task manager, a new entry appears which is

dllhost.exe
username: IWAM_mymachinename

running at 99% cpu until I kill the process.

Has anyone seen this before? It works fine on my local but as soon as I try it with the webserver this happens.

thanks

 

[DotNetGnat]

how many rows are you trying to update?? increase the script execution time on server?

what is the actual error you are getting...

-DNG

 

[derwent]

It is just a simple rs.update on four fields, the databases on my local SQL Server and the webserver are identical.

After about 2 mins of nowt happening it just says timed out

 

[derwent]

it should take a matter of seconds, if that.

 

[DotNetGnat]

increase the execution time of scripts and see what error it throws...

-DNG

 

[derwent]

what time would you suggest?

 

[Sheco]

Does it do other tasks correctly?

I mean is it just this UPDATE that has problems? Is it all OK when you do a SELECT or INSERT or DELETE or DROP or stored proc or whatever?

Perhaps there is a permissions problem.

 

[derwent]

yeah, all other queries seem to work no problems.

I have been using this script for several weeks to update the DB with no problems, but have had this problem now for a few days.

 

[Sheco]

Are you fetching an ado recordset and then changing the field values and updating it?

Or do you have an SQL UPDATE statment that you execute using your connection object like this:
adoCN.Execute "UPDATE MyTable SET MyField = 'Foo' WHERE KeyField = 45643

 

[derwent]

SQL = "SELECT * FROM table WHERE id = " & request.form("id")
set rs = server.CreateObject("ADODB.RECORDSET")
rs.open SQL,objConn,3,3



rs.Fields("field1")=trim(request.form("field1"))
rs.Fields("field2")=trim(request.form("field2"))
rs.Fields("field3")=trim(request.form("field3"))
rs.Fields("field4")=trim(request.form("field4"))


rs.Update

rs.Close


response.redirect "edit.asp?id=12"

Why would it suppenly stop working on the webserver?

 

[Sheco]

Well you could pretty much rule out permissions by checking to see if this would run:

SQL = "UPDATE table SET " _
    & "  field1 = " & trim(request.form("field1")) _
    & "  field2 = " & trim(request.form("field2")) _
    & "  field3 = " & trim(request.form("field3")) _
    & "  field4 = " & trim(request.form("field4")) _
    & " WHERE id = & request.form("id")

objConn.Execute SQL

 

[DotNetGnat]

could you also make sure that you are correctly retrieving the id value using request.form("id")...

-DNG

 

[derwent]

I am retrieving the id ok

Sheco, neither the webserver or my local were happy with your code, so I tried entering straight into the query analyzer

UPDATE    table
SET              field1= "hello"
WHERE     id = 21

however on both I received the error when executing
"invalid column name 'hello'.

 

[DotNetGnat]

it should be:

UPDATE    table
SET field1= 'hello'
WHERE     id = 21

Sheco, just provided you the sample code...you need to change it to adapt it to your table and field names...

-DNG

 

[derwent]

it works if I make a simple update using query analyzer, however I made the simple admin because the update contains several thousand characters.

Tried again this morning, went fine on local but as soon as I change the database to the webserver it hangs.

 

[Sheco]

1. Did you try the simple update from the web server?

2. When you run it from the web server, does it connect to the same instance of SQL Server that it does when you run it from your machine?

 

[derwent]

1. yes, worked fine although it took a while to execute, whereas on local it was done in a flash (webserver was always faster than my local)

2. I`m not sure what you mean by this.

I use the same code, two copies of it, one on my local and one on webserver. SQL Server is same on my local and webserver also

 

[DotNetGnat]

can you show us your connection strings...and the asp page code template you are using...

-DNG

 

[derwent]

sure

Dim cs, objConn
Set objConn=Server.CreateObject("ADODB.Connection")
cs = "DRIVER={SQL Server}; SERVER=(local); UID=*removed*; PWD=*removed*; DATABASE=students; REGIONAL=NO'"
objConn.open cs

and

SQL = "SELECT * FROM t_students WHERE id = " & request.form("id")
set rs = server.CreateObject("ADODB.RECORDSET")
rs.open SQL,objConn,3,3


rs.Fields("inventory")=trim(request.form("inventory"))
rs.Fields("parking")=trim(request.form("parking"))
rs.Fields("safety")=trim(request.form("safety"))
rs.Fields("security")=trim(request.form("security"))
rs.Fields("tvlicence")=trim(request.form("tvlicence"))
rs.Fields("counciltax")=trim(request.form("counciltax"))
rs.Fields("officehours")=trim(request.form("officehours"))
rs.Fields("orientation")=trim(request.form("orientation"))
rs.Fields("telephone")=trim(request.form("telephone"))
rs.Fields("outabout")=trim(request.form("outabout"))

rs.Update

rs.Close




'SQL = "UPDATE t_students SET " _
'    & "  inventory = " & trim(request.form("inventory")) _
'    & "  parking = " & trim(request.form("parking")) _
'    & "  safety = " & trim(request.form("safety")) _
'    & "  security = " & trim(request.form("security")) _
'				& "  tvlicence = " & trim(request.form("tvlicence")) _
'    & "  counciltax = " & trim(request.form("counciltax")) _
'				& "  officehours = " & trim(request.form("officehours")) _
'    & "  orientation = " & trim(request.form("orientation")) _
	'			& "  telephone = " & trim(request.form("telephone")) _
 '   & "  outabout = " & trim(request.form("outabout")) _
'    & " WHERE id = " & request.form("id")

'objConn.Execute SQL


response.redirect "students.asp?id=12"

This works fine when running from local, but stopped a few days ago when running from webserver. Also have this to input data

<form action='studentedit.asp' method=post>
<!--#include virtual="/include/dbconnstudents.asp"-->
<%
SQL = "SELECT * FROM t_students where id = " & request.querystring("id")
set rs = server.CreateObject("ADODB.RECORDSET")
rs.open SQL,objConn,3,3
%>
title: <%=rs("title")%><br />
<input type='hidden' name='id' value='<%=request.querystring("id")%>'>
inventory1: <textarea name="inventory" cols="100" rows="10"><%=rs("inventory")%></textarea><br />
parking: <textarea name="parking" cols="100" rows="10"><%=rs("parking")%></textarea><br />
safety: <textarea name="safety" cols="100" rows="10"><%=rs("safety")%></textarea><br />
security: <textarea name="security" cols="100" rows="10"><%=rs("security")%></textarea><br />
tvlicence: <textarea name="tvlicence" cols="100" rows="10"><%=rs("tvlicence")%></textarea><br />
counciltax: <textarea name="counciltax" cols="100" rows="10"><%=rs("counciltax")%></textarea><br />
officehours: <textarea name="officehours" cols="100" rows="10"><%=rs("officehours")%></textarea><br />
orientation: <textarea name="orientation" cols="100" rows="10"><%=rs("orientation")%></textarea><br />
telephone: <textarea name="telephone" cols="100" rows="10"><%=rs("telephone")%></textarea><br />
outabout: <textarea name="outabout" cols="100" rows="10"><%=rs("outabout")%></textarea><br />

<input type=submit value=submit>
</form>

 

[Sheco]

cs = "DRIVER={SQL Server}; SERVER=[red](local)[/red]; UID=*removed*;

I assume you use the IP address of you development box instead of (local) in the connection string on the web server?