Hi all I am tasked to put up an

[sunnywink]

Hi all

I am tasked to put up an entire database on the intranet with multiple queries and report.

I am new to this topic. I understand that with the following commands, i can set up the database on the net where users can enter this ASP page to do editing:

<%
dim myconnection
set myconnection = Server.createobject(&quot;ADODB.connection&quot
myconnection.openProvider=Microsoft.Jet. OLEDB.3.51;&quot;_
&&quot;DataSource=Pathname of datasource&quot;
%>


I know as for query, we can set them using the SQL language.

Eg.

Set rsTitleList=myconnection.Execut(Select * from List)

But I am confused about how will the user be able to select a query to run? Do they go to the query view on the Access database as in normal database application, and double click to do query?

And how will the server recognise which query the user has selected? In addition, how can dynamic reports be set up to reflect the updated information?

Many thanks in advance.

 

[browolf]

it's not like running access. everything that you want your users to be able to has to programmed into asp pages.

for different queries you have to program them all, but there are ways, that you dont have to program them all individually.

for instance suppose your users want to select a subset of data. well since you know what fields are possible you can build sql queries dynamically.

e.g.
sqlstring = &quot;select * from table where &quot; & fieldname & &quot;=&quot; & input_value

so fieldname could be a dropdown list of possible fieldnames and input value would be what they typed into a textbox.

because asp pages are by nature dynamic, then there's no problem having updated reports.
after you've done an sql update the table is updated. there are ado cursors for defining how multiple users affect databases. tbh i've never used these myself.
there's a good explanation of them here:

http://www.adopenstatic.com/faq/jetcursortypes.asp

As with queries you have to program all possible reports, but as above there are again shortcuts.

You have to program the whole database interface. It's a good thing in some ways because you can limit the interface so everything becomes ultra easy for users but if its a big database with a lot of possibities then it's gonna be a big job.
I'm sure it is possible to program more dynamic queries than i've suggested, but it starts to get complicated if you got a relational database and is beyond me at this point in time.

I suggest messing about with a database you make yourself for a while until you've got stuff figured. Read as many websites you can find. Get all the relevant chm files. vbscript, asp, ado,. some are freely available from ms some included with other applications.

does this help? anything else you want to know. ===============
Security Forums

http://www.security-forums.com

 

[sunnywink]

Hi, thanks for the information.

To double confirm, are the statements below sufficient to link to the database on the website and show the form directly for editing?

<%
dim myconnection
set myconnection = Server.createobject(&quot;ADODB.connection&quot
myconnection.openProvider=Microsoft.Jet. OLEDB.3.51;&quot;_
&&quot;DataSource=Pathname of datasource&quot;
%>


Any ideas of websites where I can learn more of setting queries on the intranet? I tried using search engines to find the information, but the results are not often relevant. I currently do not have access to the IIS and can only learn by reading.

 

[browolf]

ok. you need to get your hands on IIS . it's really hard to do asp stuff when you havent been able to practice. Fear not tho. If you have a win2k pro machine you can install iis. The only difference afaik is that win2k limits the connections to IIS. This isnt a problem for testing/learning purposes.

i think that you are quoting DSN-less connection.
the one i use is:
dbPath = &quot;d:\onreport\report.mdb&quot;
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=&quot; & dbPath

all this does is open a connection to the database. it doesnt do anything else.
it doesnt show any forms for editing.
if you want a form you have to make one in html. and then have a submit button that either does a POST or a GET to an asp page that processes the contents of the html form and uses SQL to insert or edit the database.

i've only done asp on nt4 so i'm not sure whether the oledb version is different on win2k

i can list the websites i have in my favorites:

http://www.tutorial-web.com/asp/database/sql.asp

http://www.asp-help.com/database/

http://www.ultimateasp.com/tutorial/sql.asp

http://www.adopenstatic.com/

http://www.4guysfromrolla.com

<< really good site

http://www.w3schools.com/vbscript/default.asp

<< vbscript

http://www.w3schools.com/asp/default.asp

<< asp

http://www.w3schools.com/sql/default.asp

<< SQl

http://www.w3schools.com/ado/default.asp

<<
ADO

===============
Security Forums

http://www.security-forums.com

 

[browolf]

and this:

An Overview in Utilizing ASP to Provide Database Access Through Web Browsers

http://www.tutorial-web.com/asp/database/

===============
Security Forums

http://www.security-forums.com

 

[browolf]

After you open a connection to the database You use sql to retrieve information from the db into a object called a recordset:

sql=&quot;SELECT * FROM onreport WHERE end_date > #&quot; & today2 & &quot;#&quot;

'create recordset for reports.
set rs = server.createobject (&quot;ADODB.recordset&quot
rs.Open sql, conn, 3,3

in nt4 rs.open interogates the db using the sql sring. the resulting information is then dumped into a recordset called rs

using a loop we can output the information in the recordset into a html table:

if not (rs.BOF and rs.EOF) then

w=&quot;<tr bgcolor='#CCCCCC'>&quot;
w=w & &quot;<td width='30%'>Pupil Info</td><td width='60%'>Report Info</td><td width='10%'>Check</td></tr>&quot;
response.write(w)


do while not rs.EOF

w=&quot;<tr>&quot;
w= w & &quot;<td><font size='2'>&quot; & rs(&quot;firstname&quot & &quot; &quot; & rs(&quot;surname&quot & &quot; [&quot; & rs(&quot;form&quot & &quot;]</font></td>&quot;
w= w & &quot;<td><font size='2'>&quot; & rs(&quot;lesson_date&quot & &quot; P&quot; & rs(&quot;period&quot & &quot; &quot; & rs(&quot;staffcode&quot & &quot;</font></td>&quot;
w= w & &quot;<td><input type='checkbox' name='checkbox' value='&quot;& rs(&quot;teacher_id&quot & &quot;'></td></tr>&quot;
w=w & &quot;<tr><td colspan='3'><font size='2'><i>&quot; & rs(&quot;comments&quot & &quot;</i></font></td>&quot;

w=w & &quot;</tr>&quot;
response.write(w)
rs.MoveNext
loop
end if

===============
Security Forums

http://www.security-forums.com