Can someone please help me with this sql statement?

[hughesmi]

Can someone please help me with this sql statement.


mySQL = "Select Count(*) AS myTotal FROM Data WHERE='Request.QueryString('Name')'"


The ISS error is


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '='Request.QueryString('Name')''.
pulldata.asp, line 24

[miranda]

what field are you looking for in the WHERE statement?
Also you did not include the concatenation operator &

You need to have a field name in there.

if this is a text datatype field then it will look like this
WHERE somefield = '" & Request.QueryString("Name") & "'"

Unlike PHP you cannot skip using the concatenation operator

[hughesmi]

Thanks again for your ASP and SQL help - you have offered me loads of help over that last few days.

I wonder if I could impose on your good nature again?

What I'm design is a voting system for my team at work. The design is to allow a team member to vote for the most helpful team member and this is done by 1st Helpful , 2nd Helpful and 3rd Helpful.

The voter can only get one chance at voting once - you have helped me work that bit out, by sending data into two tables in my db.

This bit I'm working on just now is the reporting bit. I just need to see how many vote's each person has in each feild in my Data table. Your last post help me do that individually.

My question is, I can't think of way to display all the names and counting how many times that person names occurs in my 3 fields in my Data table. I.e Ch1 - Ch2 - Ch3

This is what I have come up with so far, but as I say I would need to go trough each name and to see the total count for that name.

Have got any tips and or examples you can think of?



<b><font size="2" face="Verdana">
<!--#INCLUDE file="dropdown.asp"-->
</font></b>

<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>

<%
'here's the connection to the mdb
Dim myConn,myPath
Set myConn = Server.CreateObject("ADODB.Connection")
myPath = Server.MapPath("database")
myConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";"
'now we will set up an sql statement to do the counting
Dim mySQL

mySQL = "Select Count(*) AS myTotal FROM Data WHERE Ch1 = '" & Request.QueryString("Name") & "'"

'here's a recordset being setup to run the connection
'and to run the sql statement thus doing all our math
Dim myRS
Set myRS = Server.CreateObject("ADODB.RecordSet")
myRS.Open mySQL, myConn

'and now we write it to the page
%>

Number Votes Found: <%=myRS("myTotal")%><%
' and we ALWAYS want to close our connections and recordsets!!!
myRS.Close
Set myRS = Nothing
myConn.Close
Set myConn = Nothing
%>

[miranda]

This will loop through each user for you. It does not use an ADO recordset, instead it uses the execute method of the connection object.

<%
'here's the connection to the mdb
Dim myConn,myPath,mySQL,myRS
Set myConn = Server.CreateObject("ADODB.Connection")
myPath = Server.MapPath("database")
myConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";"
Dim RS
'get the persons name
Set RS = myConn.Execute("SELECT name FROM name")
'loop through each of the people
Do Until RS.EOF
'now we will set up an sql statement to do the counting
mySQL = "SELECT COUNT(*) AS myTotal FROM Data WHERE Ch1 = '" & RS("Name") & "'"
SET myRS = myConn.Execute(mySQL)
'and now we write it to the page
%>
Number Votes For <%=RS("name")%>: <%=myRS("myTotal")%>
<br>
<%
Set myRS = Nothing 'release so we can reuse it
RS.MoveNext
Loop
' and we ALWAYS want to close our connections and recordsets!!!
Set RS = Nothing
myConn.Close
Set myConn = Nothing
%>

[hughesmi]

Thanks for that. It's bringing up a cheecky error.

Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'
pulldata.asp, line 18
Loop


If I'm understanding this correctly. What you have done is pull all the names in my name table and the count the number of records for each name in the data table?

I'm way off... I am not?

[hughesmi]

I fixed it. Works real good.

[miranda]

glad I could help

What it does is pull up each name one at a time loops through the entire name table and checks each person for votes along the way.

[hughesmi]

yeah.

It was the Do Until RS.EOF it need to be a line of it's own. right?

[miranda]

yes, where did you try to put it?

[hughesmi]

i'm not sure. buy hey its fixed.

[Roelf]

This will loop through each user for you. It does not use an ADO recordset, instead it uses the execute method of the connection object.

<%
'here's the connection to the mdb
Dim myConn,myPath,mySQL,myRS
Set myConn = Server.CreateObject("ADODB.Connection")
myPath = Server.MapPath("database")
myConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";"
Dim RS
'get the persons name
Set RS = myConn.Execute("SELECT name FROM name")
'loop through each of the people
Do Until RS.EOF
'now we will set up an sql statement to do the counting
mySQL = "SELECT COUNT(*) AS myTotal FROM Data WHERE Ch1 = '" & RS("Name") & "'"
SET myRS = myConn.Execute(mySQL)
'and now we write it to the page
%>
Number Votes For <%=RS("name")%>: <%=myRS("myTotal")%>
<br>
<%
Set myRS = Nothing 'release so we can reuse it
RS.MoveNext
Loop
' and we ALWAYS want to close our connections and recordsets!!!
Set RS = Nothing
myConn.Close
Set myConn = Nothing
%>

This code does fire a lot of sql queries to the database, where it all can be done in one query:
select Ch1, count(Ch1) as mytotal from data group by Ch1