ASP / Access etc...

[Agent_Embryo]

So I've re-hacked the webshop I made during my ASP-class in school last spring, and it will now serve as an ordering system for internal computer-orders here at work. The basics work, but there's still lots to deal with. So I thought I'd put a post up to see if anyone can help me with any cool solutions to the remaining problems. Here we go.

* The database connection. Since I'm using an Access 2000 database(there is no need for anything bigger, it's a small system), I've been thinking about the security sides of the database connection. It'll be an internal system, so it's not that big of a deal, but I still want a fairly secure system. I've read something about using the IIS Metabase to connect to databases, what's that all about?

* Right now, the orders are registered in the database, and then nothing more happens with them. I'd like to have the orders be sent in an email to someone (the one who actually makes the order). Any ideas?

I'm sure I can think of more things that need to be tweaked and added, but this'll do for now.

[vass0922]

* Never heard of using the metabase, I've always preferred using a system DSN to connect with or just a simple connection string.

* EMail - http://www.emailarchitect.net/webapp...mtpservice.asp
Install the IIS SMTP service and you're good to go.

[Agent_Embryo]

Ok, here's the thing. I need help with this:

So once the order is complete, this is sent to the database(Not real db names):

table1(Ordertable): CustomerID, OrderID, Date
table2(OrderRow): OrderID, ProductID, Howmany

Now I'm trying to figure out how to use this information to grab the Product Name and Price and put together a nice order confirmation (that one can print out) AND send the same information in an email. What I can't figure out is how to grab the product name and price for each product ID that's listed.

[vass0922]

Umm where are you fetching the price from? I don't see that in the table.

Hmmm
There needs to be a Product table
You'll join the product table with table2 on ProductID

You have a customer table that lists all of thier orders
you have an orders table that lists all of the items on that order by product id
now you need a table that lists those product names/prices by product id

[Agent_Embryo]

Yeah there's a product table. Sorry for leaving that information out. There's a table with product id, name, info and price.

[vass0922]

assuming table3 is the product table
SELECT table2.OrderID, table3.ProductID, table3.ProductName, table3.Info, table3.Price, table2. HowMany
FROM table2
INNER JOIN table3
ON table2.ProductID = table3.ProductID
WHERE Table2.OrderId = 2432343

[Agent_Embryo]

Coolness, I have to try that out.

[Agent_Embryo]

Uhm...there has to be a table 1 too. Ihave the order divided into two tables:

table1: oderid, customerid, date
table2(what's in the order): orderid, productid, howmany

..still trying to figure this out...

Edit: Hm, I think I got it now vass...

[Agent_Embryo]

Now I just need to figure out how to pass the OrderID variable from one subroutine to another. Should I maybe have a global variable to store it in? How should I do it?

[vass0922]

Depends on how you're calling the subroutine.

Dim aVar
Dim bVar


do some stuff...
bVar = FunctionCall(aVar)
OtherFunction bVar

Private Function(psVar)
do some other stuff
psVar = psVar + 1
End Function

Private Sub(psOtherVar)
Response.Write psOtherVar
End Sub

Something like that?