Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MSSQL Databse query with vbscript

Status
Not open for further replies.
digiman1134

digiman1134

Programmer
Nov 23, 2004
19
US
My problem is that I am creating a web page that should query a MSSQL database table, and then after querying that table would take only one of the columns and put it into a drop down list form so that it could be chosen and plugged into a second script where it would be used to generate a report based on the criteria chosen

this is what I have so far, any ideas of where I should go from here?

Code: 
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>



<script type= "text/vbscript">
DIM MMS
DIM MMS1
DIM MMS2


Function GetReportButton_onclick()
SET MMS = CreateObject("ADODB.Connection")
MMS.ConnectionString = "Provider=MSDASQL.1;Password=reports;Persist Security Info=True;User ID=reports;Data Source=Aberdeen Live Database;Extended Properties=DSN=Aberdeen Live Database;Description=Aberdeen Live Protean Database;UID=reports;APP=Microsoft Development Environment;WSID=IGNITION;DATABASE=ot30;User Id=reports;PASSWORD=reports', 'SELECT ResourceUK FROM dbo.fdBasResc WHERE (ResourceUK LIKE \'bt%\') ORDER BY ResourceUK';"
MMS.open 
SET MMS2 = Createobject("ADODB.command")
SET MMS2.ActiveConnection = MMS
SET MMS2.commandText = "SELECT FROM Products WHERE all"
MMS2.execute
Set MMS1 = CreateObject("ADODB.RECORDSET")
MMS1.Open "ResourceUK", MMS
End Function
</script>



<input LANGUAGE="text/vbscript" onclick="id=GetReportButton" name= "GetReportButton" type= "button" value=" Get Report " onclick= "return GetReportButton_onclick()" tabIndex=2 style="HEIGHT: 41px; WIDTH: 128px"> 




</body>

</html>
 
Sort by date Sort by votes
You don't need to use the Set keyword for the .CommandText property of the ADO Command object because the datatype is just a string rather than an object.

It should be:
MMS2.commandText = "SELECT FROM Products WHERE all"

Also the SQL looks fishy.
 
Upvote 0 Downvote
not to mention trying to client-side scripting with server-side scripting.
 
Upvote 0 Downvote
It is client side, see how CreateObject() is used isntead of Server.CreateObject()

But you are correct, the best practice is not to do data access from the client side. It will work if all users are on the same network or, for a public site, it can work if you open your database to the live internet, but that is a bad idea.

It really should be on the server such that the web server will run the script and determine what fields should be in the dropdown and then the script will create the HTML to send to the client but with these fields already pre-generated... so that the client only receives text.
 
Upvote 0 Downvote
Since the SQL that you pass to the Execute() method of the ADO Command object returns a Recordset then you could use MM1 like this:
Set MM1 = MMS2.execute

That would allow you to get rid of these lines:
Set MMS1 = CreateObject("ADODB.RECORDSET")
MMS1.Open "ResourceUK", MMS


On the other hand, you could get rid of the Command object and just pass the SQL to the Open() method of the recordset.
 
Upvote 0 Downvote
yeah i looked right over that. but i knew that it would be best to just put it all server side.
 
Upvote 0 Downvote
it is being hosted on an intranet and doesn't need to be serverside, this is going to be used to generate reports that need to be accessible to everyone on the network, so the script needs to be clientside, built into the website we use to control the entire plant
 
Upvote 0 Downvote
It is just as accessable to people if it is on the client as if it is on the server.

The way it works:
1. Client requests web page from server.
2. Server runs script (logic, database access, etc) to build HTML page
3. Server responds to client by sending by HTML page it created in step 2.

This is much better than the alternative. If the data access is done on the server you do not have to expose the details of your database security to every potential user.
 
Upvote 0 Downvote
I don't know how to script serverside, if somebody would be so kind as to point me towards a tutorial, I would be able to build this script much better
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
558
leifoet
leifoet
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
411
JScannell
JScannell
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
905
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
357
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
548
xwb
xwb

Part and Inventory Search

Sponsor

Back
Top