Home > Archive > ASP > July 2004 > Retrieving Data from Excel by using ASP
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Retrieving Data from Excel by using ASP
|
|
|
| Hello there,
Could someone please help me out, I have an excel sheet for
Student Grades and I want to post the grades online but I want every
student to have his own ID and password to retrieve his own grades. for
example when he wants to check his grades he has to type in his Id and
password and then if the password and id are right it gives him/her
grades on a table.
Any help appericaited, thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Ray at 2004-07-17, 3:55 pm |
| Excel isn't really the best datasource around, but you can use it. If you
send me a copy (or dummy copy) of the spreadsheet to ray at lane34.com, I'll
take a look at it and see if I can come up with something.
Ray at work
"Ab" <anonymous@develpersdex> wrote in message
news:eAfSNr8aEHA.904@TK2MSFTNGP09.phx.gbl...
> Hello there,
> Could someone please help me out, I have an excel sheet for
> Student Grades and I want to post the grades online but I want every
> student to have his own ID and password to retrieve his own grades. for
> example when he wants to check his grades he has to type in his Id and
> password and then if the password and id are right it gives him/her
> grades on a table.
>
> Any help appericaited, thanks!
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
| |
|
| Yeah I agree with you Ray. but I want something so simple and easy and
thats what I thought of. If there is something similar and easier please
advice me anyway I'll send a dummy file for you now.
Cheers,
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Ray at 2004-07-19, 3:55 pm |
| Here is some code. Note that I changed the Std_Pwd column to explicitly be
text instead of numeric. If you intend to always have numeric passwords and
numeric datatype in the spreadsheet, remove the ' delimiters for Std_Pwd in
the query.
This would use a form like:
<form method="post" action="results.asp">
<input name="txtStd_ID" type="text">
<input name="txtStd_pwd" type="password">
<input type="submit">
</form>
<%
Dim oADO, oRS, sSQL
Dim sUserID, sPassword
sStd_ID = Request.Form("txtStd_ID")
sStd_pwd = Request.Form("txtStd_pwd")
sSQL = "SELECT Quizes,Midterm1,Midterm2,Final,Bonus,Gra
de FROM [sheet1$]
WHERE [Std_ID]='" & q(sStd_ID) & "' AND [Std_pwd]='" & q(sStd_pwd) & "'"
Set oADO = CreateObject("ADODB.Connection")
oADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\Path\To\This\File.xls;Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1"""
Set oRS = oADO.Execute(sSQL)
If oRS.EOF Then
Response.Write "Login failed."
Else
%>
<table>
<tr>
<th>Quizzes</th>
<th>Midterm 1</th>
<th>Midterm 2</th>
<th>Final</th>
<th>Bonus</th>
<th>Grade</th>
</tr>
<tr>
<td><%=oRS.Fields.Item(0).Value%></td>
<td><%=oRS.Fields.Item(1).Value%></td>
<td><%=oRS.Fields.Item(2).Value%></td>
<td><%=oRS.Fields.Item(3).Value%></td>
<td><%=oRS.Fields.Item(4).Value%></td>
<td><%=oRS.Fields.Item(5).Value%></td>
</tr>
</table>
<%
End If
oRS.Close : Set oRS = Nothing
oADO.Close : Set oADO = Nothing
Function q(s)
q = Replace(s, "'", "''")
End Function
%>
Ray at work
"Ab" <anonymous@develpersdex> wrote in message
news:%23PNGbVJbEHA.996@TK2MSFTNGP12.phx.gbl...
> Yeah I agree with you Ray. but I want something so simple and easy and
> thats what I thought of. If there is something similar and easier please
> advice me anyway I'll send a dummy file for you now.
>
>
> Cheers,
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
| |
|
|
Thanks for your help and sorry for my late reply but I was busy lately
couldn't even access the net. Anyway I tried your code but seems to be
there are some problems or I'm not doing it right. Let me tell you what
I did.
I took this code and put it in a page named form.asp
"<form method="post" action="results.asp">
<input name="txtStd_ID" type="text">
<input name="txtStd_pwd" type="password">
<input type="submit">
</form>"
and the other code and named it results.asp
and I did some modification here
Source=D:\Path\To\This\File.xls;Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1""" ....When go to forms.asp and type in the name and
password it gives me an error like this
Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/results.asp, line 12, column 72
sSQL = "SELECT Quizes,Midterm1,Midterm2,Final,Bonus,Gra
de FROM [sheet1$]
-----------------------------------------------------------------------^
and I no noting about asp but I tried to delete some things from the
code which is
" Quizes,Midterm1,Midterm2,Final,Bonus,Gra
de" and then the error
disappear except another error came up
Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/results.asp, line 14, column 48
oADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
-----------------------------------------------^
So do you have any idea ?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Ray at 2004-07-22, 8:55 pm |
| If you copied and pasted my code, you'd have to deal with linewrap in your
newsreader. One line of code may not fit on one line of display in your
reader. You can't have a line of code like
sVar = "some string"
span two lines like
sVar = "some
string"
(In VBS)
Ray at work
"Ab" <anonymous@develpersdex> wrote in message
news:uoUSiIBcEHA.3988@tk2msftngp13.phx.gbl...
>
> Error Type:
> Microsoft VBScript compilation (0x800A0409)
> Unterminated string constant
> /results.asp, line 12, column 72
> sSQL = "SELECT Quizes,Midterm1,Midterm2,Final,Bonus,Gra
de FROM [sheet1$]
> -----------------------------------------------------------------------^
>
> and I no noting about asp but I tried to delete some things from the
>
> Error Type:
> Microsoft VBScript compilation (0x800A0409)
> Unterminated string constant
> /results.asp, line 14, column 48
> oADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
> -----------------------------------------------^
>
| |
|
|
Hi Ray I know am making it much harder but be patient with me please.
Well everything is going ok except when I type the username and password
it gives me this new error
Technical Information (for support personnel)
Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/results.asp, line 16
Line 16 is :-
Set oRS = oADO.Execute(sSQL)
what do you think the problem is ?
thanx a million
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Ray at 2004-07-26, 3:55 pm |
| Replace:
Set oRS = oADO.Execute(sSQL)
With this code:
Response.Write sSQL
Response.End
Set oRS = oADO.Execute(sSQL)
Can you copy and paste the SQL that shows up in your browser when you load
the page then?
Ray at work
"Ab" <anonymous@develpersdex> wrote in message
news:ullkDOVcEHA.3016@tk2msftngp13.phx.gbl...
>
> Hi Ray I know am making it much harder but be patient with me please.
> Well everything is going ok except when I type the username and password
> it gives me this new error
> Technical Information (for support personnel)
>
> Error Type:
> Microsoft JET Database Engine (0x80040E07)
> Data type mismatch in criteria expression.
> /results.asp, line 16
>
> Line 16 is :-
> Set oRS = oADO.Execute(sSQL)
>
>
> what do you think the problem is ?
>
>
> thanx a million
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
| |
|
|
Hello Ray,
Here it is
SELECT Quizes,Midterm1,Midterm2,Final,Bonus,Gra
de FROM [sheet1$] WHERE
[Std_ID]='1112' AND [Std_pwd]='1112'
that is the result
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
|
| Hello again,
I think I got it now I should've taken out the single quotes '' from
both Std_ID and Std_pwd
thank you very much for supporting me I learned from you alot and if you
can give me some resources for asp for beginners something that is easy
to understand and give me the way to start building new skills. I think
I like ASP thats great thank you again
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Ray at 2004-07-27, 3:55 am |
| Nice. :] Yes, I'd imagine that unless you specifically formatted the
columns in Excel to interpret the data as text data, it'll assume an
all-numeric column contains numeric data. Actually, if you have a column
that has numeric data in the first record and text data in subsequent
records, I believe it'll still be interpreted as numeric data. This causes
problems. The "IMEX=1" part in the connection string deals with that. See
www.connectionstrings.com.
Ray at home
"Ab" <anonymous@develpersdex> wrote in message
news:%23NYmQWzcEHA.712@TK2MSFTNGP11.phx.gbl...
> Hello again,
>
> I think I got it now I should've taken out the single quotes '' from
> both Std_ID and Std_pwd
>
>
> thank you very much for supporting me I learned from you alot and if you
> can give me some resources for asp for beginners something that is easy
> to understand and give me the way to start building new skills. I think
> I like ASP thats great thank you again
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
| |
| Jason Youngberg 2004-07-27, 3:55 pm |
| Ray,
I use pretty much the same code but I've had a unique problem crop up.
In one of the Excel spreadsheets the program goes through, it's not
recognizing the values in a column. I have it display the .value and
it's just an empty string (not null). I don't think there is anything
special about this table, it's just like all the others as far as I can
tell. Excel sees the value, when I go to a web page, the value is saved,
but "rsXLBody.Fields.Item(Rank_Column_ID).value" returns "" only on this
sheet. Here's basically what the sheet looks like.
1 Fred's Supermarket
Apples
Oranges
2 Whole Grains Market
Bread
Grapes
and so on, the "rank column" says whether the row is a store or delivery
item.
I thought about trying "rsXLBody.Fields.Item(Rank_Column_ID).num" like
what happens if you try to convert it to a web page, but it didn't work.
Are .value and .name the only acceptable values?
Oh, I should mention if I go through the spreadsheet and replace the
numbers with letters, it works normally. (Doesn't matter what's in the
stor rows as long as there's something.)
Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Ray at 2004-07-28, 3:55 pm |
| You aren't talking about the empty columns to the left of Apples, Oranges,
Bread, and Grapes, are you? They're empty. ?
Ray at work
"Jason Youngberg" <moquif@excite.com> wrote in message
news:Oa0JW8%23cEHA.2352@TK2MSFTNGP09.phx.gbl...
> Ray,
> I use pretty much the same code but I've had a unique problem crop up.
> In one of the Excel spreadsheets the program goes through, it's not
> recognizing the values in a column. I have it display the .value and
> it's just an empty string (not null). I don't think there is anything
> special about this table, it's just like all the others as far as I can
> tell. Excel sees the value, when I go to a web page, the value is saved,
> but "rsXLBody.Fields.Item(Rank_Column_ID).value" returns "" only on this
> sheet. Here's basically what the sheet looks like.
>
> 1 Fred's Supermarket
> Apples
> Oranges
> 2 Whole Grains Market
> Bread
> Grapes
>
> and so on, the "rank column" says whether the row is a store or delivery
> item.
>
> I thought about trying "rsXLBody.Fields.Item(Rank_Column_ID).num" like
> what happens if you try to convert it to a web page, but it didn't work.
> Are .value and .name the only acceptable values?
>
> Oh, I should mention if I go through the spreadsheet and replace the
> numbers with letters, it works normally. (Doesn't matter what's in the
> stor rows as long as there's something.)
>
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
|
|
|
|
|