Classic ASP and MS Access Batch Update

Question

I am using the following code to update an Access Database with Classic Asp:

<%@ Language=VBScript %>
<% Option Explicit %>
<%
Response.Buffer = True
'First, we need to get the total number of items that could be updated
Dim iCount
iCount = Request("Count")

'We need to obtain each cost and ID
Dim strstudent, strcourse, strgrade, strcomments

'We will also need to build a SQL statement
Dim strSQL

Dim conn


set conn=server.CreateObject("ADODB.connection")
conn.ConnectionString="provider=Microsoft.jet.OLEDB.4.0;data source=C:\db\agsystem.mdb"
conn.Open
'Now, we want to loop through each form element
Dim iLoop
For iLoop = 0 to iCount
'student data
strstudent = Request(iLoop & ".Student")
'course data
strcourse = Request(iLoop & ".course")
'grade
if isNull(Request(iLoop & ".grade")) or Request(iLoop & ".grade")="" then
strgrade="null"
else
strgrade= Request(iLoop & ".grade")
end if
if isNull(Request(iLoop & ".comments"))  or Request(iLoop & ".comments")="" then
strcomments=null
else
strcomments=Request(iLoop & ".comments")
end if
strSQL = "UPDATE testing SET semester2 = " & strgrade & ", commentss=" & "'" & strcomments & "'" & "  WHERE newstudentid = " &"'"& strstudent&"'" & " and Courseid = " & "'"& strcourse & "'"
conn.Execute strSQL
Next

conn.Close
Set conn = Nothing

Response.Redirect "protected.asp"
%>

The problem is that when tested in the server it updates without any issues. But when access from a wireless network it won't update.

The target table to update has about 27,000 records I need to know what I'm doing wrong or if there is another approach.

Sorry, meant to say that when updated from the local server it updates without any problem. But when accessed from a WAN it does not update — Elmer Tillett, Nov 01 '13 at 00:13
Actually only certain records are updated even from localhost. — Elmer Tillett, Nov 01 '13 at 15:01

score 0 · Answer 1 · answered Nov 01 '13 at 15:09

I found the error after carefully analyzing the situation.

Records in primary key that have spaces for example '2 OR 13' will not update. But records without spaces in primary key like '2CEN13' updates perfectly. I did not had time to solve it in my asp code, so i edited all records with spaces and that solve the problem.

Classic ASP and MS Access Batch Update

1 Answers1