beginners question on ASP/Web databases

spooydermot

hi,

i want to set up a database on a web site(its using windows hosting)
i've used databases and programmed for them on a single user basis, but have never set one up for use on the web.
I'm assuming that I'll access it throgh ASP and can display the resultsing record sets from queries on a HTML page.

I've checked a few tutorials online and he ASP looks fairly easy, very similar to vb stuff.
My main problem is where to start? Do i simply up-load the database to a dircetory on the web space, how
do i send queries to this database and how do i display them on the webpage?

Sorry, that sounds like a complete tutorial I'm asking for, but I'm really just looking for a push in the right direction,all the websites i've looked on seem to jump straight into the ASP, but I'm more interested in the set up and a 'hello world' type query to work from. Any help greatly appreciated!

car

www.w3schools.com was a great help when I was trying to learn asp.
or www.stardeveloper.com also helped.

spooydermot

thanks Car, W3Schools is a fairly hany resource allright, I just asked a collegue though who outlined the kind of structure I'm looking for.

There is also a pertty good walkthrough on ASP for a mySQL db here:
http://216.147.98.109/support_aspdb.html

Baz_

in terms of where to put your database and how to access it once its there your web hosts would probably be able to answer that a bit better.

Seth Brundle

with many hosts there is often a private folder with write permissions outside of the main web folder. It is often called one of the following: _private, private, data or db whilst the main folder may be something like yourdomain, wwwroot, html or htdocs.

rsynnott

IF you can possibly avoid it, don't put Access databases on th'internet. It'll come back to haunt you...

spooydermot

rsynnott wrote:

IF you can possibly avoid it, don't put Access databases on th'internet. It'll come back to haunt you...

yeah its not my ideal solution, i'd rather try it with a mySQL db (cause I'd really like to stat using mySQL) but the problem is that the people who are going to be using this are versed in Access and want to continue using it.
How compatible are Access / mySQL in terms of porting data from one to another?

What kind of pitfalls am I going to face with Access on the web?

Seth Brundle

Access is fine for small low usage websites and as long as its in a relatively secure directory then it should be OK. I have had to use it for many sites and never had a problem, nor has it haunted me.
However, it is not as good as something like MySQL!

preilly79

spooydermot wrote:

yeah its not my ideal solution, i'd rather try it with a mySQL db (cause I'd really like to stat using mySQL) but the problem is that the people who are going to be using this are versed in Access and want to continue using it.
How compatible are Access / mySQL in terms of porting data from one to another?

What kind of pitfalls am I going to face with Access on the web?

there's a MySQL migration tool for access which is so simple to use it's unbelievable. i'm in the process of converting an ASP driven website with access in the backend into a PHP/MySQL site.

pitfalls:
not scalable (can only handle up to 2gb of data (i think))
can only handle something like 10 concurrent connections reliably (could be wrong but that's what springs to mind)
security is an issue as it's not a database designed to be stuck up for the whole world to see, though if you're good at security you can minimize your exposure.
the access ODBC driver can be a little bit of a pain in the ass sometimes.

you say that your end users have knowledge of access and that's the reason you have chosen it, but i'm confused: if you're sticking a web front-end on it they're not going to see the DB, and even if you're going to let them add tables and queries will they know how to create a dynamic page to display that new table/query etc?

Seth Brundle

spooydermot wrote:

yeah its not my ideal solution, i'd rather try it with a mySQL db (cause I'd really like to stat using mySQL) but the problem is that the people who are going to be using this are versed in Access and want to continue using it.
How compatible are Access / mySQL in terms of porting data from one to another?

What kind of pitfalls am I going to face with Access on the web?

1. is the DB going to be used directly by the staff or just to power the site? Are you creating an admin area for them to modify content?
2. Converting from access to MySQL would be easy enough. However, it may require much SQL & server side coding to be different as MySQL doesn't facilitate recordsets, etc.

S2000

I can recommend the following sites:

http://www.aspfree.com/
http://www.aspin.com/

Adrian

spooydermot

kbannon wrote:

1. is the DB going to be used directly by the staff or just to power the site? Are you creating an admin area for them to modify content?
2. Converting from access to MySQL would be easy enough. However, it may require much SQL & server side coding to be different as MySQL doesn't facilitate recordsets, etc.

its going to power certain sections of the site, but to be honest its not going to be under heavy usage, the queries will be fairly simple ones, andn the recordsets will not be very large.
Occassionally the staff will alter and delete information in the DB, but I'm thinking of putting together a VB app to make it a bit more user friendly, i.e. so they are not writing SQL code themselves.

Seth Brundle

If its web based then forget about using a VB app. Just create a pwd protected admin section to the site where they can alter data.

spooydermot

kbannon wrote:

If its web based then forget about using a VB app. Just create a pwd protected admin section to the site where they can alter data.

thats probably a more direct root, plus i need to gain more web programming experience anyways, a VB app would just be messy.


Thanks for the links S2000 I'm checking them out now

Eoin

I would recommend getting IIS installed on your machine so you can develop on it there before worrying about the configuration of the server etc.

In it's simplest form, to get up and running:

Create a DSN to the database in your control panel.

To get data from the database:

Dim objConn, objRS, sSQL
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open("Your Connection String here")
sSQL = "select FirstName, LastName from myTable order by LastName"
set objRS = objConn.Execute(sSQL)
If not(objRS.eof) Then
	Do while not(objRS.eof)
		Response.Write objRS(0) & " " objRS(1) & "<br/>" ' or objRS("FirstName") 
	objRS.MoveNext
	Loop
End If

To update the database:

Dim objConn, objRS, sSQL
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open("Your Connection String here")
sSQL = "insert into MyTable(Firstname, LastName)"
sSQL = sSQL & " values('John', 'Doe')"
objConn.Execute(sSQL)

rsynnott

kbannon wrote:

Access is fine for small low usage websites and as long as its in a relatively secure directory then it should be OK. I have had to use it for many sites and never had a problem, nor has it haunted me.

Until the day that the site grows beyond a certain size, or the database decides to silently, subtly corrupt itself...

If you want something that's (largely) compatible with Access, consider SQL Server.

spooydermot

thats all for you suggestions,links and code snippets - its really appreciated, hopefully ill sit down this weekend and make a good stab at it!

thanks again, any further tips appreciated!

mewso

One more tip. Consider using Asp.Net. If you are just starting then it might be worth learning the current version of vb.net as it is somewhat different to what you will learn if you try the vbscript route.

rsynnott

musician wrote:

One more tip. Consider using Asp.Net. If you are just starting then it might be worth learning the current version of vb.net as it is somewhat different to what you will learn if you try the vbscript route.

Yep, should have thought of that.