question

Eightces avatar image
Eightces asked

SQL Web Interface

Hi all.. I am a newbie to SQL Server. I have built a database in SQL 2005. Now few items are remaining to store in the database. But my company is asking me to build WebInterface so that they can edit, delete, update, download files etc. Is Reporting Service is used for this purpose? Or Can anyone give me brief idea of How to and Where to start?....I am in a trouble...THank you in advance They want me to build like FrontEnd so that they can access it besides looking into SQL Database and manages it....
sql-server-2005sqlwebinterface
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nil avatar image
nil answered
Use Query Displayer to run this piece of code is a neat way for you to access your data via the web. One word of warning - anyone stumbling across this page could delete your entire database, so make sure you put it behind a secure password. The script is ready to run, you'll need to add your odbc connection string, that's all. <%@ Language=VBScript %> <% 'set a high timeout for long queries server.scripttimeout=100000000 dim sql sql=Request.form("query") Call showForm() 'store the query in a cookie (useful if using the same query on multiple occasions) If sql<>"" then Response.Cookies ("query")("query_screen") = sql Response.Cookies ("query").Expires = DATE + 365 Call ShowStructure() End If %>

<% Sub ShowStructure() dim objConn,objRs dim fld,totrec set objConn=server.CreateObject("ADODB.Connection") set objRs=server.CreateObject("ADODB.Recordset") 'edit this line with your odbc connection details (can be access or SQL server) objConn.Open "dsn=your_database_name" If lcase(left(trim(sql),6))<>"select" then objConn.Execute sql,totrec 'show the number of records altered by any update or insert Response.Write totrec & " records are affected!" exit sub End If set objRs=objConn.Execute(sql) If err.number=0 then Response.Write " " for each fld in objRs.Fields Response.Write " " & trim(fld.name) & "" next Response.Write " " While not objRs.EOF Response.Write " " for each fld in objRs.Fields Response.Write " " & fld.value & "" next Response.Write " " objRs.Movenext Wend Response.Write " " Response.Write " " Else Exit sub End If End Sub Sub ShowForm() %> <% if request.form("query")="" then Response.Write Request.Cookies("query")("query_screen") else response.write request.form("query") end if %> <% End Sub %> Thanks Nilanka
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm sorry, but I did -1 this. There are huge problems with this code, not least of which is the danger of it, as you mention yourself. However, things like: * Lack of any action taken if any error is encountered (not even displaying the error to the user) * Detection of SELECT wouldn't work if anything is at the start (e.g. comment, CTE definition) * Lack of differentiation between NULL and empty field * Massive timeout - easily could bring a server to it's knees So yeah, sorry, but -1.
4 Likes 4 ·
mmm, -1 from me too. far too dangerous to be recommended to someone who has explained they have no experience.
2 Likes 2 ·
It made me smile, so many thanks to Nilanka. I'm afraid I had to vote it down, but just as a warning to anyone who stumbles across the answer without realizing...
2 Likes 2 ·
@Magnus Ahlkvist Perhaps, but then again, Nilanka is a brand new user, who already received a not so warm welcome to the site.
1 Like 1 ·
@Oleg - the voting isnt anything but a recommendation of whether you think an answer will help the other person to solve their problem and learn about the area that they are asking about. With this answer voting it up as such is not possible, there are too many risks in implementing it to a novice to leave it without some sort of peer opinion and simply adding a comment may not be clear enough so I support all those that down-voted. I respect your explanation and if you choose not to then that is fine. @Nil shouldnt feel we are being rude or offensive, we simply feel the suggestion is not suitable as an answer to this question. Hopefully there will be other questions that can be answered and we will all be busy upvoting them.

@Nil, you do have the option as @Oleg suggests to delete your answer and achieve a badge that noone on this forum has yet earned!
1 Like 1 ·
Show more comments
Matt Whitfield avatar image
Matt Whitfield answered
If you have relatively little experience, you could look at something like [IronSpeed][1]. There are various tools like that - allowing a website to be generated based on meta-data, rather than hand coding an object layer etc. It would be less efficient than a hand-coded solution, certainly, but would be a way to get something up and running quickly. So you really have two options 1. Use that, be happy, buy it, use it long term 2. Use something along the same lines, look at how the code works, implement something yourself based on that. If you have any more specific questions, please do ask. [1]: http://www.ironspeed.com/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
It all depends upon what you are storing in the database, but the normal way would be to design a data access layer (DAL) within the database. You would create stored procedures, view, functions etc. to do the data manipulation. It is not normally a good idea to give the end user access to the underlying tables (security being the first concern). You abstract the data storage from the data access so that changes can be made to tables without it affecting your users/programs directly. This may be overkill if you have 2 tables and are storing a bit of information, much more than that and a DAL can be a huge help. You can then build a front-end in a language you are familiar with. For SQL Server and Web access, I would think something like ASP.Net would do the job. If i recall correctly, there is a PHP driver for SQL server, so if you are more familiar with PHP that would be a possibility.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Several things...
Is Reporting Service is used for this purpose
No. Reporting Services is for displaying data in report-style formats. You can use it to do updates, but it involves a fair amount of bodging around, and is not really recommended.
Or Can anyone give me brief idea of How to and Where to start?
Others have answered this already, but I would suggest learning ASP.NET. It's not going to be a quick fix for you, though.
I am in a trouble...
In trouble? Why? For not having the experience / skills to do something that has not yet ever been part of your job? If that's the case, you might want to look around for something else...
THank you in advance
You're welcome. You know where to find us...
They want me to build like FrontEnd so that they can access it besides looking into SQL Database and manages it....
They want to be able to manage the server via the same interface? There's a whole new world of pain opening up right there. Top tip: get some backups going, right now. Schedule them to run regularly. Make sure you know how to recover from them when something goes wrong, because, by the sound of it, it will. Full backup every day, transaction log backups regularly - hourly, every ten minutes, whatever floats your boat. Keep 'em safe. Keep yourself safe. Good luck!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
If you don't have any development experience I think you have to explain to them that it will take a fair amount of time to learn how this is done. If it was easy, everyone would be doing it. They need to consider whether they should call in an experienced developer to get this going. That would probably be cheaper in the short term than paying you to learn. But in the longer term it might be worth their while to invest in training you - *if you want to learn this stuff*. If you really want to have a go, you MUST make them understand it's an experiment, they can't expect miracles, and you can't promise what you'll be able to deliver. Another tool you might want to check out is Microsoft's WebMatrix. I don't know it myself but I'm hearing some pretty hardcore developers say that it's fairly quick and easy and that they like the way it does things. Before they can get a system from anyone, though, they will have to define what they mean by "edit, delete, update, download files etc.".
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered
For an admin front-end that gives you something approaching SSMS on a website, nothing beats [MyLittleAdmin][1] [1]: http://www.mylittleadmin.com/en/welcome.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wow, never seen that before. Downloaded, installed and working in < 10 mins. Thats a tool for keeping. Thanks Phil.
0 Likes 0 ·
+1, linkised your link, hope you don't mind
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.