Query MSSQL Database with VBScript

[digiman1134]

I am trying to query a MSSQl database in my vbscript, could anybody tell me what I'm doing wrong, if anything other than just having the servername wrong

Function GetReportButton_onclick()
SET MMS = CreateObject("ADODB.Connection")
MMS.ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=MMS;UID=abdreports;PWD=abdreports"
MMS.open 
SET MMS2 = Createobject("ADODB.command")
MMS2.ActiveConnection = MMS
MMS1.commandText = "SELECT FROM Products WHERE all"
MMS2.execute

SET MMS1 = Nothing
End Function

 

[ecompa]

Hi digiman1134,

with the OLEDB syntax that you are using, I think you need to replace UID and PWD by a the full name as shown below

"Provider=SQLOLEDB;Data Source=server;User Id=xxx;Password=yyy;"

The site

http://www.connectionstrings.com

can help you.
Also you could use a recordset object, but I don't think the way you did is wrong.

Set rstList = createobject("ADODB.Recordset")
rstList.Open strQuery, MMS

E.

 

[digiman1134]

this is the connectstring in VB6 how would this translate into vbscript

Set dbsMMS = OpenDatabase("MMSDowntime", dbDriverNoPrompt, True, "ODBC;DSN=MMSDowntime;UID=*****;PWD=*****;")

 

[Sheco]

What line is giving you an error?

Also I noticed that you are doing an object assignment here:
MMS2.ActiveConnection = MMS

You should use the Set keyword with objects.

Set MMS2.ActiveConnection = MMS

 

[digiman1134]

right now the only line giving me any real error is the Connectionstring line

MMS.ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=MMS;UID=abdreports;PWD=abdreports"

 

[gmmastros]

Try specifying the database on the server. Ex...

MMS.ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=MMS;Initial Catalog=MyDatabase;UID=abdreports;PWD=abdreports"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Sheco]

Are you saying it is giving you an error when you set the string?

This line should not give an error, even if you do something silly like:
MMS.ConnectionString = "April Fools!"

I guess unless your call to CreateObject failed.

 

[digiman1134]

my call to CREATOBJECT works, and as you said Sheco, when you put in "something silly" it doesn't give any errors, so I think I am just putting the server name and stuff into the incorrect syntax, could somebody please help me know which is the correct syntax, again, the VB is

Set dbsMMS = OpenDatabase("MMSDowntime", dbDriverNoPrompt, True, "ODBC;DSN=MMSDowntime;UID=*****;PWD=*****;")

 

[PHV]

"SELECT FROM Products WHERE all"
Are you sure your SQL is valid ?
I'd try this:
"SELECT * FROM Products"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sheco]

Maybe if you just did something like this:
"SELECT * FROM Products

 

[digiman1134]

okay, I solved that, next problem, how do I make it display on the screen?, how do I, maybe, put it into a dropdown form

 

[Sheco]

Wait, first you have to tell us what the problem was...

Not only are we curious, someone with the same problem as you might use the search tool on this site and find this thread... So it would be good for them to get the answer!



PS: It sounds like maybe you are making an ASP page? If so we have a special forum separate from the VBScript forumn for those. Here is a link for it: forum333 A lot of the questions in the plain VBScript forum are from Network Admin type people that use VBScript to help keep up their systems so ASP questions are better for the other forum.


PSS: The answer for your new question is to use Response.Write to create the proper HTML elements... in this case you would use a loop to move thru the recordset creating <option> elements for to go inside your <select></select> tags.

 

[digiman1134]

well, my problem was that I was creating a web page that would 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, sorry I posted in this forum, I just assumed since I was only programming in VB that I could post here, I think I can solve my next problem with a listbox, but any more advice would be wonderful

 

[digiman1134]

this is the code I have so far, where do I pull the value from?

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

 

[Sheco]

Well you have another problem that you have to fix first... You are executing your query as a command object but not returning it into any ADO.Recordset object.

Perhaps this is what you inteded to do with MM1... make it into a Recordset? It would be good if you had better names for your variables that indicated what they are.

 

[digiman1134]

can you show me how I would use the ADO.Recordset object?, I'm not sure how to use it

 

[PHV]

Search your local local drives for files named ado*.chm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tsuji]

>can you show me how I would use the ADO.Recordset object?, I'm not sure how to use it
The first post gave you the advice.