PDA

View Full Version : Update a particular row from a query

otherjugdish
06-02-2005, 06:50 PM
I am using FrontPage2003 and have Database Results query that shows a list of names and scores, as follows:
id...name...score...newscore

The newscore field is where the user can enter an updated score. When the field is exited, the form submits to 'score_upd.asp and the correct record is updated.

The problem is that the 'id' being transferred to the update page looks like: '23, 43, 45, 32' (a list of each id from the form). Is there any way of getting only the 'id' from the row that is being changed??

Thanks.
miranda
06-02-2005, 07:47 PM
I take you display the results in a table with each row allowing the user to update a score. Each row would also have a field with the id in it. This can be hidden if you don't want to show it.
When you do a Request.Form("id") you get an array like so id(23, 43, 45, 32)
the same for Request.Form("score") score(4-2,,2-0,0-0)
even though there is no score in for the second row there would be a placeholder for it so all you have to do is loop through the arrays and update each row


Dim scores, i, idList, SQL, objConn
idList = Request.Form("id")
scores = Request.Form("score")
Set ObjConn = Server.CreateObject("ADODB.Connection")
objConn.Open(your dsn or dsnless connection string here)
For i = 0 to uBound(idList)
SQL = "UPDATE myTable SET score = '" & scores(i) & "' WHERE id = " & idList(i) & ""
objConn.Execute(SQL)
Next
otherjugdish
06-03-2005, 04:02 PM
Thank you very much for the for...next, it is getting me ever so close. However, in order for it to work, I had to split the array into the variable (id). Now, it updates the first row of the table (on the webpage) correctly, but not the others. For instance:

id...name...score...newscore
3...Joe...115...[117]
7...Sue...111...[empty]
9...Art...99...[empty]
this updates correctly, however:

3...Joe...115...[empty]
7...Sue...111...[117]
9...Art...99...[empty]
this will not update, it says syntax error in update query.

I have checked all of the numbers that are being entered into the SQL statement and nothing changes when they are converted to Int or Lng. Plus, it works correctly on the data from the first record.

Any ideas?
miranda
06-03-2005, 09:14 PM
i made a test datatable and had to tweak it a bit here you go this should work for you. In particular you want to look at the updateScores sub routine. That is the code you can copy and paste

<%@ Language=VBScript %>
<%Option Explicit
Dim oConn, oRs, sSql
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\Inetpub\Database\test.mdb")
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="generator" content="Microsoft Visual Studio 6.0">
<title></title>
</head>
<body>
<FORM action="<%=Request.ServerVariables("url")%>" method="POST" id=form1 name=form1>
<%

If Request.Form("submit") = "Submit" Then
DisplayTable()
Else
UpdateScores()
DisplayTable()
End If
Cleanup()

Sub UpdateScores()
Dim i, idList, Score
idList = Split(Request.Form("id"),",")
score = Split(Request.Form("newScore"), ",")

For i = 0 to UBound(idList)
Dim theScore : theScore = score(i)
theScore = Trim(theScore)
If Len(theScore) <> 0 Then
sSql = "UPDATE scores SET score = '" & score(i) & "' WHERE id = " & idList(i) & ""
oConn.Execute(sSql)
End If
Next
End Sub

Sub DisplayTable()
sSql = "SELECT * FROM scores"
Set oRs = oConn.Execute(sSql)
%>
<br>
<TABLE WIDTH=75% BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD>ID</TD>
<TD>Name</TD>
<TD>Score</TD>
<TD>New Score</TD>
</TR>
<% Do until oRs.EOF %>
<TR>
<TD><INPUT type="text" readonly size=3 name="id" value="<%=oRs("id")%>"></TD>
<TD><%=oRs("name")%></TD>
<TD><%=oRs("score")%></TD>
<TD><INPUT type="text" id=text1 name="newScore"></TD>
</TR>
<% oRs.MoveNext
Loop
%>
<tr>
<td colspan=4 align=center>
<INPUT type="submit" value="Submit" id=submit1 name=submit1>
</td>
</tr>
</TABLE>
<%
End Sub

Sub Cleanup()
Set oRs = nothing
oConn.Close
Set oConn = Nothing
End Sub
%>
</FORM>
</body>
</html>