Web project problems.

[[BTF]adam]

Hai! It's me again, captain can't-code-for-shit.

I'm currently involved in a project for my course spanning several months and our client wants basically a input data output data in the form of a viewable website with report functions and an input form feeding his session data (he helps people around the college + so do his staff, he needs this info recording). anyway, to the point we had our second meeting with him today and he brought up a few things that he would like us to implement but frankly we've no idea how. I'll need to elaborate when my team has the meeting transcripted so I can pinpoint exactly what it is he wants but for starters, he said (roughly):

He wants some sort of admin page where he can modify the drop-down menus in the form and update them when he wants. e.g. he wanted us to put his staff in the drop down menu for the 'Staff Member' field, so say theres a bunch of people in there and he wants to add someone, he wants to be able to that without having to edit the html files directly/ use dreamweaver to do it manually. (yes I know this is stupid and easy to do but he requested it and doesen't know the faintest thing about web design). I presume this is doable by some sort of script? we're using Access+ASP to link the database if it's relevant. (personally i'd have preferred to use SQL+PHP but we aren't familar enough with it).

The other thing he wanted (amongst others, but we can probably manage the others) was to specify the reports he wants. such as, being able to specify that you want a report of all the things 'x' person did. or a report of all sessions logged between x date and y date. I'm pretty awful at report features at the best of times, let alone something like that.

Regards,

Will Smith.

[peewee_RotA]

asp.net

Get MS visual webdev 2010 (should still be in beta)
You can run an IIS server from any XP pro, windows 7 (pro?), or sever2003 box. I believe the server 2000 version of IIS is compatible and upgradable to the current version.

I strongly recommend using C# as your language with ASP. Visual Basic is generally the same thing but C# has much better documentation for ASP.net tools, from my experience. The old joke in my VB.net class when C# was new was "What is this C# crap? It's just visual basic with semi-colons!"
 

You can definitely accomplish the same goals with Apache, PHP, and even PERL. Actually I've heard Cold Fusion and PHP as the most recommended languages for apache servers. (I'm not familiar with cold fusion. I don't know if it is a language, standard, or an application). The reason why I am so supportive of ASP.net lately is that generally everything you want to do is as easy as declaring a class and setting 3 or 4 properties. It's sick how fast you can develop professional forms and tools with things like the dataview object and even the "wizard" object. Plus applications like linQ and Yet Another Forum are a breeze to set up and configure, yet powerful enough to do almost anything if you put enough time into it.

[reaper]

for the reports you could generate something like:
select * from employeetable where date is x

this is the code that searches the database, and x is a value input from a web form.  you will probably want to look at sample web forms, and look at sessions, with ASP.NET the session state database, PHP has functions made to keep track of the user, same with ASP, it might even do it by default.  those two things and it should come together pretty easily.

the admin page is kind of like a CMS, you can change the web application through the web interface you make.  maybe try to put a footer on all pages first with ASP.  ASP is pretty similar to PHP and both should be able to do this.

[[BTF]adam]

thanks guys, looking into them. helpful stuff

[[BTF]adam]

so we're pretty far along in the project, we have the code sorted to put form data into a database and for the most part it works fine but there's a few things that I don't know how to do at all, wondering if anyone can offer some ideas.

all in ASP btw, not asp.net

firstly, I need to display the top ten most recent entries on a page in my website. so I figured, it would be something like using some sql in the asp code and using the TOP 10 modifier but how the fuck one would implement the actual code i don't know. so like in the body of the site, select top 10 from staff order by asc ?

secondly, I need to generate reports and everything I google across comes up with shit like needing external resources like snapshot viewers? im not exactly sure what he wants in these reports but im going to assume its something along the lines of a report detailing everything on a certain day, or a time period of the last week etc. this is my biggest problem I think, I have no fucking CLUE what to do here.

and oh finally, thirdly, I need to be able to search the database and pull up entries based on what the search criteria is. i dont need everything searchable, hell it could even be a drop down list of like the staffs names so they can pull up just their entries, but also i need it to be able to pull up entries from a specified date. again im assuming this is a select * from staff where date = "blah/blah/blah" but its how to get that from an open text field (or dropdown) to pulling it out and displaying it.

thanks for any help guys

[peewee_RotA]

ASP.NET makes all of this stuff insanely easy, especially with the additional LINQ extensions.

For the first problem, you'll want to add a timestamp column to whatever DB table that is holding that information. The setup that I've seen for things like that is one table holds the topic, a timestamp, the name of the user,  some configuration flags, and an ID that points to a second table which holds the actual message body. You would sort by that timestamp.

You'd probably create some kind of object or div that you can dynamically edit the html using the code behind. Then when you get and sort through your db information you can format it however you need and display it in that serverside object.


As for generating reports, you are currently facing the curse of the buzz word. Gotta run, I'll respond to the rest in a bit.

[[BTF]adam]

thanks peewee. sadly asp.net is out of the question this project is designed to be run on the college computers and the network admins are vaginas and won't install the neccesary features to use asp.net, let alone do we have the knowledge of how to do it.

[[BTF]adam]

So this is our SQL/ASP for Viewing the top 10 recent entries

<%
Set objRs=Server.CreateObject("ADODB.Recordset")
Set objConn=Server.CreateObject("ADODB.Connection")
dbpath=server.mappath("products.mdb")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ="&dbpath&";"

strSQL="SELECT TOP 10 txtDate FROM users order by txtDate asc"

objRs.Open strSQL, objConn
%>

it doesen't work lol i've tried all sorts of variants but i think the problem lies within i cant remember how to structure the top 10 query correctly. txtDate is the date field, users is the table name.

[quadz]

Disclaimer - I know f*ckall about Microsoft's database API's, so these are some general observations that may or may not apply...

objConn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ="&dbpath&";"

The nested quotes look a little suspicious.  Is that really the correct syntax?  "Driver=...DBQ="&dbpath&";" ?  Usually nested quotes like that would present a problem for a parser, unless the "& ..&" means something special.

strSQL="SELECT TOP 10 txtDate FROM users order by txtDate asc"

Usually that would be written:

  SELECT txtDate FROM users ORDER BY txtDate ASC LIMIT 10;


Regards,

[[BTF]adam]

thanks for the statement correction, im pretty sure the syntax is fine since we can SELECT * FROM USERS and stuff perfectly fine and it takes it from the database and displays into a table for ujs.

[peewee_RotA]

What type of DB are you using? MSSQL, MySQL, and oracle SQL have slightly different syntax.

SELECT TOP 10 is correct for MSSQL, I'm not sure about order by.

Code: [Select]

"Driver=...DBQ="&dbpath&";"
This appears to be correct. I believe that the & symbol is the string concat character. VB? I don't remember VB enough though. In C# it's a regular +

And you know what they say about C#. It's just VB with semi colons. Speaking of which, from my experience MSSQL doesn't require semicolons but your sql statement is missing one. Versions of MSSQL older than 2005 may require them. Your old ass non-.net connection object may parse it and require one.

I think that your order by might be the problem. You may also need to specify the DB and table in your query. I.E.

SELECT TOP 10 [myDBName].users.txtDate FROM users order by [myDBName].users.txtDate asc;

Do you get any specific errors that you can copy/paste here? You may need to either run your site in debug or try running that query in the SQL management studio. There is a free express edition of MS SQL Management studio if you don't already have it.

The last thing, and probably the first thing that we should have mentioned is this. What variable type is txtDate? Is it a string/varchar/nvarchar? Only certain sequential types are able to be ordered in sql. You might need to change the type of that column or get the data unordered and manually sort it by using a loop in your code behind.

[[BTF]adam]

Access Database (old version, 2000)

as far as errors go, loads. but we've been trying so many different versions of that select statement trying to get it to work, here are some:

adam says:
 SELECT TOP 10 txtDate FROM USERS
 strSQL="SELECT TOP 10 txtDate FROM users"

Tom says:
 ADODB.Recordset  error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/200910/FdSc2/oldgreggg/Testsite/R/view.asp, line 80
 that error was for the second one

the first one's error says:
 Microsoft VBScript compilation  error '800a03fd'

Expected 'Case'

/200910/FdSc2/oldgreggg/Testsite/R/view.asp, line 8

SELECT TOP 10 txtDate FROM USERS


and the most recent we tried (quadz's);


Quote from: [BTF]adam on Today at 12:33:22 PM
strSQL="SELECT TOP 10 txtDate FROM users order by txtDate asc"

Usually that would be written:

  SELECT txtDate FROM users ORDER BY txtDate ASC LIMIT 10;

Tom says:
 Item cannot be found in the collection corresponding to the requested name or ordinal.
adam says:
 
This like the error above is a recordset error. You have tried requesting a field from the recordset that does not exist.
 
Check when you are reading in the field from the recordset into a variable or to display in a web page that you have spelt the field name correctly and that the field exists in the database.

Tom says:
 Well it's using the exacts spelling the line below is using which works perfectly
adam says:
 in the one that works we dont use txtdate tho do we
 thats where the issue is im just not sure where 
Tom says:
from this line:
 <td class="style7" height="40"><div align="left"><%=objRs.fields("txtDate").value%></div></td>

i using txtDate and is working fine

might be a bit hard to follow but thats all I can gleam from the most recent msn window i don't save chatlogs so they're the only error codes I have.

variable type, uh it's just a field in the database. we only named it txtDate out of familiarity with the txtXXXXX naming scheme - it could just as easily be named Date, in the database all it is is a field named txtDate that, when the input form is used, inserts data from a standard three option drop down menu consisting of dd/mm/yyyy and then puts it into the database, which is a "Text" specified Data Type.

[peewee_RotA]

Based on those errors it sounds like the users table doesn't exist, or the txtDate column doesn't exist on that table, but your code where you bind it to another object means that it has to. So it  has got to be a simple mistake somewhere.

See if you can just get "SELECT txtDate FROM users;" to work. Try making a new query inside of access. If that won't work then go back into the edit mode for your table and try to list the structure of it. Basically what columns, what are their datatypes, and which are primary and foreign keys?

It sounds like tom is your guy doing the programming for this portion. Is he running the website locally on his machine? Have him try stopping and starting the web service again. Depending on your set up the DB connection string could be a compiled component that gets stored in memory and you have to stop and restart the web service so that it can reload that setting. It's a long shot but it's worth a try. If he is only running it locally while he is developing then it probably does stop and start everytime that he makes a change. However, I know that Visual WebDev 2010 will leave the local web process running in your task tray until you manually kill it. 2008 didn't seem to do this for some reason. I don't know which is more desirable, I personally prefer it to stop the webservice whenever I am done debuging. That way I know that it is restarted the next time that I need to compile.

[[BTF]adam]

alriiiiighty, asked him to try the select txtdate from users, will report back what happens. what happens is below.


------  ADODB.Recordset  error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/200910/FdSc2/oldgreggg/Testsite/R/view.asp, line 80


query inside access, I'll give that a try. well I tried and failed, truthfully I don't know how to do queries properly inside of Access. Here's the structure: there are no primary and foreign keys, didn't see it as neccesary. (yeah we're so bad at this )



Tom is doing it on his machine but uploading the files and asp to a webspace provided by the college, which you can see here; http://www.chesterfieldstudents.co.uk/200910/FdSc2/oldgreggg/ but we're all working on it.

thanks for the help peewee, sorry for our utter newbness :p

[[BTF]adam]

here is the total asp file if it helps any.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%
Set objRs=Server.CreateObject("ADODB.Recordset")
Set objConn=Server.CreateObject("ADODB.Connection")
dbpath=server.mappath("products.mdb")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ="&dbpath&";"

strSQL="SELECT txtDate FROM users"

objRs.Open strSQL, objConn
%>

<html xmlns="http://www.w3.org/1999/xhtml">



<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Stylus Music - Products</title>
<style type="text/css">
<!--
}
body, td, th {
   color: #000000;
}
a:link {
   color: #000000;
}
a:visited {
   color: #000000;
}
.style7 {
   color: #000000;
   font-family: "Courier New", Courier, monospace;
}
.style9 {font-family: "Courier New", Courier, monospace}
.style10 {color: #000000}
-->
</style>
<script type="text/JavaScript">
<!--
function MM_swapImgRestore() { //v3.0
  var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a)&&x.oSrc;i++) x.src=x.oSrc;
}

function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a.indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a;}}
}

