how to update this database table

I need some guidance. I have:

<td><%=rs("id")%></br>
<input type="hidden" name="id_<%=intRec%>"
value="<%=rs.Fields("id").Value%>">

<textarea name="gemetria_<%=intRec%>"
id="gemetria_<%=intRec%>">
</textarea>

<select name="insert" id="insert" size="1" style="width:150;">
<option value="passages">passages</option>
<option value="projective link">projective link</option>
<option value="book number linked to book number">book number linked to book number</option>
<option value="book number linked to chapter number">book number linked to chapter number</option>
<option value="book number linked to verse number">book number linked to verse number</option>
<option value="book number linked to number in text">book number linked to number in text</option>
<option value="chapter number linked to book number">chapter number linked to book number </option>
<option value="chapter number linked to book number">chapter number linked to book number</option>
<option value="chapter number linked to chapter number">chapter number linked to chapter number</option>
<option value="verse number linked to verse number">verse number linked to verse number</option>
<option value="book number linked to number in text">book number linked to number in text</option>
<option value="number linked to book">number linked to book</option>
<option value="number linked to chapter">number linked to chapter</option>
<option value="number linked to verse">number linked to verse</option>
<option value="number linked to number">number linked to number</option>
<option value="alphanumeric word">alphanumeric word</option>
<option value="non-alphanumeric word">non-alphanumeric word</option>
<option value="spoke of number in text">spoke of number in text</option>
<option value="word gemetrial passage">word gemetrial passage</option>
</select>
</td>


To each record I have assigned a textarea, a hidden and a select option dropdown. Within the table where updating is made I already have ID numbered. It has to update where the ID = <%=intRec%>.

I have difficulty setting up the response page. This was a model code where changes should be made:


Dim connStr, ans, i, rs, SQL, objConn
Dim TheString, ArrayTemp, NumberOfWords, Word

Dim insert
Dim passages
Dim intRec


insert = Request.Querystring("insert")
gemetria = Request.Querystring("gemetria")

connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Kjv.mdb")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connStr
Set rs = objConn.Execute("SELECT * FROM insertanswer")

If Not rs.EOF Then
Dim gemetria
Dim iCounter
iCounter = 0
Do Until rs.EOF

SQL = "UPDATE insertanswer "
SQL = SQL & "SET passages = '" & gemetria & "'"
SQL = SQL & " WHERE id = id_" & intRec
objConn.Execute(SQL)


I get:
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters

SQL = SQL & " WHERE id = id_" & intRec

Where is that intRec coming from? I see no assignment.
If you wanted it from the form or querystring, you have to get it yourself.

Maybe I'm not clear with you. The ID's are set already in the table, leaving the rest of the fields with blank left to be updated. Let's say I have:

id
1
2
3
4
5
.
.
.

and want to update ID = 3 only. How would I write sql?

No, that was clear.
What isn't clear is where you are getting the id to update.
You have it as a hidden field in the form, but in the action page, you either don't get it or didn't post the part where you get it.
The sql I quoted was fine, but only if that variable exists in the first place. ;)

The id DOES exist in the table. The rest of the fields are blank.

NikkiH is trying to tell you that the variable intRec is never assigned any values. You declare it and then that's it! You don't bother to assign the variable intRec to an ID value from the table in your database or anything.

-Shane

No, no.
Same book, different chapter.

SQL = "UPDATE insertanswer "
SQL = SQL & "SET passages = '" & gemetria & "'"
SQL = SQL & " WHERE id = id_" & intRec

See that?
See the variable called intRec?
It has no value here. Neither does gemetria, coincidentally, I noticed after checking your html.
Nothing. Nada. You never assigned it anything. Therefore, the sql getting executed looks like this:

UPDATE insertanswer
SET passages = ''
WHERE id = id_

See this html?

<input type="hidden" name="id_<%=intRec%>"
value="<%=rs.Fields("id").Value%>">

<textarea name="gemetria_<%=intRec%>"
id="gemetria_<%=intRec%>">

Those are being posted to the server as
id_x=idxValue
gemetria_x=gemetria_xValue

You are not getting your parameters properly, so the sql constructed from them is not what you wanted.

What you wanted was this in the first page
<input type="hidden" name="id"
value="<%=rs.Fields("id").Value%>">

<textarea name="gemetria"
id="gemetria_<%=intRec%>">

then this on the second

id= Request.Querystring("id")
gemetria = Request.Querystring("gemetria")

...

SQL = "UPDATE insertanswer "
SQL = SQL & "SET passages = '" & gemetria & "'"
SQL = SQL & " WHERE id = & id
objConn.Execute(SQL)

I found the error. I used post Method instead of Get. But after adding back parts I got error again:
UPDATE insertanswer SET passages = '' WHERE id = ;

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'id ='.


<form method="get"...

