Solved

SQL query from webpage

Posted on 2004-10-28
337 Views
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
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.

0
Question by:tabiv
    2 Comments
     
    LVL 9

    Accepted Solution

    by:
    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
     
    LVL 11

    Assisted Solution

    by:rdrunner
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: Foundations of Front-End Development

    Jump-start a lucrative career in front-end web development, with zero previous coding experience required. This course covers the basic programming concepts and languages required for creating engaging websites from scratch.

    Suggested Solutions

    I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    884 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now