SQL query from webpage

I've been looking into this and I am not sure what path I need to be going down. I would like to take some basic SQL queries that I have and put them onto a webpage and just access them from there instead of using SQL Query Analyzer.

The database is SQL 2000 on a Win2k server. This place is littered with Windows servers, so I can easily host the page on an IIS server.

I am not sure if it's possible, but I would like to do the queries without having to use PERL or PHP. While I like both of them (and have built webpages using both), it would be nice to not have to install anything on the server to accomplish this. Not sure if there is something within ODBC or osql that can be used instead. I know that you can just query strings for osql and I also read some stuff about passing parameters to Stored Procedures. To many options was hurting my brain.

I do have a few queries that are static and will not need to change any parameters, but in most I will need to change a few parameters like username and database.

Sample queries that I would like to put on the webpage:

Use mydatabasenamehere
SELECT Date_Time, Port, Duration, URL
FROM INCOMING, User_Names
Where User_Names.User_ID = Incoming.User_ID and
      (User_Names.User_Full_Name = 'Jones, Tom')
order by Date_Time

In that query I regularly need to change the database being used, and the first and last name of the user. I figure these will be added to a form field or something in the page.

I think I have included all the information that I needed too. Please let me know if I forgot anything or I am not making any sense.


Thanks in advance for the help!


Ted

PS: I probably will not get a chance to check responses until morning if anyone gets to this tonight.

LVL 2
tabivAsked:
Who is Participating?
 
apirniaCommented:
You can use ASP and HTML to achive this.
Basicaly from your ASP page you make a connection to your SQL Server DB
Next you run the SQL Query and put the results in a Record Set
Then you loop through the Recordset and print all the data out
Next you close your connections
*******************************************

There are different ways of connecting to your DB. you can use ODBC/DSN, ADO, OLEDB.
I'll give you a DSN example.
To do this first create the DSN connection:
Start >>Control Pannel >> Admin Tools >> ODBC >> Select the secound TAB (System DSN) >> ADD
At this screen select "SQL Server" >> Finish then follow the steps.
The name that you set for this will be used in your ASP to make the connection:


This is the ASP:
**********************************************
<%

      set dbConnection = Server.CreateObject("ADODB.Connection")
      dbConnection.Open "dsn=MyTestingLocal"


sSQL = "SELECT pkMember FROM MyTable WHERE sLogin = '" & sLogin & "'"
                  set rs= dbConnection2.Execute(sSQL)
                  MyVariable = rs("pkMember ")
                  rs.close
                  set rs= nothing
Response.write MyVariable

So basically we are passing the login from the ASP page to the Query.
To start with you can just do a "Select * from mytable"




0
 
rdrunnerCommented:
Hello,

THere are several options here... You could install a reporting tool on the server and querry the reports.(Crystalreport or others) This will produce the cleanest output for you. Another way would be enabeling web querries on your DB. But this would allow everyone to querry everything on the server unless you set them up correctly. (You can restrict them to certain templates for example)

Example : http://itddev:3999/etime?sql=Select%20*%20from%20etime%20for%20xml%20auto&root=root

Take a look here...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/anch_SQLXML.asp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.