<td><%=rs("id")%></br>
<input type="hidden" name="<%=intRec%>" id="<%=intRec%>" value="<%=rs.Fields("id").Value%>">

<textarea name="Keyword_<%=intRec%>" id="Keyword_<%=intRec%>"></textarea>

<select name="insert" id="insert" size="1" style="width:150;">
<option value="passages">passages</option>
<option value="projective link">projective link</option>
<option value="book number linked to book number">book number linked to book number</option>
<option value="book number linked to chapter number">book number linked to chapter number</option>
<option value="book number linked to verse number">book number linked to verse number</option>
<option value="book number linked to number in text">book number linked to number in text</option>
<option value="chapter number linked to book number">chapter number linked to book number </option>
<option value="chapter number linked to book number">chapter number linked to book number</option>
<option value="chapter number linked to chapter number">chapter number linked to chapter number</option>
<option value="verse number linked to verse number">verse number linked to verse number</option>
<option value="book number linked to number in text">book number linked to number in text</option>
<option value="number linked to book">number linked to book</option>
<option value="number linked to chapter">number linked to chapter</option>
<option value="number linked to verse">number linked to verse</option>
<option value="number linked to number">number linked to number</option>
<option value="alphanumeric word">alphanumeric word</option>
<option value="non-alphanumeric word">non-alphanumeric word</option>
<option value="spoke of number in text">spoke of number in text</option>
<option value="word gemetrial passage">word gemetrial passage</option>
</select>
</td>

I haven't worked on the select yet. I think the problem should be in:

<td><%=rs("id")%></br>
<input type="hidden" name="<%=intRec%>" id="<%=intRec%>" value="<%=rs.Fields("id").Value%>">

<textarea name="Keyword_<%=intRec%>" id="Keyword_<%=intRec%>"></textarea>
and the sql statement:
SQL = "UPDATE insertanswer "
SQL = SQL & "SET passages = '" & request.querystring("Keyword_") & intRec & "'"
SQL = SQL & " WHERE id = " & request.querystring("id_") & intRec & ";"

I have:

SQL = "UPDATE insertanswer "
SQL = SQL & "SET "

SQL = SQL & "passages = """ & request.querystring("Keyword_" & rs.Fields("id").value ) & """"

SQL = SQL & " WHERE [id] = " & request.querystring("id_") & rs.Fields("id").value & ";"

but because of a loop it goes on and on. I only want to update the textareas where there are things written therein.

How should I do it?

@gilgalbiblewhee,
Ok, I think I see what is that you are trying to do. Try something like this:
Form Code:
<td><%=rs("id")%></br>
<input type="hidden" name="id_<%=rs.Fields("id").Value%>"
value="<%=rs.Fields("id").Value%>">

<textarea name="gemetria_<%=rs.Fields("id").Value%>"
id="gemetria_<%=rs.Fields("id").Value%>">
</textarea>

<select name="insert_<%=rs.Fields("id").Value%>" id="insert_<%=rs.Fields("id").Value%>" size="1" style="width:150;">
<option value="passages">passages</option>
<option value="projective link">projective link</option>
<option value="book number linked to book number">book number linked to book number</option>
<option value="book number linked to chapter number">book number linked to chapter number</option>
<option value="book number linked to verse number">book number linked to verse number</option>
<option value="book number linked to number in text">book number linked to number in text</option>
<option value="chapter number linked to book number">chapter number linked to book number </option>
<option value="chapter number linked to book number">chapter number linked to book number</option>
<option value="chapter number linked to chapter number">chapter number linked to chapter number</option>
<option value="verse number linked to verse number">verse number linked to verse number</option>
<option value="book number linked to number in text">book number linked to number in text</option>
<option value="number linked to book">number linked to book</option>
<option value="number linked to chapter">number linked to chapter</option>
<option value="number linked to verse">number linked to verse</option>
<option value="number linked to number">number linked to number</option>
<option value="alphanumeric word">alphanumeric word</option>
<option value="non-alphanumeric word">non-alphanumeric word</option>
<option value="spoke of number in text">spoke of number in text</option>
<option value="word gemetrial passage">word gemetrial passage</option>
</select>
</td>
ASP Code:
Dim connStr, ans, i, rs, SQL, objConn
Dim TheString, ArrayTemp, NumberOfWords, Word

Dim insert
Dim passages
Dim intRec

connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Kjv.mdb")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connStr
Set rs = objConn.Execute("SELECT * FROM insertanswer")

If Not rs.EOF Then
Do Until rs.EOF
SQL = "UPDATE insertanswer " & _
"SET passages = '" & Replace(Request("gemetria_" & rs.Fields("id").Value), "'", "''") & "'" & _
" WHERE id = " & Request("id_" & rs.Fields("id").Value)
objConn.Execute(SQL)