function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers.document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_swapImage() { //v3.0
  var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
   if ((x=MM_findObj(a))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}
//-->
</script>
</head>
<div align="center">Data from Timesheet Database: </div>
<tr>
  <td width="9000" height="377" colspan="9" align="center" valign="top" bgcolor="#FFFFFF"><div align="center"><span class="style7">
    <TABLE WIDTH="100%" BORDER="1" height="43">
    <p>&nbsp;</p>
    <div align="center" class="style9">
      <div align="center">
        <%
do while not objRs.EOF
%>
      </div>
    </div>
  <tr>
  <td class="style7" height="40"><div align="left"><%=objRs.fields("staff").value%></div></td>
  <td class="style7" height="40"><div align="left"><%=objRs.fields("txtStudent").value%></div></td>
  <td class="style7" height="40"><div align="left"><%=objRs.fields("txtDate").value%></div></td>
  <td class="style7" height="40"><div align="left"><%=objRs.fields("txtProject").value%></div></td>
  <td class="style7" height="40"><div align="left"><%=objRs.fields("worktype").value%></div></td>
  <td class="style7" height="40"><div align="left"><%=objRs.fields("txtDesc").value%></div></td>
  <td class="style7" height="40"><div align="left"><%=objRs.fields("duration").value%></div></td>
  <td class="style7" height="40"><div align="left"><%=objRs.fields("txtCom").value%></div></td>
  <td class="style7" height="40"><div align="left"><%=objRs.fields("txtNotes").value%></div></td>
  </tr>
     <div align="center" class="style9">
       <div align="center"><span class="style10">
         <%
objrs.movenext
loop
%>
       </span></div>
     </div>
</body>
</html>