how to update this database table

[gilgalbiblewheel]

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

[exam]

Sorry I don't know anything about ASP or Microsoft databases, but a quick search in Google for "Microsoft JET Database Engine error '80040e10'" turned up some interesting pages that might help you.

[relixx]

Here's your problem:

"gemetria"

First of all, you declare it as a passive variable (ie, you didn't dim it) by simply stating "gemetria = Request.Querystring("gemetria")"

Then, later on you go and state "Dim gemetria" -.-;. By doing this, you've erased the original variable called "gemetria" and created a new variable with the same name, when you should have dimmed it initially and left it be. Then, you're next mistake is that you didn't go assign any value to it, so it's sitting there with a Null value.

To fix this problem, changed the code:


Dim insert, gemetria
Dim passages
Dim intRec


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


and remove the line


Dim gemetria


from the if statement

[gilgalbiblewheel]

ok I have this now:
Dim connStr, ans, i, rs, SQL, objConn
Dim TheString, ArrayTemp, NumberOfWords, Word

Dim iCounter
Dim insert, gemetria
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
iCounter = 0
intRec = rs("id")
Do Until rs.EOF

SQL = "UPDATE insertanswer "
SQL = SQL & "SET passages = 'gemetria_" & intRec & "'"
SQL = SQL & " WHERE [id] = id_" & intRec

response.write sql
objConn.Execute(SQL)

With this result:
UPDATE insertanswer SET passages = 'gemetria_1' WHERE [id] = id_1

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters

[gilgalbiblewheel]

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?

[gilgalbiblewheel]

I have a list of dropdowns which are fieldnames. "Passages" was one of them. How do I include that in the sql? Do I use IFs and THENs? I'm not sure how that works.
<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>

THe reponse page:
Dim intRec
Dim Keyword
Dim insert
Do Until rs.EOF

Keyword = request.querystring("Keyword_" & rs.Fields("id").value )
insert = Request.Querystring("insert")
if(Keyword<>"") then
SQL = "UPDATE insertanswer "
SQL = SQL & "SET "
If insert <>"" then
SQL = SQL & "[" & insert & "] = """ & Keyword & """"
End if

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

[relixx]

Ok, give me a bit of time, i'll try to help when i get the chance.

<edit>you've lost me, you're jumping around the whole time</edit>

[relixx]

ok I have this now:
Dim connStr, ans, i, rs, SQL, objConn
Dim TheString, ArrayTemp, NumberOfWords, Word

Dim iCounter
Dim insert, gemetria
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
iCounter = 0
intRec = rs("id")
Do Until rs.EOF

SQL = "UPDATE insertanswer "
SQL = SQL & "SET passages = 'gemetria_" & intRec & "'"
SQL = SQL & " WHERE [id] = id_" & intRec

response.write sql
objConn.Execute(SQL)



Ok, if you're still getting that error, it means that it's expecting data but isn't getting it. Check for things like the correct spelling of variables, and of the 'name' and 'value' attributes in the form, you could have mispelled something there. Also check for correct spelling of column names.

[relixx]

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?

I'm assuming you have an infinity loop on your hands. Lol, I remember some of those before, one of them crashed my testing PC :o Stop the infinity loop by adding a line (or lines) of code that allow the loop condition to be met. In this case you want it to loop until the 'EOF' message is returned from the database. Thus, you will want to add rs.movenext just at the end, eg

while not rs.eof
response.write "Blah <br>"
rs.movenext
wend


On a side note, you deserve a slap for putting unsanitised code directly into a SQL query


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

request.querystring("id_") & rs.Fields("id").value & ";"


I mean, you're opening yourself up for a SQL injection, which is a type of hacker attack. Always store the data inside a variable, always sanitise (ie, validate) the data.

[relixx]

I have a list of dropdowns which are fieldnames. "Passages" was one of them. How do I include that in the sql? Do I use IFs and THENs? I'm not sure how that works.
<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>

THe reponse page:
Dim intRec
Dim Keyword
Dim insert
Do Until rs.EOF

Keyword = request.querystring("Keyword_" & rs.Fields("id").value )
insert = Request.Querystring("insert")
if(Keyword<>"") then
SQL = "UPDATE insertanswer "
SQL = SQL & "SET "
If insert <>"" then
SQL = SQL & "[" & insert & "] = """ & Keyword & """"
End if

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


What? Why would you want to use if statements for that????? Request.querystring or request.form should get the data from the form.

[relixx]

hang on a minute....

are you submitting the info from the form using GET or POST? Ie, does your form say method="post" or method="get"?

The reason I'm asking is that if you are sending the information across using GET then you have to use request.querystring to retrieve the information (as it sends the information across using a querystring), and if you are sending the information across using POST then you have to use request.form. If you use the wrong one, ASP will look in the wrong place for the data it wants an thus you'll get Null values

This could be the root problem behind your "No value given for one or more required parameters" error.