How to set SQL query result to a variable? 1

[acewilli]

I need to do the following but I'm not sure how to set the results of the SQL query to a variable that I can use?

strtotal = "Select cast(sum(npoints) as decimal) from team"
Set rsTotal = Server.CreateObject("ADODB.Recordset")
rsTotal.open strtotal, db
db.Execute strtotal

Thanks in advance!

 

[PHV]

I guess you want to use rsTotal.Fields(0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[acewilli]

When I use rsTotal.Fields(0) it returns 0? I am at a total loss on this one as I just need the number from the SUM sql query. I would think it wouldn't be this hard but I can't find anything about this particular problem anywhere.

I sure appreciate you replying back to me as I have been waiting since last night for someone to answer me back. Thanks again for anymore help you can give me. Below is what I've been trying recently.

<center>
<FORM>
<INPUT TYPE="BUTTON" VALUE="Home" ONCLICK="window.location.href='../cssopt/Home.asp'"> 
</FORM>
</center>
<!-- #includes file="includes/dbconn.inc" -->
<!-- #includes file="includes/dbconn2.inc" -->
<hr>
<%
	If Request.QueryString("s") = "" Or Request.QueryString("s") = "sn" Then
		sortBy = "member"
	Elseif Request.QueryString("s") = "pp" Then
		sortBy = "points"
	Elseif Request.QueryString("s") = "np" Then
		sortBy = "npoints"
	Elseif Request.QueryString("s") = "tp" Then
		sortBy = "tpoints"
	End If
	
	strSQL = "Select * FROM Team ORDER BY "& sortBy
	Set rsStatus = Server.CreateObject("ADODB.Recordset")
	rsStatus.open strSQL, db

	If Request.Form("submit") = "Update" Then
		While not rsStatus.eof
			sID = rsStatus("ID")
			smember= Replace(Trim(Request.Form("txtmember"& sID)),"'","''")
			spoints= Replace(Trim(Request.Form("txtpoints"& sID)),"'","''")
			snpoints= Replace(Trim(Request.Form("txtnpoints"& sID)),"'","''")
			stpoints= Replace(Trim(Request.Form("txttpoints"& sID)),"'","''")
			smopoints= Replace(Trim(Request.Form("txtmopoints"& sID)),"'","''")
			syrpoints= Replace(Trim(Request.Form("txtyrpoints"& sID)),"'","''")

strsum = "Select sum(npoints) as strtotal from team"
Set rsTotal = Server.CreateObject("ADODB.Recordset")
rsTotal.open strsum, db
rsTotal("strtotal")

 				If sID = "1" then
				response.write(strtotal)
				syrpoints = snpoints + smopoints
				smopoints = snpoints
				stpoints = spoints - snpoints
				strSQL = "UPDATE Team SET yrpoints='"& syrpoints &"', mopoints='"& smopoints &"', points='"& spoints &"', npoints='"& snpoints &"', tpoints='"& stpoints &"' WHERE ID='1'"
				db.Execute strSQL
			else
				syrpoints = snpoints + smopoints
				smopoints = snpoints
				strSQL = "UPDATE Team SET yrpoints='"& syrpoints &"', mopoints='"& smopoints &"', npoints='"& snpoints &"' WHERE ID="& sID
				db.Execute strSQL
			end if
			strSQL = "UPDATE Team Set npoints='0', tpoints='0' WHERE ID="& sID
			db.Execute strSQL
			rsStatus.movenext
		Wend
	rsStatus.movefirst
	End If
%>
<style>
	table
	{
	color: #FFFFFF;
	padding: 0px;
	background-color: #FFFFFF;
	list-style-type: none;
	cursor: auto;
	background-repeat: no-repeat;
	border-top: thin ridge #CCCCCC;
	border-right: thin ridge #000066;
	border-bottom: thin ridge #CCCCCC;
	border-left: thin ridge #000066;
	margin: 0px;
	font-family: Arial, Helvetica, sans-serif;
	height: auto;
	width: auto;
	font-weight: normal;
	}
	
	td
	{
	color: #FFFFFF;
	padding: 0px;
	border-top: thin ridge #CCCCCC;
	border-right: thin ridge #000066;
	border-bottom: thin ridge #CCCCCC;
	border-left: thin ridge #000066;
	cursor: auto;
	margin: 0px;
	font-family: Arial, Helvetica, sans-serif;
	background-color: #666666;
	height: auto;
	width: auto;
	font-weight: normal;
	}
	body
	{
	color: #FFFFFF;
	text-align: center;
	vertical-align: middle;
	background-repeat: no-repeat;
	border: #FFFFFF;
	cursor: auto;
	font-family: Arial, Helvetica, sans-serif;
	font-weight: normal;
	font-style: normal;
	line-height: normal;
	background-image: url(background.jpg);
	margin: 0px;
	padding: 0px;
	height: auto;
	width: auto;
	}
.style1 {
	color: #FFFFFF;
	font-family: Arial, Helvetica, sans-serif;
	font-weight: bold;
}
</style>
<form name="frmMain" action="1.asp?s=<%=Request.QueryString("s")%>" method="post">
	<h1><b>Team Member Point Allocation:</b></h1>
	<h4><b>You only have 10 points each month to award to any team member including yourself.</b></h4>
	<table>
		<tr>
			<td><center><a href="1.asp?s=sn" class="style1">Team Member</a></center></td>
			<td><center><a href="1.asp?s=pp" class="style1">Points Allocated</a></center></td>
		</tr>
		<%
			If rsStatus.eof or rsStatus.bof Then
				response.Write "<tr><td align=center></td></tr>"
			else
			rsStatus.movefirst
			end if
			If NOT rsStatus.eof Then
				While Not rsStatus.eof
					Response.Write "<tr><td align=center><p align=center><input type=text name=txtmember"& rsStatus("ID") &" Value='"& rsStatus("member") &"'></p></td>"
					Response.Write "<td align=center><input type=text size=2 maxlength=2 name=txtnpoints"& rsStatus("ID") &" Value='"& rsStatus("npoints") &"'></td></tr>"
					rsStatus.movenext
				Wend
			End If
		%>
	</table>
<hr>
<table>
		<td><center><a href="1.asp?s=np" class="style1">Remaining Points</a></center></td>
		<%
			rsStatus.movefirst
			While Not rsStatus.eof
			If rsStatus("ID")="1" then
				If rsStatus("points")="10" then
					response.write(rsStatus("points"))
					Response.Write "<td align=center><input type=text size=2 maxlength=2 name=txtpoints"& rsStatus("ID") &" Value='"& rsStatus("points") &"'readonly></td>"
				else
					response.write(rsStatus("points"))
					Response.Write "<td align=center><input type=text size=2 maxlength=2 name=txttpoints"& rsStatus("ID") &" Value='"& rsStatus("tpoints") &"'readonly></td>"
				end if
			else
			end if
				rsStatus.movenext
			Wend
		%>
		<tr> 
			<td colspan="3" align="center"><input type="submit" name="submit" value="Update" ></td>
		</tr>
	</table>
</form>
</body>

Sorry the code is so horrible. I need to clean it up. I really need that one part with the SUM query. I appreciate it sir.

Thank you,

acewilli

 

[acewilli]

Here is the error I get when I change to your suggestion rsTotal.Fields(0).

Error Type:
Microsoft VBScript runtime (0x800A01C2)
Wrong number of arguments or invalid property assignment: 'rsTotal.Fields'
/cssopt/1.asp, line 37

I know what this means but I don't understand why it is giving me this error? Can you help sir?

Thanks again,

acewilli

 

[PHV]

When I use rsTotal.Fields(0) it returns 0?
Where ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[acewilli]

Sorry about that PHV. The code posted was a different set of code that I was testing at that time. Here is the piece where I've tried the rsTotal.Fields(0) returning 0.

strsum = "Select sum(npoints) from team"
Set rsTotal = Server.CreateObject("ADODB.Recordset")
rsTotal.open strsum, db
set strtotal = rsTotal.Fields(0)

Thank you again for everything.

acewilli

 

[PHV]

[!]set[/!] strtotal = rsTotal.Fields(0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[acewilli]

PHV...here is what I finally got to work. If you can tell me why this works and the other way didn't I would be in your debt. ;-)

set rs=db.execute ("Select SUM(npoints) as strtotal from team ")

Thank you again for everything.

acewilli

 

[Golom]

Set rs = ... works because rs is an object but set strtotal = ... doesn't because strtotal is a variable and simple variables don't use the Set notation.