Website front end on SQL database

[PiMMeL]

Hi,

I am fairly new to SQL but a friend asked me if I could research this. Since there are always helpfull people in this group I thought I would try it here first.

All I am looking for is a start on how to go about this. Is there some software package 3rd party or maybe something build into SQL Dev Server that would allow me to build a webinterface on the SQL database?

Thank you so much already,

Pim

 

[plsh]

What are you wanting to do? Display records from specific tables? Build a site based on values?

Cheers

 

[PiMMeL]

Hi,

They have a SQL database with a frontend on it. I like to create a website with similar fileds as the front end where they can enter their data. They enter workorders, jobs, payroll etc in the program/database. Right now they would like to have laptops in the office where they can use them with the software and be updated ( this I got working) Now they like to be able to add entries to the database when they are offsite and then when they come back in they want to update the main database but I am afraid that more then 1 person will enter workorders that get a number attached to it so when they come back into the office and update the main database it will create problems i think if 2 people created a workorder with the same number. So I thought a website would be perfect. Life and direct updates.

 

[Narizz28]

We use web interfaces for almost all of our database dependant applications. If you do it like we do, you'll want to learn about the ADO object and it's methods. Ado together with XML "data islands" (made up term) can be used to effectively enter and view data from a database on a web page. ADO and XML code snippets below.

THE FOLLOWING CODE IS NOT INTENDED TO BE A COMPLETE EXAMPLE OF ALL THE NEEDED CODE, AS LINES HAVE BEEN LEFT OUT. IT IS INTENDED ONLY AS AN EXAMPLE OF THE WAY THE OBJECTS ARE USED!

First, insatniate the ADO object:

<%@ Language=VBScript %>
<%
dim db
dim rs

set db = createobject("adodb.connection")
set rs = createobject("ADODB.Recordset")

From there, it's just a matter of doing the SQL statements against the instantiated objects. Here's the XML data island for record set outputting:

rs.Open s, db
if rs.EOF then

   redim xa(5,0)
else
   xa = rs.GetRows
end if
rs.Close
db.Close
set rs=nothing
set db=nothing

s = "<?xml version='1.0' encoding='ISO-8859-1'?>"
s = s & "<rooty>"
for i = 0 to ubound(xa,2)

s = s & "<entry>"
s = s & "<Column001>" & xa(0,i) & "</Column001>"
s = s & "<Column002>" & [I]text-format-function[/I](xa(1,i)) & "</Column002>"
s = s & "<Column003>" & xa(2,i) & "</Column003>"
s = s & "<Column004>" & xa(3,i) & "</Column004>"
s = s & "<Column005>" & xa(4,i) & "</Column005>"
s = s & "<Column006>" & xa(5,i) & "</Column006>"
s = s & "</entry>"
next
s = s & "</rooty>"
Response.Write s

In the code above, s is the SQL string. Also, what wasn't posted above is an open statement. In the example above, we use a DSN on the web server and open it with db.Open "dsn=dsnname" and the SQL query is run with a rs.Open s, db where s is the select statement.

AGAIN, LET ME EMPHASIZE THAT THIS CODE IS JUST AN EXAMPLE FROM ONE ASP PAGE FROM A COMPLETE APPLICATION. THERE ARE A LOT OF OTHER FACTORS THAT INFLUENCE HOW THE DATA INTERACTS FROM THE WEB SERVER TO THE DATABASE NOT DESCRIBED HERE.

I hope this encourages you to investigate further.

 

[PiMMeL]

Hi Narizz28,

I think I see stars after I read your reply. Puts me back in my place when I thought I knew some stuff about computers. But I guess we can't know everything.
Good thing is that it DOES encourage me to go on with my quest.

Does this way allow one to publish the webinterface on the internet? I need this to be accesses remote in some cases.

Can you give some pointers on good books or links to read up on this subject?

I found some info on the microsoft website.

Thank you,

Pim

 

[Narizz28]

Between MSDN and Google, I've been able to find the answer to just about anything I've looked for. MSDN for the methods of objects, and google for the tought stuff ;-)

And yes, If you set the web app up as an app in IIS, and make sure that the auth accounts have access to the DB, it should work just fine. AKA, force auth for the app, or open the DB up to the account used for anonymous access (just make sure the DB server isn't available to the outside network if you do this, and put the safegaurds in place for malformed URL's).