Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SQL query from webpage

Posted on 2004-10-28
Medium Priority
Last Modified: 2012-06-27
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
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!


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

Question by:tabiv
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

apirnia earned 1000 total points
ID: 12440798
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 ")
                  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"

LVL 11

Assisted Solution

rdrunner earned 1000 total points
ID: 12442025

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...


Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question