i got access database and i need an ASP script to search it

[theguy]

hi,
i got an access database like this:


F1 F2 F3 F4 F5
2050610 English
100480 3 pass Math1 73
111100 3 pass Arts1 64
221700 3 pass Music1 66
330102 3 pass English1 76

F1-F5 colmns 1-5
then you have student ID under F1 and his major under F3
and on line3 you got course# , under F2 you got how many credits, under F3 pass or failing, under F4 couse title and under F5 his score from 100

is this a legit database? it's not corrupt?
can someone write a small asp script to allow it to search the database and give the results once the student enter his id in the search form?
and if possible give his average

thanks

[Goofy]

Hello theguy,

Honestly, the way you described it, it seems more like an Excel worksheet. Anyway, the columna you are reffering to are actually called fields in DB terms. So make them more meaningful. i.e. F1 should be student_id, F2 - credits etc.. This was you could make things much more easier.

In order to search the database all you need is to create the proper conenction and dataset. Your query will be the trump to get the data, and would read something like "SELECT result FORM tblstudents WHERE student_id = 1100480.

First try to make things a bit more clear, and let me know if you need any help. good luck !

[theguy]

you are right, it's originally an an excell sheet

is it possible to write a script that search excell on the net if so how

can you give me an example?

[Goofy]

I wouldn't reccommend using excel for this kinda purpose. It's not relational and could slow things down. However if you need to use Excel read this http://sites.encompassnow.com/idude/articles/using_excel_file_datasources.asp

Other possible options are to create a new Access Database, or append your current Excel Worksheet to an Access Database. If you don't know to do this you might find some info here http://www.freevbcode.com/ShowCode.asp?ID=1302

If you decide to use an Access Database you will have to create a student table

tblstudents {student_id, credits, grade, course, score} PRIMARY KEY {student_id}

and, to search the database using ASP.

Dim studentID 'parsed Student ID
Dim stAverage 'Student's Average
Dim objConn ' Connection Name
Dim strConn ' Connection String
Dim objRS ' Recordset Variable
Dim strSQL ' variable for SQL statement
Dim counter

Const adOpenStatic = 3
Const adLockReadOnly = 1

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

strConn = "DBQ=" & Server.MapPath("DatabaseName.mdb")
strConn = "DRIVER={Microsoft Access Driver(*.mdb)};" & strConn
objConn.Open strConn

strSQL = "SELECT course, score FROM tblstudents WHERE student_id=" & studentID

objRS.open strSql, objConn, adOpenStatic, adLockReadOnly

IF NOT objRS.EOF = TRUE

While objRS.EOF = FALSE
Response.Write objRS(0).value & " : " & objRS(1).value
counter = counter + 1
totalScore = totatScore + 1
objRS.Movenext
End While

stAverage = totalScore / counter

Response.Write "Your Average: " & stAverage

ELSE

Response.Write "Invalid Student ID"

End IF


objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing


The above method is not relational either. The course name is repeated for every student entry. Also I'm not quite sure how your credit system works. So this might not be the ideal solution. and if you are using ASP.Net this won't be helpful either. Let me know what you decide to do.