Connecting A Spreadsheet to a Form - ?

[Guest]

Is there any way (in Microsoft FrontPage) to gather data entered into a form
into a SPREADSHEET (rather than a database) -- and then to use a query to
gather the information from that spreadsheet (via an ASP page)? I guess what
I'm basically asking is whether there is any way to get a spreasheet to ACT
as a database, for this purpose?

I'm trying to allow users to enter certain data, then have the spreadsheet
do calculations based on that data, and then return other data to the web
site. I am guessing this is not possible with a database (since
spreadsheets, not databases, can perform calculations)? Am I right?

 

[Mark Fitzpatrick]

I am guessing this is not possible with a database (since

spreadsheets, not databases, can perform calculations)? Am I right?

It depends upon the calculations you want. The SQL language does offer a lot
of features for performing calculations. Excel does have a lot more for
doing certain types of calculations, especially financial. The other thing
is, how are you going to get the spreadsheet to do the calculations? Getting
the data into a spreadsheet and out using ODBC or OleDb isn't hard, but you
won't be able to perform any calculations as far as I know because these
methods both are database methods.

First though, you may want to determine if the calculations you are looking
for are available in the database itself, such as Access. If so then I would
use the built-in functions that can be called by SQL (and hence ASP) to
perform the tasks. Otherwise you're looking at finding a third-party
component that would let you read the spreadhseet, perform calculations, and
generate responses for old-fashion ASP. These components aren't cheap, and
most hosts or server admins don't let you install them since they are
third-party and could cause problems with the server.

If you could find a way to perform calculations off-line, you could import
the data from a db such as Access directly into Excel to work with (it's
really neat and darned easy to do) using the Data Import feature of Excel.
Then you could perform calculations to be saved in seperate columns that
find a way to have the results queried from this spreadsheet.

Bottom line, you can save results to a spreadsheet as if it was a database
by choosing the appropriate driver, it's a little tricker, less efficient
and may be more difficult in the long run.

 

[Guest]

OK...so could you give me some instruction on how to use SQL statements to
perform calculations from my database. Essentially, here's what I want to do:

* I want to allow a user to input numeric data into different fields (let's
call them Games, Points, Rebounds, and Assists). - I know how to do this.

* I want that information to be saved into a database. - I know how to do
this.

* I want the database (or SQL statements, I guess) to perform calculations
to generate "spit out" results for Points per Game, Rebounds per Game, and
Assists per Game to my web site. - THIS is what I do not know how to do.

Any ideas?

-- Nate

 

[David Berry]

You can use ASP to do the math for you. For example (*not complete code*):

<%

' first connect to the database and set up your recordset

strConnection = Application.Contents("DSN_NAME")
Set adoCN = server.CreateObject("ADODB.connection")
Set adoRS = server.CreateObject("ADODB.recordset")
adoCN.Open strConnection
adoRS.ActiveConnection = adoCN
adoRS.CursorLocation = 3

' select your records and open the recordset

strSQL = "SELECT * FROM Table"
adoRS.Open strSQL

assign variables to the vaules you retrieve

intGames = adoRS("Games")
intPoints = adoRS("Points")
intRebounds = adoRS("Rebounds")
intAssists = adoRS("Assists")

%>

then you'd do the math. For example

<%
intPointsPerGame = intPoints / intGames
%>

then your HTML code using the variable intPointsPerGame to show the result.
Ex:

Points Per Game = <%=intPointsPerGame%>

Because you're doing math you may also want to first check to make sure the
value isn't a null before doing division or make sure that it is an integer
but this is the general idea.

 

[Guest]

Is there a way for me to accomplish this same basic thing at the time when
the data is being ENTERED into the database? In other words, can I have the
form accept the data, then perform calculations, and enter the data AND the
calculated data into the database?

If so, can you give me an idea of how to accomplish that?

Thanks!

-- Nate

 

[David Berry]

Sure. You just do the math before you insert the data into the database.
For example

' get the data from the form

intGames = Request.Form("Games")
intPoints = Request.Form("Points")
intRebounds = Request.Form("Rebounds")
intAssists = Request.Form("Assists")

' now do the math

intPointsPerGame = intPoints / intGames

then when you do your INSERT statement you'd insert the value of the
variable intPointsPerGame into the field that you want to hold the
information along with the other data you're inserting. Ex:

strSQL = "INSERT INTO Table
(Games,Points,Rebounds,Pickup,Assists,PointsPerGame) "
strSQL = strSQL & "VALUES( " & _
intGames & ", " & _
intPoints & ", " & _
intRebounds & ", " & _
intAssists & ", " & _
intPointsPerGame & "' )"
adoCN.Execute strSQL


Whether you do the math before you do your insert or after is up to you.
Depending on what's being done with the data it might be better to keep the
raw numbers in the database and do the math after so that you have them in
case you want to use them again.

 

[Guest]

OK, I've decided that I want to perform the calculations AFTER entering the
data into the form (as the data is being drawn from the form to the web
site)...but I know VERY little about writing the code. (I have just been
using FrontPage to build my site, and do not know much beyond the extreme
basics.) So...I'm wondering if someone could help tell me WHERE to put each
part of the code. I'm just not sure how to do this, so could use some
step-by-step help. Thanks!

-- Nate

 

[Guest]

I have decided that I'd like to perform the calculations AFTER entering the
data into the form (as the data is being drawn into the web site), but I know
very little about writing code, so I could use some help knowing exactly
WHERE to put each bit of code (which was suggested a couple of posts ago). I
have only used FrontPage to build my site, and know how to use just the
basics. I could use some step-by-step help from someone who is willing.
Thanks!

-- Nate