PDA

Click to See Complete Forum and Search --> : Limit using ADODB.Recordset update

il_betto
05-08-2009, 04:16 AM
Hi to All !!!

I have a big problem :mad:

I use Access 2000 and i have created a table with 140 fields
I use a page called index.asp in which a person can choose different values in different menus and when all are selected the employ can submit the Form and these values are put in the DB.
This is OK for fields in the DB less than 100, when I had to increase the number of the fields in the DB compared the message:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][Driver ODBC Microsoft Access] Query much complex.
aggiorna.asp, line 120

I read in the Microsoft page that it' s a limit of ADODB.Recordset: is there some way to solve it maintaing the code ??

- I would like to maintain, if it' s possible, the code that i have created in these monthsbecause it cost very much effort, but if there isn' t a way to solve this problem I have to create an UPDATE classic ??
- I used response.buffer = true but with no result

Thanks a lot for All Help in advance !!!!


This is index.asp:

<%@ Language=VBScript %>
<% Option Explicit
Dim check, nr, objConn, objRS, objRS2, rec, strSQL
'
check = 0
nr = 0
rec = 1
'
if trim(request("rec"))<>"" then rec = Request("rec")
%>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>SSD</title>
<Script Language="JavaScript">
function notify(n) {
if (n==0) {
var answer = confirm("Salvare le modifiche?")
if (answer) {
document.FrontPage_Form.action = "aggiorna.asp"
document.FrontPage_Form.submit();
return true; } }
}
</script>
</head>

<body>
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=ssd09.dsn"
objConn.Open
'
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open "t_check", objConn, , 3, 2
Do While Not objRS2.EOF
If (objRS2("Numero") = Cint(rec)) Then
strSQL = "SELECT * FROM t_check WHERE Numero = " & rec
Set objRS = objConn.Execute(strSQL)
check = check + 1
End If
objRS2.MoveNext
nr = nr + 1
Loop
If (check = 0) Then
rec = 1
strSQL = "SELECT * FROM t_check WHERE Numero = " & rec
Set objRS = objConn.Execute(strSQL)
End If
'
%>

<form method="POST" webbot-onSubmit language="JavaScript" name="FrontPage_Form">
<table>
<td width="104" align="center" height="24">
<p style="margin-top: 10px">
<select size="1" name="arrivo_camp" id="arrivo_camp" style="font-family: Arial; font-size: 10 pt; width: 80">
<option selected><%=objRS("Arrivo_Camp")%></option>
<option>184</option>
<option>190</option>
<option>P291</option>
<option>P294</option>
</select></td>
....................
<input type="submit" name="salva" value="Salva" onClick="notify(0)">
.......................
</table></form>
<%
objRS.Close
objRS2.Close
objConn.Close
Set objConn = Nothing
%>

And this is the code of aggiorna.asp:

<%@ Language=VBScript %>
<%
Option Explicit
Dim objConn, objRS, rec
'
rec = Cint(Request.Form("rec_mod"))
'
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=ssd09.dsn"
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "t_check", objConn, , 3, 2
'
Do While Not objRS.EOF
If (objRS("Numero")=rec) Then
objRS("Arrivo_Camp") = Request.Form("arrivo_camp")
objRS("Arrivo_Proc") = Request.Form("arrivo_proc")
...... I have more than 100 of these fields !!! <----
'
objRS.Update
'
End If
objRS.MoveNext
Loop
'
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

<HTML>
<BODY> ...... ... </BODY>
</HTML>
Kuriyama
05-08-2009, 10:03 AM
If you have more than 100 columns in your database table, then your database design is poor.

The cheap and dirty way would be to create another table to store the additional data.

The right way to handle this would be to normalize that table.
il_betto
05-12-2009, 03:29 AM
In the page index.asp I made no changes, but in the page aggiorna.asp I wrote this code that runs perfectly only for 127 fields of my table "t_check" of Access 2000:

<%@ Language=VBScript %>
<%
Option Explicit
Dim objConn, objRS, rec, sql
'
rec = Cint(Request.Form("rec_mod"))
'
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=ssd09.dsn"
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "t_check", objConn, , 3, 2
'
Do While Not objRS.EOF
If (objRS("Numero")=rec) Then
sql = "UPDATE t_check SET Camp='"& Request.Form("camp") &"', " & _
"Proc='"& Request.Form("proc") &"', " & _
"Rep='"& Request.Form("rep") &"', " & _
.........
"Vel='"& Request.Form("vel") &"' WHERE Numero =" & rec & ""
objConn.Execute(sql)
'
End If
objRS.MoveNext
Loop
'
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

<HTML>
<BODY>
........
</BODY>
</HTML>

My problem is that the fields in the table are 131 and only 4 reamins out of this kind of UPDATE !!!
Why is there this limit ??
The error says "Too many fields defined"

In which way is possible to solve this problem ??
I have to create a sql2?? If yes, in which way ??

Thanks a lot in advance !!!
il_betto
05-12-2009, 09:42 AM
Hi to All,
I have finally solved the problem in this way; if anybody has other possible solutions I pay always attention :)

<%@ Language=VBScript %>
<%
Option Explicit
Dim objConn, objRS, rec, sql, sql2
'
rec = Cint(Request.Form("rec_mod"))
'
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=ssd09.dsn"
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "t_check", objConn, , 3, 2
'
Do While Not objRS.EOF
If (objRS("Numero")=rec) Then
sql = "UPDATE t_check SET Camp='"& Request.Form("camp") &"', " & _
"Proc='"& Request.Form("aesi_proc") &"', " & _
......
"Vel='"& Request.Form("vel") &"' WHERE Numero =" & rec & ""
objConn.Execute(sql)
sql2 = "UPDATE t_check SET Ore_P='"& Request.Form("ore_p") &"', " & _
"Ore_S='"& Request.Form("ore_s") &"' WHERE Numero =" & rec & ""
objConn.Execute(sql2)
'
End If
objRS.MoveNext
Loop
'
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

<HTML>
<BODY>
....</BODY>
</HTML>

:)